MySQL 在丢失/断开连接的事务上回滚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9936699/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL rollback on transaction with lost/disconnected connection
提问by qsoft
I need to make MySQL server to rollback transaction immediately after its client disconnected, because each client works concurrently. The problem can be reproduced like these (using an innodb table type)
On Client A:
我需要让 MySQL 服务器在其客户端断开连接后立即回滚事务，因为每个客户端都是并发工作的。问题可以像这样
在客户端 A上重现（使用 innodb 表类型）
：
START TRANSACTION;
SELECT MAX(ID) FROM tblone FOR UPDATE;
#... then disconnect your connection to the server
On Client B:
在客户端 B 上：
START TRANSACTION;
SELECT MAX(ID) FROM tblone FOR UPDATE;
#... lock wait time out will occur here
I had set MySQL's server option like innodb_rollback_on_timeoutand using mysql's client mysql --skip-reconnecton both client. I tried this using one server and two client, on a network. I disconnected the network physically (unplug the cable) after SELECT ... FOR UPDATE;line. I need to make other clients to be able to use tbloneon a transaction (lock it, update it) immediately, and for that to happen I think the server should rollback the transaction for Client A, after Client A disconnects.
我已经设置了 MySQL 的服务器选项，比如在两个客户innodb_rollback_on_timeout端mysql --skip-reconnect上都使用了 mysql 的客户端。我在网络上使用一台服务器和两个客户端进行了尝试。我在SELECT ... FOR UPDATE;线路后物理断开网络（拔下电缆）。我需要让其他客户端能够立即tblone在事务上使用（锁定它，更新它），为此我认为服务器应该在客户端 A 断开连接后回滚客户端 A 的事务。
回答by Andreas Wederbrand
When you are physically disconnecting a client you're not sending a normal disconnect (which would have caused a rollback) and the MySQL protocol isn't very chatty so the server never knows that the client isn't there. I think this is a flaw in the protocol when comparing to other database systems where the client and server talks internally much more.
当您物理断开客户端时，您不会发送正常的断开连接（这会导致回滚）并且 MySQL 协议不是很健谈，因此服务器永远不会知道客户端不存在。我认为与客户端和服务器在内部进行更多对话的其他数据库系统相比，这是协议中的一个缺陷。
Anyway. There are two variables that you could change. They basically do the same but for different clients.
反正。您可以更改两个变量。他们基本上做相同的事情，但针对不同的客户。
The first is wait_timeoutand it is used by application clients like java or php.
第一个是wait_timeout，它由java 或php 等应用程序客户端使用。
The other is interactive_timeoutand it is used by the mysql client (as in your tests)
另一个是interactive_timeout，它由mysql客户端使用（如在您的测试中）
In both cases the server to kills the connection after a number of seconds and when doing so rollbacks all transactions and releases all locks.
在这两种情况下，服务器都会在几秒钟后终止连接，并且这样做时会回滚所有事务并释放所有锁。
