mysql死锁 百科_一个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)