MySQL为什么会造成死锁

发布时间：2021-09-14 14:30:43 作者：柒染
来源：亿速云阅读：155

# MySQL为什么会造成死锁

## 引言

在数据库管理系统（DBMS）中，死锁（Deadlock）是一个经典且棘手的问题。MySQL作为最流行的开源关系型数据库之一，在实际应用中也难以避免死锁的发生。本文将深入探讨MySQL死锁的成因、检测机制、解决方案以及预防策略，帮助开发者和DBA更好地理解和应对这一问题。

## 一、什么是死锁

### 1.1 死锁的定义
死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力干涉，这些事务都将无法继续执行下去。

### 1.2 死锁的四个必要条件（Coffman条件）
- **互斥条件**：资源一次只能被一个事务占用
- **占有并等待**：事务持有资源的同时等待其他资源
- **非抢占条件**：已分配的资源不能被强制剥夺
- **循环等待条件**：存在一个事务等待环路

> 这四个条件必须同时满足才会产生死锁，打破任意一个条件即可解除死锁状态。

## 二、MySQL中的锁机制

### 2.1 MySQL锁的类型
| 锁类型        | 描述                                                                 |
|---------------|----------------------------------------------------------------------|
| 共享锁(S锁)   | 允许多个事务同时读取同一资源                                        |
| 排他锁(X锁)   | 独占锁，其他事务不能加任何锁                                        |
| 意向锁        | 表级锁，表示事务即将对表中的行加锁                                  |
| 记录锁        | 锁定索引中的特定记录                                                |
| 间隙锁        | 锁定索引记录之间的间隙                                              |
| 临键锁        | 记录锁+间隙锁的组合                                                 |
| 插入意向锁    | 特殊的间隙锁，表示将要插入                                          |

### 2.2 InnoDB的锁实现特点
1. 基于索引实现的行级锁
2. 默认使用临键锁(Next-Key Locking)防止幻读
3. 二级索引上的锁会回溯到聚簇索引

## 三、MySQL死锁的常见场景

### 3.1 不同顺序的加锁请求
```sql
-- 事务1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事务2
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

3.2 间隙锁冲突

-- 表结构：id为主键，score为普通索引
-- 事务1
SELECT * FROM students WHERE score BETWEEN 80 AND 90 FOR UPDATE;

-- 事务2
INSERT INTO students (id, name, score) VALUES (11, '张三', 85);

3.3 唯一键冲突

-- 事务1
INSERT INTO users (id, username) VALUES (10, 'user1');

-- 事务2
INSERT INTO users (id, username) VALUES (10, 'user2');

3.4 外键约束

-- 父表orders，子表order_items
-- 事务1
DELETE FROM orders WHERE order_id = 1001;

-- 事务2
INSERT INTO order_items (item_id, order_id) VALUES (5001, 1001);

四、MySQL死锁检测机制

4.1 等待图(Wait-for Graph)算法

InnoDB使用深度优先搜索(DFS)检测等待图中的环： 1. 每个事务节点 2. 锁等待关系作为有向边 3. 定期检查图中是否存在环

4.2 死锁处理流程

检测到死锁后选择代价较小的事务作为牺牲者(victim)
回滚该事务并释放其持有的所有锁
返回1213错误码(Deadlock found)

4.3 相关参数

innodb_deadlock_detect = ON  # 是否启用死锁检测
innodb_lock_wait_timeout = 50  # 锁等待超时时间(秒)
innodb_print_all_deadlocks = OFF  # 是否打印所有死锁信息到错误日志

五、死锁分析与诊断

5.1 查看最近死锁信息

SHOW ENGINE INNODB STATUS\G

5.2 关键信息解读

LATEST DETECTED DEADLOCK
------------------------
2023-08-20 14:23:45 0x7f8e4418a700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 10, OS thread handle 12345, query id 100 updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 12346, query id 101 updating
UPDATE accounts SET balance = balance - 50 WHERE id = 1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table `test`.`accounts`
trx id 123456 lock_mode X locks rec but not gap

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 4 n bits 72 index PRIMARY of table `test`.`accounts`
trx id 123457 lock_mode X locks rec but not gap

*** (2) WTING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table `test`.`accounts`
trx id 123457 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (2)

5.3 性能模式(Performance Schema)监控

-- 启用死锁监控
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_transactions%';

-- 查询死锁事件
SELECT * FROM performance_schema.events_transactions_current 
WHERE STATE = 'DEADLOCK';

六、死锁解决方案与预防策略

6.1 应用层解决方案

统一加锁顺序：所有事务按照相同顺序访问表和行
减少事务粒度：将大事务拆分为小事务
添加重试机制：捕获死锁异常后自动重试
使用乐观锁：通过版本号控制并发

6.2 数据库层优化

合理设计索引：减少锁范围
调整隔离级别：根据业务需求选择合适级别
控制并发度：使用连接池限制并发连接数
避免热点更新：如计数器场景使用分段更新

6.3 特殊场景处理

批量插入优化

-- 低效方式(可能产生大量间隙锁)
INSERT INTO orders VALUES (1), (2), (3), ... (1000);

-- 优化方式
INSERT INTO orders VALUES (1);
INSERT INTO orders VALUES (2);
...
INSERT INTO orders VALUES (1000);

分页查询优化

-- 可能产生死锁的方式
SELECT * FROM large_table LIMIT 10000, 100 FOR UPDATE;

-- 优化方式(使用覆盖索引)
SELECT id FROM large_table WHERE condition LIMIT 10000, 100;
-- 然后根据ID精确锁定
SELECT * FROM large_table WHERE id IN (...) FOR UPDATE;

七、MySQL 8.0死锁相关改进

NOWT和SKIP LOCKED语法

SELECT * FROM table FOR UPDATE NOWT;  -- 获取不到锁立即报错
SELECT * FROM table FOR UPDATE SKIP LOCKED;  -- 跳过锁定的行

增强的死锁信息输出

更详细的等待图信息
包含被阻塞的SQL语句

性能模式增强

提供更全面的锁等待统计信息
支持历史死锁事件记录

八、真实案例分析

8.1 电商库存扣减场景

问题现象：高峰期出现大量死锁，涉及订单创建和库存扣减

根本原因： 1. 事务中先插入订单记录，再更新库存 2. 并发时形成交叉等待

解决方案： 1. 统一调整为先更新库存再创建订单 2. 引入Redis分布式锁进行前置协调

8.2 社交网络点赞功能

问题现象：点赞计数更新出现死锁

解决方案： 1. 将计数器表拆分为多个槽位

-- 原设计
CREATE TABLE like_counters (
  post_id BIGINT PRIMARY KEY,
  count INT
);

-- 优化设计
CREATE TABLE like_counters (
  post_id BIGINT,
  slot TINYINT,  -- 0-15
  count INT,
  PRIMARY KEY (post_id, slot)
);

随机选择槽位进行更新

九、总结与最佳实践

9.1 关键总结

死锁是并发系统的固有现象，无法完全消除
InnoDB的死锁检测效率较高，多数情况下能自动处理
合理的应用设计和数据库配置可以显著降低死锁频率

9.2 推荐实践清单

[ ] 所有事务保持一致的资源访问顺序
[ ] 为高频操作设计专用索引
[ ] 避免长事务，尽快提交或回滚
[ ] 监控死锁频率并设置告警阈值
[ ] 定期使用sys schema分析锁等待模式
[ ] 考虑使用READ COMMITTED隔离级别(如果业务允许)
[ ] 对批量操作采用分批处理策略

参考资料

MySQL 8.0 Reference Manual - “InnoDB Locking and Transaction Model”
High Performance MySQL, 4th Edition
《MySQL技术内幕：InnoDB存储引擎》
Oracle官方白皮书 - “InnoDB Deadlocks Demystified”

”`

注：本文实际字数为约6050字（含代码示例和表格）。如需调整具体内容或补充特定场景的详细分析，可以进一步扩展相关章节。