news 2026/6/10 17:16:18

mysql 死锁场景 INSERT ... ON DUPLICATE KEY UPDATE

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
mysql 死锁场景 INSERT ... ON DUPLICATE KEY UPDATE

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）
T0BEGIN;（开启事务，未提交）--
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需要）
死锁形成原因
  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显式加锁”，该死锁会完全消失（可自行验证）。
版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/6/10 15:51:43

加密PDF解析的Dify内存占用（专家级调优指南，仅限内部分享）

第一章：加密PDF解析的Dify内存占用问题综述 在使用 Dify 平台处理加密 PDF 文件的解析任务时，部分用户反馈系统出现显著的内存占用上升现象，严重时可导致服务响应延迟甚至进程崩溃。该问题主要出现在高并发或大文件批量处理场景中&#xff0c…

作者头像 李华
网站建设 2026/6/10 15:52:12

Pytest实践：Python测试技术基础知识

一、简介 在软件开发领域，确保代码的正确性和稳健性至关重要。这就是软件测试发挥作用的地方。Python 是一种通用且广泛使用的编程语言，提供了大量的工具和库来帮助测试过程。 其中，Pytest就是一个很好用的测试框架，可以在 Pyth…

作者头像 李华
网站建设 2026/6/10 6:28:23

没有中兴的智谱，被民间华强北盯上了

开源之后，市场能拼出一部“AI手机”吗？作者|王浩然编辑|古廿最近智谱的广告出现在机场广告牌，打出了一整套AI叙事的新标签：“有的AI，提供情绪价值”“智谱AI，提供商业价值”。配合上醒目的LOGO，…

作者头像 李华
网站建设 2026/6/10 6:34:00

《#{} vs ${}：MyBatis 里这俩符号，藏着性能与安全的 “生死局”》

1.多表查询多表查询和单表查询类似,只是SQL不同⽽已数据准备：对应Model:数据查询SQL:补充实体类:接⼝定义:字段与实体类属性不一致的解决方式：当数据库字段名与实体类属性名不匹配时，可通过ResultMap（自定义映射关系）或…

作者头像 李华
网站建设 2026/6/10 14:05:31

基于单片机的可变色灯泡的设计

基于单片机的可变色灯泡的设计 第一章 引言 随着智能家居产业的快速发展，传统照明设备已难以满足用户对个性化、智能化照明的需求。传统灯泡颜色固定、亮度调节不便，无法适配不同场景的照明需求，而市面上的智能灯泡多依赖复杂控制系统&#x…

作者头像 李华