MySQL 行锁等待超时问题分析
在使用 MySQL 时，经常会遇到 Lock wait timeout exceeded 错误，这通常被称为行锁等待超时。本文将探讨如何有效解决这种问题。
行锁的等待
行锁等待超时的原因是多个事务同时尝试操作同一行数据。MySQL 使用锁机制来保证事务的 ACID 特性，避免数据不一致。只有获得锁的事务才能操作该行数据，其他事务则需要等待。如果等待时间超过 innodb_lock_wait_timeout 设置的时间，默认为 50 秒，则会抛出行锁等待超时错误。
例如，事务 A 和事务 B 同时尝试插入主键值为 1 的数据。由于事务 A 先获得了该行的锁，事务 B 必须等待事务 A 提交后才能继续。
产生原因
根据经验，行锁等待超时的主要原因包括：
- 在事务中执行非数据库交互操作，如接口调用或文件操作，可能导致事务挂起。
- 事务中包含慢查询，使得其他 DML 操作无法及时释放行锁。
- 事务中包含大量 SQL 语句，导致事务执行时间过长。
- 级联更新的 SQL 执行时间较长，影响多张表的行锁。
- 磁盘问题导致的事务挂起，较少见但不可忽视。
定位难点
定位行锁等待超时问题的难点在于：
- MySQL 不会主动记录行锁等待信息，难以事后分析。
- 锁争用的原因多样，难以判断具体原因，特别是无法复现问题时。
- 即使找到问题 SQL，也难以从代码中挖掘出完整的事务，需要借助 DBA 收集完整的事务 SQL。
常用方法
解决行锁等待超时问题通常有两种情景：
- 手动复现：可以通过特定操作复现错误，使用行锁等待脚本查询当前 MySQL 的行锁等待情况。
- 随机复现：无法手动复现时，需通过后台监控脚本记录 MySQL 的阻塞情况。
例如，通过以下脚本监控 MySQL 的行锁等待情况：
SELECT r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query,
CONCAT(TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP()), 's') AS duration,
b.trx_mysql_thread_id AS blocking_thread, t.processlist_command AS state,
b.trx_query AS blocking_current_query, e.sql_text AS blocking_last_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN performance_schema.threads t ON t.processlist_id = b.trx_mysql_thread_id
JOIN performance_schema.events_statements_current e USING(thread_id);
Performance Schema
Performance Schema 提供了一种更简便的方法来分析行锁等待问题。主要步骤包括：
- 启用 Performance Schema 的相关监控项。
- 查询回滚事务的 SQL，确认是否是日志中报错的事务。
- 筛选可疑事务，即执行时间超过
innodb_lock_wait_timeout的事务。
例如，查询回滚事务的 SQL：
SELECT a.THREAD_ID, b.EVENT_ID, a.EVENT_NAME,
CONCAT(b.TIMER_WAIT / 1000000000000, 's') AS trx_duration,
CONCAT(a.TIMER_WAIT / 1000000000000, 's') AS sql_duration,
a.SQL_TEXT, b.STATE, a.MESSAGE_TEXT
FROM performance_schema.events_statements_history_long a
JOIN performance_schema.events_transactions_history_long b ON a.THREAD_ID = b.THREAD_ID
AND (a.NESTING_EVENT_ID = b.EVENT_ID OR a.EVENT_ID = b.NESTING_EVENT_ID)
WHERE b.autocommit = 'NO' AND a.SQL_TEXT IS NOT NULL AND b.STATE = 'ROLLED BACK';
