我有一个具有复合唯一键（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）执行时，当并发事务要在同一行上执行时，有时会发生死锁，虽然有惟一索引，但仍然面临死锁问题。有什么建议可以避免僵局吗？
僵局result:-
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)
1条答案
按热度按时间eanckbw91#
你不能合并
INSERT IGNORE以及INSERT ON DUPLICATE KEY UPDATE. 这没有道理。你的全部
IF块可以简化为一个语句：因为可以将整个过程简化为一个语句，所以实际上整个过程是多余的
你真的，真的确定死锁在碰撞吗
INSERT声明？我有我的疑虑。你检查过这本书的死锁部分了吗SHOW ENGINE INNODB STATUS\G? 如果是，公布结果。关于我对问题实际上只是insert语句的怀疑，手册中没有任何描述这应该如何可能的内容。insert语句出现死锁的唯一方法是存在间隙锁，只有在搜索条件中没有使用组合索引的所有列时，间隙锁才会出现在唯一索引中。但事实并非如此。最后一点，这里是如何最小化和处理死锁的手册页面。希望对你有帮助。。。
问题更新后编辑：
我只能引用我已经提供的链接中给出的提示之一：
使事务保持小而短的持续时间，以使它们不易发生冲突。
特别是insert语句没有问题。整个交易花的时间太长了。