1. 概述
本文将深入探讨MySQL中的"Lock wait timeout exceeded"错误。我们会分析该错误的成因，以及MySQL锁机制中的一些关键细节。
为简化讨论，我们将聚焦于MySQL的InnoDB引擎（最常用的存储引擎）。但请注意，文中提到的测试方法同样适用于其他存储引擎。
2. MySQL中的锁机制
锁是控制资源访问的特殊对象。在MySQL中，这些资源可以是表、行或内部数据结构。
另一个需要掌握的概念是锁模式：
- 共享锁（S锁）：允许事务读取行。多个事务可同时持有同一行的共享锁
- 排他锁（X锁）：仅允许单个事务持有。持有事务可更新或删除行，其他事务必须等待锁释放才能获取
MySQL还支持意向锁（与表相关），用于表明事务打算在表的行上获取何种类型的锁。
锁机制在高并发环境中对保证一致性和可靠性至关重要，但性能优化时需要权衡。此时选择正确的隔离级别就变得尤为重要。
3. 隔离级别
MySQL InnoDB提供四种事务隔离级别，它们在性能、一致性、可靠性和可重复性之间提供不同程度的平衡（从宽松到严格排序）：
- READ UNCOMMITTED：所有事务能读取其他事务未提交的更改
- READ COMMITTED：仅已提交的更改对其他事务可见
- REPEATABLE READ：首次查询定义快照作为基准。即使其他事务在读取后立即修改行，只要首次查询后无变更，始终返回基准数据
- SERIALIZABLE：行为类似REPEATABLE READ，但禁用自动提交时，更新/删除会锁定行，读取只能在提交后进行
理解隔离级别后，我们通过测试场景分析锁机制。为简洁起见，所有测试在默认隔离级别REPEATABLE READ下进行，后续可扩展到其他级别。
4. 监控
本文工具主要用于测试环境，帮助理解底层机制，不一定适用于生产环境（尤其在错误发生后）。
4.1. InnoDB状态
命令SHOW ENGINE INNODB STATUS显示大量内部结构、对象和指标信息。输出可能因活跃连接数被截断，但我们的用例只需关注事务部分。
事务部分包含：
- 活跃事务数
- 每个事务状态
- 每个事务涉及的表数
- 事务持有的锁数
- 可能阻塞事务的执行语句
- 锁等待信息
4.2. 进程列表
命令SHOW PROCESSLIST展示当前打开会话的表格，显示：
- 会话ID
- 用户名
- 连接主机
- 数据库
- 命令/当前活动语句类型
- 运行时间
- 连接状态
- 会话描述
此命令提供活跃会话概览、状态及活动情况。
4.3. 查询语句
MySQL通过特定表暴露有用信息，可用于理解给定场景的锁策略。例如当前事务ID。
本文使用information_schema.innodb_trx和performance_schema.data_locks表。
5. 测试环境搭建
使用Docker创建测试数据库：
# 创建MySQL容器
docker run --network host --name example_db -e MYSQL_ROOT_PASSWORD=root -d mysql
连接数据库并执行初始化脚本：
# 登录MySQL
docker exec -it example_db mysql -uroot -p
输入密码后创建数据库和测试数据：
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE zipcode (
code varchar(100) not null,
city varchar(100) not null,
country varchar(3) not null,
PRIMARY KEY (code)
);
INSERT INTO zipcode(code, city, country)
VALUES ('08025', 'Barcelona', 'ESP'),
('10583', 'New York', 'USA'),
('11075-430', 'Santos', 'BRA'),
('SW6', 'London', 'GBR');
6. 测试场景
核心要点：当事务等待其他事务持有的锁时，就会触发"Lock wait timeout exceeded"错误
等待时长取决于全局或会话级别的innodb_lock_wait_timeout参数。该错误的发生概率与事务复杂度和TPS相关，下面我们重现几个常见场景。
简单重试机制通常能解决此错误。测试前在所有会话执行：
USE example_db;
-- 设置10秒超时
SET @@SESSION.innodb_lock_wait_timeout = 10;
6.1. 行锁
行锁在多种场景下出现，我们重现一个典型示例：
- 使用两个不同会话连接数据库
- 在两个会话中执行：
SET autocommit=0; UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
10秒后第二个会话报错：
mysql> UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
踩坑原因：第一个会话因禁用自动提交开启事务，UPDATE获取行排他锁但未提交，导致第二个事务等待超时。此问题同样适用于DELETE语句。
6.2. 在数据锁表中检查行锁
回滚两个会话后，在第一个会话执行：
SET autocommit=0;
UPDATE zipcode SET code = 'Test' WHERE code = '08025';
在第二个会话执行：
SET autocommit=0;
UPDATE zipcode SET code = 'Test2' WHERE code = '10583';
两语句均成功执行（因锁定不同行）。在任意会话验证：
SELECT * FROM performance_schema.data_locks;
返回4行记录：
- 2个表意向锁（表明事务可能锁定表中的行）
- 2个记录锁
通过LOCK_TYPE、LOCK_MODE和LOCK_DATA列可确认锁类型：
回滚后再次查询结果为空。
6.3. 行锁与索引
这次在WHERE子句使用非索引列。第一个会话执行：
SET autocommit=0;
UPDATE zipcode SET city = 'SW6 1AA' WHERE country = 'USA';
第二个会话执行：
SET autocommit=0;
UPDATE zipcode SET city = '11025-030' WHERE country = 'BRA';
意外发生：尽管操作不同行，仍出现锁超时。通过performance_schema.data_locks检查发现，第一个会话锁定了所有行！
根本原因：WHERE条件列无索引，MySQL需全表扫描匹配条件，导致扫描到的行被锁定。务必确保语句优化到位。
6.4. 涉及多表的更新/删除与行锁
多表DELETE/UPDATE也是常见踩坑点。锁定行数取决于执行计划，需注意所有涉及表都可能被锁定。
回滚事务后执行：
CREATE TABLE zipcode_backup SELECT * FROM zipcode;
SET autocommit=0;
DELETE FROM zipcode_backup WHERE code IN (SELECT code FROM zipcode);
在第二个会话执行：
SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
事务二再次超时，因事务一已锁定表中的行。通过data_locks查询可验证。最后回滚所有会话。
6.5. 填充临时表时的行锁
在第一个会话执行DDL+DML：
CREATE TEMPORARY TABLE temp_zipcode SELECT * FROM zipcode;
在第二个会话执行之前的更新语句，会再次触发锁超时。
6.6. 共享锁与排他锁
每个测试结束记得回滚！通过LOCK IN SHARE MODE和FOR UPDATE显式定义锁类型：
共享锁示例：
SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' LOCK IN SHARE MODE;
在第二个会话执行更新将超时。注意：共享锁允许读取。
排他锁示例：
SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' FOR UPDATE;
在第二个会话执行共享锁查询同样超时。关键差异：
- 共享锁：多会话可持有，阻止写入
- 排他锁（FOR UPDATE）：允许读取，阻止其他锁读取和写入
6.7. 表锁
表锁无超时机制，InnoDB中不推荐使用：
LOCK TABLE zipcode WRITE;
在第二个会话执行查询/更新会被阻塞，但不会超时。在第三个会话查看：
SHOW PROCESSLIST;
会显示第一个会话休眠，第二个会话等待元数据锁。解决方案：
UNLOCK TABLES;
类似场景也出现在执行ALTER TABLE等DDL操作时。
6.8. 间隙锁
间隙锁锁定索引记录区间，影响区间内的操作（包括插入）。
在第一个会话执行：
CREATE TABLE address_type ( id bigint(20) not null, name varchar(255) not null, PRIMARY KEY (id) );
SET autocommit=0;
INSERT INTO address_type(id, name) VALUES (1, 'Street'), (2, 'Avenue'), (5, 'Square');
COMMIT;
SET autocommit=0;
SELECT * FROM address_type WHERE id BETWEEN 1 and 5 LOCK IN SHARE MODE;
在第二个会话执行：
SET autocommit=0;
INSERT INTO address_type(id, name) VALUES (3, 'Road'), (4, 'Park');
在第三个会话查询锁状态，会发现新的LOCK MODE值为GAP。此现象同样适用于UPDATE和DELETE。
6.9. 死锁
MySQL默认自动检测死锁，解决依赖关系后回滚一个事务。否则将出现锁超时。
死锁模拟场景：
会话1：
SET autocommit=0;
SELECT * FROM address_type WHERE id = 1 FOR UPDATE;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
会话2：
SET autocommit=0;
SELECT * FROM address_type WHERE id = 2 FOR UPDATE;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
SELECT * FROM address_type WHERE id = 1 FOR UPDATE;
会话1继续执行：
SELECT * FROM address_type WHERE id = 2 FOR UPDATE;
立即报错：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
在会话3查看：
SHOW ENGINE INNODB STATUS;
输出包含关键信息（示例片段）：
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 4036, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, OS thread handle 139794615064320, query id 252...
SELECT * FROM address_type WHERE id = 1 FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap
...
*** (2) TRANSACTION:
TRANSACTION 4035, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
...
*** WE ROLL BACK TRANSACTION (2)
通过之前获取的事务ID，可找到：
- 错误时连接状态
- 行锁数量
- 最后执行的命令
- 持有锁详情
- 等待锁详情
- 被回滚的事务信息
7. 总结
本文深入剖析了MySQL锁机制、工作原理及触发"Lock wait timeout exceeded"错误的场景。通过测试案例重现错误，展示了数据库服务器处理事务时的内部机制。掌握这些知识有助于开发更健壮的数据库应用。