MySQL INSERT 导致的死锁分析

MySQL INSERT 导致的死锁分析在MySQL的并发事务处理中死锁是一个常见且棘手的问题。许多开发者认为死锁只会在UPDATE或DELETE操作中发生但实际上INSERT语句也能导致死锁。本文将从原理出发深入剖析INSERT导致死锁的机制并通过可运行的代码示例演示其发生场景。### 一、INSERT 死锁的原理#### 1.1 MySQL的锁机制回顾MySQL的InnoDB存储引擎使用行级锁来支持高并发。当执行INSERT时InnoDB会- 对插入的行加插入意向锁Insert Intention Lock这是一种间隙锁Gap Lock的特殊形式。- 如果插入的键值唯一还会检查唯一性约束此时可能对相邻的行加共享锁S Lock来验证唯一性。死锁发生的核心条件是两个或多个事务相互等待对方释放锁形成循环等待。INSERT的死锁通常与间隙锁和唯一性检查有关。#### 1.2 INSERT 死锁的常见场景-唯一索引冲突当两个事务同时插入相同的唯一键值时它们会先加共享锁检查唯一性然后在插入时尝试加排他锁导致相互等待。-间隙锁与插入意向锁冲突事务A持有某个间隙的锁事务B尝试在该间隙插入数据需要插入意向锁但被事务A的锁阻塞形成死锁。### 二、代码演示唯一索引导致的死锁以下示例展示两个并发事务因唯一索引冲突导致的死锁。#### 2.1 环境准备首先创建测试表sql-- 创建测试表包含唯一索引CREATE TABLE test_deadlock ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) UNIQUE, data VARCHAR(100)) ENGINEInnoDB;-- 插入初始数据INSERT INTO test_deadlock (name, data) VALUES (A, original);INSERT INTO test_deadlock (name, data) VALUES (B, original);#### 2.2 死锁复现代码sql-- 事务1START TRANSACTION;-- 步骤1: 检查唯一性加共享锁INSERT INTO test_deadlock (name, data) VALUES (C, tx1);-- 步骤2: 这里等待事务2释放锁...-- 事务2在另一个会话中START TRANSACTION;-- 步骤1: 检查唯一性加共享锁INSERT INTO test_deadlock (name, data) VALUES (C, tx2);-- 步骤2: 这里等待事务1释放锁...-- 实际死锁发生事务1在步骤2尝试插入时需要排他锁但被事务2的共享锁阻塞-- 事务2在步骤2尝试插入时需要排他锁但被事务1的共享锁阻塞。-- 两者互相等待形成死锁。#### 2.3 运行结果分析执行上述代码后MySQL会检测到死锁并回滚其中一个事务。错误信息类似ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction死锁发生时InnoDB会自动选择回滚代价较小的事务通常是事务2释放其锁使事务1成功执行。### 三、代码演示间隙锁导致的死锁间隙锁死锁通常发生在范围查询与插入操作并发时。#### 3.1 环境准备使用相同表结构但插入不同数据sqlDELETE FROM test_deadlock;INSERT INTO test_deadlock (id, name, data) VALUES (1, A, data1);INSERT INTO test_deadlock (id, name, data) VALUES (5, B, data2);#### 3.2 死锁复现代码python# Python 模拟并发事务import mysql.connectorfrom concurrent.futures import ThreadPoolExecutordef transaction_1(): conn mysql.connector.connect(userroot, passwordpass, databasetest) cursor conn.cursor() try: cursor.execute(START TRANSACTION) # 步骤1: 加间隙锁范围 (1,5 cursor.execute(SELECT * FROM test_deadlock WHERE id BETWEEN 2 AND 4 FOR UPDATE) # 步骤2: 尝试插入 id3 的记录需要插入意向锁 cursor.execute(INSERT INTO test_deadlock (id, name, data) VALUES (3, C, tx1)) conn.commit() except mysql.connector.Error as e: print(fTransaction 1 error: {e}) conn.rollback() finally: cursor.close() conn.close()def transaction_2(): conn mysql.connector.connect(userroot, passwordpass, databasetest) cursor conn.cursor() try: cursor.execute(START TRANSACTION) # 步骤1: 加间隙锁范围 (1,5 cursor.execute(SELECT * FROM test_deadlock WHERE id BETWEEN 3 AND 4 FOR UPDATE) # 步骤2: 尝试插入 id3 的记录需要插入意向锁 cursor.execute(INSERT INTO test_deadlock (id, name, data) VALUES (3, D, tx2)) conn.commit() except mysql.connector.Error as e: print(fTransaction 2 error: {e}) conn.rollback() finally: cursor.close() conn.close()# 并发执行两个事务with ThreadPoolExecutor(max_workers2) as executor: future1 executor.submit(transaction_1) future2 executor.submit(transaction_2)#### 3.3 死锁原理分析- 事务1执行FOR UPDATE锁定 id 在 (1,5) 范围内的间隙包括 id2,3,4 的间隙。- 事务2执行FOR UPDATE锁定 id 在 (3,5) 范围内的间隙包括 id3,4 的间隙。- 当事务1尝试插入 id3 时需要获得该间隙的插入意向锁但事务2已经持有该间隙的锁因此事务1等待事务2。- 当事务2尝试插入 id3 时需要获得该间隙的插入意向锁但事务1已经持有该间隙的锁因此事务2等待事务1。- 形成循环等待触发死锁。### 四、如何避免 INSERT 死锁1.使用唯一键的INSERT时尽量先查询SELECT … FOR UPDATE再插入减少锁竞争。2.控制事务的粒度缩短事务执行时间减少锁持有时间。3.按固定顺序访问资源例如按id升序执行插入避免循环等待。4.使用INSERT ... ON DUPLICATE KEY UPDATE替代先查后插减少锁操作。5.监控死锁日志通过SHOW ENGINE INNODB STATUS查看死锁信息优化业务逻辑。### 五、总结MySQL中INSERT导致的死锁本质上是锁冲突的体现主要源于唯一索引检查和间隙锁的相互作用。通过本文的分析和代码示例我们可以看到- 唯一索引冲突时两个事务相互等待对方的共享锁释放形成死锁。- 间隙锁与插入意向锁冲突时两个事务同时锁定不同范围的间隙但插入时相互等待。理解这些原理后开发人员可以在设计表结构和编写并发代码时考虑锁的影响采取合理的事务隔离级别和锁策略从而有效避免死锁。在实际生产环境中死锁虽然无法完全避免但通过良好的设计和监控可以将其影响降到最低。