MySQL 锁与等待事件

锁的基本概念

锁的定义

锁是数据库管理系统用于控制并发访问共享资源的机制，它可以防止多个事务同时修改同一数据，确保数据一致性和完整性。

锁的作用

确保数据一致性和完整性
控制并发访问
防止脏读、不可重复读和幻读

锁的粒度

MySQL 支持多种粒度的锁，从行级锁到表级锁：

行级锁：锁定单个数据行，粒度最细，并发性能最好
页级锁：锁定数据页，粒度中等
表级锁：锁定整个表，粒度最粗，并发性能最差

MySQL 锁的类型

1. 共享锁（Shared Lock，S锁）

读锁，允许其他事务读取数据，但不允许修改
使用 SELECT ... LOCK IN SHARE MODE 获取
多个事务可以同时持有同一资源的共享锁

示例：

sql

-- 获取共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

2. 排他锁（Exclusive Lock，X锁）

写锁，不允许其他事务读取或修改数据
使用 SELECT ... FOR UPDATE 或 DML 语句获取
同一资源只能有一个排他锁

示例：

sql

-- 获取排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- DML 语句自动获取排他锁
UPDATE users SET name = 'new_name' WHERE id = 1;

3. 意向锁（Intention Lock）

表级锁，用于表明事务即将获取的锁类型
分为意向共享锁（IS锁）和意向排他锁（IX锁）
自动获取，无需手动操作

4. 间隙锁（Gap Lock）

锁定索引记录之间的间隙
防止其他事务在间隙中插入数据
用于可重复读隔离级别

5. 临键锁（Next-Key Lock）

行锁和间隙锁的组合
锁定索引记录及其之前的间隙
用于可重复读隔离级别

6. 记录锁（Record Lock）

锁定索引记录
用于行级锁

InnoDB 锁机制

InnoDB 锁的特点

支持行级锁
基于索引实现
支持多种锁类型
自动死锁检测和处理

InnoDB 锁的实现

1. 锁结构

锁信息存储在内存中的锁表
包含锁类型、锁定对象、事务ID等信息

2. 锁冲突检测

当事务请求锁时，检查是否与现有锁冲突
如果冲突，事务进入等待状态

3. 死锁检测

自动检测死锁
选择回滚代价最小的事务
死锁超时时间由 innodb_lock_wait_timeout 控制

InnoDB 锁配置参数

ini

# 设置 InnoDB 锁等待超时时间（秒）
innodb_lock_wait_timeout = 50

# 启用 InnoDB 死锁检测
innodb_deadlock_detect = ON

等待事件（Wait Events）

等待事件的定义

等待事件是指事务在执行过程中需要等待的资源或条件，包括锁等待、I/O等待、内存等待等。

等待事件的类型

1. 锁等待事件

wait/lock/table/sql/handler：表级锁等待
wait/lock/row/lock/innodb/record_lock：行级锁等待
wait/lock/row/lock/innodb/gap_lock：间隙锁等待
wait/lock/row/lock/innodb/next_key_lock：临键锁等待

2. I/O 等待事件

wait/io/file/innodb/innodb_data_file：InnoDB 数据文件 I/O 等待
wait/io/file/sql/binlog：二进制日志 I/O 等待
wait/io/file/sql/slow_log：慢查询日志 I/O 等待

3. 内存等待事件

wait/memory/innodb/buf_buf_pool：InnoDB 缓冲池内存等待
wait/memory/sql/join_buffer：连接缓冲内存等待

4. 其他等待事件

wait/synch/mutex/innodb/：InnoDB 互斥锁等待
wait/synch/cond/innodb/：InnoDB 条件变量等待
wait/lock/metadata/sql/mdl：元数据锁等待

元数据锁（Metadata Lock，MDL）

元数据锁的作用

保护表结构的完整性
防止在查询过程中表结构被修改
用于 DDL 操作和 DML 操作之间的协调

元数据锁的类型

SHARED_READ：读锁，允许读取表结构
SHARED_WRITE：写锁，允许修改表数据
EXCLUSIVE：排他锁，允许修改表结构

元数据锁的等待事件

sql

-- 查看元数据锁等待
SELECT * FROM performance_schema.metadata_locks;

锁等待分析

1. 查看当前锁等待

sql

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

-- 查看当前锁
SELECT * FROM information_schema.innodb_locks;

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

2. 使用 Performance Schema 分析锁等待

sql

-- 查看等待事件统计
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE '%lock%';

-- 查看当前等待事件
SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID();
SELECT * FROM performance_schema.events_waits_current WHERE THREAD_ID = <thread_id>;

3. 使用 sys schema 分析锁等待

sql

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 查看阻塞的事务
SELECT * FROM sys.schema_table_lock_waits;

死锁处理

死锁的定义

死锁是指两个或多个事务相互等待对方释放资源，导致所有事务都无法继续执行的状态。

死锁的原因

事务以不同的顺序访问资源
长时间持有锁
复杂的事务逻辑

死锁的检测

InnoDB 自动检测死锁
死锁信息记录在错误日志中

错误日志示例：

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: Deadlock detected.
2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 1 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 123, OS thread handle 140123456789012, query id 456 localhost root statistics
SELECT * FROM users WHERE id = 1 FOR UPDATE

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `test`.`users` trx id 12345 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 5 n bits 72 index idx_name of table `test`.`users` trx id 12345 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 7465737431; asc test1;;
 1: len 4; hex 80000002; asc     ;;

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 124, OS thread handle 140123456789013, query id 457 localhost root update
INSERT INTO users (name) VALUES ('test3')

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 5 n bits 72 index idx_name of table `test`.`users` trx id 12346 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 7465737431; asc test1;;
 1: len 4; hex 80000002; asc     ;;

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `test`.`users` trx id 12346 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2023-01-01T10:00:00.123456Z 123 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

死锁的处理

1. 自动处理

InnoDB 自动检测死锁
选择回滚代价最小的事务
死锁信息记录在错误日志中

2. 手动处理

杀死阻塞的事务
优化查询，减少锁持有时间
调整事务隔离级别
重构应用程序逻辑

死锁的预防

以相同的顺序访问资源
减少锁持有时间
使用低隔离级别
避免长事务
使用索引减少扫描行数
考虑使用乐观锁替代悲观锁

锁优化最佳实践

1. 选择合适的隔离级别

大多数应用使用 READ COMMITTED 或 REPEATABLE READ
避免使用 SERIALIZABLE，会导致大量锁冲突

2. 优化查询

添加合适的索引
减少扫描行数
避免全表扫描
优化 JOIN 操作

3. 控制事务大小

拆分大事务为多个小事务
减少事务持有锁的时间
及时提交或回滚事务

4. 避免锁定不必要的资源

使用 LIMIT 限制返回行数
避免使用 SELECT *
考虑使用覆盖索引

5. 使用乐观锁

对于读多写少的场景，考虑使用乐观锁替代悲观锁：

sql

-- 乐观锁实现
UPDATE users SET balance = balance - 100, version = version + 1 WHERE id = 1 AND version = 1;

不同版本的锁机制差异

MySQL 5.5 及之前

InnoDB 支持行级锁
死锁检测机制相对简单
锁等待信息有限

MySQL 5.6

增强了 Performance Schema，支持更细粒度的锁监控
引入了更多的锁等待事件
改进了死锁检测算法

MySQL 5.7

引入了 sys schema，提供了更友好的锁监控视图
增强了 Performance Schema 中的锁监控
改进了锁等待信息的详细程度

MySQL 8.0

进一步增强了 Performance Schema 中的锁监控
支持锁超时的精确到毫秒
改进了死锁检测和处理
引入了更多的锁等待事件类型

常见问题（FAQ）

Q1: 如何查看当前的锁等待？

A1: 可以通过以下方式查看当前的锁等待：

sql

-- 使用 InnoDB 锁等待视图
SELECT * FROM information_schema.innodb_lock_waits;

-- 使用 sys schema 视图
SELECT * FROM sys.innodb_lock_waits;

-- 使用 Performance Schema
SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE '%lock%';

Q2: 如何处理锁等待超时？

A2: 处理锁等待超时的方法包括：

优化查询，减少锁持有时间
增加锁等待超时时间（innodb_lock_wait_timeout）
杀死阻塞的事务
调整事务隔离级别
重构应用程序逻辑

Q3: 如何避免死锁？

A3: 避免死锁的方法包括：

以相同的顺序访问资源
减少锁持有时间
使用低隔离级别
避免长事务
使用索引减少扫描行数
考虑使用乐观锁替代悲观锁

Q4: 行级锁一定比表级锁性能好吗？

A4: 不一定，行级锁的性能取决于具体情况：

对于频繁更新的表，行级锁可以提供更好的并发性能
对于全表扫描的查询，表级锁可能比行级锁性能更好
行级锁的实现成本比表级锁高

Q5: 如何分析慢查询日志中的锁等待？

A5: 分析慢查询日志中的锁等待的方法包括：

使用 mysqldumpslow 工具分析慢查询日志
使用 pt-query-digest 工具生成详细的锁等待报告
结合 EXPLAIN 分析查询执行计划
检查索引使用情况
优化查询或调整数据库配置

Q6: 元数据锁会导致什么问题？

A6: 元数据锁可能导致的问题包括：

DDL 操作阻塞 DML 操作
DML 操作阻塞 DDL 操作
长时间的元数据锁等待
影响数据库的并发性能

Q7: 如何监控锁和等待事件？

A7: 监控锁和等待事件的方法包括：

使用 Performance Schema 监控锁等待事件
使用 sys schema 提供的视图查看锁信息
使用第三方监控工具，如 Percona Monitoring and Management (PMM)
定期分析慢查询日志
使用 MySQL Enterprise Monitor 等商业工具

MySQL 锁与等待事件 ​

锁的基本概念 ​

锁的定义 ​

锁的作用 ​

锁的粒度 ​

MySQL 锁的类型 ​

1. 共享锁（Shared Lock，S锁） ​

2. 排他锁（Exclusive Lock，X锁） ​

3. 意向锁（Intention Lock） ​

4. 间隙锁（Gap Lock） ​

5. 临键锁（Next-Key Lock） ​

6. 记录锁（Record Lock） ​

InnoDB 锁机制 ​

InnoDB 锁的特点 ​

InnoDB 锁的实现 ​

1. 锁结构 ​

2. 锁冲突检测 ​

3. 死锁检测 ​

InnoDB 锁配置参数 ​

等待事件（Wait Events） ​

等待事件的定义 ​

等待事件的类型 ​

1. 锁等待事件 ​

2. I/O 等待事件 ​

3. 内存等待事件 ​

4. 其他等待事件 ​

元数据锁（Metadata Lock，MDL） ​

元数据锁的作用 ​

元数据锁的类型 ​

元数据锁的等待事件 ​

锁等待分析 ​

1. 查看当前锁等待 ​

2. 使用 Performance Schema 分析锁等待 ​

3. 使用 sys schema 分析锁等待 ​

死锁处理 ​

死锁的定义 ​

死锁的原因 ​

死锁的检测 ​

死锁的处理 ​

1. 自动处理 ​

2. 手动处理 ​

死锁的预防 ​

锁优化最佳实践 ​

1. 选择合适的隔离级别 ​

2. 优化查询 ​

3. 控制事务大小 ​

4. 避免锁定不必要的资源 ​

5. 使用乐观锁 ​

不同版本的锁机制差异 ​

MySQL 5.5 及之前 ​

MySQL 5.6 ​

MySQL 5.7 ​

MySQL 8.0 ​