MySQL rollback on transaction with lost/disconnected connection

Question

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

On Client 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_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.

Answer 1

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.

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

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.