MySQL死锁分析

ゞ浴缸里的玫瑰 2022-08-20 05:21 288阅读 0赞

## 1. 测试描述 ##

环境说明：RHEL 6.4 x86\_64 + MySQL 5.5.37，事务隔离级别为RC

测试表：

**\[sql\]**  [view plain][]   [copy][view plain]

[print][view plain] [?][view plain]

1.  mysql> show **create** **table** t1\\G  
2.  \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
3.         **Table**: t1  
4.  **Create** **Table**: **CREATE** **TABLE** \`t1\` (  
5.    \`a\` **int**(11) NOT NULL **DEFAULT** '0',  
6.    \`b\` **int**(11) **DEFAULT** NULL,  
7.    **PRIMARY** **KEY** (\`a\`),  
8.    **KEY** \`b\` (\`b\`)  
9.  ) ENGINE=InnoDB **DEFAULT** CHARSET=utf8  
10. 1 row in **set** (0.00 sec)

测试表中的数据：

**\[sql\]**  [view plain][]   [copy][view plain]

[print][view plain] [?][view plain]

1.  mysql> **select** \* **from** t1;  
2.  \+\----+------+  
3.  | a  | b    |  
4.  \+\----+------+  
5.  |  1 |    1 |  
6.  |  3 |    3 |  
7.  |  5 |    6 |  
8.  \+\----+------+  
9.  3 **rows** in **set** (0.01 sec)

## 2. 测试过程 ##

![SouthEast][]

## 3. 死锁日志分析 ##

此时查询show engine innodb status\\G查看死锁信息，下面只摘取了死锁信息部分，其他的省略。

**\[html\]**  [view plain][]   [copy][view plain]

[print][view plain] [?][view plain]

1.  \------------------------  
2.  LATEST DETECTED DEADLOCK  
3.  \------------------------  
4.  140824  1:01:24  
5.  \*\*\* (1) TRANSACTION:  
6.  TRANSACTION 110E, ACTIVE 73 sec starting index read   \#\# 事务ID=110E，活跃了73s  
7.  mysql tables in use 1, locked 1  
8.  LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)  \#\# 有2个行锁  
9.  MySQL thread id 1, OS thread handle 0x7f55ea639700, query id 81 localhost root updating  \#\# 该事务的线程ID=1  
10. delete from t1 where a=1   \#\# 这是当前事务执行的SQL  
11. \*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED:  \#\# 上面SQL等待的锁信息  
12. RECORD LOCKS space id 12 page no 3 n bits 80 index \`PRIMARY\` of table \`test\`.\`t1\` trx id 110E lock\_mode X locks rec but not gap waiting  
13. Record lock, heap no 2 PHYSICAL RECORD: n\_fields 4; compact format; info bits 32  \#\# 等待在主键上的page num=3上有加一个X锁(not gap waiting)，锁80 bits  
14.  0: len 4; hex 80000001; asc     ;;  
15.  1: len 6; hex 00000000110c; asc       ;;  
16.  2: len 7; hex 0d000002350084; asc     5  ;;  
17.  3: len 4; hex 80000001; asc     ;;  
18.   
19. \*\*\* (2) TRANSACTION:  
20. TRANSACTION 110C, ACTIVE 1716 sec starting index read, thread declared inside InnoDB 500  \#\# 事务ID=110C，活跃了1716s  
21. mysql tables in use 1, locked 1  
22. 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1  \#\# 3个锁，2个行锁，1个undo log  
23. MySQL thread id 2, OS thread handle 0x7f563c05e700, query id 82 localhost root updating  \#\# 该事务的线程ID=2  
24. delete from t1 where a=3   \#\# 这是当前事务执行的SQL  
25. \*\*\* (2) HOLDS THE LOCK(S):  \#\# 这个事务持有的锁信息  
26. RECORD LOCKS space id 12 page no 3 n bits 80 index \`PRIMARY\` of table \`test\`.\`t1\` trx id 110C lock\_mode X locks rec but not gap  
27. Record lock, heap no 2 PHYSICAL RECORD: n\_fields 4; compact format; info bits 32  \#\# 在主键上的page num=3上已持有一个X锁(not gap)，锁80 bits  
28.  0: len 4; hex 80000001; asc     ;;  
29.  1: len 6; hex 00000000110c; asc       ;;  
30.  2: len 7; hex 0d000002350084; asc     5  ;;  
31.  3: len 4; hex 80000001; asc     ;;  
32.   
33. \*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED:  \#\# 同时这个事务还等待的锁信息  
34. RECORD LOCKS space id 12 page no 3 n bits 80 index \`PRIMARY\` of table \`test\`.\`t1\` trx id 110C lock\_mode X locks rec but not gap waiting  
35. Record lock, heap no 3 PHYSICAL RECORD: n\_fields 4; compact format; info bits 0  \#\# 同样等待在主键上的page num=3上有加一个X锁(not gap waiting)，锁80 bits  
36.  0: len 4; hex 80000003; asc     ;;  
37.  1: len 6; hex 000000000f71; asc      q;;  
38.  2: len 7; hex ed0000022f0090; asc     /  ;;  
39.  3: len 4; hex 80000003; asc     ;;  
40.   
41. \*\*\* WE ROLL BACK TRANSACTION (1)   \#\# 这里选择回滚了事务110E。  
42.   
43. 也就是说，这个死锁使用事务110E中的SQL没有执行，回滚了：  
44. delete from t1 where a=1  
45. 而事务110C中的SQL是正常被执行的：  
46. delete from t1 where a=3

\-- Bosco

\---- END ----

\-------------------------------------------------------------------------------------------------------

[view plain]: http://blog.csdn.net/hw_libo/article/details/38789115#
[SouthEast]: /images/20220731/3f67f5c9c9d34385847983489965d9f7.png