存储过程中的Mysql死锁，复合唯一键

Question

我有一个带有复合唯一键的表(3 列组合的唯一性。)表结构:

CREATE TABLE `userreview` (
 `cid` bigint(12) unsigned NOT NULL,
 `conid` bigint(12) unsigned NOT NULL,
 `userid` bigint(12) unsigned NOT NULL,
 `flag` int(12) unsigned NOT NULL DEFAULT '0',
 `updatedat` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON 
  UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY `idx_userreview_cid_conid_userid` (`cid`,`conid`,`userid`)
 ) ;

下面是存储过程。

CREATE  PROCEDURE `testdeadlock`(IN pconid   BIGINT(12), IN pcid 
  BIGINT(12), IN puserid BIGINT(12),IN pflag INT(1))
  BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
   ROLLBACK;
   RESIGNAL;
 END;

START TRANSACTION;  
IF pflag = 1 THEN

 INSERT ignore into userreview(cid,conid,userid,flag)
            VALUES(pcid,pconid,puserid,1)
          ON DUPLICATE KEY UPDATE flag = 1;
         /*update operation in table 1*/
 ELSEIF pflag = 0 THEN

   INSERT ignore into userreview(cid,conid,userid)
            VALUES(pcid,pconid,puserid)
          ON DUPLICATE KEY UPDATE flag = 0;
       /*update operation in table 2*/
    ELSEIF pflag = 2 THEN

    INSERT ignore into userreview(cid,conid,userid)
            VALUES(pcid,pconid,puserid)
          ON DUPLICATE KEY UPDATE flag = 2;
       /*update operation in table 3*/
 end if;
COMMIT;
end;

当此过程从客户端(使用 node.js)执行时，当并发事务将在同一行上执行时，有时会发生死锁。尽管唯一索引仍然面临死锁问题。有什么避免死锁的建议吗？

死锁结果:-

                LATEST DETECTED DEADLOCK
            ------------------------
            2018-07-06 16:55:51 0x2b0b08e99700
            *** (1) TRANSACTION:
            TRANSACTION 102648166, ACTIVE 0 sec inserting
            mysql tables in use 2, locked 2
            LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
            MySQL thread id 108948, OS thread handle 47323865503488, query id 242017676 10.0.3.130 numbertankroot update
            INSERT ignore into userreview(cid,conid,userid,flag)
                    VALUES( NAME_CONST('pcid',8157), NAME_CONST('pconid',3158), NAME_CONST('puserid',85217))
                  ON DUPLICATE KEY UPDATE flag = 0
            *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
            RECORD LOCKS space id 735 page no 17308 n bits 256 index idx_userreview_cid_conid_userid of table `userreview` trx id 102648166 lock_mode X waiting
            Record lock, heap no 183 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
             0: len 8; hex 0000000000001fdd; asc         ;;
             1: len 8; hex 000000000005af9f; asc         ;;
             2: len 8; hex 00000000000bd0b7; asc         ;;
             3: len 6; hex 0000061e4965; asc     Ie;;
             4: len 7; hex 2800000eb42873; asc (    (s;;
             5: len 1; hex 80; asc  ;;
             6: len 1; hex 84; asc  ;;
             7: len 4; hex 00000001; asc     ;;
             8: len 1; hex 01; asc  ;;
             9: len 8; hex 0000000000001fdd; asc         ;;
             10: len 4; hex 00000000; asc     ;;
             11: len 4; hex 00000000; asc     ;;
             12: len 4; hex 00000001; asc     ;;
             13: len 6; hex 5b3f9f170000; asc [?    ;;
             14: len 6; hex 5b3f9f17213e; asc [?  !>;;

            *** (2) TRANSACTION:
            TRANSACTION 102648170, ACTIVE 0 sec inserting
            mysql tables in use 2, locked 2
            4 lock struct(s), heap size 1136, 2 row lock(s)
            MySQL thread id 108960, OS thread handle 47326394160896, query id 242017689 10.0.3.130 numbertankroot update
            INSERT ignore into userreview(cid,conid,userid,flag)
                    VALUES( NAME_CONST('pcid',8157), NAME_CONST('pconid',3158), NAME_CONST('puserid',85217))
                  ON DUPLICATE KEY UPDATE flag = 1
            *** (2) HOLDS THE LOCK(S):
            RECORD LOCKS space id 735 page no 17308 n bits 256 index `idx_userreview_cid_conid_userid` of table `userreview` trx id 102648170 lock mode S locks rec but not gap
            Record lock, heap no 183 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
             0: len 8; hex 0000000000001fdd; asc         ;;
             1: len 8; hex 000000000005af9f; asc         ;;
             2: len 8; hex 00000000000bd0b7; asc         ;;
             3: len 6; hex 0000061e4965; asc     Ie;;
             4: len 7; hex 2800000eb42873; asc (    (s;;
             5: len 1; hex 80; asc  ;;
             6: len 1; hex 84; asc  ;;
             7: len 4; hex 00000001; asc     ;;
             8: len 1; hex 01; asc  ;;
             9: len 8; hex 0000000000001fdd; asc         ;;
             10: len 4; hex 00000000; asc     ;;
             11: len 4; hex 00000000; asc     ;;
             12: len 4; hex 00000001; asc     ;;
             13: len 6; hex 5b3f9f170000; asc [?    ;;
             14: len 6; hex 5b3f9f17213e; asc [?  !>;;

            *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
            RECORD LOCKS space id 735 page no 17308 n bits 256 index `idx_userreview_cid_conid_userid` of table  `userreview` trx id 102648170 lock_mode X waiting
            Record lock, heap no 183 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
             0: len 8; hex 0000000000001fdd; asc         ;;
             1: len 8; hex 000000000005af9f; asc         ;;
             2: len 8; hex 00000000000bd0b7; asc         ;;
             3: len 6; hex 0000061e4965; asc     Ie;;
             4: len 7; hex 2800000eb42873; asc (    (s;;
             5: len 1; hex 80; asc  ;;
             6: len 1; hex 84; asc  ;;
             7: len 4; hex 00000001; asc     ;;
             8: len 1; hex 01; asc  ;;
             9: len 8; hex 0000000000001fdd; asc         ;;
             10: len 4; hex 00000000; asc     ;;
             11: len 4; hex 00000000; asc     ;;
             12: len 4; hex 00000001; asc     ;;
             13: len 6; hex 5b3f9f170000; asc [?    ;;
             14: len 6; hex 5b3f9f17213e; asc [?  !>;;

            *** WE ROLL BACK TRANSACTION (1)

Answer 1

• 您不能同时使用 INSERT IGNORE 和 INSERT ON DUPLICATE KEY UPDATE。这没有意义。

• 您的整个 IF block 可以减少为一条语句:

  INSERT into userreview(cid,conid,userid,flag)
      VALUES(pcid,pconid,puserid,1)
    ON DUPLICATE KEY UPDATE flag = pflag;
  

• 因为你可以将整个过程简化为一条语句，所以实际上整个过程是多余的
• 您真的、真的确定死锁是 INSERT 语句的冲突吗？我有我的疑问。您是否检查过 SHOW ENGINE INNODB STATUS\G 的死锁部分？如果是，请发布结果。
• 关于我怀疑问题实际上只是插入语句，手册中没有任何内容描述这应该如何可能。插入语句发生死锁的唯一方法是存在间隙锁，只有在搜索条件中未使用组合索引的所有列时，才会发生唯一索引。这不是这里的情况。最后一点，这是 How to Minimize and Handle Deadlocks 的手册页.希望对您有所帮助...

问题更新后编辑:

我只能引用我已经提供的链接中给出的一个提示:

Keep transactions small and short in duration to make them less prone to collision.

特别是插入语句不是问题。整个交易花费的时间太长。