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

这个线程的一半以上似乎是关于如何在ServerFault上提出问题。 我认为这个问题是有道理的，而且非常简单：如何自动回滚停滞的交易？

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

既然你的问题在这里被问到ServerFault，那么假设你正在寻求一个MySQL解决scheme来解决MySQL问题是合乎逻辑的，特别是在系统pipe理员和/或DBA所具有的专业知识领域。因此，以下部分解决您的问题：

如果第一笔交易从未提交或回滚，则第二笔交易将被无限期封锁

不，不会的 我想你不了解innodb_lock_wait_timeout 。 它正是你所需要的。

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

 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

• 根据定义，这不是不确定的 。 如果您的应用程序重复连接和阻塞，那么您的应用程序将“无限期阻塞”，而不是事务。 第二个事务非常明确地为innodb_lock_wait_timeout秒钟阻塞。

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

如果你想自动回滚，这也在手册中解释：

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

RE：您的许多更新和评论

首先，你在你的评论中说过，你的意思是说，你想要一种方法来超时无限期地阻塞第一个事务。 从原来的问题来看，这并不明显，并且与“如果第一笔交易从未提交或回滚，则第二笔交易将被无限期封锁”。

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

唯一的另一种方法是使用或编写一个利用非阻塞I / O的mysql库，这将允许你的应用程序在N秒之后终止查询的线程/分支。 这样的库的实现和使用超出了ServerFault的范围。 这是StackOverflow的一个合适的问题。

其次，你在评论中说了以下几点：

实际上，在事务过程中，客户端应用程序挂起（比如陷入无限循环）的情况比在事务处理需要很长时间的情况下更让我担心。

这在你原来的问题中并不明显，但现在还不是。 只有在评论中分享了这个相当重要的珍闻之后才能看出这一点。

如果这实际上是你正在努力解决的问题，那么恐怕你在错误的论坛上提出了这个问题。 你已经描述了一个应用级编程的问题，它需要一个编程解决scheme，MySQL不能提供这个解决scheme，并且超出了这个社区的范围。 您的最新答案回答了“如何防止Ruby程序无限循环？”的问题。 这个问题是这个社区脱离主题，应该问StackOverflow 。

在类似的情况下，我的团队决定使用pt-kill（ https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html ）。 它可以报告或终止（其中包括）运行时间过长的查询。 然后可以每X分钟运行一次cron。

问题在于，执行时间意外长（例如不确定）的查询locking了一个尝试使用读取locking刷新表的查询，这反过来locking了“等待表清空”的所有其他查询，而这些查询从未超时因为他们没有等待locking，导致应用程序没有错误，最终导致pipe理员和应用程序停止提醒。

这个修复显然是为了修复最初的查询，但为了避免将来发生意外情况，如果可以自动杀死（或报告）比特定时间更长的事务/查询，这个问题的作者似乎在问。 这可以通过pt-kill来实现。

在search了一段时间之后，看起来没有直接的方法来设置每个事务的时间限制。 这是我能find的最佳解决scheme：

命令

 SHOW PROCESSLIST; 

为您提供一个表格，其中包含当前正在运行的所有命令以及自启动以来的时间（以秒为单位）。 当客户端停止发送命令时，它们被描述为给出Sleep命令，其中Time列是自上次命令完成以来的秒数。 因此，如果您确信没有查询在数据库上的查询时间应该超过5秒，那么您可以手动进入并KILL Time值超过5秒的所有内容。 例如，如果id为3的进程的“ Time列中的值为12，则可以这样做

 KILL 3; 

KILL语法的文档表明，由具有该id的线程执行的事务将被回滚（尽pipe我没有对此进行testing，所以要谨慎）。

但是如何自动化这个并且每隔5秒就杀死所有加class脚本呢？ 在同一页面上的第一个评论给了我们一个提示，但代码是在PHP中; 看起来我们不能在MySQL客户端中对SHOW PROCESSLIST执行SELECT操作。 不过，假设我们有一个PHP守护进程，我们每运行$MAX_TIME秒，它可能看起来像这样：

 $result = mysql_query("SHOW FULL PROCESSLIST"); while ($row=mysql_fetch_array($result)) { $process_id=$row["Id"]; if ($row["Time"] > $MAX_TIME) { $sql="KILL $process_id"; mysql_query($sql); } } 

请注意，这将会强制所有闲置的客户端连接重新连接，而不仅仅是那些行为不端的副作用。

如果有人有更好的答案，我很乐意听到。

编辑：这种方式至less有一个使用KILL严重风险。 假设您使用上面的脚本来KILL每个闲置了10秒的连接。 连接空闲10秒后，但在KILL发出之前，连接KILL的用户将发出START TRANSACTION命令。 他们然后KILL编辑。 他们发送一个UPDATE ，然后，思考两次，发出一个ROLLBACK 。 但是，由于KILL回滚了原来的事务，所以更新会立即进行，无法回滚！ 看到这个post的testing用例。

正如hobodave在评论中所build议的那样，在某些客户端（尽pipe不是，显然是mysql命令行实用程序）可以为事务设置一个时间限制。 以下是在Ruby中使用ActiveRecord的示例：

 require 'rubygems' require 'timeout' require 'active_record' Timeout::timeout(5) { Foo.transaction do Foo.create(:name => 'Bar') sleep 10 end } 

在此示例中， 事务在5秒后超时并自动回滚 。 （ 响应hobodave的评论更新：如果数据库需要超过5秒的响应时间，交易将尽快回滚。）如果您想确保所有交易在n秒之后超时，你可以围绕ActiveRecord构build一个包装器。 我猜这也适用于Java，.NET，Python等最受欢迎的库，但是我还没有testing过它们。 （如果有，请发表评论这个答案。）

ActiveRecord中的事务也具有在发出KILL安全的优点，与从命令行完成的事务不同。 请参阅https://dba.stackexchange.com/questions/1561/mysql-client-believes-theyre-in-a-transaction-gets-killed-wreaks-havoc 。

除了通过我在其他答案中发布的脚本之外，似乎没有可能在服务器端强制执行最大交易时间。