MySQL锁机制与死锁排查

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、锁的基本概念

#

1.1 为什么需要锁

数据库是多用户共享资源，锁用于：

• 保证数据一致性
• 控制并发访问
• 实现事务隔离

#

1.2 锁的分类

按粒度：

• 全局锁
• 表级锁
• 页级锁
• 行级锁

按功能：

• 共享锁（S Lock / Read Lock）
• 排他锁（X Lock / Write Lock）

按使用方式：

• 乐观锁
• 悲观锁

二、InnoDB锁类型

#

2.1 行锁（Record Lock）

锁定索引记录本身：

-- 对id=1的记录加排他锁
SELECT * FROM user WHERE id = 1 FOR UPDATE;

-- 对id=1的记录加共享锁
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;

注意：InnoDB行锁是通过锁定索引实现的。如果查询条件没有用到索引，会退化为表锁。

#

2.2 间隙锁（Gap Lock）

锁定索引记录之间的”间隙”，防止幻读：

已有记录id: 1, 5, 10

间隙：(-∞, 1), (1, 5), (5, 10), (10, +∞)

-- 锁定id>5的间隙
SELECT * FROM user WHERE id > 5 FOR UPDATE;
-- 锁住 (5,10] 和 (10,+∞) 的间隙

间隙锁特点：

• 只在REPEATABLE READ下生效
• 间隙锁之间不冲突（多个事务可同时持有同一间隙锁）
• 阻止在间隙中插入数据

#

2.3 临键锁（Next-Key Lock）

Record Lock + Gap Lock的组合，锁定记录及其前面的间隙：

记录10的临键锁：锁定(5, 10]区间

InnoDB默认使用临键锁进行搜索和索引扫描。

#

2.4 插入意向锁（Insert Intention Lock）

插入操作前设置的间隙锁，表明要在某个间隙插入数据：

事务T1持有(5, 10)的间隙锁
事务T2想插入id=7的记录

T2获得(5, 10)的插入意向锁
-- 插入意向锁与间隙锁兼容，T2可以等待

#

2.5 自增锁（AUTO-INC Lock）

保证自增ID的连续性：

INSERT INTO user (name) VALUES ('Alice'), ('Bob');

三种模式（innodb_autoinc_lock_mode）：

• 0：传统模式，每次INSERT加锁
• 1：连续模式（默认），批量INSERT加锁，单行使用轻量锁
• 2：交错模式，所有使用轻量锁，ID可能不连续但并发最高

三、锁的兼容性

#

3.1 锁兼容矩阵

	X	S	IX	IS	AI
X	✗	✗	✗	✗	✗
S	✗	✓	✗	✓	✗
IX	✗	✗	✓	✓	✓
IS	✗	✓	✓	✓	✓
AI	✗	✗	✓	✓	✗

• X：排他锁
• S：共享锁
• IX：意向排他锁
• IS：意向共享锁
• AI：自增锁

#

3.2 意向锁（Intention Lock）

意向锁是表级锁，用于协调行锁和表锁：

-- 事务要更新某行，先加意向排他锁
UPDATE user SET name = 'Alice' WHERE id = 1;
-- 自动加IX锁

-- 其他事务要加表锁，检查意向锁冲突
LOCK TABLES user WRITE;  -- 需要等待IX释放

作用：避免加表锁时逐行检查行锁冲突。

四、锁的查看与分析

#

4.1 查看当前锁

-- MySQL 8.0
SELECT 
    ENGINE_TRANSACTION_ID,
    OBJECT_NAME,
    OBJECT_SCHEMA,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks;

-- 查看锁等待
SELECT 
    REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
    LOCK_TYPE,
    LOCK_MODE
FROM performance_schema.data_lock_waits;

#

4.2 查看事务和锁（传统方式）

-- 查看InnoDB状态（包含最近死锁信息）
SHOW ENGINE INNODB STATUS;

-- 查看事务
SELECT * FROM information_schema.innodb_trx;

-- 查看锁
SELECT * FROM information_schema.innodb_locks;      -- MySQL 5.7
SELECT * FROM performance_schema.data_locks;        -- MySQL 8.0

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits; -- MySQL 5.7
SELECT * FROM performance_schema.data_lock_waits;   -- MySQL 8.0

五、死锁

#

5.1 什么是死锁

两个或多个事务互相等待对方释放锁，形成循环等待。

事务T1: 持有A的锁，请求B的锁
事务T2: 持有B的锁，请求A的锁

T1等待T2释放B
T2等待T1释放A

→ 死锁！

#

5.2 死锁示例

-- 表: account(id, balance)
-- 数据: (1, 1000), (2, 2000)

-- 事务T1
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- 锁id=1
-- ... 其他操作 ...
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- 等待id=2

-- 事务T2（同时执行）
BEGIN;
UPDATE account SET balance = balance - 200 WHERE id = 2;  -- 锁id=2
-- ... 其他操作 ...
UPDATE account SET balance = balance + 200 WHERE id = 1;  -- 等待id=1
-- ERROR 1213 (40001): Deadlock found

#

5.3 死锁检测

InnoDB自动检测死锁：

[mysqld]
innodb_deadlock_detect = ON   -- 默认开启
innodb_lock_wait_timeout = 50  -- 锁等待超时时间（秒）

死锁处理策略：

• 选择undo量最小的事务回滚（代价最小）
• 被回滚的事务会收到ERROR 1213

#

5.4 查看死锁日志

SHOW ENGINE INNODB STATUS;

在LATEST DETECTED DEADLOCK部分：

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (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 100, OS thread handle 1234567, query id 500 localhost root updating
UPDATE account SET balance = balance + 100 WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table `test`.`account`
trx id 12345 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 101, OS thread handle 1234568, query id 501 localhost root updating
UPDATE account SET balance = balance + 200 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
... 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
...

*** WE ROLL BACK TRANSACTION (2)

#

5.5 死锁预防

1. 固定访问顺序

// 按id从小到大排序，统一访问顺序
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    Long firstId = Math.min(fromId, toId);
    Long secondId = Math.max(fromId, toId);
    
    Account first = accountDao.selectForUpdate(firstId);
    Account second = accountDao.selectForUpdate(secondId);
    
    // 执行转账逻辑
}

2. 降低隔离级别

-- READ COMMITTED减少间隙锁使用
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 减少事务持有锁的时间

// 不好的做法：事务中做RPC
@Transactional
public void badPractice() {
    updateDB();      // 加锁
    callRemoteAPI(); // 网络IO，锁长时间持有
    updateDB2();     // 加锁
}

// 好的做法：锁内只保留必要操作
public void goodPractice() {
    // 事务1：获取必要数据
    Data data = transactionTemplate.execute(status -> {
        return getData();
    });
    
    // 外部做RPC
    Result result = callRemoteAPI(data);
    
    // 事务2：更新数据
    transactionTemplate.execute(status -> {
        updateData(result);
        return null;
    });
}

4. 使用乐观锁

-- 添加版本号字段
ALTER TABLE account ADD COLUMN version INT DEFAULT 0;

-- 更新时检查版本号
UPDATE account 
SET balance = balance - 100, version = version + 1 
WHERE id = 1 AND version = 5;

-- 如果影响行数为0，说明数据被修改，需要重试

#

5.6 死锁排查流程

发现死锁错误
    │
    ├── 查看死锁日志
    │       └── SHOW ENGINE INNODB STATUS
    │
    ├── 分析死锁原因
    │       ├── 事务访问顺序不一致？→ 统一访问顺序
    │       ├── 间隙锁冲突？→ 考虑降低隔离级别
    │       ├── 事务时间过长？→ 缩短事务
    │       └── 批量操作？→ 分批处理
    │
    └── 验证修复效果
            └── 持续监控错误日志

六、锁优化建议

#

6.1 减少锁竞争

1. 使用合适的索引：无索引的WHERE条件会锁全表
2. 控制事务大小：大批量操作分批执行
3. 避免长事务：及时提交或回滚
4. 使用READ COMMITTED：减少间隙锁使用

#

6.2 锁粒度选择

场景	建议
读多写少	乐观锁（版本号）
写多读少	悲观锁（FOR UPDATE）
竞争不激烈	普通事务
竞争激烈	考虑队列串行化

#

6.3 监控指标

-- 锁等待次数
SHOW STATUS LIKE 'Table_locks_waited';
SHOW STATUS LIKE 'Innodb_row_lock_waits';

-- 平均锁等待时间
SHOW STATUS LIKE 'Innodb_row_lock_time_avg';

-- 死锁次数
SHOW STATUS LIKE 'Innodb_deadlocks';

七、总结

锁类型	锁定范围	用途
Record Lock	索引记录	行级读写控制
Gap Lock	索引间隙	防止幻读
Next-Key Lock	记录+间隙	默认行锁
Insert Intention	插入间隙	插入并发控制
Intention Lock	表级	协调行锁和表锁

问题	解决方案
死锁	固定访问顺序、缩短事务、乐观锁
锁等待超时	优化SQL、添加索引、分批处理
锁升级	确保使用索引、避免大事务

理解锁机制的核心要点：

1. 行锁基于索引实现，无索引则锁表
2. RR隔离级别使用间隙锁防止幻读
3. 死锁通过固定访问顺序和缩短事务预防
4. 监控锁等待和死锁指标，及时发现和解决问题

核心要点

1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

3. 事务隔离级别的选择：根据业务需求选择合适的级别

4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。