当你碰到了MySQL中的死锁，你了解这些机制吗？

MySQL死锁怎么来的？

当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现“死锁”。

总结一下生产死锁的4个条件：

  1. 两个或者两个以上事务

  2. 每个事务都已经持有锁并且申请新的锁

  3. 锁资源同时只能被同一个事务持有或者不兼容

  4. 事务之间因为持有锁和申请锁导致彼此循环等待

举个例子：用户表，id是主键

事务1

事务2

begin;update user set username = '旭阳' where id = 10;

begin;update user set username = 'alvin' where id = 20;

update user set username = 'alvin' where id = 20;

update user set username = '旭阳' where id = 10;

  • 事务1优先获得了id=10的记录锁

  • 事务2其次获得了id=20的记录锁

  • 然后事务1现在请求id=20的记录锁资源，发现已经被占用了，阻塞等待其他事务释放

  • 同理，事务2请求id=10的记录锁资源，却被事务1占用了

  • 由于事务1和事务2都没有提交，所以他们不会释放锁，导致死锁，得到下面的报错：

​死锁的关键在于：两个（或以上）的事务加锁的顺序不一致。如果上面的事务2也是先锁id=10的记录，再锁id=20的记录，就不会出现死锁。

但是这很难达成，因为我们不同的业务，会有不同的逻辑处理，肯定会出现加锁顺序不一样的情况，特别是在高并发的场景下。

MySQL遇到死锁怎么办？

MySQL发生了死锁，总不能一直让事务现场等在那里，多蠢啊，那它采用什么策略解决死锁问题呢？

  1. 等待，直到超时

即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。innoDB存储引擎中，通过innodb_lock_wait_timeout配置设置超时时间，默认50秒。

优点：

  • 简单有效

缺点：

  • 如果真的发生死锁，等待50s这么久一般难以接受，设置太短比如1s, 2s，有可能误触发正常的锁等待。

  1. 使用死锁检测处理死锁程序

由于上面等待的方式太过被动，那有没有可以主动检测出是否存在死锁的方式呢？

MySQL提供了一种主动的死锁检测机制, 相当于将锁等待的信息保存下来，绘制出一个等待的有向图，如下：

​如果检测到存在环，说明有死锁，那么InnoDB存储引擎会选择回滚undo量最小的事务，让其他事务继续执行。

这种机制通过配置 innodb_deadlock_detect控制，默认打开。

​优点：

  • 主动检测，时效性低

缺点： 并发越高，图形越庞大，检测越耗时。每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作时间复杂度是O(n)。如果100个并发线程同时更新同一行，意味着要检测100*100=1万次，1万个线程就会有1千万次检测。

上面两种方式并不冲突，默认情况下，MySQL都处于开启状态。

死锁日志怎么看？

虽然MySQL提供了默认的死锁处理机制，更重要的还是我们需要去从业务逻辑代码层面分析发生死锁的根本原因，然后去合理的规避死锁。那为了能够快速定位到死锁相关的业务代码，我们还需要会分析死锁日志。

死锁日志在哪呢？

  1. 通过show engine innodb status命令可以查看最近一次发生死锁的日志。

  2. 通过命令set global innodb_print_all_deadlocks = 1;开启死锁日志记录到MySQL错误日志中，默认情况是不开启的。

​死锁例子超详细解读

我们以上面的用户表为例，解读下死锁日志。

执行命令show engine innodb status获取死锁日志如下图所示：

​LATEST DETECTED DEADLOCK表示InnoDB引擎上次发生的死锁。死锁是至少需要有两个事务参与的，所以在其内部包含了发生死锁的具体两个事务。

  1. 第一个事务 (1) TRANSACTION

  • TRANSACTION 2415258889, ACTIVE 31 sec starting index read

说明： 事务号为2415258889，活跃 31秒，starting index read 表示事务状态为根据索引读取数据。注意删除和更新InnoDB内部也是要进行读操作的。

  • mysql tables in use 1, locked 1

说明： 说明当前的事务使用一个表，并且有一个表锁，DML操作会对表施加意向锁，本例是IX。

  • LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)

说明： LOCK WAIT 表示正在等待锁，3 lock struct(s)表示 trx->trx_locks 锁链表的长度为3，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。2 row lock(s)表示当前事务持有的行记录锁/ gap 锁的个数。

  • MySQL thread id 4576535

说明： 表示执行事务的线程id是4576535，即 show processlist; 展示的 ID。

  • update user set username = 'alvin' where id = 20

说明： 表示事务执行正在阻塞的SQL，注意不会显示事务全部执行的SQL

  • (1) WAITING FOR THIS LOCK TO BE GRANTED:

​说明： 这行信息表示等待的锁是一个record lock，空间id是221580，页编号为3，大概位置在页的80位处，锁发生在表user的主键索引上，是一个X锁，但是不是gap lock，waiting表示正在等待锁

  1. 第二个事务 (2) TRANSACTION

第二个事务基本上和第一个事务分析是一样的，我们挑选重要的和不一样的点：

  • (2) HOLDS THE LOCK(S)

​说明： 表明了事务二持有的锁正是事务1想要获取的锁。

  1. WE ROLL BACK TRANSACTION (2)

由于发生了死锁，最终回滚事务2。

我们可以借助死锁日志分析死锁发生可能的原因，但是由于死锁日志只记录发生阻塞的SQL，所以仅仅根据日志也很难以分析死锁的问题的根本原因，但是可以有一定的帮助。

怎么尽量避免死锁？

我们在平时的开发设计有没有一些准则可以帮助我们避免死锁发生？

  1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。

  2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。

  3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。

  4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。

  5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。

  6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性。

  7. 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。

总结一下： 也就是在业务允许的情况下，尽量缩短一个事务持有锁的时间、减小锁的粒度以及锁的数量。

总结

本文讲解了MySQL中死锁发生的原因，以及如何通过日志排查死锁，最后给了一些平时开发过程中避免死锁的建议。虽然MySQL会自动解除死锁，但是这个死锁问题以后绝对会再次出现，一定要记住去排除业务SQL的执行逻辑，找到产生死锁的业务，然后调整业务SQL的执行顺序，这样才能从根源上避免死锁产生。

如果本文对你有帮助的话，请留下一个赞吧