14.3.5.3 How to Minimize and Handle Deadlocks 如何减少和处理死锁

这个章节建立关于死锁的概念信息,它解释如何组织数据库操作来减少死锁和随后的错误处理:

Deadlocks 是一个金典问题在事务数据库, 但是它们并不危险除非它们是如此拼单

以至于你根本不能运行某些事务

你必须写你的应用 以便它们总是准备重新执行一个事务 如果被回滚

InnoDB 使用自动row-level locking, 你可以得到死锁甚至在事务里,

插入或者删除单行记录。

那是因为那些操作不是真正的"原子的", 它们自动设置锁在(可能的多个)插入或者删除的index records。

你能处理 deadlocks 和降低其发生的可能性 使用下面的技术:

1.在任何时间, 执行SHOW ENGINE INNODB STATUS 命令来确定最近的死锁信息。

这个有助于你调整你的应用避免死锁:

2.如果频繁的死锁警告,搜集更多的扩展调试信息通过启用 innodb_print_all_deadlocks  配置选项

信息关于每个死锁信息, 不只是最后一个, 是记录在MySQL error log. 

关闭这个选项当你完成调试时:

总是准备重新执行一个事务如果由于死锁失败,死锁并不危险,只是猜尝试一次

3. 保持事务尽可能的小和短,使它们不容易冲突

4.  立即提交事务 在做了一组相关的改变让它们不易冲突。

特别的, 不要让一个交互的mysql session 打开很长一段时间,没有提交事务

5. 如果你使用locking reads(SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE),

尝试使用一个较低的隔离级别 比如READ COMMITTED

6. 当在一个事务里修改多个表, 或者相同表的不同结果集,

做这些操作每次按一致的顺序。 然后事务形成良好的定义的队列,也不会死锁。

比如, 组织表操作到函数里,或者调用存储过程,相比编码很多类似的插入,更新和删除语句的类似序列在不同

的地方。

7.  在你的表上增加精心选项的索引,然后你的查询需要扫描更少的index records从而设置更少的锁,

使用EXPLAIN SELECT to 确定哪个索引MySQL 服务器视为最合适你查询的索引

8.使用更少的锁,你可以容许一个SELECT 从老的快照返回谁,

不需要增加子句 FOR UPDATE or LOCK IN SHARE MODE。

使用READ COMMITTED隔离级别在这里是更好的,因为每个一致性读在相同的事务读取它自己最新鲜的快照。

9.如果没有其他帮助,序列化你的事务 使用表级锁。

正确的方式是使用LOCK TABLES 在你的事务表上,比如InnoDB 表,

开始事务设置 autocommit = 0 不是START TRANSACTION （关闭自动提交）

不要调用unlock tables 直到你显示的提交事务。

比如,如果你需要写一个表t1,从表t2读取,你可以这么做:

SET autocommit=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

... do something with tables t1 and t2 here ...

COMMIT;

UNLOCK TABLES;

Table-level locks 阻止并发更新表, 避免死锁代价是更少的响应对于一个繁忙的系统