我试图调试以下场景:有两个并发进程,向具有唯一约束条件的表中插入完全相同的行。这是在显式事务之外完成的(尽管我假设InnoDB会将其作为单个语句内部处理,带有自动提交?)
模式如下:
CREATE TABLE locks (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
lock_uid varchar(255) NOT NULL,
count smallint(6) NOT NULL,
processor_id varchar(255) DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id),
UNIQUE INDEX locks_lock_uid_unique (lock_uid)
)
如您所见,lock_uid
上有唯一索引,以防止表中存在具有相同值的多行。
正在运行的命令(为了上下文,这些命令来自于完整的通用查询日志,除了排序命令外,两个线程都没有其他语句):
线程1:
insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`)
values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')
线程2:
insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`)
values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')
这导致了以下死锁情况:
LATEST DETECTED DEADLOCK
------------------------
2017-11-07 10:46:36 0x2ac88f791700
*** (1) TRANSACTION:
TRANSACTION 6089510736, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 177584962, OS thread handle 47059008030464, query id 13109086103 ec2-34-232-58-13.compute-1.amazonaws.com 34.232.58.13 appserver update
insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`) values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6403 page no 4 n bits 176 index locks_lock_uid_unique of table `core`.`locks` trx id 6089510736 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363137; asc 111617; (total 32 bytes);
1: len 8; hex 0000000003266637; asc &f7;;
*** (2) TRANSACTION:
TRANSACTION 6089510734, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 177584971, OS thread handle 47040888903424, query id 13109086092 ec2-34-237-3-244.compute-1.amazonaws.com 34.237.3.244 appserver update
insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`) values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6403 page no 4 n bits 176 index locks_lock_uid_unique of table `core`.`locks` trx id 6089510734 lock mode S
Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363135; asc 111615; (total 32 bytes);
1: len 8; hex 0000000003266632; asc &f2;;
Record lock, heap no 105 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363135; asc 111615; (total 32 bytes);
1: len 8; hex 0000000003266634; asc &f4;;
Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363137; asc 111617; (total 32 bytes);
1: len 8; hex 0000000003266637; asc &f7;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6403 page no 4 n bits 176 index locks_lock_uid_unique of table `core`.`locks` trx id 6089510734 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363137; asc 111617; (total 32 bytes);
1: len 8; hex 0000000003266637; asc &f7;;
*** WE ROLL BACK TRANSACTION (2)
我已经阅读了类似的答案(例如MySQL锁定中的重复键错误),但我无法完全理解此情况发生了什么。解释与死锁输出不匹配。
- 为什么这个死锁会在事务外的2个插入操作中发生?
- 为什么T2在请求X之前已经持有S锁,而完全相同的T1没有持有S锁?
UNIQUE
键(其中一个是PRIMARY
)是问题的一部分。但我无法证明。你每秒执行数百个INSERTs
吗? - Rick JamesINSERT IGNORE ...
后面加上一个测试(对rows_affected
进行测试?)是否可以避免死锁,并告诉你当前的情况。 - Rick James