MySQL出现“Lock wait timeout exceeded”错误的原因是什么?

1. 概述

在本教程中，我们将讨论MySQL中的“Lock wait timeout exceeded(锁等待超时)”错误。我们将讨论导致这个错误的原因以及MySQL锁的一些细微差别。

为了简单起见，我们将关注MySQL的InnoDB引擎，因为它是最受欢迎的引擎之一。但是，我们可以使用这里使用的相同测试来检查其他引擎的行为。

2. 在MySQL中的锁

lock是一个特殊的对象，用于控制对资源的访问。在MySQL中，这些资源可以是表、行或内部数据结构。

另一个需要习惯的概念是锁模式。锁模式S(共享)允许事务读取一行。多个事务可以同时获得某一行的锁。

X(排他)锁允许单个事务获取它。一个事务可以更新或删除行，而其他事务必须等待锁被释放，以便获取它。

MySQL 也有意向锁。 这些与表相关，并指示事务打算在表中的行上获取的锁类型。

锁定对于保证高并发环境中的一致性和可靠性至关重要。 但是，在优化性能时，必须进行一些权衡，在这些情况下，选择正确的隔离级别至关重要。

3. 隔离级别

MySQL InnoDB 提供4个事务 隔离级别。 它们在性能、一致性、可靠性和可重复性之间提供不同级别的平衡。 它们分别从最不严格到最严格：

• READ UNCOMMITTED: 顾名思义，就是读未提交，也就是说事务所作的修改在未提交前，其他并发事务是可以读到的。

  存在"脏读"问题。

• READ COMMITTED: 顾名思义，就是读已提交，一个事务只能看到其他并发的已提交事务所作的修改。很显然，该级别可以解决Read Uncommitted中出现的“脏读“问题。除了Mysql，很多数据库都以Read Committed作为默认的事务隔离级别。

  存在"不可重复读"问题。虽然解决了“脏读”问题，但是Read Committed不能保证在一个事务中每次读都能读到相同的数据

• REPEATABLE READ: 顾名思义，可重复读，也即在一个事务范围内相同的查询会返回相同的数据。

  存在"幻读"问题。也即在一次事务范围内多次进行查询，如果其他并发事务中途插入了新的记录，那么之后的查询会读取到这些“幻影”行。

• SERIALIZABLE: 顾名思义，可串行化的，也即并发事务串行执行。很显然，该级别可以避免前面讲到的所有问题：“脏读”、“不可重复读”和“幻读”。

  代价是处理事务的吞吐量低，严重浪费数据库的性能，因此要慎用此事务隔离级别。

🏷注意: *"不可重复读"对应的是修改即Update，“幻读”*对应的是插入即Insert。

现在我们了解了不同隔离级别的工作原理，让我们运行一些测试来检查锁定场景。 首先，为了简短起见，我们将在默认隔离级别 REPEATABLE READ 中运行所有测试。 但是，稍后我们可以运行所有其他级别的测试。

4. 监控

我们将在这里看到的工具不一定适用于生产用途。 相反，它们会让我们了解幕后发生的事情。

这些命令将描述 MySQL 如何处理事务以及哪些锁与哪些事务相关或如何从此类事务中获取更多数据。 再说一遍，这些工具将在我们的测试期间帮助我们，但可能不适用于生产环境，或者至少在错误已经发生时不适用.

开启收集数据库服务器性能参数(5.7以上是自动开启的),在MySql的配置文件中的[mysqld]段里加入一下语句:

# 收集数据库服务器性能参数
performance_schema=ON
performance_schema_instrument='%lock%=on'

检查性能数据库是否启动的命令:

SHOW VARIABLES LIKE 'performance_schema';

☢警告: 如果打开performance_schema选项来收集执行过的语句和事务会有性能损失，一般建议需要的时候开启，然后在线关闭掉。

4.1. InnoDB 状态

命令 SHOW ENGINE INNODB STATUS 向我们展示了有关内部结构、对象、 和指标。 根据可用和活动连接的数量，输出可能会被截断。 但是，我们只需要查看我们用例的事务部分。

在事务部分，我们会发现如下内容:

• 活动事务数
• 每个事务的状态
• 每个事务中涉及的表数
• 事务获取的锁数
• 执行的语句可能持有的事务
• 锁等待信息

那里有很多值得看的东西，但现在对我们来说已经足够了。

4.2. 进程列表

命令 SHOW PROCESSLIST 显示一个当前会话打开的表，该表显示如下信息:

• 会话id
• 用户名
• 主机连接
• 数据库
• 命令/当前活动语句类型
• 运行时间
• 连接状态
• 会话描述

这个命令让我们了解不同的活动会话、它们的状态和它们的活动。

4.3. Select语句

MySQL通过一些表公开了一些有用的信息，我们可以使用它们来理解给定场景中应用的锁策略的类型。它们还保存诸如当前事务id之类的东西。

在本文中，我们将使用表 information_schema.innodb_trx 和 performance_schema.data_locks。

5. 测试设置

为了运行我们的测试，我们将使用 MySQL 的 docker 映像来创建我们的数据库并填充我们的测试模式，以便我们可以练习一些事务场景 ：

# Create MySQL container 
docker run --network host --name example_db -e MYSQL_ROOT_PASSWORD=root -d mysql

一旦我们有了数据库服务器，我们就可以通过连接到它并执行脚本来创建模式：

# Logging in 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 中定义的值。

面临此错误的可能性取决于复杂性和每秒事务的数量。 但是，我们将尝试重现一些常见的场景。

💡提示: 还有一点可能值得一提的是，一个简单的重试策略就可以解决这个错误导致的问题。

为了在测试过程中提供帮助，我们将对打开的所有会话运行以下命令:

USE example_db;

-- Set our timeout to 10 seconds
SET @@SESSION.innodb_lock_wait_timeout = 10;

这将锁等待超时定义为10秒，防止我们等待太久才看到错误。

6.1. 行锁

由于行锁是在不同的情况下获得的，让我们试着重现一个示例。

首先，我们将使用前面看到的登录MySQL脚本从两个不同的会话连接到服务器。之后，让我们在两个会话中运行下面的语句:

SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';

10秒后，第二个会话将失败:

mysql>  UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
> 1205 - Lock wait timeout exceeded; try restarting transaction
> Time: 11.095s

发生错误的原因是由于禁用了自动提交，第一个会话启动了一个事务。接下来，一旦UPDATE语句在事务中运行，就会获得该行的独占锁。但是，没有执行提交，使事务处于打开状态，并导致其他事务一直等待。由于提交没有发生，锁等待的超时达到了限制。这也适用于DELETE语句。

6.2. 检查数据锁表中的行锁

现在，让我们在两个会话中回滚，并像在第一个会话中一样运行脚本，但这次，在第二个会话中，让我们运行以下语句:

SET autocommit=0;
UPDATE zipcode SET code = 'Test' WHERE code = '08025';

我们可以观察到，这两个语句都能成功执行，因为它们不再需要同一行的锁。

为了确认这一点，我们将在任何一个会话或新的会话中运行以下语句:

SELECT * FROM performance_schema.data_locks;

//8.0以下用这个: SELECT * FROM sys.innodb_lock_waits;

上面的语句返回四行，其中两行是表意向锁，指定事务可能打算锁表中的一行，另外两行是记录锁. 查看列LOCK_TYPE、LOCK_MODE和LOCK_DATA，我们可以确认刚才描述的锁:

在5.7里是这样:

在两个会话中运行回滚并再次查询，结果是一个空数据集。

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 运行 SELECT 语句后立即重复同样的测试，我们会看到实际上，第一个会话锁定了所有行，而第二个会话正在等待。

问题与 MySQL 执行查询 如何查找更新的候选者有关，因为 WHERE 子句中使用的列没有索引。 MySQL 必须扫描所有行以找到与 WHERE 条件匹配的行，这也会导致这些行被锁定。

⚠重要: 确保我们的SQL语句是最优的是很重要的.

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);

在这里，我们创建了一个表，并启动了一个事务，该事务在单个语句中读取zipcode表，并写入zipcode_backup表。

下一步是在第二个会话中运行以下语句:

SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';

再次，事务 2 超时，因为第一个事务获得了表中行的锁定。 让我们在 data_lock 表中运行 SELECT 语句来演示发生了什么。 然后，让我们回滚两个会话。

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;

现在，我们将运行与之前相同的更新，结果又是超时。 除此之外，我们应该记住这里允许读取。

与 LOCK IN SHARE MODE 不同，FOR UPDATE 不允许读锁，如下所示，当我们在第一个会话中运行语句时：

SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' FOR UPDATE;

然后，我们运行相同的SELECT语句，并在第一个会话中使用LOCK IN SHARE MODE选项，但现在在第二个会话中，我们将再次观察到超时错误。总结一下，可以为多个会话获取LOCK IN SHARE MODE锁，并且它锁定 写 操作。独占锁或FOR UPDATE选项允许读，但不允许读锁或写锁。

6.7. 表锁

表锁没有超时，不推荐用于InnoDB：

LOCK TABLE zipcode WRITE;

一旦我们运行它，我们可以打开另一个会话，尝试选择或更新，并检查它是否会被锁定，但这一次，没有超时发生。 更进一步，我们可以打开第三个会话并运行：

SHOW PROCESSLIST;

它显示活动会话及其状态，我们将看到第一个会话处于睡眠状态，第二个会话正在等待表的元数据锁定。 在这种情况下，解决方案将是运行下一个命令：

UNLOCK TABLES;

我们可能会发现会话等待获取某些元数据锁的其他场景是在 DDL 执行期间，例如 ALTER TABLEs。

6.8. 间隙锁

Gap locks发生在索引记录被锁定的特定时间间隔内，而另一个会话试图在这个时间间隔内执行某些操作。在这种情况下，甚至插入也会受到影响。

让我们考虑在第一个会话中执行的以下语句：

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. Deadlocks

默认情况下，MySQL 会尝试识别死锁，如果它设法解决事务之间的依赖关系图，它会自动终止其中一个任务以允许其他任务通过。 否则，我们会得到一个锁定超时错误，就像我们之前看到的那样。

让我们模拟一个简单的死锁场景。 对于第一个会话，我们执行：

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();

最后一个 SELECT 语句将给我们当前的事务 ID。 稍后我们将需要它来检查日志。 然后，对于第二个会话，让我们运行：

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;

在这个序列中，我们回到会话一并运行:

SELECT * FROM address_type WHERE id = 2 FOR UPDATE;

马上，我们会得到一个错误：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

最后，我们进入第三个会话，我们运行：

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
Record lock 
...

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap waiting
Record lock
...
*** (2) TRANSACTION:
TRANSACTION 4035, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), ... , 2 row lock(s)
MySQL thread id 11, .. query id 253 ...
SELECT * FROM address_type WHERE id = 2 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap
Record lock
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap waiting
Record lock
...
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 4037
...
LIST OF TRANSACTIONS FOR EACH SESSION:
...
---TRANSACTION 4036, ACTIVE 18 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, ... , query id 252 ...

使用我们之前得到的事务id，我们可以找到很多有用的信息，比如错误时刻的连接状态、行锁的数量、最后执行的命令、持有锁的描述、 事务正在等待的锁的描述。 之后，它对死锁中涉及的其他事务重复相同的操作。 此外，最后，我们找到了有关哪些事务被回滚的信息。

7. 结尾

在本文中，我们研究了 MySQL 中的锁，它们是如何工作的，以及它们何时导致“超出锁定等待超时”错误。

我们定义了测试场景，允许我们重现这个错误，并在处理事务时检查数据库服务器的内部细微差别。