[线上问题]两个update造成的死锁分析

  1. 云栖社区>
  2. 博客>
  3. 正文

[线上问题]两个update造成的死锁分析

shuangbest 2018-01-04 18:53:34 浏览8132
展开阅读全文

背景
元旦节后第一天上班,rd反映线上财务系统出现了死锁。还很苦恼地说不管他怎么想都想不明白,为啥这两条SQL查的是两个独立通道,查出来的数据肯定都不会相同,怎么会出现死锁呢?连锁等待都不会有的啊。
趁着分析这个死锁问题,将《MySQL技术技术内幕:InnoDB存储引擎》锁那一张又重新看了一遍,也仔细阅读了一遍何大师关于锁的分析,又有了跟以前不一样的感悟。

不多说,下面记录详细的分析过程。

分析过程
1 首先,在主库找到相应的死锁日志

*** (1) TRANSACTION:
TRANSACTION 14834740990, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 70 lock struct(s), heap size 13864, 2946 row lock(s), undo log entries 1469
MySQL thread id 46186908, OS thread handle 0x7f282ea3d700, query id 4727809231 10.92.188.13 payfndz_w updating
UPDATE DB.test SET F_error = '0', F_comment = '',F_proc='0' WHERE F_x_id='601' AND F_type  >= 4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1225516 page no 1232 n bits 152 index `PRIMARY` of table `DB`.`test` trx id 14834740990 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 14834735786, ACTIVE 5 sec fetching rows, thread declared inside InnoDB 3529
mysql tables in use 1, locked 1
7151 lock struct(s), heap size 652840, 334815 row lock(s), undo log entries 43879
MySQL thread id 46187084, OS thread handle 0x7f282f4f5700, query id 4727797957 10.92.180.27 payfndz_w updating
UPDATE DB.test SET F_error = '0', F_comment = '',F_proc='0' WHERE F_x_id='301' AND F_type  >= 4

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1225516 page no 1232 n bits 152 index `PRIMARY` of table `DB`.`test` trx id 14834735786 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1225516 page no 15807 n bits 136 index `PRIMARY` of table `DB`.`test` trx id 14834735786 lock_mode X waiting
Record lock, heap no 50 PHYSICAL RECORD: n_fields 38; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

2、查看F_x_id字段上所有索引
KEY I_1 (F_x_id,F_x_no,F_a)
KEY I_2 (F_x_id,F_y_id)
KEY I_3 (F_x_id,F_z_id)
KEY I_4 (F_x_id,F_z_part)

3、下面开始分析
看上面的日志,两个SQL在主键发生死锁。
常规分析:造成死锁的这两个SQL,SET和WHERE的字段中,只有WHERE条件的F_x_id是索引字段。
这个表在该字段上有四个可用索引,随便用哪一个,都会首先扫描二级索引在对应行加X锁和在间隙加GAP锁,然后根据二级索引中的主键值再去聚簇索引对相应数据行加X锁。因为F_x_id两个通道结果肯定不同,所以可能不会有相同的主键,更不用说会锁冲突了。简易分析如下图:
12a9ea1ca5f26326bfdd031b1802a4bb

当然,这有一个前提,就是这两条SQL实际执行时都能用到索引。

但是实际却发生了死锁,为什么?为什么?为什么?
再仔细分析死锁日志,301通道加了334815 row lock(s),问题来了。这个表一共38万条数据,却加了33万行的锁,咋回事?
难道全表扫描了?下面看看执行计划:

mysql> explain UPDATE DB.test SET F_error = '0', F_comment = '',F_proc='0' WHERE F_x_id='301' AND F_type  >= 4; 
+----+-------------+---------------+-------+------------------------------------------------------------------------+---------+-
| id | select_type | table         | type  | possible_keys   | key     | key_len | ref  | rows   | Extra                        |
+----+-------------+---------------+-------+------------------------------------------------------------------------+---------+-
|  1 | SIMPLE      | t_bank_return | index | I_1,I_2,I_3,I_4 | PRIMARY | 4       | NULL | 383431 | Using where; Using temporary |
+----+-------------+---------------+-------+------------------------------------------------------------------------+---------+-
mysql> explain UPDATE DB.test SET F_error = '0', F_comment = '',F_proc='0' WHERE F_x_id='601' AND F_type  >= 4;
+----+-------------+---------------+-------+------------------------------------------------------------------------+--------------
| id | select_type | table         | type  | possible_keys     | key       | key_len | ref   | rows | Extra                        |
+----+-------------+---------------+-------+------------------------------------------------------------------------+---------------
|  1 | SIMPLE      | t_bank_return | range | I_1,I_2,I_3,I_4   | I_1       | 4       | const | 1487 | Using where; Using temporary |
+----+-------------+---------------+-------+------------------------------------------------------------------------+-------------# # # **********-

那就能说通了,如下分析:

  1. InnoDB是边扫描边加锁的。如果走二级索引,边扫描边对二级索引行加X锁及间隙加GAP锁,然后再根据二级索引里的主键信息去扫描聚簇索引对主键行加X锁。
  2. 301这条SQL首先执行,由上面执行计划看,SQL走了主键索引,所以边扫描边对主键加X锁; —-很不幸,301这条还没执行完(38万条数据,加33万行锁),601这条SQL就开始执行了
  3. 601这条SQL接着执行,601走了二级索引,在对主键行加X锁时,发现要加锁的行已经被301锁定,waiting。 --也很不幸,601执行时301还没对所有主键行加完锁,所以601也锁定了一些主键行
  4. 301继续边扫边加锁,301要加锁的主键行,不幸被601已经上锁,又开始了等待601。
  5. 好吧,整个一死循环,死锁产生了。

处理办法

select F_seq from DB.test where F_x_id='301' AND F_type  >= 4;
UPDATE DB.test SET F_error = '0', F_comment = '',F_proc='0' where F_seq in (.....);

这样完美避开了锁等待及死锁。

后续学习
经过这个Case,又重新看了一遍《MySQL技术内幕:InnoDB存储引擎》锁那一张
也重新看了下之前看了无数遍的何大师的分析,又有了新的收获,真好:
http://hedengcheng.com/?p=771#_Toc374698318
http://hedengcheng.com/?p=844
http://hedengcheng.com/?p=286
http://hedengcheng.com/?p=577

网友评论

登录后评论
0/500
评论
shuangbest
+ 关注