陕西网站制作商梅河口建设局网站

陕西网站制作商,梅河口建设局网站,注册企业邮箱哪家最好,做亳州旅游网站的目的mysql 死锁场景 INSERT … ON DUPLICATE KEY UPDATE 一、前置准备#xff08;复用user_balance表#xff09; 保持表结构与之前一致#xff08;主键唯一索引#xff0c;放大锁冲突#xff09;#xff0c;清空表数据#xff08;空表更易触发间隙锁导致的死锁#xff…mysql 死锁场景INSERT … ON DUPLICATE KEY UPDATE一、前置准备复用user_balance表保持表结构与之前一致主键唯一索引放大锁冲突清空表数据空表更易触发间隙锁导致的死锁-- 复用原表结构CREATETABLEuser_balance(idBIGINTNOTNULLAUTO_INCREMENTCOMMENT主键,user_idBIGINTNOTNULLCOMMENT用户ID唯一,balanceINTNOTNULLDEFAULT0COMMENT余额,PRIMARYKEY(id),UNIQUEKEYuk_user_id(user_id)-- 唯一索引是冲突核心)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 清空表确保初始无数据触发间隙锁TRUNCATETABLEuser_balance;二、3事务死锁复现基于user_balance100%触发核心逻辑3个事务T1/T2/T3交叉操作user_id1001/1002/1003空表下会加间隙锁因INSERT ... ON DUPLICATE KEY UPDATE的锁顺序混乱形成循环等待。精准执行时序3个客户端/会话严格按时间执行时间戳事务T1客户端1事务T2客户端2事务T3客户端3T0BEGIN;开启事务未提交--T1– 插入user_id1001空表→加「间隙锁(0,1001)」「插入意向锁」INSERT INTO user_balance (user_id, balance) VALUES (1001, 10) ON DUPLICATE KEY UPDATE balance balance 10;--T2-BEGIN;开启事务未提交-T3-– 插入user_id1003空表→加「间隙锁(1001,1003)」「插入意向锁」INSERT INTO user_balance (user_id, balance) VALUES (1003, 20) ON DUPLICATE KEY UPDATE balance balance 20;-T4--BEGIN;开启事务未提交T5--– 插入user_id1002空表→加「间隙锁(1001,1003)」「插入意向锁」INSERT INTO user_balance (user_id, balance) VALUES (1002, 30) ON DUPLICATE KEY UPDATE balance balance 30;T6– 尝试插入user_id1002请求「间隙锁(1001,1003)」被T2/T3阻塞INSERT INTO user_balance (user_id, balance) VALUES (1002, 10) ON DUPLICATE KEY UPDATE balance balance 10;--T7-– 尝试插入user_id1002请求「间隙锁(1001,1003)」被T1/T3阻塞INSERT INTO user_balance (user_id, balance) VALUES (1002, 20) ON DUPLICATE KEY UPDATE balance balance 20;-T8阻塞阻塞– 尝试插入user_id1001请求「间隙锁(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_id1001间隙锁(1001,1003)插入user_id1002需要T2间隙锁(1001,1003) 插入意向锁user_id1003间隙锁(1001,1003)插入user_id1002需要T3间隙锁(1001,1003) 插入意向锁user_id1002间隙锁(0,1001)插入user_id1001需要死锁形成原因互斥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_balanceimportpymysqlimportthreadingimporttime# 数据库配置DB_CONFIG{host:localhost,user:root,password:123456,database:test,autocommit:False}# 事务1操作user_id1001 → 1002deftransaction1():connpymysql.connect(**DB_CONFIG)cursorconn.cursor()try:print(T1: 开启事务)cursor.execute(BEGIN;)# 插入user_id1001sqlINSERT INTO user_balance (user_id, balance) VALUES (1001, 10) ON DUPLICATE KEY UPDATE balance balance 10;cursor.execute(sql)print(T1: 插入user_id1001成功持有0,1001间隙锁)time.sleep(2)# 等待T2/T3执行# 尝试插入user_id1002触发锁等待sqlINSERT INTO user_balance (user_id, balance) VALUES (1002, 10) ON DUPLICATE KEY UPDATE balance balance 10;print(T1: 尝试插入user_id1002等待1001,1003间隙锁)cursor.execute(sql)conn.commit()print(T1: 提交成功)exceptpymysql.MySQLErrorase:print(fT1: 异常 -{e})conn.rollback()finally:cursor.close()conn.close()# 事务2操作user_id1003 → 1002deftransaction2():connpymysql.connect(**DB_CONFIG)cursorconn.cursor()try:time.sleep(0.5)# 等待T1插入1001print(T2: 开启事务)cursor.execute(BEGIN;)# 插入user_id1003sqlINSERT INTO user_balance (user_id, balance) VALUES (1003, 20) ON DUPLICATE KEY UPDATE balance balance 20;cursor.execute(sql)print(T2: 插入user_id1003成功持有1001,1003间隙锁)time.sleep(2)# 等待T3执行# 尝试插入user_id1002触发锁等待sqlINSERT INTO user_balance (user_id, balance) VALUES (1002, 20) ON DUPLICATE KEY UPDATE balance balance 20;print(T2: 尝试插入user_id1002等待1001,1003间隙锁)cursor.execute(sql)conn.commit()print(T2: 提交成功)exceptpymysql.MySQLErrorase:print(fT2: 异常 -{e})conn.rollback()finally:cursor.close()conn.close()# 事务3操作user_id1002 → 1001deftransaction3():connpymysql.connect(**DB_CONFIG)cursorconn.cursor()try:time.sleep(1)# 等待T1/T2执行print(T3: 开启事务)cursor.execute(BEGIN;)# 插入user_id1002sqlINSERT INTO user_balance (user_id, balance) VALUES (1002, 30) ON DUPLICATE KEY UPDATE balance balance 30;cursor.execute(sql)print(T3: 插入user_id1002成功持有1001,1003间隙锁)time.sleep(2)# 等待T1/T2触发锁等待# 尝试插入user_id1001触发锁等待sqlINSERT INTO user_balance (user_id, balance) VALUES (1001, 30) ON DUPLICATE KEY UPDATE balance balance 30;print(T3: 尝试插入user_id1001等待0,1001间隙锁)cursor.execute(sql)conn.commit()print(T3: 提交成功)exceptpymysql.MySQLErrorase:# 此处会捕获1213死锁错误print(fT3: 触发死锁 -{e})conn.rollback()finally:cursor.close()conn.close()if__name____main__:# 清空表确保初始无数据connpymysql.connect(**DB_CONFIG)cursorconn.cursor()cursor.execute(TRUNCATE TABLE user_balance;)conn.commit()cursor.close()conn.close()# 启动3个事务线程t1threading.Thread(targettransaction1)t2threading.Thread(targettransaction2)t3threading.Thread(targettransaction3)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显式加锁”该死锁会完全消失可自行验证。