MySQL 死锁案例分析：5 种常见场景与解决方案的详细使用教程

大, 虾 4 月 26, 2026 0 0

MySQL 死锁案例分析：5 种常见场景与解决方案的详细使用教程

引言：死锁是并发编程的噩梦

死锁是 MySQL 数据库中最棘手的问题之一。一个死锁可能导致整个系统瘫痪，业务中断。

根据我们的统计，生产环境中平均每 1000 个请求就有 1-2 个死锁。今天这篇教程将通过 5 个真实场景，带你彻底掌握死锁的原理和解决方案。

第一章：死锁原理

1.1 什么是死锁？

死锁（Deadlock）：
两个或多个事务在执行过程中，因争夺资源而造成的一种
互相等待的现象，若无外力作用，它们都将无法推进下去。

典型场景：
事务 A: 持有锁 1 → 等待锁 2
事务 B: 持有锁 2 → 等待锁 1

结果：互相等待，形成死锁！

1.2 MySQL 死锁检测机制

InnoDB 死锁检测：

等待图（Wait-for Graph）
节点：事务
边：等待关系
检测：查找循环依赖



死锁检测算法
每次加锁失败时触发
构建等待图
检测循环
选择牺牲者（Rollback）



死锁处理策略
回滚一个事务（牺牲者）
重新执行
返回死锁错误码：1213

1.3 锁类型

InnoDB 锁类型：


共享锁（Shared Lock / S Lock）
读锁，允许其他读
阻塞写操作
SELECT ... LOCK IN SHARE MODE



排他锁（Exclusive Lock / X Lock）
写锁，独占资源
阻塞读和写
INSERT, UPDATE, DELETE



间隙锁（Gap Lock）
锁定间隙，防止幻读
BETWEEN, IN, >, <



临键锁（Next-Key Lock）
记录锁 + 间隙锁
防止插入和修改

第二章：5 种常见死锁场景

场景 1：不同顺序更新多行记录

-- 场景描述：
-- 两个事务按不同顺序更新相同的两条记录

-- 事务 A（按 ID 顺序）
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- ⚠️ 死锁！
COMMIT;

-- 事务 B（按 ID 逆序）
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- ⚠️ 死锁！
COMMIT;

-- 死锁过程：
-- 事务 A：持有 id=1 的锁 → 等待 id=2 的锁
-- 事务 B：持有 id=2 的锁 → 等待 id=1 的锁
-- 结果：相互等待，形成死锁！

-- ✅ 解决方案 1：统一更新顺序
-- 所有事务都按 ID 升序或降序更新

START TRANSACTION;
-- 按 ID 排序后更新
UPDATE accounts SET balance = balance - 100 
WHERE id IN (1, 2) 
ORDER BY id ASC;
COMMIT;

-- ✅ 解决方案 2：使用锁提示（不推荐）
START TRANSACTION;
LOCK TABLES accounts WRITE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UNLOCK TABLES;
COMMIT;

-- ✅ 解决方案 3：重试机制（推荐）
function transfer(fromId, toId, amount) {
    let retries = 5;
    while (retries > 0) {
        try {
            conn.beginTransaction();
            
            // 按固定顺序查询和更新
            const accounts = await query(
                'SELECT * FROM accounts WHERE id IN (?, ?) FOR UPDATE',
                [fromId, toId].sort()  // 确保顺序一致
            );
            
            if (accounts[0].balance >= amount) {
                await query(
                    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
                    [amount, fromId]
                );
                await query(
                    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
                    [amount, toId]
                );
                conn.commit();
                return true;
            }
            
            conn.rollback();
            return false;
        } catch (error) {
            if (error.errno === 1213) {  // 死锁错误码
                retries--;
                await sleep(100 * (5 - retries));  // 指数退避
                continue;
            }
            conn.rollback();
            throw error;
        }
    }
    throw new Error('Transfer failed after retries');
}

场景 2：索引缺失导致的间隙锁

-- 场景描述：
-- 没有索引的 UPDATE 语句会锁定整个表

-- ❌ 错误示例（导致死锁）
-- 表结构
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,  -- 没有索引！
    status VARCHAR(20),
    amount DECIMAL(10,2)
);

-- 事务 A：删除 order_id=10 的记录
START TRANSACTION;
DELETE FROM orders WHERE user_id = 100;  -- 全表扫描，间隙锁
ROLLBACK;  -- 释放锁

-- 事务 B：更新 user_id=100 的记录
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE user_id = 100;  -- 全表扫描，间隙锁
-- ⚠️ 死锁！

-- 原因：没有索引时，UPDATE/DELETE 会锁定整个表的所有记录

-- ✅ 解决方案：添加索引

-- 方案 1：添加业务索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 方案 2：添加覆盖索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 验证索引使用
EXPLAIN DELETE FROM orders WHERE user_id = 100;
-- type: ref 或 range（使用了索引）
-- rows: 1-100（只扫描需要的行）

-- 如果没有索引：
-- type: ALL（全表扫描）
-- rows: 1000000（扫描所有行）

场景 3：自增 ID 插入死锁

-- 场景描述：
-- 并发插入自增 ID 时可能产生间隙锁死锁

-- 表结构
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 事务 A
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('user1', 'a@b.com');
-- 锁：插入位置（间隙锁）

-- 事务 B
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('user2', 'c@d.com');
-- ⚠️ 等待事务 A 的插入位置

-- 事务 A 提交
COMMIT;

-- 事务 B 继续
-- 但如果顺序相反，可能产生死锁

-- ✅ 解决方案 1：减少并发插入
-- 使用批量插入
INSERT INTO users (username, email) 
VALUES 
    ('user1', 'a@b.com'),
    ('user2', 'c@d.com'),
    ('user3', 'e@f.com');

-- ✅ 解决方案 2：使用 UUID 作为主键
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- ✅ 解决方案 3：使用序列
CREATE SEQUENCE user_seq START 1;
INSERT INTO users (id, username, email) 
VALUES (NEXTVAL('user_seq'), 'user1', 'a@b.com');

-- ✅ 解决方案 4：分表写入
-- 将一个大表分成多个表
-- users_0, users_1, ... users_15
-- 使用用户 ID 哈希分散插入

场景 4：外键约束导致死锁

-- 场景描述：
-- 外键约束在删除/插入时可能导致死锁

-- 表结构
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 事务 A：删除部门
START TRANSACTION;
DELETE FROM departments WHERE id = 1;
-- 等待检查外键约束

-- 事务 B：插入员工到该部门
START TRANSACTION;
INSERT INTO employees (name, dept_id) VALUES ('Alice', 1);
-- ⚠️ 死锁！

-- ✅ 解决方案 1：延迟检查
ALTER TABLE employees 
ADD CONSTRAINT fk_dept 
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE NO ACTION 
ON UPDATE CASCADE;

-- ✅ 解决方案 2：先删除子表记录
START TRANSACTION;
-- 先删除子表数据
DELETE FROM employees WHERE dept_id = 1;
-- 再删除父表数据
DELETE FROM departments WHERE id = 1;
COMMIT;

-- ✅ 解决方案 3：使用触发器
DELIMITER $$
CREATE TRIGGER check_delete_before 
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
    DECLARE child_count INT;
    SELECT COUNT(*) INTO child_count 
    FROM employees WHERE dept_id = OLD.id;
    
    IF child_count > 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Cannot delete department with employees';
    END IF;
END$$
DELIMITER ;

-- ✅ 解决方案 4：软删除
ALTER TABLE departments ADD COLUMN is_deleted TINYINT DEFAULT 0;

UPDATE departments SET is_deleted = 1 WHERE id = 1;
-- 物理删除由定时任务完成

场景 5：事务超时导致死锁

-- 场景描述：
-- 事务超时时间过短，频繁回滚导致死锁

-- 配置问题
SET innodb_lock_wait_timeout = 1;  -- 1 秒太短！
SET innodb_deadlock_detect = 0;    -- 禁用死锁检测！

-- ❌ 错误配置导致的问题
-- 事务 A: 持有锁 → 等待 → 1 秒后超时回滚
-- 事务 B: 持有锁 → 等待 → 1 秒后超时回滚
-- 结果：频繁死锁和回滚

-- ✅ 解决方案：合理的超时设置

-- 方案 1：增加超时时间
SET innodb_lock_wait_timeout = 50;  -- 50 秒

-- 方案 2：启用死锁检测
SET innodb_deadlock_detect = ON;

-- 方案 3：监控超时
-- 查询死锁信息
SHOW ENGINE INNODB STATUS\G

-- 查看最近死锁
SELECT * FROM information_schema.innodb_lock_waits;

-- 方案 4：优化长事务
-- 缩短事务时间
START TRANSACTION;
-- 快速操作
UPDATE users SET name = 'Alice' WHERE id = 1;
INSERT INTO logs VALUES ('updated');
COMMIT;

-- 避免在事务中调用外部 API
START TRANSACTION;
-- ❌ 不要在事务中：
-- - 调用 HTTP 请求
-- - 读写文件
-- - 执行耗时操作

-- ✅ 方案 5：分批处理
-- 分批更新大量数据
FOR i IN 1..1000 LOOP
    UPDATE large_table SET status = 1 
    WHERE id >= (i-1)*1000 AND id < i*1000;
    COMMIT;
    sleep(0.1);  -- 让其他事务执行
END LOOP;

第三章：死锁诊断

3.1 死锁诊断工具

-- 1. 查看当前锁状态
SELECT * FROM information_schema.innodb_locks\G

-- 2. 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits\G

-- 3. 查看事务状态
SELECT * FROM information_schema.innodb_trx\G

-- 4. 查看最近死锁
SELECT 
    trx_id,
    trx_state,
    trx_query,
    trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE trx_started < DATE_SUB(NOW(), INTERVAL 1 MINUTE);

-- 5. 查看死锁日志
SELECT 
    deadlock_id,
    deadlock_time,
    deadlock_info
FROM mysql.general_log 
WHERE command_type = 'Query' 
AND argument LIKE '%DEADLOCK%';

-- 6. 启用死锁日志记录
-- 在 my.cnf 中配置
[mysqld]
innodb_print_all_deadlocks = 1

3.2 死锁信息分析

死锁日志示例：
------------------------
TRANSACTION 0 123456789
TRIED TO ROLLBACK HANDLE...
...
LATEST DETECTED DEADLOCK
------------------------
1 transaction in database
Deadlock found when trying to get lock...

*** (1) TRANSACTION:
TRANSACTION 123456789, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 1234567890
query id 000001 updating

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

*** (2) HOLDING THE LOCK:
TRANSACTION 123456788, ACTIVE 0 sec starting index read
MySQL thread id 9
query id 000002 updating

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS lock no 0x6f lock mode X locks rec but not p
...
*** WE ROLLBACK TRANSACTION (2)

第四章：预防策略

4.1 编码规范

-- ✅ 最佳实践

-- 1. 统一锁顺序
-- 所有事务按相同顺序访问资源
-- ORDER BY id ASC 或 DESC

-- 2. 使用索引
-- 确保 WHERE 条件使用索引
CREATE INDEX idx_balance_id ON accounts(id);
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 3. 小事务
-- 事务尽量短，快速提交
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
INSERT INTO logs VALUES ('transfer');
COMMIT;

-- 4. 避免锁升级
-- 避免大事务锁定过多行
-- 分批处理大量更新

-- 5. 使用隔离级别
-- 降低隔离级别减少锁
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 6. 索引优化
-- 覆盖索引减少回表
CREATE INDEX idx_covering ON orders(user_id, status, amount);

4.2 监控告警

-- 监控死锁数量
CREATE TABLE deadlock_stats (
    date DATE,
    deadlock_count INT,
    avg_lock_wait_ms INT,
    PRIMARY KEY (date)
);

-- 定时任务收集
INSERT INTO deadlock_stats 
SELECT 
    CURDATE(),
    COUNT(*),
    AVG(lock_wait_time)
FROM information_schema.innodb_lock_waits
GROUP BY 1;

-- 告警阈值
-- 1 小时内死锁 > 100 次 → 告警
-- 平均锁等待 > 1 秒 → 告警
-- 死锁率 > 1% → 告警

4.3 重试策略

```python

死锁重试示例

import pymysql
import time
import random

class DeadlockRetry:
def __init__(self, max_retries=5, base_delay=0.1):
self.max_retries = max_retries
self.base_delay = base_delay

def execute_with_retry(self, conn, operation, *args):
for attempt in range(self.max_retries):
try:
with conn.cursor() as cursor:
cursor.execute(operation, args)
conn.commit()
return True
except pymysql.Error as e:
if e.args[0] == 1213: # Deadlock found
delay = self.base_delay * (2 ** attempt)
time.sleep(delay + random.random() * 0.1)
conn.rollback()
continue
else:
raise
raise Exception("Max retries exceeded")

# 使用示例
def transfer_money(self, conn, from_id, to_id, amount):
self.execute_with_retry(
conn,
"""
UPDATE accounts SET balance = balance - %s
WHERE id = %s AND balance >= %s
""",
(amount, from_id, amount)
)
self.execute_with_retry(
conn,
"""
UPDATE accounts SET balance = balance + %s
WHERE id = %s
""",
(amount, to_id)
)
conn.commit()
```

总结：死锁处理最佳实践

通过正确的策略：

预防措施：

✅ 统一访问顺序
✅ 使用索引
✅ 小事务快速提交
✅ 避免锁升级

监控方案：

✅ 监控死锁数量
✅ 监控锁等待时间
✅ 告警机制

处理策略：

✅ 自动重试（指数退避）
✅ 快速回滚
✅ 死锁日志分析

掌握这些技巧，让你的 MySQL 应用告别死锁！🚀

---

参考资源：

[MySQL 死锁文档](https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html)
[InnoDB 锁机制](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html)
[性能优化指南](https://dev.mysql.com/doc/refman/8.0/en/innodb-performance.html)
[隔离级别](https://dev.mysql.com/doc/refman/8.0/en/innodb-isolation-levels.html)

打赏赞

MySQL 死锁案例分析：5 种常见场景与解决方案的详细使用教程

MySQL 死锁案例分析：5 种常见场景与解决方案的详细使用教程

引言：死锁是并发编程的噩梦

第一章：死锁原理

1.1 什么是死锁？

1.2 MySQL 死锁检测机制

1.3 锁类型

第二章：5 种常见死锁场景

场景 1：不同顺序更新多行记录

场景 2：索引缺失导致的间隙锁

场景 3：自增 ID 插入死锁

场景 4：外键约束导致死锁

场景 5：事务超时导致死锁

第三章：死锁诊断

3.1 死锁诊断工具

3.2 死锁信息分析

第四章：预防策略

4.1 编码规范

4.2 监控告警

4.3 重试策略

死锁重试示例

总结：死锁处理最佳实践

标签

发表评论

取消回复

Recent Posts

Recent Comments

Archives

Categories

MySQL 死锁案例分析：5 种常见场景与解决方案的详细使用教程

引言：死锁是并发编程的噩梦

第一章：死锁原理

1.1 什么是死锁？

1.2 MySQL 死锁检测机制

1.3 锁类型

第二章：5 种常见死锁场景

场景 1：不同顺序更新多行记录

场景 2：索引缺失导致的间隙锁

场景 3：自增 ID 插入死锁

场景 4：外键约束导致死锁

场景 5：事务超时导致死锁

第三章：死锁诊断

3.1 死锁诊断工具

3.2 死锁信息分析

第四章：预防策略

4.1 编码规范

4.2 监控告警

4.3 重试策略

死锁重试示例

总结：死锁处理最佳实践

标签

相关推荐

发表评论

取消回复

Recent Posts

Recent Comments

Archives

Categories