(六)事务的提交与回滚极死锁检测、处理和预防

事务的提交与回滚极死锁检测、处理和预防

(一)MySQL InnoDB事务模型

(二)MySQL InnoDB锁模型

(三)MySQL InnoDB非锁定一致性读与锁定读

(四)MySQL InnoDB锁类型及幻象读问题

(五)MySQL InnoDB中各类语句加锁方式

(六)事务的提交与回滚极死锁检测、处理和预防

事务的提交与回滚

默认情况下,MySQL开启自动提交,每条语句执行完成且运行无误的情况下会被自动提交。若语句发生了错误,提交或回滚与具体的SQL有关。另外,还有一些语句会隐式的结束一个事务,就好比在执行这些SQL前执行了COMMIT语句。

若发生了Table is full错误 InnoDB会回滚语句。

死锁导致InnoDB回滚整个事务。若单是发生了 lock wait timeoutInnoDB仅会回滚事务中等待锁并发生超时的SQL语句。若想在此种情况下回滚整个事务,可通过同时开启 --innodb_rollback_on_timeout选项。死锁和锁等待在繁忙的服务器中很常见。应用需妥善处理这些情况,尽可能在较少的记录上持有锁,并且锁定的时间尽可能的短。若是通过START TRANSACTIONBEGIN明确开启事务,则死锁或者锁等待超时导致的回滚并不会关闭当前事务,后续的SQL语句仍会成为当前事务的一部分,除非使用COMMITROLLBACK或者其他隐式提交事务的语句。

若没有指定IGNORE则 duplicate-key error会导致SQL语句回滚。row too long error也会导致SQL语句回滚。其他的错误大多由MySQL Server层而非InnoDB引擎层检测,会回滚SQL语句。单挑SQL语句回滚不会释放事务持有的锁。

一些会隐式的结束事务的SQL,分为几大类:定义或修改数据库对象的DDL语句;隐式的使用或者修改mysql库中的表的语句;事务控制与锁定语句;数据导入语句;数据库管理语句;复制控制语句等。具体可参考官方手册:http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

InnoDB中死锁自动被检测出,并选择代价较小的事务进行回滚以打破死锁。事务完全回滚后其保持的锁被全部释放,若是仅有单条SQL由于错误发生了回滚则语句保持的锁可能不会被释放,因为InnoDB中不保存哪条语句持有哪些锁的信息。若事务中的select调用了存储函数,函数中的SQL执行失败,则该语句被回滚。

死锁是事务型应用中的典型问题,不可消除只能尽可能避免。死锁并不危险但频繁出现就有问题了。应用中应做好出现死锁导致事务回滚后的后续处理逻辑。

如何预防和处理死锁?

对于DBA,可以通过SHOW ENGINE INNODB STATUS 查看最近的死锁信息,以辅助调整应用。另外,若想看到更加详细的信息可开启innodb_print_all_deadlocks 配置,这样可以在error log中看到所有的死锁信息而非最近的一个(调试结束后记得关闭)。
应用中需要有死锁发生后导致事务回滚的处理逻辑。

尽量保持事务短小精悍以避免冲突。

尽早提交事务,不要保持一个有长期未关闭事务的交互式mysql session

若非必要则不使用锁定读,若要选择使用锁定读则可选择较低的事务隔离级别如 READ COMMITTED

若在同一事务中修改多个表或者修改同一表中的不同行,则每次尽量按一致的顺序进行操作。

为表添加合理的索引,并用explain确认SQL能否使用到合适的索引。

特殊情况下可以使用表锁。

通过设置辅助表来序列化事务,表中只包含一行,事务在访问其他表前必须更新该表中的行,这样可保证事务的串行执行,避免死锁。

不好的事务使用习惯

· 在循环中提交事务

· 使用自动提交

· 自动回滚

· 长事务

展开阅读全文

没有更多推荐了,返回首页