一个MySQL死锁问题的复现
很久之前有一个同事问我一个关于死锁的问题，一直在拖这个事情，总算找了空来看看。
这个环境的事务隔离级别是RR,仔细看了下问题描述和背景，发现还真不是一块好啃的骨头。根据她的描述，是在两个会话并发对同一个表的不同行数据进行变更，两者是没有任何交集的，但是会抛出死锁问题。
这个问题我略做了改进，我改造成了两个SQL语句，最后再改进，就用一个shell脚本就能模拟出来了。
CREATE TABLE `t5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`col` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 ；然后使用下面的脚本来顺序插入，更新，整个过程两个会话里的操作看起来应该是是有条不紊的，把下面的信息直接放到一个脚本里运行即可。
for i in `seq 201 300`;do mysql -e "use test;begin;insert into test.t5 values(null,'aa','$i');update test.t5 set name='aa$i' where col='$i';commit;";done &
for i in `seq 301 400`;do mysql -e "use test;begin;insert into test.t5 values(null,'aa','$i');update test.t5 set name='aa$i' where col='$i';commit;";done &如果想得到明细的死锁信息，一般来说我们可以使用show engine innodb status\G可以看，但是这个是原理，什么时候运行这个，这个就难说了。
如果你守在电脑前不停的刷这个结果，很可能刷不到，而且这个死锁问题的复现有一定的概率下是不会出现的，所以要抓到时机来分析，还是有技巧可循，MySQL中有一个参数innodb_print_all_deadlocks默认是关闭的，开启之后我们就可以及时抓取到死锁信息，还有一个参数是检测死锁，这个是默认开启的。
> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON |
+----------------------------+-------+通过如上的信息，我们要得到死锁信息就建议开启这个选项，方便排查问题，得到的死锁信息如下，接下来的事情就有趣了。那就是分析这段日志来看看到底是怎么触发死锁问题的。
看死锁问题，那得多向“死锁小王子”何登成来学习，他分享过一篇很经典的死锁，是不可思议的死锁问题，一个delete操作在一定的场景下也可能触发死锁。
这段死锁日志我就先贴出来，也给大家留个作业，我上次还留了一个死锁的问题，这几天一并详细分析出来。
2017-08-28T03:21:21.661454Z 13964 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2017-08-28T03:21:21.661495Z 13964 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 31386, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 13965, OS thread handle 139809904252672, query id 107245 localhost root updating
update test.t5 set name='aa317' where col='317'
2017-08-28T03:21:21.661561Z 13964 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32 page no 3 n bits 104 index PRIMARY of table `test`.`t5` trx id 31386 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000007a57; asc zW;;
2: len 7; hex 3b000001b02be8; asc ; + ;;
3: len 5; hex 6161333031; asc aa301;;
4: len 3; hex 333031; asc 301;;
2017-08-28T03:21:21.661870Z 13964 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 31385, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 34 row lock(s), undo log entries 2
MySQL thread id 13964, OS thread handle 139809904518912, query id 107244 localhost root updating
update test.t5 set name='aa216' where col='216'
2017-08-28T03:21:21.661923Z 13964 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 32 page no 3 n bits 104 index PRIMARY of table `test`.`t5` trx id 31385 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000007a57; asc zW;;
2: len 7; hex 3b000001b02be8; asc ; + ;;
3: len 5; hex 6161333031; asc aa301;;
4: len 3; hex 333031; asc 301;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000007a59; asc zY;;
2: len 7; hex 3c000001972f65; asc < /e;;
3: len 5; hex 6161323031; asc aa201;;
4: len 3; hex 323031; asc 201;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000007a5f; asc z_;;
2: len 7; hex 3f0000018d2f40; asc ? /@;;
3: len 5; hex 6161333032; asc aa302;;
4: len 3; hex 333032; asc 302;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000007a61; asc za;;
2: len 7; hex 40000001c90204; asc @ ;;
3: len 5; hex 6161323032; asc aa202;;
4: len 3; hex 323032; asc 202;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000007a63; asc zc;;
2: len 7; hex 41000001462ccb; asc A F, ;;
3: len 5; hex 6161333033; asc aa303;;
4: len 3; hex 333033; asc 303;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 6; hex 000000007a65; asc ze;;
2: len 7; hex 42000001472cb0; asc B G, ;;
3: len 5; hex 6161323033; asc aa203;;
4: len 3; hex 323033; asc 203;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000000007a67; asc zg;;
2: len 7; hex 43000001482cc7; asc C H, ;;
3: len 5; hex 6161333034; asc aa304;;
4: len 3; hex 333034; asc 304;;
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 6; hex 000000007a69; asc zi;;
2: len 7; hex 44000001362da0; asc D 6- ;;
3: len 5; hex 6161323034; asc aa204;;
4: len 3; hex 323034; asc 204;;
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 6; hex 000000007a6b; asc zk;;
2: len 7; hex 45000001e42f52; asc E /R;;
3: len 5; hex 6161333035; asc aa305;;
4: len 3; hex 333035; asc 305;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000007a6d; asc zm;;
2: len 7; hex 46000001492cc7; asc F I, ;;
3: len 5; hex 6161323035; asc aa205;;
4: len 3; hex 323035; asc 205;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 000000007a6f; asc zo;;
2: len 7; hex 470000014a2cc7; asc G J, ;;
3: len 5; hex 6161333036; asc aa306;;
4: len 3; hex 333036; asc 306;;
Record lock, heap no 14 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000c; asc ;;
1: len 6; hex 000000007a71; asc zq;;
2: len 7; hex 480000014b2cc7; asc H K, ;;
3: len 5; hex 6161323036; asc aa206;;
4: len 3; hex 323036; asc 206;;
Record lock, heap no 15 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 000000007a73; asc zs;;
2: len 7; hex 490000014c2cc7; asc I L, ;;
3: len 5; hex 6161333037; asc aa307;;
4: len 3; hex 333037; asc 307;;
Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000e; asc ;;
1: len 6; hex 000000007a75; asc zu;;
2: len 7; hex 4a0000014d2cc7; asc J M, ;;
3: len 5; hex 6161323037; asc aa207;;
4: len 3; hex 323037; asc 207;;
Record lock, heap no 17 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000007a77; asc zw;;
2: len 7; hex 4b0000014e2cc7; asc K N, ;;
3: len 5; hex 6161333038; asc aa308;;
4: len 3; hex 333038; asc 308;;
Record lock, heap no 18 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 6; hex 000000007a79; asc zy;;
2: len 7; hex 4c0000014f2cc7; asc L O, ;;
3: len 5; hex 6161323038; asc aa208;;
4: len 3; hex 323038; asc 208;;
Record lock, heap no 19 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 000000007a7b; asc z{;;
2: len 7; hex 4d000001502cc7; asc M P, ;;
3: len 5; hex 6161333039; asc aa309;;
4: len 3; hex 333039; asc 309;;
Record lock, heap no 20 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000012; asc ;;
1: len 6; hex 000000007a7d; asc z};;
2: len 7; hex 4e000001512cc4; asc N Q, ;;
3: len 5; hex 6161323039; asc aa209;;
4: len 3; hex 323039; asc 209;;
Record lock, heap no 21 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000013; asc ;;
1: len 6; hex 000000007a7f; asc z ;;
2: len 7; hex 4f000001522cc7; asc O R, ;;
3: len 5; hex 6161333130; asc aa310;;
4: len 3; hex 333130; asc 310;;
Record lock, heap no 22 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000007a81; asc z ;;
2: len 7; hex 50000001532cc7; asc P S, ;;
3: len 5; hex 6161323130; asc aa210;;
4: len 3; hex 323130; asc 210;;
Record lock, heap no 23 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000015; asc ;;
1: len 6; hex 000000007a83; asc z ;;
2: len 7; hex 51000001542bf2; asc Q T+ ;;
3: len 5; hex 6161333131; asc aa311;;
4: len 3; hex 333131; asc 311;;
Record lock, heap no 24 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 6; hex 000000007a85; asc z ;;
2: len 7; hex 52000001732bee; asc R s+ ;;
3: len 5; hex 6161323131; asc aa211;;
4: len 3; hex 323131; asc 211;;
Record lock, heap no 25 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000017; asc ;;
1: len 6; hex 000000007a87; asc z ;;
2: len 7; hex 53000001552cc7; asc S U, ;;
3: len 5; hex 6161333132; asc aa312;;
4: len 3; hex 333132; asc 312;;
Record lock, heap no 26 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000018; asc ;;
1: len 6; hex 000000007a89; asc z ;;
2: len 7; hex 54000001572cc7; asc T W, ;;
3: len 5; hex 6161323132; asc aa212;;
4: len 3; hex 323132; asc 212;;
Record lock, heap no 27 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000007a8b; asc z ;;
2: len 7; hex 55000001592cc7; asc U Y, ;;
3: len 5; hex 6161333133; asc aa313;;
4: len 3; hex 333133; asc 313;;
Record lock, heap no 28 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000001a; asc ;;
1: len 6; hex 000000007a8d; asc z ;;
2: len 7; hex 56000001372da0; asc V 7- ;;
3: len 5; hex 6161323133; asc aa213;;
4: len 3; hex 323133; asc 213;;
Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000001b; asc ;;
1: len 6; hex 000000007a8f; asc z ;;
2: len 7; hex 57000001db302b; asc W 0+;;
3: len 5; hex 6161333134; asc aa314;;
4: len 3; hex 333134; asc 314;;
Record lock, heap no 30 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000001c; asc ;;
1: len 6; hex 000000007a91; asc z ;;
2: len 7; hex 58000001ea2e73; asc X .s;;
3: len 5; hex 6161323134; asc aa214;;
4: len 3; hex 323134; asc 214;;
Record lock, heap no 31 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000001d; asc ;;
1: len 6; hex 000000007a93; asc z ;;
2: len 7; hex 590000015b2cb2; asc Y [, ;;
3: len 5; hex 6161333135; asc aa315;;
4: len 3; hex 333135; asc 315;;
Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000007a95; asc z ;;
2: len 7; hex 5a000001382d88; asc Z 8- ;;
3: len 5; hex 6161323135; asc aa215;;
4: len 3; hex 323135; asc 215;;
Record lock, heap no 33 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000001f; asc ;;
1: len 6; hex 000000007a97; asc z ;;
2: len 7; hex 5b000001752bee; asc [ u+ ;;
3: len 5; hex 6161333136; asc aa316;;
4: len 3; hex 333136; asc 316;;
Record lock, heap no 35 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000020; asc ;;
1: len 6; hex 000000007a99; asc z ;;
2: len 7; hex 5c000001772be5; asc \ w+ ;;
3: len 5; hex 6161323136; asc aa216;;
4: len 3; hex 323136; asc 216;;
2017-08-28T03:21:21.697975Z 13964 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32 page no 3 n bits 104 index PRIMARY of table `test`.`t5` trx id 31385 lock_mode X waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000021; asc !;;
1: len 6; hex 000000007a9a; asc z ;;
2: len 7; hex dd0000018e0110; asc ;;
3: len 2; hex 6161; asc aa;;
4: len 3; hex 333137; asc 317;;
2017-08-28T03:21:21.698251Z 13964 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2017-08-29，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。