MySQL死锁分析

2025-05-21 18:32:00
丁国栋
原创 8
摘要：MySQL死锁案例分析和解决。

MySQL 死锁分析

使用 show engine innodb status;发现存在死锁。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-05-21 11:23:05 140245465437760
*** (1) TRANSACTION:
TRANSACTION 27804122, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 7980727, OS thread handle 140245264070208, query id 1624996326 10.42.8.24 zenta0Abm1n update
REPLACE INTO `zt_userview` SET `account` = 'zhaixiaojian',`programs` = '',`products` = '',`projects` = '',`sprints` = ''
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 762 page no 52 n bits 80 index PRIMARY of table `zentaopaas`.`zt_userview` trx id 27804122 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;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 762 page no 52 n bits 80 index PRIMARY of table `zentaopaas`.`zt_userview` trx id 27804122 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;;
*** (2) TRANSACTION:
TRANSACTION 27804123, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 7980728, OS thread handle 140245161330240, query id 1624996327 10.42.8.24 zenta0Abm1n update
REPLACE INTO `zt_userview` SET `account` = 'lipengrun',`programs` = '',`products` = '116,191',`projects` = '1304,1653,1357,1361,1351,1369,1368,1306,2104,1307,1370',`sprints` = '1655,1656,2105,2387,2388,2922,4005,4104'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 762 page no 52 n bits 80 index PRIMARY of table `zentaopaas`.`zt_userview` trx id 27804123 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;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 762 page no 52 n bits 80 index PRIMARY of table `zentaopaas`.`zt_userview` trx id 27804123 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;;
*** WE ROLL BACK TRANSACTION (2)

MySQL InnoDB 死锁原因分析

这个死锁案例涉及两个并发事务都执行 REPLACE INTO 操作导致的死锁。以下是详细分析：

死锁发生过程

  1. 事务1 (27804122) 和 事务2 (27804123) 几乎同时执行 REPLACE INTO 语句

    • 事务1更新 account = 'zhaixiaojian' 的记录

    • 事务2更新 account = 'lipengrun' 的记录

  2. 锁获取情况：

    • 两个事务都持有 lock_mode X (排他锁) 在 supremum 记录上

    • 同时都在等待获取 insert intention (插入意向锁)

  3. 死锁形成：

    • 事务1持有X锁，等待插入意向锁

    • 事务2持有X锁，等待插入意向锁

    • 形成互相等待的循环依赖，导致死锁

根本原因

  1. REPLACE INTO 的工作机制：

    • 实际上是先DELETE(如果记录存在)再INSERT

    • 这个操作需要获取多种锁，增加了死锁概率

  2. supremum 记录锁：

    • supremum是索引中的伪记录，表示比索引中任何实际值都大的值

    • 两个事务都锁定了这个伪记录，导致冲突

  3. 插入意向锁冲突：

    • 插入意向锁是一种间隙锁，允许多个事务在同一个间隙中插入不冲突的记录

    • 但在某些情况下(如REPLACE操作)会产生冲突

解决方案建议

  1. 避免使用 REPLACE INTO：

    • 改用 INSERT ... ON DUPLICATE KEY UPDATE 语法

    • 这种语法不会先删除记录，减少了锁冲突

  2. 添加事务隔离控制：

    START TRANSACTION;
INSERT INTO `zt_userview` (...) VALUES (...)
AS new
ON DUPLICATE KEY UPDATE
`programs` = new.`programs`,
`products` = new.`products`,
`projects` = new.`projects`,
`sprints` = new.`sprints`;
COMMIT;

  3. 优化索引设计：

    • 确保 account 字段有合适的索引

    • 减少锁的范围

  4. 应用层重试机制：

    • 捕获死锁异常并自动重试操作

  5. 批量操作优化：

    • 如果可能，合并多个操作为一个批量操作

这种死锁在并发REPLACE操作中较为常见，通过改用ON DUPLICATE KEY UPDATE通常可以显著减少死锁发生概率。

在MySQL中，使用REPLACE INTO ... SET ... 语句有什么风险？

MySQL中REPLACE INTO ... SET ... 语句的风险分析

REPLACE INTO ... SET ... 是MySQL中一种特殊的语法，它结合了INSERT和UPDATE的功能，但使用时存在一些潜在风险：

主要风险

  1. 自动删除后插入：

    • REPLACE INTO实际上是先删除匹配的行（如果存在），然后插入新行

    • 这会导致隐式的DELETE操作，可能触发DELETE触发器

  2. 自增ID问题：

    • 如果表有自增主键，替换操作会导致新行获得新的自增ID

    • 这可能破坏外键关系或导致ID不连续

  3. 性能影响：

    • 对于大表，删除和插入操作比单纯更新更消耗资源

    • 需要重建索引，影响性能

  4. 并发问题：

    • 在高并发环境下，可能导致死锁或竞争条件

    • 因为实际执行的是DELETE+INSERT而非原子操作

  5. 触发器执行：

    • 会触发DELETE和INSERT触发器，而不仅仅是UPDATE触发器

    • 可能导致意外的副作用

  6. 数据丢失风险：

    • 如果SET子句不包含所有列值，未指定的列将被设置为默认值（可能丢失原有数据）

替代方案建议

  1. 使用INSERT ... ON DUPLICATE KEY UPDATE：

    • 更高效，只更新冲突的记录

    • 不会删除原有记录

  2. 明确使用UPDATE：

    • 当确定记录存在时，直接使用UPDATE更安全

  3. 事务控制：

    • 如果需要原子性操作，考虑使用显式事务

使用场景

REPLACE INTO最适合用于：

  • 简单的键值存储

  • 不关心自增ID变化的场景

  • 需要完全替换整行数据的场合

在大多数现代应用中，ON DUPLICATE KEY UPDATE通常是更好的选择。

假设表的结构是

CREATE TABLE `zt_userview` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `account` char(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `programs` longtext COLLATE utf8mb4_general_ci,
  `sprints` longtext COLLATE utf8mb4_general_ci,
  `products` longtext COLLATE utf8mb4_general_ci,
  `projects` longtext COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account` (`account`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 。

使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 改写 SQL 语句 REPLACE INTO `zt_userview` SET `account` = 'lipengrun',`programs` = '',`products` = '116,191',`projects` = '1304,1653,1357,1361,1351,1369,1368,1306,2104,1307,1370',`sprints` = '1655,1656,2105,2387,2388,2922,4005,4104'

INSERT INTO `zt_userview`
  (`account`, `programs`, `products`, `projects`, `sprints`)
VALUES
  ('dingguodong', '', '116,191', '1304,1653,1357,1361,1351,1369,1368,1306,2104,1307,1370', '1655,1656,2105,2387,2388,2922,4005,4104')
AS new
ON DUPLICATE KEY UPDATE
  `programs` = new.`programs`,
  `products` = new.`products`,
  `projects` = new.`projects`,
  `sprints` = new.`sprints`;

或者先查询account是否存在，如果不存在则插入，如果存在则更新。

注意：在选择某个方案时需要尽可能选择开销小的。比如“先查询account是否存在，如果不存在则插入，如果存在则更新” 和 “先按照account删除，再插入”，后者开销更大些。

InnoDB作为事务型存储引擎，必须保证ACID特性：

  • 原子性 (Atomicity): 每个操作要么完全执行，要么完全不执行

  • 一致性 (Consistency): 数据库从一个一致状态变为另一个一致状态

  • 隔离性 (Isolation): 并发事务互不干扰

  • 持久性 (Durability): 提交的事务永久保存

即使简单查询也需要这些保障，因此必须放在事务中执行，所以InnoDB引擎的每一个查询暗含事务，每个查询都是事务的。

发表评论
博客分类