面试题：MariaDB 高并发场景下调试锁争用问题

Question

在 MariaDB 处于高并发读写的场景中，出现了锁争用导致性能下降的问题。请阐述你诊断和解决该问题的思路，包括如何监控锁的状态，判断锁争用的类型以及对应的优化方法。

Answer 1

诊断思路

1. 监控锁状态
   • SHOW STATUS：使用SHOW STATUS LIKE 'innodb_row_lock%';命令查看 InnoDB 行锁相关状态变量。例如，Innodb_row_lock_current_waits表示当前正在等待行锁的数量，Innodb_row_lock_time表示等待行锁的总时间，Innodb_row_lock_time_avg为平均等待行锁时间。
   • SHOW ENGINE INNODB STATUS：运行SHOW ENGINE INNODB STATUS;获取 InnoDB 存储引擎的详细状态信息，其中LATEST DETECTED DEADLOCK部分会显示最近检测到的死锁情况，TRANSACTIONS部分能查看事务当前持有的锁及等待锁的信息。
2. 判断锁争用类型
   • 行锁争用：若Innodb_row_lock_time持续增长，Innodb_row_lock_current_waits数值较大，且SHOW ENGINE INNODB STATUS中TRANSACTIONS部分显示事务等待行锁，则可能是行锁争用。常见原因是索引设计不合理，导致查询不能高效定位行数据，从而引发大量行锁竞争。
   • 表锁争用：如果执行SHOW STATUS LIKE 'table_locks%';，发现Table_locks_waited值较高，说明存在表锁争用。通常是由于应用中频繁执行全表扫描操作或者对表进行大量的 DDL 操作（如添加列、修改表结构）导致。

解决方法

1. 行锁争用优化
   • 优化索引：确保查询语句使用的列上有合适的索引。可以通过EXPLAIN关键字分析查询语句的执行计划，查看是否使用了索引以及索引的使用是否高效。例如，对于SELECT * FROM table_name WHERE column_name = 'value';，若column_name没有索引，应添加索引CREATE INDEX idx_column_name ON table_name (column_name);
   • 调整事务隔离级别：如果业务允许，可适当降低事务隔离级别。例如，从默认的REPEATABLE READ调整为READ COMMITTED。在READ COMMITTED隔离级别下，InnoDB 存储引擎的行锁持有时间相对较短，能减少锁争用。但需注意，降低隔离级别可能会引发其他数据一致性问题，需谨慎评估。
   • 优化事务逻辑：尽量缩短事务的执行时间，将大事务拆分为多个小事务。例如，在一个事务中若包含多个操作，可以根据业务逻辑将其拆分为几个独立的事务依次执行，减少锁的持有时间。
2. 表锁争用优化
   • 避免全表扫描：对查询语句进行优化，添加合适的索引以避免全表扫描。例如，对于SELECT * FROM large_table;，可改写为SELECT * FROM large_table WHERE some_column = 'value';并为some_column添加索引。
   • 合理安排 DDL 操作：将 DDL 操作安排在数据库低峰期执行，减少对业务的影响。如果可能，使用在线 DDL 工具（如 pt-online-schema-change 等），在不锁表的情况下进行表结构修改。