MySQL中插入意向锁的解决方案

2022-01-06 00:00:00 concurrency transactions locking mysql

我有一个非常简单的表格:

I have very simple table:

CREATE TABLE `d` (
    `id` int(11) DEFAULT NULL,
    UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

无记录:

select * from d;
Empty set (0,01 sec)

然后我尝试在不同的会话中打开两个事务:

Then I try to open two transactions in different sessions:

会话 #1:

begin;
Query OK, 0 rows affected (0,00 sec)

select * from d where id = 100 for update;
Empty set (0,00 sec)

会话 #2:

begin;
Query OK, 0 rows affected (0,00 sec)

select * from d where id = 700 for update;
Empty set (0,00 sec)

现在我尝试在 Session #2 和会话冻结"中插入新记录:

Now I try to insert new record in Session #2 and session "freezes":

insert into d values (700);

当我尝试在 Session #1 中执行相同操作(使用另一个 id 字段)时，它崩溃了:

And when I try to do the same (with another id field) in Session #1 it crashes:

insert into d values (100); --> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in Session #1
insert into d values (700); --> Query OK, 1 row affected (4,08 sec) in Session #2

我该如何解决死锁?InnoDB 状态为:

How can I to fix the deadlock? InnoDB status is:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-06 15:59:25 0x70000350d000
*** (1) TRANSACTION:
TRANSACTION 43567, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 123145358217216, query id 89 localhost root update
insert into d values (700)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43567 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 43568, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145357938688, query id 90 localhost root update
insert into d values (100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 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;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

推荐答案

我怀疑死锁的发生是因为 InnoDB 在处理差距"方面过于保守.请注意，100 和 700 都在未受影响的土地上的同一个模糊区域.InnoDB 不能(或至少不能)处理100"和700"不同的事实.InnoDB 想要标记单个行，但表中已经没有这些 id 的行了.

I suspect the deadlock happens because InnoDB is conservative on dealing with "gaps". Note that 100 and 700 are both in the same nebulous area of untouched land. InnoDB can't (or at least does not) deal with the fact that "100" and "700" are different. InnoDB would like to tag individual rows but there are no rows already in the table with those ids.

事务 2 可能会超时(请参阅 innodb_lock_wait_timeout).当你第二次戳事务 1 并且 #2 仍然想要锁时，InnoDB 放弃了.

Transaction 2 was probably going to timeout (see innodb_lock_wait_timeout). When you poked Transaction 1 a second time with #2 still wanting a lock, InnoDB punted and gave up.

底线:与僵局共存.当它们发生时，从 BEGIN 开始.您还发现了另一个晦涩的案例，其中发生了不必要的死锁.

Bottom Line: Live with deadlocks. When they happen, start back at the BEGIN. You have found yet-another obscure case where an unnecessary deadlock happens.

此外，我怀疑修复此案例的代码会减慢大多数其他案例的速度，并导致一系列错误，而这些错误需要多个版本才能发现和修复.

Furthermore, I suspect that fixing the code for this case would slow down most other cases, and lead to a series of errors that would take several releases to discover and fix.

相关文章