多个事务并发执行update出现的数据库死锁问题排除

现象：死锁，时间：2017-08-3100:00:00.841，故障解决：按最小代价自行回滚

事由：退优惠券，并发执行update语句.

死锁日志（详细）：

 Expand source

** (1) TRANSACTION:

TRANSACTION 6648945293, ACTIVE 0 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 5 lock struct(s), heap size 1184, 4 row lock(s)

MySQL thread id 7953966, OS thread handle 0x7f5b58350700, query id 8422437535 10.129.128.237 xxxdb Searching rows for update

update xxxtable

        set

        used_time = null,

        gmt_modified = '2017-08-31 00:00:00.841',

        status = 'NOT_USED',

        trade_no = null

        where

        code = '4ab5bf23-d09e-4947-8e83-4e6619c1f750'

        and user_id = 29096550200

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

RECORD LOCKS space id 285 page no 33715 n bits 152 index `PRIMARY` of table `xxxdb`.`xxxtable` trx id 6648945293 lock_mode X locks rec but not gap waiting

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

 0: len 8; hex 8000000000192b58; asc       +X;;

 1: len 6; hex 000188c2bcfa; asc       ;;

 2: len 7; hex 1e00001dd70680; asc        ;;

 3: len 8; hex 80000000000009e2; asc         ;;

 4: len 28; hex e696b0e4babae4b893e4baab35e58583e697a0e997a8e6a79be588b8; asc             5               ;;

 5: len 8; hex 80000006c64a1f38; asc      J 8;;

 6: SQL NULL;

 7: SQL NULL;

 8: SQL NULL;

 9: len 30; hex 30626430373532632d636638662d346264352d383961302d373064313266; asc 0bd0752c-cf8f-4bd5-89a0-70d12f; (total 36 bytes);

 10: SQL NULL;

 11: len 4; hex 55534544; asc USED;;

 12: len 5; hex 999d6e0a7b; asc   n {;;

 13: len 5; hex 999d795e39; asc   y^9;;

 14: len 5; hex 999d6e0a7b; asc   n {;;

 15: len 5; hex 999d8d7efb; asc    ~ ;;

 16: len 5; hex 999d6e0a7b; asc   n {;;

 17: len 5; hex 999d795e39; asc   y^9;;

 18: len 30; hex 66646635363862312d663036632d346232382d383935382d636463316433; asc fdf568b1-f06c-4b28-8958-cdc1d3; (total 36 bytes);

 19: len 7; hex 74726964656e74; asc trident;;

 20: len 27; hex 75736572696e766974653a32393039363535303230303a32353330; asc userinvite:29096550200:2530;;

 21: len 3; hex 414c4c; asc ALL;;

*** (2) TRANSACTION:

TRANSACTION 6648945294, ACTIVE 0 sec starting index read

mysql tables in use 3, locked 3

4 lock struct(s), heap size 1184, 3 row lock(s)

MySQL thread id 7953138, OS thread handle 0x7f5b3e8b7700, query id 8422437534 10.129.129.119 xxxdb Searching rows for update

update xxxtable

        set

        used_time = null,

        gmt_modified = '2017-08-31 00:00:00.841',

        status = 'NOT_USED',

        trade_no = null

        where

        code = '0bd0752c-cf8f-4bd5-89a0-70d12fd26dd3'

        and user_id = 29096550200

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

RECORD LOCKS space id 285 page no 33715 n bits 152 index `PRIMARY` of table `xxxdb`.`xxxtable` trx id 6648945294 lock_mode X locks rec but not gap

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

 0: len 8; hex 8000000000192b58; asc       +X;;

 1: len 6; hex 000188c2bcfa; asc       ;;

 2: len 7; hex 1e00001dd70680; asc        ;;

 3: len 8; hex 80000000000009e2; asc         ;;

 4: len 28; hex e696b0e4babae4b893e4baab35e58583e697a0e997a8e6a79be588b8; asc             5               ;;

 5: len 8; hex 80000006c64a1f38; asc      J 8;;

 6: SQL NULL;

 7: SQL NULL;

 8: SQL NULL;

 9: len 30; hex 30626430373532632d636638662d346264352d383961302d373064313266; asc 0bd0752c-cf8f-4bd5-89a0-70d12f; (total 36 bytes);

 10: SQL NULL;

 11: len 4; hex 55534544; asc USED;;

 12: len 5; hex 999d6e0a7b; asc   n {;;

 13: len 5; hex 999d795e39; asc   y^9;;

 14: len 5; hex 999d6e0a7b; asc   n {;;

 15: len 5; hex 999d8d7efb; asc    ~ ;;

 16: len 5; hex 999d6e0a7b; asc   n {;;

 17: len 5; hex 999d795e39; asc   y^9;;

 18: len 30; hex 66646635363862312d663036632d346232382d383935382d636463316433; asc fdf568b1-f06c-4b28-8958-cdc1d3; (total 36 bytes);

 19: len 7; hex 74726964656e74; asc trident;;

 20: len 27; hex 75736572696e766974653a32393039363535303230303a32353330; asc userinvite:29096550200:2530;;

 21: len 3; hex 414c4c; asc ALL;;

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

RECORD LOCKS space id 285 page no 33489 n bits 792 index `ix_user_id` of table `xxxdb`.`xxxtable` trx id 6648945294 lock_mode X locks rec but not gap waiting

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

 0: len 8; hex 80000006c64a1f38; asc      J 8;;

 1: len 8; hex 8000000000192b58; asc       +X;;

*** WE ROLL BACK TRANSACTION (2)

我们知道mysql 事务具有acid属性，分别是代表原子性，隔离性，一致性，持久性。

根据原子性可以知道事务操作是不可再分的，每个事务要么全部成功要么全部失败，通过隔离性可以知道：事务之间不会相互影响。

但是本次为什么还出现了并发事务出现了死锁问题呢？甚是不解。。。

 

根据死锁日志可以提取几个有用信息：

	trx1（事务1）	trx2（事务2）
id	6648945293	6648945294
特征	活跃0秒，正在index读	活跃0秒，正在index读
原因	Searching rows for update	Searching rows for update
sql	update xxxtable set used_time = null, gmt_modified = '2017-08-31 00:00:00.841', status = 'NOT_USED', trade_no = null where code = '4ab5bf23-d09e-4947-8e83-4e6619c1f750' and user_id = 29096550200	update xxxtable set used_time = null, gmt_modified = '2017-08-31 00:00:00.841', status = 'NOT_USED', trade_no = null where code = '0bd0752c-cf8f-4bd5-89a0-70d12fd26dd3' and user_id = 29096550200
锁定行数	4	3
正在等待的锁特性/ 正在持有锁特性	等待：{聚集索引id：285，页码：33715，锁类型：排他锁，非GAP(间隙)锁， 事务号：6648945293，索引：主键索引}   持有：idx_user_id的锁	持有：{锁id：285，页码：33715，锁类型：共享锁，非GAP(间隙)锁， 事务号：6648945293，索引：主键索引} 等待： {聚集索引id：285，页码：33489，锁类型：排他锁，非GAP(间隙)锁， 事务号：6648945293，索引：idx_user_id}
是否回滚	否	是

 

 

首先mysql默认的隔离级别是可重复读，事务未提交之前总是读到相同的记录，该隔离级别就是为了避免读已提交出现的幻读现象，采用的是GAP间隙锁实现。

根据上表可以得到信息，两个事务都未提交，或者说行锁锁定的记录之外没有其他事务提交的与之有关的记录，所以都未用到gap锁，有点绕。。。

根据日志可以发现update语句其实就是select xxxfor update，这个语句会持有排他锁（共享锁是in share mode）。

事务1等待排他锁，事务2持有事务1的共享锁，并且等待排他锁。这样就能死锁了？？为什么事务1没有持有事务2的共享锁

mysql官方有个bug帖子，如下：

https://bugs.mysql.com/bug.php?id=77209

建议：

Donot use index merge when single index is good enough
Try to avoid using index merge in UPDATE to not provoke deadlocks

 

所以在写sql的时候能用一个索引尽量不要使用两个混合索引去更新，可以先根据索引查询出结果，再执行更新。