是什么导致MySQL 中出现“超过锁定等待超时”错误？-编程知识-白鹭情

一、概述

在本教程中，我们将讨论MySQL 中的“Lock wait timeout exceeded”错误。我们将讨论导致此错误的原因以及有关MySQL 锁的一些细微差别。

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

2. 锁定MySQL

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

另一个习惯的概念是锁定模式。锁定模式“S”（共享）允许事务读取一行。多个事务可以同时获取特定行的锁。

“X”（排他）锁允许单个事务获取它。事务可以更新或删除行，而另一个必须等到锁被释放才能获取它。

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

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

3. 隔离级别

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

READ UNCOMMITTED ：简而言之，所有事务都可以读取其他人所做的所有更改，即使他们没有提交
READ COMMITTED：只有已提交的更改对其他事务可见
可重复读取：第一个查询定义了一个快照，它成为该行的基线。即使另一个事务在读取后立即更改了行，如果在第一次查询后没有更改，基线也将始终返回
SERIALIZABLE：行为与前一个完全一样，除了如果禁用自动提交，它会在任何更新或删除期间锁定行，并且仅在提交后才允许读取

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

4. 监控

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

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

4.1。InnoDB 状态

命令[SHOW ENGINE INNODB STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-engine.html)向我们展示了有关内部结构、对象和指标的大量信息。根据可用和活动连接的数量，输出可能会被截断。但是，我们只需要查看我们用例的事务部分。

在交易部分，我们会发现如下内容：

活跃交易数量
每笔交易的状态
每个事务涉及的表数
事务获取的锁数
可能执行的语句可能持有事务
关于锁等待的信息

那里还有更多可看的东西，但现在对我们来说已经足够了。

4.2.进程列表

命令[SHOW PROCESSLIST](https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html#:~:text=The%20SHOW%20PROCESSLIST%20statement%20is,those%20belonging%20to%20other%20users.)显示一个表，其中包含当前打开的会话，该表显示以下信息：

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

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

4.3.选择语句

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. 测试场景

要记住的最重要的事情是，当一个事务正在等待另一个事务获得的锁时，会发生“超出锁定等待超时”错误。

事务等待的时间取决于在全局或会话级别定义的属性[innodb_lock_wait_timeout](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_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';
 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';

正如我们所看到的，两条语句都成功执行，因为它们不再需要同一行的锁。

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

SELECT * FROM performance_schema.data_locks;

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

what-causes-lock-wait-timeout-exceeded-error-in-mysql.png

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

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条件匹配的行，这也会导致这些行被锁定。

确保我们的陈述是最优的很重要。

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;

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

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

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

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

6.7.表锁

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

LOCK TABLE zipcode WRITE;

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

SHOW PROCESSLIST;

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

UNLOCK TABLES;

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

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

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

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