mysql并发死锁问题解决

一般根据多条件过滤后更新update在高并发的时候会导致死锁,进而事务失败。解决办法就是为表增加主键,先查询出主键,再按主键更新,避免死锁。

这是因为mysql行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引,如果没有主键则使用聚簇索引。如果更新的时候使用到多个索引则可能会导致死锁.https://bugs.mysql.com/bug.php?id=77209

规避办法就是update更新时避免使用多个索引,尽量使用单个索引(或主键)。我这个表没有主键，我就新增了个主键，update时先根据条件查出主键再根据主键更新状态，然后再没有出现更深死锁的问题了。

 

下面是定位过程：

新开发的并发模板执行功能发现状态更新有问题，有较大概率部分节点状态不更新。怀疑没执行或异常了，直接开启mysql执行日志，从日志里查看导致状态更新语句执行了没有。

开启之后，运行程序发现有个节点状态没更新，根据节点id去mysql执行日志里找下看到底执行了没有：

发现执行了，但是查询状态却仍然是没有更新。怀疑存储过程有问题，一看原来存储过程里捕获了异常，怪不得没有抛异常。

注释掉存储过程的异常捕获，再跑一次，这次异常抛出到程序在日志里可以看到,原来是mysql数据库死锁了导致事务回滚了,所以执行了,但是状态没有更新。

在数据库执行 show engine innodb status; 查询到如下死锁信息,两条update语句根据多个条件查询并更新,使用到了多个索引,并且有个主要索引的值也是相等的。

------------------------

LATEST DETECTED DEADLOCK

------------------------

2020-09-11 14:23:54 7fbb807e2700

*** (1) TRANSACTION:

TRANSACTION 2093530753, ACTIVE 0 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

MySQL thread id 443592, OS thread handle 0x7fb9eb451700, query id 87154676 192.168.129.118 root Searching rows for update

update etl_jobentry_status

       set status = v_status,

           result = v_result,

           createdate=IF(v_status=0,NOW(),createdate) ,

           enddate = NOW(),

           verify_result = V_verify_result,

           error_num = p_error_num

     where id_jobentry = p_id_jobentry

       and id_job = p_id_job

       and id_batch = p_id_batch

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 107797 page no 11303 n bits 1120 index `index_id_job` of table `etl_test_1210`.`etl_jobentry_status` trx id 2093530753 lock_mode X waiting

Record lock, heap no 225 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 4; hex 800050cc; asc   P ;;

 1: len 6; hex 0000913735c6; asc    75 ;;

 

*** (2) TRANSACTION:

TRANSACTION 2093530752, ACTIVE 0 sec fetching rows

mysql tables in use 3, locked 3

49 lock struct(s), heap size 6544, 6189 row lock(s), undo log entries 1

MySQL thread id 442593, OS thread handle 0x7fbb807e2700, query id 87154635 192.168.129.118 root Searching rows for update

update etl_jobentry_status

       set status = v_status,

           result = v_result,

           createdate=IF(v_status=0,NOW(),createdate) ,

           enddate = NOW(),

           verify_result = V_verify_result,

           error_num = p_error_num

     where id_jobentry = p_id_jobentry

       and id_job = p_id_job

       and id_batch = p_id_batch

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 107797 page no 11303 n bits 1120 index `index_id_job` of table `etl_test_1210`.`etl_jobentry_status` trx id 2093530752 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

 0: len 8; hex 73757072656d756d; asc supremum;;

 

Record lock, heap no 225 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 4; hex 800050cc; asc   P ;;

 1: len 6; hex 0000913735c6; asc    75 ;;

 

 

 

参考：

https://blog.csdn.net/guanfengliang1988/article/details/80356648

https://blog.csdn.net/usst_lidawei/article/details/79494177