连接丢失/断开连接的事务的MySQL回滚 [英] MySQL rollback on transaction with lost/disconnected connection

问题描述

我需要让MySQL服务器在客户端断开连接后立即回滚事务，因为每个客户端可以同时工作.可以像这样重现该问题(使用innodb表类型)

在客户A上:

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:

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的事务.

I had set MySQL's server option like innodb_rollback_on_timeout and using mysql's client mysql --skip-reconnect on 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 tblone on 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协议不是很健谈，因此服务器永远不会知道客户端不在那里.与客户端和服务器内部进行更多对话的其他数据库系统相比，我认为这是该协议中的一个缺陷.

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.

无论如何.您可以更改两个变量.他们基本上是相同的，只是针对不同的客户.

Anyway. There are two variables that you could change. They basically do the same but for different clients.

第一个是 wait_timeout 它由Java或php之类的应用程序客户端使用.

The first is wait_timeout and it is used by application clients like java or php.

另一个是 interactive_timeout 并由mysql客户端使用(如您的测试中一样)

The other is interactive_timeout and it is used by the mysql client (as in your tests)

在这两种情况下，服务器都会在几秒钟后终止连接，并且这样做会回滚所有事务并释放所有锁.

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.

这篇关于连接丢失/断开连接的事务的MySQL回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！