MySQL在失去/断开连接的事务中的回滚

Question

我需要让MySQL服务器在客户端断开连接后立即回滚事务，因为每个客户端都在同时工作。可以通过以下方式重现问题（使用innodb表类型）：

在客户端A上：

START TRANSACTION;
SELECT MAX(ID) FROM tblone FOR UPDATE;
#... then disconnect your connection to the server

在客户端B上：

START TRANSACTION;
SELECT MAX(ID) FROM tblone FOR UPDATE;
#... lock wait time out will occur here

我已经设置了MySQL的服务器选项，比如innodb_rollback_on_timeout，并在两个客户端上使用mysql的客户端mysql --skip-reconnect。在一个网络上，我尝试使用一个服务器和两个客户端进行测试。我在SELECT ... FOR UPDATE;语句后，物理断开了网络连接（拔掉电缆）。我需要让其他客户端立即能够在事务中使用tblone（锁定它，更新它），为此我认为服务器应该在客户端A断开连接后回滚客户端A的事务。

- qsoft

---

有趣的问题。我以为这是自动的！所以我们需要像innodb_rollback_on_disconnect这样的东西..那将会很棒，我认为应该成为默认设置！这将是mysql的一个合理变更请求。 - Tomas

@Tomas 在2012年首次提出这个问题后，我也遇到了同样的问题！我编写了一个简单的Python脚本来模拟这个问题，并发现第二个客户端立即获得锁定，如果第一个客户端失去了连接。但我不明白MySQL是如何做到的，请参考github.com/qiulang/mysql。 - Qiulang

Answer 1

当你物理断开一个客户端连接时，你不会发送正常的断开连接（这将导致回滚），而且MySQL协议并不是非常善谈，所以服务器永远不知道客户端是否在那里。与其他数据库系统相比，我认为这是协议的一个缺陷，因为客户端和服务器之间的内部通信要多得多。

无论如何，有两个变量可以更改。它们基本上执行相同的操作，但针对不同的客户端。

第一个是wait_timeout，它由应用程序客户端（如java或php）使用。

另一个是interactive_timeout，它由mysql客户端使用（就像您的测试一样）

在这两种情况下，服务器在若干秒后终止连接，并在这样做时回滚所有事务并释放所有锁定。

- Andreas Wederbrand

---

谢谢您的回复，我已经尝试使用两个选项，并将它们设置为60秒（用于实验），但又出现了另一个问题。在60秒的不活动时间后，连接会自动关闭，下一个查询会产生错误（服务器已断开连接），然后自动重新连接。我需要编写一些代码来查询每59秒以确保连接处于活动状态吗？还是有其他方法？如果查询时间超过60秒，会在过程中断开连接吗？ - qsoft

哇，谢谢。所以我只需要在 START TRANSACTION; 前设置 SET @old_wait_timeout := @@session.wait_timeout; SET @@session.wait_timeout := 60;，然后在 COMMIT 或 ROLLBACK 后使用 SET @@session.wait_timeout := @old_wait_timeout; 恢复它。希望这样可以解决问题。 - qsoft

@qsoft 我的理解是：wait_timeout 是一个非活动超时时间。这意味着在每次激活后它都会被清除并重新启动，例如：表扫描、行插入、网络数据包接收等。因此，在事务下，我们可以将此选项设置为较小的值（例如：5秒）。这样更适合于错误检测和故障恢复。 - ASBai

@AndreasWederbrand 我的情况是第一个客户端意外崩溃（由于其中的错误），从MySQL的角度来看，这是否与物理断开客户端相同？我需要在第一次事务回滚之前等待wait_timeout吗？ - Qiulang

@Qiulang - 我的回答希望能够解决你最后的评论。 - Rick James

Answer 2

这里是讨论一些评论。请注意，这与某些评论不同意。我将使用INSERT而不是SELECT..FOR UPDATE，因为效果更明显。

让我们看一些不同的情况：

(1) 没有SQL + 超时

START TRANSACTION;
do some SQL statement(s)
do no SQL for more than the timeout (before COMMITing)

避免这种情况，因为下面详细列出了案例。解决方案：不要依赖InnoDB来帮助您处理长事务。

(2) 长时间运行的查询。

START TRANSACTION;
do some SQL statement(s)
run an SQL query that takes more than the timeout
COMMIT;

一切正常。只要服务器（mysqld）继续执行查询，超时就不适用。也就是说，每个SQL语句结束后，超时“时钟”就重新开始计时。

(3)（自动重连）

START TRANSACTION;
INSERT ... VALUES (123);
    time passes; no SQL performed for longer than the timeout
    disconnect occurs
INSERT ... VALUES (456);
    auto-reconnect (because you have it ENabled);
    the INSERT proceeds
COMMIT;

123将被回滚；456将被插入。(类似地，SELECT..FOR UPDATE将失去锁定。)不好。解决方案是关闭“自动重新连接”。相反，检查错误并将断开连接的错误视为事务的致命错误。(然后重新启动事务。)

INSERT 456将在由autocommit控制的新事务中运行。

(4) (无自动重新连接)

START TRANSACTION;
INSERT ... VALUES (123);
    time passes; no SQL for longer than the timeout
    disconnect occurs
INSERT ... VALUES (456);
    NO auto-reconnect (because you have it DISabled)
COMMIT;

123将会被回滚。插入456将会出现类似"连接丢失"的错误。重新开始事务。

- Rick James

---

应该是 innodb_lock_wait_timeout，默认为50秒。 - Rick James

我会编写一些代码来验证它。这应该很容易验证。 - Qiulang

@Qiulang - 我对那个SO中的答案有一些异议，所以我添加了另一个答案。那个问答并没有涉及自动重新连接；你的问题和我的答案（在这里）确实涉及到了它。 - Rick James

好的，我在这里找到了https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-shared-exclusive-locks，其中提到“SELECT ... FOR UPDATE将设置一个意向排他锁”。如果它是ix锁，那么我就不明白我看到了什么。 - Qiulang

@Qiulang - 由于innodb_lock_wait_timeout可以设置为GLOBAL和SESSION，因此您实际上只能在自己的连接中看到该值的影响，而无法在其他地方看到。 （当然，很少有人更改该设置。） - Rick James