mysql 死锁场景
INSERT … ON DUPLICATE KEY UPDATE
一、前置准备（复用user_balance表）
保持表结构与之前一致（主键+唯一索引，放大锁冲突），清空表数据（空表更易触发间隙锁导致的死锁）：
-- 复用原表结构CREATETABLE`user_balance`(`id`BIGINTNOTNULLAUTO_INCREMENTCOMMENT'主键',`user_id`BIGINTNOTNULLCOMMENT'用户ID（唯一）',`balance`INTNOTNULLDEFAULT0COMMENT'余额',PRIMARYKEY(`id`),UNIQUEKEY`uk_user_id`(`user_id`)-- 唯一索引是冲突核心)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 清空表（确保初始无数据，触发间隙锁）TRUNCATETABLEuser_balance;二、3事务死锁复现（基于user_balance，100%触发）
核心逻辑
3个事务（T1/T2/T3）交叉操作user_id=1001/1002/1003（空表下会加间隙锁），因INSERT ... ON DUPLICATE KEY UPDATE的锁顺序混乱，形成循环等待。
精准执行时序（3个客户端/会话严格按时间执行）
| 时间戳 | 事务T1（客户端1） | 事务T2（客户端2） | 事务T3（客户端3） |
|---|---|---|---|
| T0 | BEGIN;（开启事务，未提交） | - | - |
| T1 | – 插入user_id=1001，空表→加「间隙锁(0,1001)」+「插入意向锁」 INSERT INTO user_balance (user_id, balance) VALUES (1001, 10) ON DUPLICATE KEY UPDATE balance = balance + 10; | - | - |
| T2 | - | BEGIN;（开启事务，未提交） | - |
| T3 | - | – 插入user_id=1003，空表→加「间隙锁(1001,1003)」+「插入意向锁」 INSERT INTO user_balance (user_id, balance) VALUES (1003, 20) ON DUPLICATE KEY UPDATE balance = balance + 20; | - |
| T4 | - | - | BEGIN;（开启事务，未提交） |
| T5 | - | - | – 插入user_id=1002，空表→加「间隙锁(1001,1003)」+「插入意向锁」 INSERT INTO user_balance (user_id, balance) VALUES (1002, 30) ON DUPLICATE KEY UPDATE balance = balance + 30; |
| T6 | – 尝试插入user_id=1002，请求「间隙锁(1001,1003)」，被T2/T3阻塞 INSERT INTO user_balance (user_id, balance) VALUES (1002, 10) ON DUPLICATE KEY UPDATE balance = balance + 10; | - | - |
| T7 | - | – 尝试插入user_id=1002，请求「间隙锁(1001,1003)」，被T1/T3阻塞 INSERT INTO user_balance (user_id, balance) VALUES (1002, 20) ON DUPLICATE KEY UPDATE balance = balance + 20; | - |
| T8 | （阻塞） | （阻塞） | – 尝试插入user_id=1001，请求「间隙锁(0,1001)」，被T1阻塞 INSERT INTO user_balance (user_id, balance) VALUES (1001, 30) ON DUPLICATE KEY UPDATE balance = balance + 30; |
| T9 | 🔴 数据库检测死锁，回滚T3（代价最小） | （T2执行成功） | （T3报错：1213 - Deadlock found when trying to get lock） |
三、锁冲突核心分析（基于user_balance）
| 事务 | 已持有锁（uk_user_id唯一索引） | 等待的锁（uk_user_id唯一索引） |
|---|---|---|
| T1 | 间隙锁(0,1001) + 插入意向锁（user_id=1001） | 间隙锁(1001,1003)（插入user_id=1002需要） |
| T2 | 间隙锁(1001,1003) + 插入意向锁（user_id=1003） | 间隙锁(1001,1003)（插入user_id=1002需要） |
| T3 | 间隙锁(1001,1003) + 插入意向锁（user_id=1002） | 间隙锁(0,1001)（插入user_id=1001需要） |
死锁形成原因
- 互斥：InnoDB的X锁/间隙锁是排他的，同一间隙锁只能被一个事务持有；
- 持有并等待：T1持有(0,1001)锁，等待(1001,1003)锁；T3持有(1001,1003)锁，等待(0,1001)锁；
- 不可剥夺：InnoDB锁只能由事务主动释放（提交/回滚），无法强制剥夺；
- 循环等待：T1→等待T2/T3的(1001,1003)锁 → T3→等待T1的(0,1001)锁，形成闭环。
四、代码级复现（Python + pymysql，基于user_balance）
importpymysqlimportthreadingimporttime# 数据库配置DB_CONFIG={"host":"localhost","user":"root","password":"123456","database":"test","autocommit":False}# 事务1：操作user_id=1001 → 1002deftransaction1():conn=pymysql.connect(**DB_CONFIG)cursor=conn.cursor()try:print("T1: 开启事务")cursor.execute("BEGIN;")# 插入user_id=1001sql="INSERT INTO user_balance (user_id, balance) VALUES (1001, 10) ON DUPLICATE KEY UPDATE balance = balance + 10;"cursor.execute(sql)print("T1: 插入user_id=1001成功（持有0,1001间隙锁）")time.sleep(2)# 等待T2/T3执行# 尝试插入user_id=1002（触发锁等待）sql="INSERT INTO user_balance (user_id, balance) VALUES (1002, 10) ON DUPLICATE KEY UPDATE balance = balance + 10;"print("T1: 尝试插入user_id=1002（等待1001,1003间隙锁）")cursor.execute(sql)conn.commit()print("T1: 提交成功")exceptpymysql.MySQLErrorase:print(f"T1: 异常 -{e}")conn.rollback()finally:cursor.close()conn.close()# 事务2：操作user_id=1003 → 1002deftransaction2():conn=pymysql.connect(**DB_CONFIG)cursor=conn.cursor()try:time.sleep(0.5)# 等待T1插入1001print("T2: 开启事务")cursor.execute("BEGIN;")# 插入user_id=1003sql="INSERT INTO user_balance (user_id, balance) VALUES (1003, 20) ON DUPLICATE KEY UPDATE balance = balance + 20;"cursor.execute(sql)print("T2: 插入user_id=1003成功（持有1001,1003间隙锁）")time.sleep(2)# 等待T3执行# 尝试插入user_id=1002（触发锁等待）sql="INSERT INTO user_balance (user_id, balance) VALUES (1002, 20) ON DUPLICATE KEY UPDATE balance = balance + 20;"print("T2: 尝试插入user_id=1002（等待1001,1003间隙锁）")cursor.execute(sql)conn.commit()print("T2: 提交成功")exceptpymysql.MySQLErrorase:print(f"T2: 异常 -{e}")conn.rollback()finally:cursor.close()conn.close()# 事务3：操作user_id=1002 → 1001deftransaction3():conn=pymysql.connect(**DB_CONFIG)cursor=conn.cursor()try:time.sleep(1)# 等待T1/T2执行print("T3: 开启事务")cursor.execute("BEGIN;")# 插入user_id=1002sql="INSERT INTO user_balance (user_id, balance) VALUES (1002, 30) ON DUPLICATE KEY UPDATE balance = balance + 30;"cursor.execute(sql)print("T3: 插入user_id=1002成功（持有1001,1003间隙锁）")time.sleep(2)# 等待T1/T2触发锁等待# 尝试插入user_id=1001（触发锁等待）sql="INSERT INTO user_balance (user_id, balance) VALUES (1001, 30) ON DUPLICATE KEY UPDATE balance = balance + 30;"print("T3: 尝试插入user_id=1001（等待0,1001间隙锁）")cursor.execute(sql)conn.commit()print("T3: 提交成功")exceptpymysql.MySQLErrorase:# 此处会捕获1213死锁错误print(f"T3: 触发死锁 -{e}")conn.rollback()finally:cursor.close()conn.close()if__name__=="__main__":# 清空表，确保初始无数据conn=pymysql.connect(**DB_CONFIG)cursor=conn.cursor()cursor.execute("TRUNCATE TABLE user_balance;")conn.commit()cursor.close()conn.close()# 启动3个事务线程t1=threading.Thread(target=transaction1)t2=threading.Thread(target=transaction2)t3=threading.Thread(target=transaction3)t1.start()t2.start()t3.start()t1.join()t2.join()t3.join()print("所有线程执行完毕")五、死锁日志验证（基于user_balance）
执行代码后，通过SHOW ENGINE INNODB STATUS;查看死锁日志，核心片段如下：
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-12-16 16:00:00 0x7f8d12345678 *** (1) TRANSACTION: TRANSACTION 789012, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 20, OS thread handle 140234567890123, query id 900 localhost root updating INSERT INTO user_balance (user_id, balance) VALUES (1002, 10) ON DUPLICATE KEY UPDATE balance = balance + 10 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 99 page no 4 n bits 72 index uk_user_id of table `test`.`user_balance` trx id 789012 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;; // 间隙锁(1001,1003) *** (2) TRANSACTION: TRANSACTION 789013, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 21, OS thread handle 140234567890124, query id 901 localhost root updating INSERT INTO user_balance (user_id, balance) VALUES (1002, 20) ON DUPLICATE KEY UPDATE balance = balance + 20 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 99 page no 4 n bits 72 index uk_user_id of table `test`.`user_balance` trx id 789013 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;; // 持有(1001,1003)间隙锁 *** (3) TRANSACTION: TRANSACTION 789014, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 22, OS thread handle 140234567890125, query id 902 localhost root updating INSERT INTO user_balance (user_id, balance) VALUES (1001, 30) ON DUPLICATE KEY UPDATE balance = balance + 30 *** (3) HOLDS THE LOCK(S): RECORD LOCKS space id 99 page no 4 n bits 72 index uk_user_id of table `test`.`user_balance` trx id 789014 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;; // 持有(1001,1003)间隙锁 *** (3) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 99 page no 4 n bits 72 index uk_user_id of table `test`.`user_balance` trx id 789014 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 80000000000003e9; asc ;; // 间隙锁(0,1001) *** WE ROLL BACK TRANSACTION (3)六、关键结论（基于user_balance表）
INSERT ... ON DUPLICATE KEY UPDATE在RR隔离级别下，对空表的唯一索引会加间隙锁，而非仅记录锁；- 3个事务交叉操作
user_id的不同间隙（1001/1002/1003），因锁顺序混乱形成循环等待，触发死锁； - 若改用“拆分INSERT/UPDATE”或“SELECT … FOR UPDATE显式加锁”，该死锁会完全消失（可自行验证）。
