MySQL 死锁故障处理

死锁是 MySQL 数据库中常见的并发问题，会导致事务无法继续执行，影响系统的可用性和性能。本文将详细介绍 MySQL 死锁的定义、产生条件、常见原因、排查步骤、解决方案和最佳实践，帮助 DBA 快速定位和解决死锁问题，兼顾不同 MySQL 版本的特性差异。

死锁概述

什么是死锁

死锁是指两个或多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。当两个事务互相持有对方需要的锁，并且都不释放自己的锁时，就会发生死锁。

死锁的产生条件

死锁的产生必须同时满足以下四个条件（称为死锁的必要条件）：

互斥条件：资源（锁）只能被一个事务占用
请求和保持条件：事务已经持有至少一个资源，同时又请求其他资源
不剥夺条件：事务已获得的资源（锁）在未使用完之前，不能被其他事务强行剥夺
循环等待条件：存在一个事务等待链，每个事务都在等待下一个事务持有的资源

死锁的影响

事务回滚：MySQL 会自动检测死锁，并回滚其中一个事务，导致该事务失败
性能下降：死锁会导致事务执行时间延长，影响系统吞吐量
应用异常：应用程序需要处理死锁异常，增加了开发复杂度
资源浪费：死锁会导致锁资源被长时间占用，无法释放

死锁的常见表现

应用程序收到 "Deadlock found when trying to get lock; try restarting transaction" 错误
事务执行时间突然延长
数据库 CPU 使用率升高
锁等待时间增加

死锁的常见原因

类别	常见原因
事务设计问题	事务顺序不一致、事务范围过大、长时间持有锁
SQL 语句问题	全表扫描、大量并发更新同一行、非索引列更新
索引问题	缺少索引、索引失效、索引设计不合理
隔离级别问题	隔离级别过高（如 SERIALIZABLE）、MVCC 实现问题
并发问题	高并发写入、热点数据竞争、连接池配置不合理
锁策略问题	锁粒度不当、锁类型选择错误、锁超时设置不合理

死锁的排查步骤

查看死锁日志

使用 SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS 是查看死锁信息的最常用命令，可以显示最近一次死锁的详细信息。

sql

-- 查看 Innodb 状态，包含死锁信息
SHOW ENGINE INNODB STATUS\G

死锁信息通常包含在 LATEST DETECTED DEADLOCK 部分，包括：

死锁发生的时间
参与死锁的事务
每个事务持有和等待的锁
每个事务执行的 SQL 语句
被回滚的事务

开启死锁日志记录

可以通过配置参数开启死锁日志记录，便于后续分析：

ini

[mysqld]
# 开启 Innodb 死锁日志
innodb_print_all_deadlocks = 1
# 日志输出格式
log_output = FILE
# 错误日志路径
log_error = /var/log/mysql/error.log

使用 Performance Schema

MySQL 5.6+ 引入了 Performance Schema，可以更详细地监控死锁情况：

sql

-- 查看死锁事件配置
SELECT * FROM performance_schema.setup_instruments WHERE name LIKE '%deadlock%';

-- 启用死锁事件监控
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE '%deadlock%';

-- 查看死锁事件
SELECT * FROM performance_schema.events_deadlocks ORDER BY event_id DESC LIMIT 10;

-- 查看锁等待情况
SELECT * FROM performance_schema.data_locks WHERE lock_type = 'RECORD';
SELECT * FROM performance_schema.data_lock_waits;

使用 Sys Schema

Sys Schema 是基于 Performance Schema 和 Information Schema 的高级监控视图，可以更方便地查看死锁相关信息：

sql

-- 查看当前锁等待情况
SELECT * FROM sys.innodb_lock_waits;

-- 查看最近的死锁
SELECT * FROM sys.innodb_deadlocks ORDER BY latest_detected DESC LIMIT 5;

-- 查看锁等待最长的事务
SELECT * FROM sys.innodb_lock_waits ORDER BY wait_age_secs DESC LIMIT 5;

死锁自动诊断脚本

bash

#!/bin/bash

# MySQL 死锁自动诊断脚本

MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASS="password"
LOG_FILE="/var/log/mysql/deadlock_diagnosis.log"
DATE=$(date +"%Y-%m-%d %H:%M:%S")

# 初始化日志
echo "[$DATE] MySQL 死锁诊断开始" >> $LOG_FILE
echo "=================================" >> $LOG_FILE
echo "" >> $LOG_FILE

# 1. 死锁基本信息
echo "[$DATE] 1. 死锁基本信息" >> $LOG_FILE
echo "-------------------" >> $LOG_FILE
echo "死锁日志配置:" >> $LOG_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks'; SHOW GLOBAL VARIABLES LIKE 'innodb_deadlock_detect';" >> $LOG_FILE
echo "" >> $LOG_FILE

# 2. 最近死锁信息
echo "[$DATE] 2. 最近死锁信息" >> $LOG_FILE
echo "-------------------" >> $LOG_FILE
echo "InnoDB 最近死锁:" >> $LOG_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SHOW ENGINE INNODB STATUS\G" | grep -A 50 "LATEST DETECTED DEADLOCK" >> $LOG_FILE
echo "" >> $LOG_FILE

# 3. 当前锁等待情况
echo "[$DATE] 3. 当前锁等待情况" >> $LOG_FILE
echo "-------------------" >> $LOG_FILE
echo "当前锁等待事务:" >> $LOG_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SELECT * FROM sys.innodb_lock_waits LIMIT 10;" >> $LOG_FILE
echo "" >> $LOG_FILE

# 4. 锁统计信息
echo "[$DATE] 4. 锁统计信息" >> $LOG_FILE
echo "-------------------" >> $LOG_FILE
echo "锁等待事件统计:" >> $LOG_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SELECT * FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE '%lock%' ORDER BY count_star DESC LIMIT 10;" >> $LOG_FILE
echo "" >> $LOG_FILE

# 5. 事务统计信息
echo "[$DATE] 5. 事务统计信息" >> $LOG_FILE
echo "-------------------" >> $LOG_FILE
echo "长时间运行的事务:" >> $LOG_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SELECT * FROM sys.processlist WHERE command = 'Query' AND time > 60 ORDER BY time DESC;" >> $LOG_FILE
echo "" >> $LOG_FILE

echo "[$DATE] MySQL 死锁诊断结束" >> $LOG_FILE
echo "=================================" >> $LOG_FILE
echo "" >> $LOG_FILE

# 发送邮件通知（可选）
# if grep -q "LATEST DETECTED DEADLOCK" $LOG_FILE; then
#   cat $LOG_FILE | mail -s "MySQL 死锁告警" admin@example.com
# fi

echo "死锁诊断完成，日志已保存到 $LOG_FILE"

分析死锁日志

分析死锁日志时，需要关注以下几个方面：

参与死锁的事务：事务 ID、事务状态、事务执行时间
持有和等待的锁：锁类型（行锁、表锁）、锁模式（共享锁、排他锁）、锁对象（表名、索引名、行记录）
执行的 SQL 语句：导致死锁的具体 SQL
事务隔离级别：事务使用的隔离级别
被回滚的事务：MySQL 选择回滚的事务

定位死锁原因

根据死锁日志分析，定位死锁的具体原因：

事务顺序问题：检查事务是否以不同顺序访问相同的资源
锁粒度问题：检查是否使用了过大的锁粒度
索引问题：检查是否缺少索引或索引失效
事务范围问题：检查事务是否包含过多操作
并发问题：检查是否存在高并发写入热点

死锁的解决方案

优化事务设计

保持事务短小

减少事务包含的操作数量
避免在事务中执行耗时操作（如网络请求、文件 I/O）
及时提交或回滚事务

sql

-- 不推荐：长事务
START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE id = 1;
-- 执行耗时操作
UPDATE table2 SET column2 = value2 WHERE id = 2;
COMMIT;

-- 推荐：短事务
START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE id = 1;
COMMIT;

-- 执行耗时操作

START TRANSACTION;
UPDATE table2 SET column2 = value2 WHERE id = 2;
COMMIT;

保持事务访问顺序一致

确保所有事务以相同的顺序访问资源，避免循环等待。

sql

-- 事务 1（推荐顺序）
START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE id = 1;
UPDATE table2 SET column2 = value2 WHERE id = 2;
COMMIT;

-- 事务 2（推荐顺序，与事务 1 一致）
START TRANSACTION;
UPDATE table1 SET column1 = value3 WHERE id = 1;
UPDATE table2 SET column2 = value4 WHERE id = 2;
COMMIT;

-- 不推荐：与事务 1 顺序不一致
START TRANSACTION;
UPDATE table2 SET column2 = value4 WHERE id = 2;
UPDATE table1 SET column1 = value3 WHERE id = 1;
COMMIT;

避免在事务中使用 SELECT ... FOR UPDATE

除非必要，否则避免使用 SELECT ... FOR UPDATE，因为它会持有排他锁，增加死锁风险。

sql

-- 不推荐：无必要的 FOR UPDATE
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 其他操作
COMMIT;

-- 推荐：使用普通 SELECT
START TRANSACTION;
SELECT * FROM orders WHERE id = 1;
-- 其他操作
COMMIT;

优化 SQL 语句

确保所有更新操作都使用索引

缺少索引会导致全表扫描，增加锁的范围，提高死锁风险。

sql

-- 不推荐：非索引列更新，导致全表扫描
UPDATE orders SET status = 'completed' WHERE order_date > '2023-01-01';

-- 推荐：为 order_date 添加索引
CREATE INDEX idx_orders_order_date ON orders(order_date);
UPDATE orders SET status = 'completed' WHERE order_date > '2023-01-01';

避免在同一事务中更新大量行

更新大量行会持有大量锁，增加死锁风险。

sql

-- 不推荐：同一事务更新大量行
START TRANSACTION;
UPDATE orders SET status = 'completed' WHERE order_date < '2022-01-01';
COMMIT;

-- 推荐：分批更新
DELIMITER //
CREATE PROCEDURE batch_update_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM orders WHERE order_date < '2022-01-01' ORDER BY id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 每次只更新一行或少量行
        UPDATE orders SET status = 'completed' WHERE id = v_id;
        
        -- 每处理 100 行提交一次事务
        IF MOD(v_id, 100) = 0 THEN
            COMMIT;
            START TRANSACTION;
        END IF;
    END LOOP;
    
    CLOSE cur;
    COMMIT;
END //
DELIMITER ;

-- 调用存储过程
CALL batch_update_orders();

调整锁策略

选择合适的隔离级别

不同的隔离级别会影响锁的行为，选择合适的隔离级别可以减少死锁风险。

隔离级别	死锁风险	数据一致性	适用场景
READ UNCOMMITTED	低	低	只读查询，对一致性要求不高
READ COMMITTED	中	中	大多数 OLTP 应用
REPEATABLE READ	高	高	对一致性要求较高的应用
SERIALIZABLE	最高	最高	对一致性要求极高的应用

MySQL 默认的隔离级别是 REPEATABLE READ，可以根据业务需求调整为 READ COMMITTED，减少死锁风险。

sql

-- 临时调整隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 永久调整隔离级别（在配置文件中）
[mysqld]
transaction-isolation = READ-COMMITTED

调整锁等待超时

合理设置锁等待超时时间，可以避免事务长时间等待锁资源。

sql

-- 查看当前锁等待超时设置
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 临时调整锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 30;

-- 永久调整锁等待超时（在配置文件中）
[mysqld]
innodb_lock_wait_timeout = 30

优化索引设计

为常用查询条件添加索引

缺少索引会导致全表扫描，增加锁的范围，提高死锁风险。

优化复合索引顺序

将选择性高的列放在复合索引的前面，可以减少锁的范围。

避免冗余索引

冗余索引会增加锁的持有时间和范围，提高死锁风险。

应用层优化

实现重试机制

应用程序可以捕获死锁异常，并实现重试机制，提高系统的容错能力。

java

// Java 示例：死锁重试机制
public void executeWithRetry(Runnable task, int maxRetries) {
    int retries = 0;
    while (retries < maxRetries) {
        try {
            task.run();
            return;
        } catch (SQLException e) {
            if (e.getMessage().contains("Deadlock found") || e.getErrorCode() == 1213) {
                retries++;
                if (retries >= maxRetries) {
                    throw e;
                }
                // 等待一段时间后重试，使用指数退避策略
                Thread.sleep(100 * (int) Math.pow(2, retries));
            } else {
                throw e;
            }
        }
    }
}

版本差异与特性

MySQL 5.6

基础的死锁检测和处理机制
支持 SHOW ENGINE INNODB STATUS 查看死锁信息
支持 innodb_lock_wait_timeout 配置
不支持 innodb_print_all_deadlocks 配置（需手动开启）
有限的 Performance Schema 支持，死锁监控能力较弱
不支持 sys schema

MySQL 5.7

增强的死锁检测机制
支持 innodb_print_all_deadlocks 配置，自动记录所有死锁
增强的 Performance Schema，支持更详细的死锁监控
引入 sys schema，提供更简单的死锁分析视图
支持 innodb_deadlock_detect 配置，可禁用死锁检测
支持更多的锁等待监控指标

MySQL 8.0

增强的死锁检测和处理能力
支持 innodb_deadlock_detect 动态配置
增强的 Performance Schema，提供更详细的死锁事件监控
增强的 sys schema，提供更多死锁诊断视图
支持 innodb_lock_wait_timeout 会话级配置
支持 SELECT ... FOR UPDATE SKIP LOCKED 语法，避免锁等待
支持 SELECT ... FOR UPDATE NOWAIT 语法，立即返回锁等待错误

死锁的监控和管理

建立死锁监控机制

使用内置监控工具：Performance Schema、Sys Schema
使用第三方监控工具：
- Prometheus + Grafana：提供死锁监控指标和可视化仪表盘
- Zabbix：通过插件监控死锁
- Nagios：通过插件监控死锁
- MySQL Enterprise Monitor：企业级监控解决方案

设置死锁告警

监控死锁发生的频率
当死锁频率超过阈值时触发告警
配置告警通知方式（邮件、短信、微信等）
建立告警处理流程

定期分析死锁日志

定期分析死锁日志，找出死锁的根本原因
制定相应的优化措施
跟踪优化效果，持续改进

死锁处理的最佳实践

预防死锁

保持事务短小：减少事务持有锁的时间
保持事务访问顺序一致：避免循环等待
使用合适的隔离级别：根据业务需求选择隔离级别
确保所有更新操作都使用索引：减少锁的范围
避免在事务中执行耗时操作：减少锁的持有时间
使用行级锁而非表级锁：减少锁的范围
实现重试机制：提高系统容错能力
避免热点数据竞争：使用分片、缓存等方式分散热点

检测死锁

开启死锁日志：记录死锁信息
使用 Performance Schema：监控死锁事件
使用 Sys Schema：查看锁等待情况
设置死锁告警：及时发现死锁问题
定期运行死锁诊断脚本：自动收集和分析死锁信息

解决死锁

分析死锁日志：找出死锁的根本原因
优化事务设计：保持事务短小，访问顺序一致
优化 SQL 语句和索引：确保使用索引，减少锁的范围
选择合适的锁策略：根据业务需求调整隔离级别
实现重试机制：提高系统容错能力
调整应用逻辑：避免并发访问热点数据

案例分析

事务顺序不一致导致的死锁

问题描述：电商平台的订单系统中，两个事务同时处理订单和库存，导致死锁：

事务 1：

sql

START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT;

事务 2：

sql

START TRANSACTION;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
UPDATE orders SET status = 'paid' WHERE id = 1;
COMMIT;

死锁原因：两个事务以不同的顺序访问 orders 和 inventory 表，导致循环等待。

解决方案：修改事务顺序，确保所有事务以相同的顺序访问资源：

sql

-- 所有事务都先更新 orders 表，再更新 inventory 表
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT;

优化效果：

死锁发生率降低 95%
事务执行时间稳定
系统吞吐量提高

缺少索引导致的死锁

问题描述：某系统中，大量并发更新 logs 表，导致死锁频繁发生。

sql

UPDATE logs SET status = 'processed' WHERE create_time < '2023-01-01';

死锁原因： create_time 字段没有索引，导致全表扫描，每个事务都持有大量锁，容易发生死锁。

解决方案：为 create_time 字段添加索引：

sql

CREATE INDEX idx_logs_create_time ON logs(create_time);

优化效果：

死锁发生率降低 90%
事务执行时间从平均 5 秒降低到 0.1 秒
系统吞吐量提高 5 倍

长事务导致的死锁

问题描述：某报表系统中，长事务持有锁时间过长，导致其他事务无法执行，发生死锁。

sql

START TRANSACTION;
-- 执行复杂查询
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
-- 生成报表（耗时操作）
UPDATE reports SET status = 'generated' WHERE id = 1;
COMMIT;

死锁原因：事务包含耗时的报表生成操作，持有锁时间过长，导致其他事务等待。

解决方案：将长事务拆分为短事务：

sql

-- 短事务 1：执行查询
START TRANSACTION;
SELECT * INTO #temp_orders FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
COMMIT;

-- 生成报表（耗时操作）

-- 短事务 2：更新报表状态
START TRANSACTION;
UPDATE reports SET status = 'generated' WHERE id = 1;
COMMIT;

总结

死锁是 MySQL 数据库中常见的并发问题，处理死锁需要综合考虑事务设计、SQL 语句、索引设计、锁策略等多个方面。通过本文的介绍，DBA 可以掌握死锁的定义、产生条件、常见原因、排查步骤、解决方案和最佳实践。

在实际运维中，DBA 应该：

建立完善的死锁监控机制：及时发现和处理死锁
定期分析死锁日志：找出死锁的根本原因
优化事务设计：保持事务短小，访问顺序一致
优化 SQL 语句和索引：确保使用索引，减少锁的范围
选择合适的锁策略：根据业务需求调整隔离级别
实现重试机制：提高系统容错能力
持续改进：根据业务变化调整死锁处理策略

通过不断优化和改进，可以显著降低死锁发生率，提高系统的可用性和性能。不同 MySQL 版本在死锁处理方面有不同的特性和工具，DBA 需要根据实际使用的版本选择合适的死锁处理策略。

MySQL 死锁故障处理 ​

死锁概述 ​

什么是死锁 ​

死锁的产生条件 ​

死锁的影响 ​

死锁的常见表现 ​

死锁的常见原因 ​

死锁的排查步骤 ​

查看死锁日志 ​

使用 SHOW ENGINE INNODB STATUS ​

开启死锁日志记录 ​

使用 Performance Schema ​

使用 Sys Schema ​

死锁自动诊断脚本 ​

分析死锁日志 ​

定位死锁原因 ​

死锁的解决方案 ​

优化事务设计 ​

保持事务短小 ​

保持事务访问顺序一致 ​

避免在事务中使用 SELECT ... FOR UPDATE ​

优化 SQL 语句 ​

确保所有更新操作都使用索引 ​

避免在同一事务中更新大量行 ​

调整锁策略 ​

选择合适的隔离级别 ​

调整锁等待超时 ​

优化索引设计 ​

为常用查询条件添加索引 ​

优化复合索引顺序 ​

避免冗余索引 ​

应用层优化 ​

实现重试机制 ​

版本差异与特性 ​

MySQL 5.6 ​

MySQL 5.7 ​

MySQL 8.0 ​

死锁的监控和管理 ​

建立死锁监控机制 ​

设置死锁告警 ​

定期分析死锁日志 ​

死锁处理的最佳实践 ​

预防死锁 ​

检测死锁 ​

解决死锁 ​

案例分析 ​

事务顺序不一致导致的死锁 ​

缺少索引导致的死锁 ​

长事务导致的死锁 ​

总结 ​