MariaDB /MySQL 数据库锁故障处理（请求超时、error: 1205）

原创小小亮 2022-10-12

669

数据库锁真的很重要。没有它们，关系数据库管理系统无法向我们保证ACID 模型的原子性和隔离性属性。它们一直在使用，常常在我们没有意识到的情况下，为我们节省了很多痛苦。

文章概览

我们不会涵盖所有不同类型的锁以及它们的工作原理，已经有很多很棒的帖子涵盖了这一点。我们将回顾：

数据库锁出错的症状
方便的查询以帮助诊断根本原因
确定根本原因后如何解决锁定问题

这些示例是针对 MariaDB 的，但它们也应该在很大程度上涵盖 MySQL。

✏️如果您想跟随诊断查询以获得一些实践经验，请设置一个简单的学生表，如下所示，稍后注意✏️符号。
CREATE TABLE students
(
id int auto_increment primary key,
first_name varchar(191) not null,
last_name varchar(191) not null
);

应用程序症状

您可能在应用程序上遇到的数据库锁定问题的常见症状是：

请求超时（HTTP 502、503 或 504 响应代码）
查询超出了它们的max_statement_time或max_execution_time
后台作业运行了很长时间并可能超时
迁移未能完成
由于无法获取锁而导致死锁受害者错误
看到类似“SQLSTATE [HY000]：error: 1205 超过锁定等待超时；尝试重启交易”

如何诊断根本原因

在本节中，我们将介绍一些方便的查询，以便确定数据库锁定是否是导致应用程序问题的原因以及如何缩小导致问题的特定线程。

快速说明：其中一些查询需要启用性能模式（任何时候您看到针对performance_schema数据库运行的查询）。

显示完整的进程列表

SHOW FULL PROCESSLIST;

这将列出数据库上的所有当前连接（线程）、用户、主机、线程的默认数据库、它们正在运行的查询以及该查询的状态。

例如，在上面的屏幕截图中，我们可以看到线程 ID 11 当前遇到锁，ALTER TABLE语句为Waiting for table metadata lock. 线程 ID 11 不是持有有问题的锁的线程，但它无法运行查询，因为锁已经到位。

该Time列可以让您大致了解查询处于该状态的时间。

如果您遇到数据库迁移超时、查询超时或请求接收 502 的问题，并且您在其中看到类似的内容，PROCESSLIST那么这很好地表明表已被锁定的时间比预期的要长。

如果您启用了性能模式，建议SELECT * FROM performance_schema.threads在 MariaDB 和performance_schema.processlistMySQL 上使用。

元数据锁定信息

SELECT * FROM information_schema.metadata_lock_info;

在 MySQL 和 MariaDB > 10.5.2 上，您可以performance_schema.metadata_locks改用

当查询卡在状态时，此查询很有用Waiting for table metadata lock。当数据库迁移超时时，您可能会看到这一点。

✏️ 要复制此场景，请在一个 SQL 会话中运行以下命令：
SET autocommit=0;
START TRANSACTION;
INSERT INTO students (first_name, last_name) VALUES (“Joe”, “Bloggs”);
并在另一个 SQL 会话中尝试运行：
ALTER TABLE students ADD COLUMN date_of_birth DATE;
第二个查询应该挂起并且无法完成。

SELECT * FROM information_schema.metadata_lock_info

在这里我们可以看到它是线程 ID 10，它在表上持有表元数据锁（写）students。

事务在它访问的每个表上获取元数据锁，以防止其他连接改变该数据库资源的结构。锁在事务结束时被释放。

想要改变表结构的数据库迁移需要在表上获取元数据锁。

更具体地说，为了整理输出，您可以运行以下查询来显示哪些线程被其他线程持有的元数据锁阻塞：

SELECT CONCAT(
    'Thread ', P.ID, ' executing "', P.INFO, 
    '" IS LOCKED BY Thread ', M.THREAD_ID) WhoLocksWho 
FROM INFORMATION_SCHEMA.PROCESSLIST P,
     INFORMATION_SCHEMA.METADATA_LOCK_INFO M 
WHERE LOCATE(lcase(LOCK_TYPE), lcase(STATE)) > 0
AND P.ID != M.THREAD_ID;

识别正在进行的数据库事务

✏️ 要复制本节中的场景，请在一个 SQL 会话中运行：
SET autocommit=0;
START TRANSACTION;
INSERT INTO students (first_name, last_name) VALUES (“Joe”, “Bloggs”);
并在另一个 SQL 会话中尝试运行：
SELECT * FROM students FOR UPDATE;
第二个查询应该挂起并且无法完成。

select * from INFORMATION_SCHEMA.INNODB_TRX\G;

该INNODB_TRX 表列出了所有活动事务，并提供了有关事务具有的任何锁的有用信息。

上面我们可以看到线程 11 有一个活动的数据库事务，但它处于状态LOCK WAIT。如果我们向下滚动输出，我们可以看到一个持有锁的线程。该trx_started字段可以让您了解锁定的持有时间。这trx_isolation_level对于找出事务隔离级别也很有用。

如果只有这 2 个并发事务，那么很明显哪个阻塞了哪个（10 阻塞了 11）。但是，在繁忙的数据库中，您可能会返回数千个结果。我们需要变得更加狡猾。

INNODB_LOCKS、INNODB_LOCK_WAITS 和连接点

信息模式上的INNODB_LOCKS 表存储有关事务已请求的锁或阻塞其他事务的锁的信息。

在 MySQL 上，等效的是性能模式上的data_locks表。

在以下部分中，让我们使用拥有 2 个线程的场景：

第一个线程已经开始了一个事务，它打算在表中插入一行students，但它被卡住了，没有提交或回滚事务。
第二个线程想要执行SELECT x FROM students FOR UPDATE查询。

我们可以看到每个事务的 2 个锁的列表，并且可以看到它们是lock_mode X学生表上的排他锁 ( )。但是，尚不清楚哪个事务阻塞了哪个事务。

信息模式上的INNODB_LOCK_WAITS 表存储有关被阻止事务的信息，以及它们被阻止的事务。

在 MySQL 上，等效的是性能模式上的data_lock_waits表。

我们可以看到请求锁的事务和阻塞它的相应事务！现在我们正在取得进展。

我们知道哪个事务阻塞了哪个事务，但是需要进一步诊断才能知道被阻塞的查询。这将帮助我们了解这个被阻塞的事务是否是我们的应用程序超时的原因。

知道线程 ID 也很好，这样我们就可以终止阻塞事务并释放我们的应用程序。

SELECT r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx b 
      ON b.trx_id = w.blocking_trx_id 
INNER JOIN information_schema.innodb_trx r
      ON r.trx_id = w.requesting_trx_id;

上面的查询将我们上面描述的三个表连接在一起，它准确地向我们展示了我们所追求的：

属于线程 11 的事务 ID 873219 正在等待执行SELECT * FROM students FOR UPDATE，这需要更新锁，但它被线程 15 拥有的事务 ID 873214 阻塞。

可以在此处找到对 MySQL 的类似查询。

如何解决锁

至此，您将知道阻止查询并导致应用程序问题的线程 ID。您还应该对发生的确切情况有更多的了解，以防止问题再次发生。

您可以运行以下命令来终止线程正在运行的阻塞查询，但保持连接本身不变：

KILL QUERY 15

这将终止线程 ID 15 当前正在运行的查询，这会阻止我们的其他查询。

如果这不起作用，您可能需要终止整个连接，您可以通过以下方式完成：

KILL 15

如果问题再次出现怎么办？

如果问题再次发生，通过我们描述的查询，您应该能够收集足够的信息来诊断根本原因并查明应用程序中的问题区域。

例如，在计划的后台作业中引入的错误代码更改可能每隔几分钟就会运行一次并重新触发问题。

如果您为不同的连接系统使用不同的数据库用户或主机，则查看违规线程 ID返回的User和Host列可以进一步细化问题的根源。SHOW FULL PROCESSLIST

另一个需要注意的常见问题是在您选择的 SQL 编辑器中使用手动事务提交模式而忘记提交您的更改！正如我们在这篇文章中所展示的，这可以保持锁打开并防止其他查询建立锁，直到您提交更改😰

最后

希望这篇文章有助于理解如何诊断数据库锁何时是您的应用程序问题的原因，以及如何查明确切的有问题的查询，以便您可以杀死它。

我很想听听任何其他的方法或对这里描述的查询的改进，欢迎评论留言！

原文标题：Diagnosing Database Locks

原文作者：Joe Alamo-Keilty
原文链接：https://engineering.oneutilitybill.co/diagnosing-database-locks-e83c05ac7611

mysql mariadb 墨力翻译小小亮的翻译文章

最后修改时间：2022-10-12 15:15:00

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者