很久之前有一個同事問我一個關於死鎖的問題，一直在拖這個事情，總算找了空來看看。

這個環境的事務隔離級別是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)

一個MySQL死鎖問題的復現

相關文章