MariaDB 死锁

死锁是 MariaDB 数据库中常见的并发问题，当两个或多个事务相互等待对方释放锁资源时就会发生死锁。死锁会导致事务回滚，影响系统的可用性和性能。本文将详细介绍 MariaDB 死锁的诊断、处理和预防方法，帮助 DBA 快速定位和解决死锁问题。

死锁概述

死锁定义

死锁是指两个或多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。当发生死锁时，MariaDB 会自动检测并回滚其中一个事务，以打破死锁循环。

死锁产生的条件

死锁的产生必须同时满足以下四个条件：

互斥条件：资源不能被共享，一次只能被一个事务使用
请求与保持条件：事务已经持有至少一个资源，但又提出了新的资源请求
不剥夺条件：事务已获得的资源，在未使用完之前，不能被强行剥夺
循环等待条件：若干事务之间形成一种头尾相接的循环等待资源关系

死锁的影响

事务回滚：死锁会导致至少一个事务被回滚，影响业务逻辑
性能下降：死锁检测和处理会消耗系统资源
用户体验：导致应用程序响应延迟或超时
数据不一致：如果死锁处理不当，可能会导致数据不一致
系统不稳定：频繁的死锁会影响系统的稳定性

版本差异

不同 MariaDB 版本在死锁处理方面存在一些差异：

MariaDB 5.5+：支持基本的死锁检测和自动回滚
MariaDB 10.0+：增强了死锁检测算法，提高了死锁检测的效率
MariaDB 10.2+：增加了更多的死锁监控指标
MariaDB 10.3+：优化了 InnoDB 锁机制，减少了死锁的发生
MariaDB 10.5+：提供了更详细的死锁日志信息
MariaDB 10.6+：引入了死锁预测功能，可以提前识别潜在的死锁

死锁诊断

查看死锁日志

启用死锁日志

ini

[mysqld]
# 启用 InnoDB 死锁日志
innodb_print_all_deadlocks = 1

# 设置错误日志位置
error_log = /var/log/mariadb/mariadb.log

查看错误日志

bash

grep -i deadlock /var/log/mariadb/mariadb.log

使用 SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS 是诊断死锁的重要工具，可以查看最近一次死锁的详细信息：

sql

SHOW ENGINE INNODB STATUSG

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

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

使用 Performance Schema

从 MariaDB 10.2 开始，可以使用 Performance Schema 监控死锁：

sql

-- 启用死锁监控
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 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.events_deadlocks_summary_by_account ORDER BY sum_timer_wait DESC;

使用 INFORMATION_SCHEMA

sql

-- 查看当前锁等待情况
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_state = 'LOCK WAIT';

监控工具集成

可以将死锁监控集成到常见的监控工具中：

Prometheus + Grafana：通过 MariaDB Exporter 收集死锁指标
Zabbix：使用自定义监控项监控死锁数量
Nagios：通过插件检查死锁情况
Percona Monitoring and Management (PMM)：提供专门的死锁监控面板

常见死锁场景

场景一：两个事务相互等待资源

事务 A：

sql

START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 等待事务 B 释放 id=2 的锁
UPDATE users SET balance = balance + 100 WHERE id = 2;
COMMIT;

事务 B：

sql

START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 2;
-- 等待事务 A 释放 id=1 的锁
UPDATE users SET balance = balance + 100 WHERE id = 1;
COMMIT;

死锁原因：两个事务相互等待对方持有的锁，形成循环等待。

场景二：间隙锁导致的死锁

事务 A：

sql

START TRANSACTION;
SELECT * FROM users WHERE id BETWEEN 1 AND 10 FOR UPDATE;
-- 持有间隙锁 (1, 10)
COMMIT;

事务 B：

sql

START TRANSACTION;
INSERT INTO users (id, name) VALUES (5, 'test');
-- 等待事务 A 释放间隙锁
COMMIT;

事务 C：

sql

START TRANSACTION;
SELECT * FROM users WHERE id BETWEEN 1 AND 10 FOR UPDATE;
-- 等待事务 B 释放插入意向锁
COMMIT;

死锁原因：间隙锁和插入意向锁之间的冲突，形成循环等待。

场景三：外键约束导致的死锁

事务 A：

sql

START TRANSACTION;
DELETE FROM orders WHERE id = 100;
-- 持有 orders 表的锁，等待 products 表的外键检查
COMMIT;

事务 B：

sql

START TRANSACTION;
DELETE FROM products WHERE id = 200;
-- 持有 products 表的锁，等待 orders 表的外键检查
COMMIT;

死锁原因：外键约束导致的相互等待，形成死锁。

场景四：事务持有锁并等待其他锁

事务 A：

sql

START TRANSACTION;
-- 持有表锁
LOCK TABLES users WRITE;
-- 等待行锁
UPDATE products SET price = price * 1.1 WHERE id = 1;
UNLOCK TABLES;
COMMIT;

事务 B：

sql

START TRANSACTION;
-- 持有行锁
UPDATE products SET price = price * 0.9 WHERE id = 1;
-- 等待表锁
LOCK TABLES users WRITE;
UNLOCK TABLES;
COMMIT;

死锁原因：表锁和行锁之间的冲突，形成循环等待。

死锁处理

自动回滚机制

MariaDB InnoDB 存储引擎会自动检测死锁，并回滚其中一个事务（称为"牺牲品"），以打破死锁循环。选择回滚事务的依据是：

事务的大小：回滚较小的事务
事务的执行时间：回滚执行时间较短的事务
事务的隔离级别：回滚隔离级别较低的事务
事务的优先级：回滚优先级较低的事务

手动处理方法

查看当前锁等待

sql

-- 查看当前事务
SHOW PROCESSLIST;

-- 查看事务详情
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 查看锁信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

终止阻塞事务

sql

-- 终止指定事务
KILL TRANSACTION_ID;

-- 例如：
KILL 12345;

优化死锁事务

缩短事务长度：将长事务拆分为多个短事务
调整事务顺序：确保所有事务以相同的顺序访问资源
减少锁的持有时间：尽快提交或回滚事务
使用更细粒度的锁：使用行锁代替表锁
避免间隙锁：调整隔离级别或使用主键查询

死锁恢复策略

应用程序重试机制：在应用程序中捕获死锁异常，自动重试事务
分级重试策略：根据死锁频率调整重试间隔
优雅降级：在死锁频繁发生时，降低系统负载或切换到只读模式
手动干预：在严重情况下，手动终止阻塞事务或重启数据库

死锁预防

事务设计优化

缩短事务长度：
- 避免在事务中执行不必要的操作
- 避免在事务中等待用户输入
- 尽快提交或回滚事务
调整事务顺序：
- 确保所有事务以相同的顺序访问表和行
- 例如：所有事务都先更新 users 表，再更新 orders 表
使用合理的隔离级别：
- 优先使用 READ COMMITTED 隔离级别，减少锁的持有时间
- 避免使用 SERIALIZABLE 隔离级别，除非必要
避免长事务：
- 定期检查长事务，及时终止不必要的长事务
- 设置合理的 innodb_lock_wait_timeout，自动终止长时间等待的事务

锁策略优化

使用行锁代替表锁：
- 尽量使用 InnoDB 存储引擎，支持行级锁
- 避免使用 LOCK TABLES 语句
减少锁的范围：
- 使用索引查询，避免全表扫描
- 避免使用 SELECT * FOR UPDATE 查询大量数据
- 使用 SELECT ... LIMIT 限制锁的范围
避免间隙锁：
- 使用主键或唯一索引查询，减少间隙锁的产生
- 调整隔离级别为 READ COMMITTED，禁用间隙锁
- 使用 innodb_locks_unsafe_for_binlog 参数（仅适用于 MariaDB 5.5-10.1）
使用乐观锁：
- 对于并发冲突较少的场景，使用乐观锁代替悲观锁
- 例如：使用版本号或时间戳字段实现乐观锁

索引优化

为查询条件添加索引：
- 确保 UPDATE、DELETE 和 SELECT FOR UPDATE 语句的 WHERE 条件有合适的索引
- 避免全表扫描，减少锁的范围
使用覆盖索引：
- 减少回表查询，降低锁的持有时间
- 例如：SELECT id, name FROM users WHERE id = 1 FOR UPDATE
优化联合索引：
- 将选择性高的列放在联合索引的前面
- 确保索引覆盖常用的查询条件

配置优化

调整锁等待超时：

ini

[mysqld]
# 设置锁等待超时时间（秒）
innodb_lock_wait_timeout = 50

优化死锁检测：

ini

[mysqld]
# 启用死锁检测（默认启用）
innodb_deadlock_detect = ON

# 设置死锁检测频率（毫秒）
innodb_deadlock_detect_iterations = 100

调整隔离级别：

ini

[mysqld]
# 设置默认隔离级别
transaction_isolation = READ-COMMITTED

优化 InnoDB 锁机制：

ini

[mysqld]
# 启用自适应哈希索引
innodb_adaptive_hash_index = ON

# 调整锁结构的内存大小
innodb_lock_structs = 10000

死锁监控与告警

监控指标

Innodb_deadlocks：死锁总数
Innodb_lock_wait_timeout：锁等待超时次数
Innodb_lock_waits：锁等待总次数
Innodb_row_lock_time_avg：平均行锁等待时间
Innodb_row_lock_time_max：最大行锁等待时间

告警设置

基于死锁数量：当死锁数超过一定阈值时触发告警
基于锁等待时间：当锁等待时间超过一定阈值时触发告警
基于锁等待次数：当锁等待次数超过一定阈值时触发告警
基于事务长度：当长事务数量超过一定阈值时触发告警

自动化处理

使用脚本定期检查死锁日志，自动生成报告
集成监控系统，实时监控死锁情况
配置自动告警，及时通知 DBA
实现自动重试机制，减少死锁对应用程序的影响

死锁最佳实践

开发规范

使用绑定变量：避免 SQL 注入，提高查询性能
限制事务大小：每个事务只处理必要的数据
使用合理的隔离级别：根据业务需求选择合适的隔离级别
避免在事务中执行 DDL 语句：DDL 语句会导致表锁，增加死锁风险
测试并发场景：在开发环境测试并发访问，提前发现死锁问题
捕获死锁异常：在应用程序中捕获死锁异常，实现自动重试

运维规范

定期分析死锁日志：查看死锁日志，分析死锁原因
监控锁等待情况：实时监控锁等待和死锁情况
优化数据库配置：根据死锁情况调整数据库配置
定期优化索引：确保查询有合适的索引，减少锁的范围
定期清理长事务：终止长时间运行的事务，减少死锁风险
制定死锁处理预案：建立死锁处理的标准流程

测试策略

并发测试：使用压测工具模拟并发访问，测试死锁情况
边界测试：测试大量数据、高并发等边界条件下的死锁情况
回归测试：在版本升级或配置变更后，测试死锁情况
混沌测试：模拟各种故障场景，测试系统的死锁处理能力

死锁案例分析

案例一：两个事务相互等待

问题描述：应用程序在高峰期经常出现死锁，错误日志显示：

LATEST DETECTED DEADLOCK
------------------------
2023-05-10 14:30:45 0x7f1234567890
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 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 140709283456789, query id 123456 localhost root updating
UPDATE users SET balance = balance - 100 WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `test`.`users` trx id 12345 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
...

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 140709283456790, query id 123457 localhost root updating
UPDATE users SET balance = balance + 100 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `test`.`users` trx id 12346 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `test`.`users` trx id 12346 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
...

*** WE ROLL BACK TRANSACTION (1)

诊断过程：

分析死锁日志，发现两个事务相互等待对方持有的锁
事务 12345 持有 id=1 的锁，等待 id=2 的锁
事务 12346 持有 id=2 的锁，等待 id=1 的锁
形成循环等待，导致死锁

处理方法：

调整应用程序代码，确保所有事务以相同的顺序访问资源
例如：所有事务都先更新 id=1 的记录，再更新 id=2 的记录
缩短事务长度，尽快提交或回滚事务

优化结果：死锁发生率降低了 90%，系统稳定性显著提高。

案例二：间隙锁导致的死锁

问题描述：在使用 REPEATABLE-READ 隔离级别时，经常出现间隙锁导致的死锁。

诊断过程：

分析死锁日志，发现死锁与间隙锁有关
事务持有范围查询的间隙锁，其他事务插入数据时等待插入意向锁
形成循环等待，导致死锁

处理方法：

将隔离级别调整为 READ-COMMITTED，减少间隙锁的产生
使用主键或唯一索引查询，避免间隙锁
调整查询条件，减少范围查询

优化结果：间隙锁导致的死锁基本消失，系统性能有所提升。

案例三：外键约束导致的死锁

问题描述：在有外键约束的表上，经常出现死锁。

诊断过程：

分析死锁日志，发现死锁与外键约束有关
事务删除父表记录时，需要检查子表的外键约束
同时，子表的事务也在等待父表的锁
形成循环等待，导致死锁

处理方法：

优化外键约束，使用 ON DELETE CASCADE 或 ON UPDATE CASCADE
调整事务顺序，先处理子表，再处理父表
考虑禁用外键约束，在应用程序中实现数据完整性检查

优化结果：外键约束导致的死锁减少了 80%，系统性能有所提升。

FAQ

Q1: 死锁会导致数据不一致吗？

A1: 不会。MariaDB InnoDB 存储引擎会自动检测死锁，并回滚其中一个事务，确保数据一致性。

Q2: 如何区分死锁和锁等待？

A2: 死锁是两个或多个事务相互等待对方释放锁，而锁等待是单个事务等待其他事务释放锁。死锁会被自动检测并回滚，而锁等待会在超时后自动回滚。

Q3: 为什么 READ-COMMITTED 隔离级别可以减少死锁？

A3: READ-COMMITTED 隔离级别不会使用间隙锁（除了外键约束和唯一索引检查），减少了锁的范围和持有时间，从而降低了死锁的风险。

Q4: 如何禁用死锁检测？

A4: 可以通过以下配置禁用死锁检测：

ini

[mysqld]
innodb_deadlock_detect = OFF

但不建议禁用死锁检测，因为这会导致锁等待无限期持续，影响系统可用性。

Q5: 如何手动模拟死锁？

A5: 可以使用两个会话模拟死锁：

会话 1：

sql

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

会话 2：

sql

START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 2;

会话 1：

sql

UPDATE users SET balance = balance + 100 WHERE id = 2;

会话 2：

sql

UPDATE users SET balance = balance + 100 WHERE id = 1;

此时会发生死锁，其中一个事务会被自动回滚。

Q6: MariaDB 和 MySQL 的死锁处理有什么区别？

A6: MariaDB 和 MySQL 的死锁处理基本相似，但 MariaDB 提供了更多的优化功能：

MariaDB 10.5+ 提供了更详细的死锁日志信息
MariaDB 10.6+ 引入了死锁预测功能
MariaDB 对死锁检测算法进行了优化
MariaDB 支持更多的死锁监控指标

Q7: 如何处理大量的死锁？

A7: 可以采取以下步骤：

分析死锁日志，找出死锁的根本原因
优化事务设计，缩短事务长度
调整事务顺序，确保所有事务以相同的顺序访问资源
优化索引，减少锁的范围
调整隔离级别，减少间隙锁
增加服务器资源，提高系统处理能力
考虑水平扩展，分散系统负载

Q8: 死锁会影响复制吗？

A8: 死锁会导致主库上的事务回滚，从库会自动跳过回滚的事务，不会影响复制的一致性。但频繁的死锁会导致主库性能下降，间接影响复制延迟。

MariaDB 死锁 ​

死锁概述 ​

死锁定义 ​

死锁产生的条件 ​

死锁的影响 ​

版本差异 ​

死锁诊断 ​

查看死锁日志 ​

启用死锁日志 ​

查看错误日志 ​

使用 SHOW ENGINE INNODB STATUS ​

使用 Performance Schema ​

使用 INFORMATION_SCHEMA ​

监控工具集成 ​

常见死锁场景 ​

场景一：两个事务相互等待资源 ​

场景二：间隙锁导致的死锁 ​

场景三：外键约束导致的死锁 ​

场景四：事务持有锁并等待其他锁 ​

死锁处理 ​

自动回滚机制 ​

手动处理方法 ​

查看当前锁等待 ​

终止阻塞事务 ​

优化死锁事务 ​

死锁恢复策略 ​

死锁预防 ​

事务设计优化 ​

锁策略优化 ​

索引优化 ​

配置优化 ​

死锁监控与告警 ​

监控指标 ​

告警设置 ​

自动化处理 ​

死锁最佳实践 ​

开发规范 ​

运维规范 ​

测试策略 ​

死锁案例分析 ​

案例一：两个事务相互等待 ​

案例二：间隙锁导致的死锁 ​

案例三：外键约束导致的死锁 ​

FAQ ​

Q1: 死锁会导致数据不一致吗？ ​

Q2: 如何区分死锁和锁等待？ ​

Q3: 为什么 READ-COMMITTED 隔离级别可以减少死锁？ ​

Q4: 如何禁用死锁检测？ ​

Q5: 如何手动模拟死锁？ ​

Q6: MariaDB 和 MySQL 的死锁处理有什么区别？ ​

Q7: 如何处理大量的死锁？ ​

Q8: 死锁会影响复制吗？ ​

MariaDB 死锁

死锁概述

死锁定义

死锁产生的条件

死锁的影响

版本差异

死锁诊断

查看死锁日志

启用死锁日志

查看错误日志

使用 SHOW ENGINE INNODB STATUS

使用 Performance Schema

使用 INFORMATION_SCHEMA

监控工具集成

常见死锁场景

场景一：两个事务相互等待资源

场景二：间隙锁导致的死锁

场景三：外键约束导致的死锁

场景四：事务持有锁并等待其他锁

死锁处理

自动回滚机制

手动处理方法

查看当前锁等待

终止阻塞事务

优化死锁事务

死锁恢复策略

死锁预防

事务设计优化

锁策略优化

索引优化

配置优化

死锁监控与告警

监控指标

告警设置

自动化处理

死锁最佳实践

开发规范

运维规范

测试策略

死锁案例分析

案例一：两个事务相互等待

案例二：间隙锁导致的死锁

案例三：外键约束导致的死锁

FAQ

Q1: 死锁会导致数据不一致吗？

Q2: 如何区分死锁和锁等待？

Q3: 为什么 READ-COMMITTED 隔离级别可以减少死锁？

Q4: 如何禁用死锁检测？

Q5: 如何手动模拟死锁？

Q6: MariaDB 和 MySQL 的死锁处理有什么区别？

Q7: 如何处理大量的死锁？

Q8: 死锁会影响复制吗？