若想直接查看问题原因及解决办法,跳到文章末尾即可。
- 查看系统报错的日志信息如下图:
- 查看mysql的日志,可以看到确实是发生了死锁:
show engine innodb status\G; #登录mysql,执行该语句
Record lock, heap no 51 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
0: len 8; hex 80000000000027ca; asc ' ;;
1: len 6; hex 000000034c59; asc LY;;
2: len 7; hex 81000000ca01ba; asc ;;
3: SQL NULL;
4: len 8; hex 800000000000195b; asc [;;
5: len 8; hex 800001742dbab8fe; asc t- ;;
6: len 8; hex 800001742dbab8fe; asc t- ;;
7: len 1; hex 80; asc ;;
8: len 8; hex 8000000000000000; asc ;;
9: len 0; hex ; asc ;;
10: len 10; hex 35362ee9989fe5919832; asc 56. 2;;
11: len 10; hex 35362ee9989fe5919832; asc 56. 2;;
12: len 13; hex e6a8a1e69dbfe5908de7a7b031; asc 1;;
13: len 4; hex 80000014; asc ;;
14: SQL NULL;
15: SQL NULL;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: len 30; hex 687474703a2f2f3231302e31322e3136362e3139373a31383039302f6772; asc http://210.12.166.197:18090/gr; (total 79 bytes);
20: len 30; hex 687474703a2f2f3231302e31322e3136362e3139373a31383039302f6772; asc http://210.12.166.197:18090/gr; (total 79 bytes);
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: len 4; hex 80000000; asc ;;
28: len 30; hex 7b22706572736f6e6e656c4e616d65223a22e9989fe59198322831353635; asc {"personnelName":" 2(1565; (total 179 bytes);
29: len 8; hex 8000000000000000; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1107 page no 77 n bits 96 index PRIMARY of table `zhzf-check`.`zhzf_check_bill_record` trx id 224290 lock_mode X waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
0: len 8; hex 80000000000028db; asc ( ;;
1: len 6; hex 000000036c23; asc l#;;
2: len 7; hex 81000000bd0154; asc T;;
3: len 8; hex 8000000000002192; asc ! ;;
4: SQL NULL;
5: len 8; hex 800001744eea0416; asc tN ;;
6: len 8; hex 800001744eea0416; asc tN ;;
7: len 1; hex 80; asc ;;
8: len 8; hex 800001744eea0416; asc tN ;;
9: len 0; hex ; asc ;;
10: len 12; hex 35392ee69d8ee680bce680bc; asc 59. ;;
11: len 12; hex 35392ee69d8ee680bce680bc; asc 59. ;;
12: len 13; hex e6a8a1e69dbfe5908de7a7b031; asc 1;;
13: len 4; hex 80000014; asc ;;
14: SQL NULL;
15: SQL NULL;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: len 8; hex 8000000000000005; asc ;;
*** (2) TRANSACTION:
TRANSACTION 224291, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 35 lock struct(s), heap size 3520, 37 row lock(s), undo log entries 77
MySQL thread id 1469, OS thread handle 140385926588160, query id 788883 ZNY-2054.smartetown.cn 172.16.20.54 root updating
UPDATE zhzf_check_bill_record SET is_deleted=1
WHERE is_deleted=0
AND id_zhzf_check_fill_bill = 6744
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1107 page no 77 n bits 96 index PRIMARY of table `zhzf-check`.`zhzf_check_bill_record` trx id 224291 lock_mode X locks rec but not gap
Record lock, heap no 27 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
0: len 8; hex 80000000000028db; asc ( ;;
1: len 6; hex 000000036c23; asc l#;;
2: len 7; hex 81000000bd0154; asc T;;
3: len 8; hex 8000000000002192; asc ! ;;
4: SQL NULL;
5: len 8; hex 800001744eea0416; asc tN ;;
6: len 8; hex 800001744eea0416; asc tN ;;
7: len 1; hex 80; asc ;;
8: len 8; hex 800001744eea0416; asc tN ;;
9: len 0; hex ; asc ;;
10: len 12; hex 35392ee69d8ee680bce680bc; asc 59. ;;
11: len 12; hex 35392ee69d8ee680bce680bc; asc 59. ;;
12: len 13; hex e6a8a1e69dbfe5908de7a7b031; asc 1;;
13: len 4; hex 80000014; asc ;;
14: SQL NULL;
15: SQL NULL;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: len 8; hex 8000000000000005; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1107 page no 28 n bits 168 index PRIMARY of table `zhzf-check`.`zhzf_check_bill_record` trx id 224291 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
0: len 8; hex 8000000000002799; asc ' ;;
1: len 6; hex 000000033c47; asc <G;;
2: len 7; hex 81000000fc0198; asc ;;
3: len 8; hex 800000000000205c; asc \;;
4: SQL NULL;
5: len 8; hex 8000017429ae99d3; asc t) ;;
6: len 8; hex 8000017429ae99d3; asc t) ;;
7: len 1; hex 80; asc ;;
8: len 8; hex 8000017429ae99d3; asc t) ;;
9: len 0; hex ; asc ;;
10: len 16; hex 33362ee8aeb8e588a92de5b180e995bf; asc 36. - ;;
11: len 16; hex 33362ee8aeb8e588a92de5b180e995bf; asc 36. - ;;
12: len 13; hex e6a8a1e69dbfe5908de7a7b031; asc 1;;
13: len 4; hex 80000014; asc ;;
14: SQL NULL;
15: SQL NULL;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: len 8; hex 8000000000000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------引发死锁的sql就是这条update语句:
UPDATE zhzf_check_bill_record SET is_deleted=1
WHERE is_deleted=0
AND id_zhzf_check_fill_bill = 6744对照分析日志:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-01-09 15:11:45 0x70000cc97000 //死锁发生时间
*** (1) TRANSACTION: //事务1
TRANSACTION 1819662/*事务id，trx->id*/, ACTIVE 13 sec starting index read //事务1从开始执行到被死锁检测经历的时间，生产环境一般看到的是0
mysql tables in use 1, locked 1 //事务1当前查询使用到的表，以及已经加锁的表。分别是trx->n_mysql_tables_in_use，trx->mysql_n_tables_locked
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)// 这行的LOCK WAIT 2 lock struct(s),应该分成两部分解读，而不是说等待两个锁结构。LOCK WAIT表示事务状态处于锁等待，但是这是个不加锁的脏读输出。有可能不准确，2 lock struct(s)表示此事务中的锁结构数目，UT_LIST_GET_LEN(trx->lock.trx_locks); heap size代表内存堆的byte长度。1 row locks(s) 是锁了一行。
MySQL thread id 5, OS thread handle 123145517932544, query id 39 localhost root statistics
select * from t2 where id = 5 for update
//processlist id , 对应的操作系统的线程结构（pthread_t），查询ID，来源IP，用户名，操作信息（proc_info，不受保护）
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: //此事务要加的锁，但是被阻塞。
RECORD LOCKS/*行锁*/ space id 68/*表空间id*/ page no 3/*page id*/ n bits 72/*bitmap的大小*/ index PRIMARY /*索引*/of table `test_lock`.`t2`/*表名*/ trx id 1819662 /*事务id*/ lock_mode X /*排他锁*/ locks rec but not gap /*只锁行记录，不锁间隙*/ waiting /*加锁等待*/
Record lock, heap no 6/*索引页中所处的逻辑位置*/ PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 0000001b984f; asc O;;
2: len 7; hex 48000001890110; asc H ;;
3: len 8; hex 7570646174655f33; asc update_3;;
*** (2) TRANSACTION:
TRANSACTION 1819663, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 4, OS thread handle 123145516838912, query id 40 localhost root statistics
select * from t2 where id = 5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `test_lock`.`t2` trx id 1819663 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 0000001b984f; asc O;;
2: len 7; hex 48000001890110; asc H ;;
3: len 8; hex 7570646174655f33; asc update_3;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `test_lock`.`t2` trx id 1819663 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 0000001b984f; asc O;;
2: len 7; hex 48000001890110; asc H ;;
3: len 8; hex 7570646174655f33; asc update_3;;
*** WE ROLL BACK TRANSACTION (1)- 问题可能分析:
- 外键字段id_zhzf_check_fill_bil没有索引,且存在很多null值,如下图:
- update语句对应的数据在数据库中不存在
UPDATE zhzf_check_bill_record SET is_deleted=1
WHERE is_deleted=0 AND id_zhzf_check_fill_bill = 6744
- 最终问题原因:
1、innodb引擎下update在默认情况下是行锁，但是在Mysql默认隔离级别（可重复读）下，一旦update更新的数据行不存在，则会产生间隙锁（Gap
lock）；2、事务1 update不存在的数据行，产生了Gap lock，事务2 update不存在的数据行，也产生了Gap lock；
3、事务1 insert操作需要等待对方释放X锁，事务2 insert操作也需要等待对方释放X锁，死锁产生，Mysql自动回滚了事务2；
- 最终解决办法:
先select出所有需要更新的记录的主键，然后再执行update(如果select为空,则不执行)，where条件只用主键。所有update语句将会只锁住主键索引，而不会尝试锁非主键索引。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
