一个read commited下的死锁分析

  • 风尘_NULL

    2017-02-28 15:39:44
  • MySQL
  • 原创


--------------------------------------这是在大神博客上看到的个问题，大神没回复给提问者（可能太简单了吧），暂且本人在这里回复@mmachera--------------------------------------
原文：

求大侠帮忙分析一个死锁原因。
背景：
mysql数据库隔离级别为（READ-COMMITED），有2个并发事务，每个事务都是针对同一张表的操作（ file_table），该表只有主键（ID字段）索引，没有其他任何索引。

每个事务同时执行两组SQL语句，每组SQL语句为先删除一条记录，然后再写入一条记录，也就是同一个事务总共会执行4条语句。

每个事务执行的SQL语句如下：
事务一：
DELETE FROM file_table WHERE SENDER_ID = 201507011326315 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_1’;
INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (1,201507011326315,201507011325162, ‘filename_1’);
DELETE FROM file_table WHERE SENDER_ID = 201507011326315 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_2’;
INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (2,201507011326315,201507011325162, ‘filename_2’);

事务二：
DELETE FROM file_table WHERE SENDER_ID = 201507011325413 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_1’;
INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (3,201507011325413,201507011325162, ‘filename_1’);
DELETE FROM file_table WHERE SENDER_ID = 201507011325413 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_2’;
INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (4,201507011325413,201507011325162, ‘filename_2’);

事务一和事务二是同时并发执行的，在所有4条语句中，其实可以不用关心RECEIVER_ID字段，因为其值都是相同的。每个事务都是根据SENDER_ID、RECEIVER_ID、FILE_NAME可以唯一确定一条记录（没有任何索引，除了主键索引），删除操作也是使用这三个字段作为条件。最终并发执行时，发生死锁，从死锁日志看出，死锁发生在表的主键上。

如果每个事务只执行一组DELETE、INSERT操作，则不会有死锁。

死锁日志截取如下（日志显示的SQL可能与上面的SQL语句不一致，但是完全相同的操作产生的）：
LATEST DETECTED DEADLOCK
————————
150707 16:22:12
*** (1) TRANSACTION:
TRANSACTION 21647F9, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 14991, OS thread handle 0x4308, query id 81304600 localhost 127.0.0.1 root updating
DELETE FROM FILE_TABLE WHERE SENDER_ID = 201507011326064 AND RECEIVER_ID = 201507011325162 AND FILE_NAME =
‘filename_1’
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12675 n bits 120 index `PRIMARY` of table `bfip_deadlock`.`FILE_TABLE` trx id 21647F9
lock_mode X locks rec but not gap waiting
Record lock, heap no 23 PHYSICAL RECORD: n_fields 32; compact format; info bits 32
0: len 8; hex 9bf6f823dda484ec; asc # ;;
1: len 6; hex 0000021647f7; asc G ;;
2: len 7; hex 5a000030822056; asc Z 0 V;;
3: len 8; hex 8000b74501d46de5; asc E m ;;
4: len 8; hex 3134363136343130; asc 14616410;;
5: len 21; hex e6b5b7e58fa3e88194e59088e5869ce59586e8a18c; asc ;;
6: len 8; hex 8000b74501d46cea; asc E l ;;
7: len 8; hex 3633303330303030; asc 63030000;;
8: len 18; hex e4b8ade59bbde58589e5a4a7e993b6e8a18c; asc ;;
9: len 1; hex 53; asc S;;
10: len 19; hex 4f46495f3134365f45425f3230313530373033; asc OFI_146_EB_20150703;;
11: len 3; hex 545854; asc TXT;;
12: len 2; hex 3030; asc 00;;
13: len 1; hex 57; asc W;;
14: len 10; hex 323031352d30372d3033; asc 2015-07-03;;
15: len 8; hex 32333a31373a3331; asc 23:17:31;;
16: SQL NULL;
17: len 8; hex 82cbe593aee48623; asc #;;
18: len 0; hex ; asc ;;
19: len 0; hex ; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: len 8; hex 7fffffffffffffff; asc ;;
25: len 19; hex 323031352d30372d30332032333a31373a3335; asc 2015-07-03 23:17:35;;
26: SQL NULL;
27: SQL NULL;
28: len 4; hex 80000001; asc ;;
29: len 1; hex 4e; asc N;;
30: SQL NULL;
31: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 21647F7, ACTIVE 0 sec, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2
MySQL thread id 14990, OS thread handle 0x88b8, query id 81304606 localhost 127.0.0.1 root updating
DELETE FROM FILE_TABLE WHERE SENDER_ID = 201507011325413 AND RECEIVER_ID = 201507011325162 AND FILE_NAME =
‘filename_1’
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 12675 n bits 120 index `PRIMARY` of table `bfip_deadlock`.`FILE_TABLE` trx id 21647F7
lock_mode X locks rec but not gap
Record lock, heap no 23 PHYSICAL RECORD: n_fields 32; compact format; info bits 32
0: len 8; hex 9bf6f823dda484ec; asc # ;;
1: len 6; hex 0000021647f7; asc G ;;
2: len 7; hex 5a000030822056; asc Z 0 V;;
3: len 8; hex 8000b74501d46de5; asc E m ;;
4: len 8; hex 3134363136343130; asc 14616410;;
5: len 21; hex e6b5b7e58fa3e88194e59088e5869ce59586e8a18c; asc ;;
6: len 8; hex 8000b74501d46cea; asc E l ;;
7: len 8; hex 3633303330303030; asc 63030000;;
8: len 18; hex e4b8ade59bbde58589e5a4a7e993b6e8a18c; asc ;;
9: len 1; hex 53; asc S;;
10: len 19; hex 4f46495f3134365f45425f3230313530373033; asc OFI_146_EB_20150703;;
11: len 3; hex 545854; asc TXT;;
12: len 2; hex 3030; asc 00;;
13: len 1; hex 57; asc W;;
14: len 10; hex 323031352d30372d3033; asc 2015-07-03;;
15: len 8; hex 32333a31373a3331; asc 23:17:31;;
16: SQL NULL;
17: len 8; hex 82cbe593aee48623; asc #;;
18: len 0; hex ; asc ;;
19: len 0; hex ; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: len 8; hex 7fffffffffffffff; asc ;;
25: len 19; hex 323031352d30372d30332032333a31373a3335; asc 2015-07-03 23:17:35;;
26: SQL NULL;
27: SQL NULL;
28: len 4; hex 80000001; asc ;;
29: len 1; hex 4e; asc N;;
30: SQL NULL;
31: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12675 n bits 120 index `PRIMARY` of table `bfip_deadlock`.`FILE_TABLE` trx id 21647F7
lock_mode X locks rec but not gap waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 32; compact format; info bits 32
0: len 8; hex 9bf6f823dda484e6; asc # ;;
1: len 6; hex 0000021647f9; asc G ;;
2: len 7; hex 5c00002630248b; asc \ &0$ ;;
3: len 8; hex 8000b74501d47070; asc E pp;;
4: len 8; hex 3634373431393131; asc 64741911;;
5: len 24; hex e5a4a9e9aa84e89299e993b6e69d91e99587e993b6e8a18c; asc ;;
6: len 8; hex 8000b74501d46cea; asc E l ;;
7: len 8; hex 3633303330303030; asc 63030000;;
8: len 18; hex e4b8ade59bbde58589e5a4a7e993b6e8a18c; asc ;;
9: len 1; hex 53; asc S;;
10: len 19; hex 4f46495f3738305f45425f3230313530373033; asc OFI_780_EB_20150703;;
11: len 3; hex 545854; asc TXT;;
12: len 2; hex 3030; asc 00;;
13: len 1; hex 57; asc W;;
14: len 10; hex 323031352d30372d3033; asc 2015-07-03;;
15: len 8; hex 32333a31373a3331; asc 23:17:31;;
16: SQL NULL;
17: len 8; hex 82cbe593aee48624; asc $;;
18: len 0; hex ; asc ;;
19: len 0; hex ; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: len 8; hex 7fffffffffffffff; asc ;;
25: len 19; hex 323031352d30372d30332032333a31373a3335; asc 2015-07-03 23:17:35;;
26: SQL NULL;
27: SQL NULL;
28: len 4; hex 80000001; asc ;;
29: len 1; hex 4e; asc N;;
30: SQL NULL;
31: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)
————
TRANSACTIONS
————
Trx id counter 2164820
Purge done for trx’s n:o < 21647FD undo n:o < 0
History list length 389

麻烦帮忙分析下原因呢，是不是跟索引有关。我做过一个测试，如果用相同的条件先把主键查出来，再用主键去DELETE，则不会有死锁发生。




----------------------------------------------------------------------------------死锁分析----------------------------------------------------------------------

分析：
我并没有去问作者的表是否有数据，姑且做下假设

1.假设表中存在数据，如下表：
+----+-----------+-------------+------------+
| id | SENDER_ID | RECEIVER_ID | FILE_NAME  |
+----+-----------+-------------+------------+
|  1 |   1326315 |     1325162 | filename_1 |
|  2 |   1326315 |     1325162 | filename_2 |
|  3 |   1325413 |     1325162 | filename_1 |
|  4 |   1325413 |     1325162 | filename_2 |
+----+-----------+-------------+------------+
那么，提问中的两个事物任何一个先执行，其中的第一个delete语句都会给表中所有记录上X锁（排它锁），
所以当另一个事物去删除数据时，那么就会是锁等待，所以在有数据的情况下，并不会发生
session1


session 2


2.如果将上表的id为1的数据也去除（即表中只剩下三条记录，主键分别为2、3、4），如果事物1线执行到第二条insert语句（事物1共有四条语句，按1-2-3-4排的第二条），那么事物1的第二条insert语句会加(-无穷,+无穷)的insert间隙锁，这样会阻塞事物2的delete语句，同理如果事物2先执行第一个delete语句，那么就会阻塞事物1的第二条insert语句，这样会产生锁等待，单也不会是死锁


事物1先执行的情况
session1



session2




事物2先执行的情况
session1


session 2

3.如果将上表的id为3的数据也去除（即表中只剩下2条记录，主键分别为2、4），如果事物1执行到第二条sql语句，同时事物2也执行到第二条sql语句，由于insert意向锁之间不互斥，所以两个事物都加上了(-无穷,+无穷)的insert间隙锁，当事物1执行第三条sql语句时，由于是delete，会与事物2的insert互斥，所以产生了锁等待 ，即事物1等待事物2;当事物2也执行第三条delete语句时，也会等待事物1的第二条insert语句释放间隙锁，即事物2等待事物1，由此死锁产生。
session 1




session 2


请使用浏览器的分享功能分享到微信等