MySQL死锁

• Z时代
• 2024-01-10
• 分类：综合

https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html

什么是mysql的死锁？

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. 

简单来说可以提炼出2个词：环路等待（each holds a lock that the other needs）和不可剥夺（neither ever release the locks it holds）。

其实广泛意义上死锁的四个必要条件也可以直接简化为上述两个条件，剩下的互斥和请求保持条件只是两个众所周知的补充。

一、一个简单的死锁示例：

会话A：

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

会话B：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;

此时会话B会被阻塞（直到锁请求超时）。

此时会话A继续执行：

DELETE FROM t WHERE i = 1;

会话B会被立马rollback，因为产生了死锁，最近的死锁信息可以通过show engine innodb statusG看到。

打开innodb_print_all_deadlocks参数之后，死锁信息还会在error日志里打印。鉴于本例过于简单就不占用篇幅分析死锁信息了。

set @@global.innodb_print_all_deadlocks=on;

innodb会选择耗费资源较少的事务进行回滚（取决于DML涉及的行数和size）。

二、一个实际的死锁示例：

error日志里显示的死锁日志为：

2019-12-18 08:38:29 7f198b208700InnoDB: transactions deadlock detected, dumping detailed information.
2019-12-18 08:38:29 7f198b208700
*** (1) TRANSACTION:
TRANSACTION 209262583957, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 129183854, OS thread handle 0x7f1aeae7a700, query id 68320628504 <服务器A信息> updating
update  tb_authorize_info set account_balance=account_balance-  100.00 
     where (SELECT a.account_balance from 
(select account_balance from tb_authorize_info a where appId =  "49E5BD695F853DC3" )a)  -  100.00 > 0 
 and appId = "49E5BD695F853DC3"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1845 page no 4 n bits 96 index `PRIMARY` of table `jilicard`.`tb_authorize_info` trx id 209262583957 lock_mode X locks rec but not gap waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
......
*** (2) TRANSACTION:
TRANSACTION 209262584968, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 129183879, OS thread handle 0x7f198b208700, query id 68320632234 <服务器B信息> updating
update  tb_authorize_info set account_balance=account_balance-  100.00 
     where (SELECT a.account_balance from 
(select account_balance from tb_authorize_info a where appId =  "49E5BD695F853DC3" )a)  -  100.00 > 0 
 and appId = "49E5BD695F853DC3"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1845 page no 4 n bits 96 index `PRIMARY` of table `jilicard`.`tb_authorize_info` trx id 209262584968 lock mode S locks rec but not gap
Record lock, heap no 18 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
......
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1845 page no 4 n bits 96 index `PRIMARY` of table `jilicard`.`tb_authorize_info` trx id 209262584968 lock_mode X locks rec but not gap waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
......
*** WE ROLL BACK TRANSACTION (2)

这个死锁属于简单的死锁，由于网络或其他延迟导致应用请求发送到了2台负载均衡的应用服务器，两个应用程序同时请求数据库执行SQL，两者都根据where条件先获取到了S锁，然后准备升级为X锁以便更新，但是各自被对方的S锁阻塞，因此形成死锁，不过死锁很快被mysql杀掉，事务1正常执行完毕，事务二回滚，前台业务除了一点点延迟基本没啥影响。

三、stackoverflow上另一个死锁：

有人在stackoverflow上发了一个死锁的信息，尝试直接解析此类信息对分析高并发下的SQL卡慢会有个帮助因此尝试自己解析，由于时间久远现在我已经找不到相关链接也懒得去找了。

LATEST DETECTED DEADLOCK
------------------------
130409  0:40:58
*** (1) TRANSACTION:
TRANSACTION 3D61D41F, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 43 lock struct(s), heap size 6960, 358 row lock(s), undo log entries 43
MySQL thread id 17241690, OS thread handle 0x7ffd3469a700, query id 860259163 localhost root update
INSERT INTO `notification` (`other_grouped_notifications_count`, `user_id`, `notifiable_type`, `action_item`, `action_id`, `created_at`, `status`, `updated_at`) 
VALUES (0, 4442, "MATCH", "MATCH", 224716, 1365448255, 1, 1365448255)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 272207 n bits 1272 index `user_id` of table `notification` trx id 3D61D41F lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 69 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000115b; asc    [;;
 1: len 4; hex 0005e0bb; asc     ;;
-- 事务1插入数据后要更新插入user_id=4442的索引，于是想要在包含(lower_bound,4443]的范围上加insert intention lock，但是被阻塞，推断这个范围上已经有了其他非insert intention lock的行锁
-- 与此同时事务1已经在等待辅助索引的插入意向锁，说明主键的插入已经完成，此时在4442对应的主键上持有record锁。
*** (2) TRANSACTION:
TRANSACTION 3D61C472, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 17266704, OS thread handle 0x7ffd34b01700, query id 860250374 localhost root Updating
UPDATE `notification` SET `status`=0 WHERE user_id = 4443 and status=1
*** (2) HOLDS THE LOCK(S):
-- 事务2的update语句要更新user_id=4443的记录，因此在查找阶段在对应的user_id索引的key=4443的位置添加了X模式的next-key行锁
RECORD LOCKS space id 0 page no 272207 n bits 1272 index `user_id` of table `notification` trx id 3D61C472 lock_mode X
Record lock, heap no 69 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000115b; asc    [;;
 1: len 4; hex 0005e0bb; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-- 当事务2尝试更新主键数据时要获取user_id=4443对应的主键的next-key行锁，但是发现4442对应的主键已经被事务1加了record lock，因此被阻塞
RECORD LOCKS space id 0 page no 261029 n bits 248 index `PRIMARY` of table `notification` trx id 3D61C472 lock_mode X locks rec but not gap waiting
Record lock, heap no 161 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 0005e0bb; asc     ;;
 1: len 6; hex 00000c75178f; asc    u  ;;
 2: len 7; hex 480007c00c1d10; asc H      ;;
 3: len 4; hex 8000115b; asc    [;;
 4: len 8; hex 5245474953544552; asc REGISTER;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 4; hex d117dd91; asc     ;;
 9: len 4; hex d117dd91; asc     ;;
 10: len 1; hex 80; asc  ;;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 4; hex 80000000; asc     ;;
*** WE ROLL BACK TRANSACTION (2)

这个死锁的出现也很容易理解，事务1先插入记录后获取了4442对应的主键的行锁，在插入辅助索引时被事务2 update语句的next-key行锁阻塞导致插入意向锁获取失败，而事务2的update获取了索引的next-key行锁后尝试更新主键(即在主键上加next-key锁)却被事务1的行锁阻塞。

两个事务都不能放弃自己已有的资源，都请求与对方不兼容的锁，不可剥夺且形成环路等待因此死锁。

四、如何避免死锁？

其实官网有一篇完整的介绍：https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html

但是内容有点多，我还是习惯用几句话总结下：

1、尽可能优化SQL的查询性能使得事务尽可能的短小。

2、如果不介意幻读可以使用read committed隔离级别以禁止范围锁。

3、如果前两者都做不到或者SQL优化的空间比较小，那么尽量分表分库，通过增加资源（或者叫分散资源）减少资源冲突的几率。

五、总结：

由于mysql innodb特殊的行锁机制，死锁通常都是涉及到插入意向锁和next-key锁的，因为这两个锁是范围锁，范围锁涉及的目的就是为避免幻读，这会锁定一些自己不需要操作的记录。

不过在mysql中死锁从来都不是大问题，死锁通常都是数据库卡慢的果，而非因。而且由于数据库中普遍存在的死锁查杀机制，死锁产生后会很快被查杀。

真正可能引发数据库性能问题的，是高并发下的长事务，这种事务会导致undo等资源的争用，会占用binlog的提交队列导致后继事务处于commit阶段无法提交，即便强制kill也会引发长时间的rollback操作。

因此高并发下的长事务和低性能SQL才是死锁的主因，因为他们慢且作为一个整体在完成之前不会释放资源产生环路等待。

以上是 MySQL死锁 的全部内容， 来源链接： utcz.com/z/531845.html