印刻数据库

外观

MariaDB 锁等待分析 ​

锁等待概述 ​

什么是锁等待 ​

锁等待是指一个事务在等待另一个事务释放锁资源的状态。当多个事务同时访问相同的资源时，MariaDB 使用锁机制来保证数据一致性，但不当的锁使用会导致锁等待和死锁问题，影响数据库性能和可用性。

锁等待的影响 ​

  • 降低并发性能
  • 导致事务超时
  • 可能引发死锁
  • 影响用户体验
  • 增加系统资源消耗

MariaDB 锁类型 ​

按锁粒度分类 ​

锁类型粒度并发性能资源消耗适用场景
行锁行级高高高并发读写场景
表锁表级低低批量操作场景
页锁页级中中特定存储引擎（如 Aria）

按锁模式分类 ​

  1. 共享锁（S Lock）

    • 允许读取但不允许修改
    • 多个事务可以同时持有共享锁
    • 使用 SELECT ... LOCK IN SHARE MODE 获取

  2. 排他锁（X Lock）

    • 允许读取和修改
    • 同一资源只能有一个排他锁
    • 使用 SELECT ... FOR UPDATE 或 DML 语句获取

  3. 意向锁

    • 表级锁，表示事务打算在表中的行上获取锁
    • 意向共享锁（IS Lock）：打算获取共享锁
    • 意向排他锁（IX Lock）：打算获取排他锁

  4. 自动锁

    • InnoDB 自动为 DML 语句添加行锁
    • SELECT 语句默认不加锁（快照读）

按锁算法分类 ​

  1. 记录锁（Record Lock）

    • 锁定单行记录
    • 基于索引记录

  2. 间隙锁（Gap Lock）

    • 锁定索引记录之间的间隙
    • 防止幻读
    • 只在 REPEATABLE READ 隔离级别下生效

  3. Next-Key Lock

    • 记录锁 + 间隙锁的组合
    • 锁定记录及其前面的间隙
    • InnoDB 默认的行锁算法

  4. 插入意向锁（Insert Intention Lock）

    • 插入操作时使用的间隙锁
    • 多个事务可以在同一间隙插入不同记录

锁等待监控 ​

查看锁等待信息 ​

  1. SHOW ENGINE INNODB STATUS
sql
-- 查看 InnoDB 状态，包含锁等待信息
SHOW ENGINE INNODB STATUS\G
  1. information_schema.innodb_locks
sql
-- 查看当前持有锁和等待锁的信息
SELECT * FROM information_schema.innodb_locks;
  1. information_schema.innodb_lock_waits
sql
-- 查看锁等待关系
SELECT * FROM information_schema.innodb_lock_waits;
  1. performance_schema.data_locks
sql
-- 查看 Performance Schema 中的锁信息
SELECT * FROM performance_schema.data_locks;
  1. performance_schema.data_lock_waits
sql
-- 查看 Performance Schema 中的锁等待信息
SELECT * FROM performance_schema.data_lock_waits;
  1. SHOW PROCESSLIST
sql
-- 查看当前进程状态，包含锁等待
SHOW FULL PROCESSLIST;

锁等待分析工具 ​

  1. pt-deadlock-logger
bash
# 监控并记录死锁
pt-deadlock-logger --user=root --password=password --host=localhost
  1. pt-query-digest
bash
# 分析慢查询日志中的锁等待
pt-query-digest --filter '($event->{Lock_time} || 0) > 0.5' /var/log/mariadb/mariadb-slow.log
  1. InnoDB Lock Monitor
sql
-- 启用 InnoDB 锁监视器
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

锁等待分析方法 ​

识别锁等待 ​

  1. 从 SHOW PROCESSLIST 识别

    • 查看 State 列包含 "Waiting for table level lock" 或 "Waiting for row lock" 的进程
    • 查看 Time 列，长时间等待的进程可能存在锁等待

  2. 从 SHOW ENGINE INNODB STATUS 识别

    • 查看 "TRANSACTIONS" 部分的 "WAITING FOR THIS LOCK TO BE GRANTED" 信息
    • 查看 "LATEST DETECTED DEADLOCK" 部分的死锁信息

  3. 从 performance_schema 识别

    • 查询 data_lock_waits 表，查看等待时间长的锁等待

分析锁等待原因 ​

  1. 查看锁持有和等待关系
sql
-- 查看锁等待关系
SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query,
  t.lock_mode lock_mode,
  t.lock_type lock_type,
  t.lock_table lock_table,
  t.lock_index lock_index,
  t.lock_space lock_space,
  t.lock_page lock_page,
  t.lock_rec lock_rec
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_locks t ON t.lock_id = w.requested_lock_id;
  1. 查看锁持有情况
sql
-- 查看当前持有锁的事务
SELECT 
  trx_id,
  trx_mysql_thread_id,
  trx_query,
  trx_started,
  trx_wait_started,
  trx_state
FROM information_schema.innodb_trx
WHERE trx_state = 'LOCK WAIT';
  1. 查看索引使用情况
sql
-- 查看查询的执行计划
EXPLAIN SELECT * FROM mytable WHERE id = 1 FOR UPDATE;

常见锁等待场景及优化 ​

场景 1：行锁等待 ​

现象 ​

  • State 显示 "Waiting for row lock"
  • 多个事务访问相同的行

常见原因 ​

  • 热点数据竞争
  • 事务持有锁时间过长
  • 缺少合适的索引，导致行锁升级为表锁

优化建议 ​

  • 优化查询，确保使用合适的索引
  • 缩短事务持有锁的时间
  • 避免在事务中执行长时间操作
  • 考虑使用乐观锁
  • 分散热点数据访问

场景 2：表锁等待 ​

现象 ​

  • State 显示 "Waiting for table level lock"
  • 通常发生在 MyISAM 表或 InnoDB 表执行 ALTER TABLE 等操作

常见原因 ​

  • MyISAM 表的读写锁冲突
  • InnoDB 表执行 DDL 操作
  • 锁升级
  • 全表扫描

优化建议 ​

  • 对于 MyISAM 表，考虑迁移到 InnoDB
  • 使用在线 DDL（MariaDB 10.0+ 支持）
  • 避免在业务高峰期执行 DDL 操作
  • 优化查询，避免全表扫描
  • 调整 innodb_lock_wait_timeout 参数

场景 3：死锁 ​

现象 ​

  • 出现 "Deadlock found when trying to get lock; try restarting transaction"
  • SHOW ENGINE INNODB STATUS 显示死锁信息

常见原因 ​

  • 事务之间以不同顺序访问相同资源
  • 长时间运行的事务
  • 缺少合适的索引
  • 间隙锁导致的死锁

优化建议 ​

  • 确保所有事务以相同顺序访问资源
  • 缩短事务持有锁的时间
  • 使用合适的索引，避免间隙锁
  • 设置合理的 innodb_deadlock_detect 参数
  • 考虑使用乐观锁
  • 在应用程序中处理死锁异常，自动重试

场景 4：间隙锁导致的锁等待 ​

现象 ​

  • 事务等待间隙锁
  • 通常发生在 REPEATABLE READ 隔离级别

常见原因 ​

  • 使用范围查询（如 BETWEEN, >, < 等）
  • 缺少合适的索引
  • 高并发插入操作

优化建议 ​

  • 考虑降低隔离级别到 READ COMMITTED
  • 使用合适的索引，减少间隙锁范围
  • 优化查询，避免大范围的范围查询
  • 分散插入操作，避免同时插入相邻数据

锁等待优化策略 ​

事务优化 ​

  1. 缩短事务长度

    • 尽量将事务拆分为多个小事务
    • 避免在事务中执行非数据库操作
    • 避免在事务中等待用户输入

  2. 优化事务顺序

    • 确保所有事务以相同顺序访问资源
    • 先获取最严格的锁
    • 先访问热点数据

  3. 合理使用隔离级别

    • 对于高并发场景，考虑使用 READ COMMITTED 隔离级别
    • 只在必要时使用 REPEATABLE READ 或 SERIALIZABLE

索引优化 ​

  1. 添加合适的索引

    • 为 WHERE、JOIN、ORDER BY 等子句中的列添加索引
    • 避免全表扫描，减少锁冲突

  2. 优化索引结构

    • 使用联合索引，遵循最左前缀原则
    • 避免创建过多索引，影响写入性能

  3. 避免索引失效

    • 避免在查询条件中使用函数或表达式
    • 确保查询条件与索引列类型一致

查询优化 ​

  1. 避免锁定不必要的行

    • 只查询和修改必要的列
    • 避免 SELECT * 语句
    • 使用 LIMIT 限制返回行数

  2. 优化 DML 操作

    • 批量更新时，考虑分批次处理
    • 避免一次性修改大量行
    • 使用 ON DUPLICATE KEY UPDATE 代替 INSERT + UPDATE

  3. 合理使用锁提示

    • 使用 SELECT ... FOR UPDATE SKIP LOCKED 跳过锁定行（MariaDB 10.6+）
    • 使用 SELECT ... FOR UPDATE NOWAIT 不等待锁（MariaDB 10.6+）

配置优化 ​

  1. 调整锁超时参数
ini
# my.cnf
[mysqld]
# 锁等待超时时间，单位秒
innodb_lock_wait_timeout = 50
  1. 启用死锁检测
ini
# my.cnf
[mysqld]
# 启用死锁检测
innodb_deadlock_detect = ON
  1. 调整事务隔离级别
ini
# my.cnf
[mysqld]
# 设置事务隔离级别
transaction_isolation = READ-COMMITTED
  1. 优化 InnoDB 并发参数
ini
# my.cnf
[mysqld]
# InnoDB 并发线程数
innodb_thread_concurrency = 0

# InnoDB 锁等待检测间隔
innodb_lock_wait_detect_interval = 1000

锁等待案例分析 ​

案例 1：热点行锁等待 ​

现象 ​

  • 多个事务同时更新同一条记录
  • 事务等待时间长
  • 并发性能下降

分析 ​

sql
-- 查看锁等待
SHOW FULL PROCESSLIST;

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

优化方案 ​

  • 分散热点数据，如使用分库分表
  • 使用乐观锁，如添加版本号列
  • 减少事务持有锁的时间
  • 考虑使用缓存，减少数据库访问

案例 2：死锁 ​

现象 ​

  • 出现死锁错误
  • 事务自动回滚
  • 应用程序报错

分析 ​

sql
-- 查看 SHOW ENGINE INNODB STATUS 中的死锁信息
SHOW ENGINE INNODB STATUS\G

优化方案 ​

  • 确保事务以相同顺序访问资源
  • 缩短事务长度
  • 使用合适的索引
  • 考虑使用乐观锁
  • 在应用程序中处理死锁异常

案例 3：间隙锁导致的锁等待 ​

现象 ​

  • 事务在 REPEATABLE READ 隔离级别下执行范围查询
  • 插入操作等待间隙锁
  • 并发插入性能下降

分析 ​

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

-- 查看锁信息
SELECT * FROM information_schema.innodb_locks;

优化方案 ​

  • 降低隔离级别到 READ COMMITTED
  • 使用合适的索引，减少间隙锁范围
  • 优化查询，避免大范围的范围查询
  • 分散插入操作

锁等待监控最佳实践 ​

  1. 建立锁等待基线

    • 记录正常情况下的锁等待数量和类型
    • 当锁等待数量或类型发生明显变化时，及时预警

  2. 监控锁等待时间

    • 设置锁等待时间告警阈值
    • 及时发现长时间的锁等待

  3. 监控死锁频率

    • 记录死锁发生的频率和原因
    • 分析死锁模式，进行针对性优化

  4. 定期分析锁等待

    • 定期分析锁等待日志
    • 找出频繁发生的锁等待场景
    • 进行持续优化

  5. 结合其他监控数据

    • 将锁等待分析与慢查询分析结合
    • 将锁等待分析与系统监控结合
    • 全面了解数据库性能状况

常见问题（FAQ） ​

Q: 如何查看当前的锁等待情况？ ​

A: 可以使用以下方法：

  • SHOW FULL PROCESSLIST 查看等待的进程
  • SHOW ENGINE INNODB STATUS 查看详细的锁信息
  • 查询 information_schema.innodb_locks 和 information_schema.innodb_lock_waits 表
  • 查询 performance_schema.data_locks 和 performance_schema.data_lock_waits 表

Q: 如何避免死锁？ ​

A: 避免死锁的方法：

  • 确保所有事务以相同顺序访问资源
  • 缩短事务持有锁的时间
  • 使用合适的索引，避免全表扫描
  • 考虑使用乐观锁
  • 降低事务隔离级别
  • 避免在事务中执行长时间操作

Q: 如何处理锁等待超时？ ​

A: 处理锁等待超时的方法：

  • 优化查询，减少锁持有时间
  • 调整 innodb_lock_wait_timeout 参数
  • 检查是否存在死锁
  • 优化索引，减少锁冲突
  • 考虑使用 SELECT ... FOR UPDATE NOWAIT 或 SELECT ... FOR UPDATE SKIP LOCKED（MariaDB 10.6+）

Q: 为什么在 READ COMMITTED 隔离级别下仍会出现锁等待？ ​

A: 即使在 READ COMMITTED 隔离级别下，仍可能出现锁等待，原因包括：

  • 事务持有排他锁
  • 多个事务同时更新相同的行
  • 缺少合适的索引
  • 长时间运行的事务

Q: 如何监控死锁？ ​

A: 监控死锁的方法：

  • 启用 innodb_deadlock_detect 参数
  • 定期检查 SHOW ENGINE INNODB STATUS 中的死锁信息
  • 使用 pt-deadlock-logger 工具监控并记录死锁
  • 在应用程序中捕获死锁异常

总结 ​

锁等待分析是 MariaDB 性能优化的重要组成部分，通过监控和分析锁等待，可以识别出数据库中的并发性能瓶颈，从而进行针对性优化。锁等待的主要原因包括不当的事务设计、缺少合适的索引、不合理的隔离级别设置等。

锁等待优化的主要策略包括：

  1. 优化事务设计，缩短事务长度
  2. 添加合适的索引，减少锁冲突
  3. 优化查询，避免锁定不必要的资源
  4. 调整配置参数，如锁超时时间和隔离级别
  5. 监控锁等待情况，及时发现和解决问题

建议 DBA 建立完善的锁等待监控机制，定期分析锁等待日志，结合其他监控数据，持续优化数据库的并发性能。