☰

在 MySQL/InnoDB 中设置事务的时间限制

tag-icon • tag-icon mysql innodb
在 MySQL/InnoDB 中设置事务的时间限制

这源于这个相关问题，我想知道如何在一个简单情况下（两个事务都只对一行进行操作）强制两个事务按顺序发生。我得到了一个答案 — 用作SELECT ... FOR UPDATE两个事务的第一行 — 但这会导致一个问题：如果第一个事务从未提交或回滚，那么第二个事务将被无限期地阻止。该innodb_lock_wait_timeout变量设置了多少秒后，尝试进行第二次交易的客户端会被告知“抱歉，请重试”……但据我所知，他们会再次尝试，直到下一次服务器重启。所以：

  1. 如果交易永远持续下去，肯定有办法强制终止ROLLBACK交易？我必须使用守护进程来终止此类交易吗？如果是，这样的守护进程会是什么样子？
  2. 如果连接在事务中途被终止wait_timeout，interactive_timeout事务是否会回滚？有没有办法从控制台测试这一点？

澄清：innodb_lock_wait_timeout设置事务在放弃之前等待锁释放的秒数；我想要的是一种强制释放锁。

更新 1：这里有一个简单的例子，说明了为什么innodb_lock_wait_timeout不足以确保第二个事务不被第一个事务阻塞：

START TRANSACTION;
SELECT SLEEP(55);
COMMIT;

使用默认设置innodb_lock_wait_timeout = 50，此事务将在 55 秒后无错误地完成。如果您UPDATE在该SLEEP行前面添加，然后从尝试访问SELECT ... FOR UPDATE同一行的另一个客户端启动第二个事务，则超时的是第二个事务，而不是休眠的事务。

我正在寻找一种方法来强制结束这项交易的沉睡。

更新 2：为了回应 hobodave 对上述示例是否真实的担忧，这里给出了另一种方案：DBA 连接到实时服务器并运行

START TRANSACTION
SELECT ... FOR UPDATE

第二行锁定了应用程序经常写入的行。然后 DBA 被打断并离开，忘记结束事务。应用程序会停止运行，直到行被解锁。我希望尽量减少应用程序因这个错误而卡住的时间。

答案1

这个帖子的一半以上似乎都是关于如何在 ServerFault 上提问。我认为这个问题很有意义，而且很简单：如何自动回滚停滞的事务？

如果你愿意终止整个连接，一个解决方案是设置 wait_timeout/interactive_timeout。请参阅https://stackoverflow.com/questions/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection。

答案2

由于您的问题是在 ServerFault 上提出的，因此可以合理地假设您正在寻找 MySQL 问题的 MySQL 解决方案，特别是在系统管理员和/或 DBA 所擅长的知识领域。因此，以下部分将解决您的问题：

如果第一个事务从未提交或回滚，那么第二个事务将被无限期阻止

不会。我觉得你不明白innodb_lock_wait_timeout。它确实满足您的需要。

它将返回一个错误，如手册中所述：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  • 根据定义，这不是不定。如果您的应用程序反复重新连接和阻塞，那么你的申请是“无限期阻塞”，而不是事务。第二个事务非常明确地阻塞了几innodb_lock_wait_timeout秒钟。

默认情况下，事务不会回滚。您的应用程序代码负责决定如何处理此错误，无论是重试还是回滚。

如果您想要自动回滚，手册中也有解释：

当前事务不会回滚。（要使整个事务回滚，请使用以下命令启动服务器--innodb_rollback_on_timeout选项。

回复：您的众多更新和评论

首先，你在评论中表示，你“想”说你想要一种方法来超时第一的无限期阻塞的事务。这从您的原始问题中看不出来，并且与“如果第一个事务从未提交或回滚，则第二个事务将被无限期阻塞”相冲突。

尽管如此，我也可以回答这个问题。MySQL 协议没有“查询超时”。这意味着您无法使第一个被阻止的事务超时。您必须等到它完成，或者终止会话。当会话被终止时，服务器将自动回滚事务。

唯一的其他选择是使用或编写一个使用非阻塞 I/O 的 mysql 库，这将允许你的应用在 N 秒后终止执行查询的线程/分支。此类库的实现和使用超出了 ServerFault 的范围。这是一个适合于堆栈溢出。

其次，您在评论中指出了以下内容：

实际上，我的问题更关心客户端应用程序在事务过程中挂起（例如，陷入无限循环）的情况，而不是事务在 MySQL 端花费很长时间的情况。

这在你最初的问题中根本就不明显，现在仍然如此。只有在你在评论中分享了这个相当重要的信息后才能看出这一点。

如果这确实是您要解决的问题，那么恐怕您问错论坛了。您描述了一个应用程序级编程问题，它需要编程解决方案，而 MySQL 无法提供该解决方案，并且超出了本社区的范围。您的最新回答回答了“如何防止 Ruby 程序无限循环？”这个问题。该问题与本社区无关，应该在堆栈溢出。

答案3

我们希望终止那些空闲的并且阻塞我想要使用的表的事务。

杀死拥有锁的线程很容易，在原始问题的例子中，锁位于单个表上，该表没有进行任何更改，因此速度很快不过要小心如果被终止的事务已经运行了几个小时，并且有大量数据需要回滚，则需要一些时间才能完成回滚，然后您才能使用它。

interactive_timeout-- 将终止休眠连接，包括具有打开事务的连接；它还将终止所有没有事务的休眠连接。

innodb_rollback_on_timeout- 并不完全按照字面意思来做（如果有需要回滚的内容，InnoDB 总是会在超时时回滚某些内容——这控制了回滚的程度（参见文档）

因此，我们要精准地杀死那些需要杀死的线程。

show processlist不会帮助你，因为它只显示谁在睡觉，但没有显示谁有任何类型的锁，但这个查询会（这是 MySQL 8 可能在旧版本中起作用，我没有尝试过）：

SELECT i.trx_mysql_thread_id thread, 
    i.trx_state, 
    trx_tables_in_use tbl_used, 
    trx_tables_locked tbl_locked, 
    p.USER, 
    p.DB, 
    p.COMMAND, 
    p.TIME, 
    TIMESTAMPDIFF(SECOND, trx_started, now()) trx_time, 
    SUBSTRING(trx_query, 1, 40) 
FROM INFORMATION_SCHEMA.PROCESSLIST p 
  JOIN INFORMATION_SCHEMA.INNODB_TRX i 
    ON i.trx_mysql_thread_id = p.ID;

注意 - TIME 和 trx_time 列 - 第一个是您希望在进程列表中看到的，第二个是此事务运行的时间。不要弄错它们，如果 TIME 非常低，1 或 2 秒，则此事务似乎正在执行某项操作。还要注意 trx_tables_in_use 和 trx_tables_locked tbl_locked 列，如果这些列为 0，则此事务不会阻止另一个查询 - 但会阻止需要“元数据锁”的 ALTER TABLE 之类的操作。最后注意 trx_state，当您尝试此操作时，它很可能处于“RUNNING”状态，在您终止事务后，它可能是“ROLLBACK”，您仍然需要等待。

现在改变上述查询以仅查找您想要终止的thread_id：

SELECT i.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.PROCESSLIST p 
  JOIN INFORMATION_SCHEMA.INNODB_TRX i 
    ON i.trx_mysql_thread_id = p.ID
WHERE i.trx_state = 'RUNNING'    
    AND trx_tables_locked > 0
    AND p.COMMAND = 'Sleep'
    AND p.TIME > 60

循环遍历结果并终止这些线程。您也可以在其他表上添加条件（仅终止此用户、仅在该数据库中终止，...）您可以在事件管理器调用的存储过程中执行此操作，或者让您的代码在运行之前执行此操作（您的用户可能需要增强权限 - 我相信您可以终止您自己的用户）。

祝你好运，弗兰克

答案4

一个简单的解决方案是使用一个存储过程来终止那些耗时超过所需时间的查询timeout，并使用innodb_rollback_on_timeout与之相关的选项。

相关内容