企业官网建设流程全解析

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个客户端/会话严格按时间执行）

三、锁冲突核心分析（基于user_balance）

死锁形成原因

1. 互斥：InnoDB的X锁/间隙锁是排他的，同一间隙锁只能被一个事务持有；
2. 持有并等待：T1持有(0,1001)锁，等待(1001,1003)锁；T3持有(1001,1003)锁，等待(0,1001)锁；
3. 不可剥夺：InnoDB锁只能由事务主动释放（提交/回滚），无法强制剥夺；
4. 循环等待：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表）

1. INSERT ... ON DUPLICATE KEY UPDATE在RR隔离级别下，对空表的唯一索引会加间隙锁，而非仅记录锁；
2. 3个事务交叉操作user_id的不同间隙（1001/1002/1003），因锁顺序混乱形成循环等待，触发死锁；
3. 若改用“拆分INSERT/UPDATE”或“SELECT … FOR UPDATE显式加锁”，该死锁会完全消失（可自行验证）。