科普文：软件架构数据库系列之【MySQL死锁案例分析：间隙锁“Gap Lock”导致的死锁及解决方案 ERROR 1213 (40001): Deadlock】

[17 Nov 2003 20:22] Will Bryant
Description:
While using InnoDB tables and the repeatable read isolation level, the following sequence of events:

1. One connection does a non-insert update operation (UPDATE, SELECT ... FOR UPDATE, or DELETE) but there is no matching row, giving this connection a lock on that gap
2. A second connection does a non-insert update operation on the same gap (blocks, waiting for the gap lock)
3. The first connection then tries to do an INSERT into that gap

is incorrectly detected as a deadlock (and the first connection's transaction is rolled back).

In other words, if one connection attempts to insert into a gap that it already holds a lock on, a deadlock condition is encountered if another connection has started waiting on that gap lock in the meantime (eg. by attempting to update that index position).

This is not correct behaviour; the connection already holds the lock on that index position, so it doesn't need to re-acquire it and so there is in fact no deadlock.

How to repeat:
To set up, run:
CREATE TABLE test (pri_id INT NOT NULL PRIMARY KEY, other_data INT NOT NULL) TYPE=InnoDB;

Use two connections, one for the statements on the left, the other for the statements on the right, and use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET AUTOCOMMIT=0;" on both.

UPDATE test 
   SET other_data = 42
 WHERE (pri_id = 1);
--- should indicate that 0 rows were updated
--- we now have the lock on the gap

                                          UPDATE test
                                             SET other_data = 23
                                           WHERE (pri_id = 1);
                                          --- blocks, as it should, 
                                          --- waiting for the lock

INSERT INTO test
   SET pri_id = 1,
       other_data = 42;
--- the deadlock detector detects a
--- deadlock and rolls back the transaction

                                          --- this statement then executes
                                          --- (and updates 0 rows)

Interestingly it is necessary for the first statement on both connections to be something other than an INSERT (SELECT ... FOR UPDATE does the same thing, but everything works fine if you try and do INSERTs).  So it is specifically to do with the locking of _gaps_ for update.

Suggested fix:
The first transaction should continue successfully, the second transaction should continue blocking until the first commits or rolls back, and the second should then continue (in this example, updating one row if the first committed, or zero rows if it rolled back).

I'm not sure whether this is a problem with the deadlock detector or with the locking logic; but either way, this is not a real deadlock condition - the first connection already holds the lock, so it doesn't need to re-acquire it and so there is in fact no deadlock.

In more general terms, while waiting on a lock should be FIFO, if you already hold the lock in question you certainly shouldn't wait at all.

Further evidence for this argument is that, as above, if you do an INSERT and then an UPDATE on that inserted row, it won't deadlock - the second connection keeps waiting correctly - so it is a problem specifically with it being a gap lock.

大概意思就是：innodb表在RR隔离级别下，update空行后，插入记录会导致死锁。然后“Will Bryant”这哥们儿给MySQL提了bug（17 Nov 2003 20:22 MySQL Server: InnoDB storage engine ），建议修复“In more general terms, while waiting on a lock should be FIFO, if you already hold the lock in question you certainly shouldn't wait at all.”第一个事务应该成功执行、第二个事务继续等待即可。

可惜啊，这个情况到现在的MySQL8.4 还存在。官方还把这个状态制成“Not a Bug”。

可以看到在RC下是没有这个问题的

死锁案例：Gap Lock间隙锁的“delete/update空行”导致的死锁

在MySQL 8中可以使用SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL命令动态地修改事务的隔离级别。我们把全局和当前会话的隔离级别都改成RR，再复现上面gap导致死锁的案例。

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

死锁错误信息：1213、1205

死锁监控信息分析

可以看到update语句都会加锁，导致后面的insert都会有锁等待lock_mode X insert intention waiting。

在InnoDB存储引擎中，RR（REPEATABLE READ）隔离级别下，当一个事务T1正在读取一条记录，并且已经获取了共享锁（lock_mode X），同时另一个事务T2试图插入一个新记录，此时T2会进入插入意图锁（insert intention）等待状态。这是因为插入操作需要在可能冲突的范围内获取排他锁，而为了保持事务的RR隔离性，需要等待T1释放其共享锁。

死锁解决方法：

优化事务设计：确保长时间只读事务尽快完成，减少事务运行时间，避免阻塞插入操作。
检查死锁：如果系统中存在死锁，可以通过查看InnoDB的状态信息来识别并解决。
调整隔离级别：如果业务允许，可以考虑将隔离级别调低，例如使用RC（READ COMMITTED）隔离级别，可以减少锁等待，但需要注意可能引发的数据并发问题。
优化索引：确保插入操作能够利用索引，减少锁的范围。
使用可重复读事务模式时，可以通过在查询时使用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE来显式指定加锁方式，有针对性地加强一致性读取。

标签：1213,lock,gap,死锁,Lock,CSDN,软件架构,MySQL
From： https://blog.csdn.net/Rookie_CEO/article/details/143081467

科普文：软件架构数据库系列之【MySQL死锁案例分析：间隙锁“Gap Lock”导致的死锁及解决方案 ERROR 1213 (40001): Deadlock】

概叙

Record Lock行锁的循环等待导致的死锁

Innodb锁相关知识梳理

Gap Lock间隙锁的“delete/update空行”导致的死锁

死锁案例：Gap Lock间隙锁的“delete/update空行”导致的死锁

死锁错误信息：1213、1205

死锁监控信息分析

死锁解决方法：

相关文章

赞助商

阅读排行

科普文：软件架构数据库系列之【MySQL死锁案例分析：间隙锁“Gap Lock”导致的死锁及解决方案 ERROR 1213 (40001): Deadlock】

概叙

Record Lock行锁的循环等待导致的死锁

Innodb锁相关知识梳理

Gap Lock间隙锁的“delete/update空行”导致的死锁

死锁案例：Gap Lock间隙锁的“delete/update空行”导致的死锁

死锁错误信息 ：1213、1205

死锁监控信息分析

死锁解决方法：

相关文章

赞助商

阅读排行

死锁错误信息：1213、1205