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屋！