MySQL rollback on transaction with lost/disconnected connection
Solution 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.
Solution 2
This is to discuss some of the Comments. Please note that this disagrees with some of the Comments. I'll use an INSERT instead of SELECT..FOR UPDATE because the effect is more visible.
Let's look at some different cases:
(1) No SQL + timeout
START TRANSACTION;
do some SQL statement(s)
do no SQL for more than the timeout (before COMMITing)
Avoid this because of the cases detailed below. Solution: Don't depend on InnoDB to help you with long transactions.
(2) long-running query
START TRANSACTION;
do some SQL statement(s)
run an SQL query that takes more than the timeout
COMMIT;
All is well. The timeout does not apply as long as the server (mysqld) is continuing to perform queries. That is, the timeout 'clock' starts over at the end of each SQL statement
(3) (auto-reconnect)
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 will be rolled back; 456 will be inserted. (Similarly SELECT..FOR UPDATE would lose the locks.) Not good. The solution is to turn off "auto-reconnect". Instead, check for errors and treat the disconnect error as a fatal error for the Transaction. (Then start over the transaction.)
The INSERT 456 will be running in a new transaction, controlled by autocommit
.
(4) (NO auto-reconnect)
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 will be rolled back. The INSERT of 456 will get an error something like "connection lost". Start the transaction over.
Comments
-
qsoft almost 2 years
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 clientmysql --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) afterSELECT ... FOR UPDATE;
line. I need to make other clients to be able to usetblone
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. -
qsoft about 12 yearsThank you for your respond, and I have tried using both option, and set them to 60 seconds (for experiment), but another problem arise. After 60 seconds of inactivity (idle) the connection automatically closed, and the next query produced an error (server has gone away) and then automatically reconnect. Do I need to code something to query every 59s to make sure the connection alive? Or is there another way? Is a long query that takes more than 60s will get disconnected in the middle of the process?
-
qsoft about 12 yearsBecause I only need this behavior while in transaction, can I do something like
SET SESSION wait_timeout = 60
just before transaction and restore it after a commit/rollback? -
Andreas Wederbrand about 12 yearsYou should be able to change it within your stored procedure or as a separate statement before you start a transaction. And you are right about the idle connection being closed. That is how it works. However, long running queries doesn't count as "idle" as far as I've seen so that should be safe (easy to test with
select 1, sleep(61) from dual
) -
qsoft about 12 yearsWow thanks, so I just set
SET @old_wait_timeout := @@session.wait_timeout; SET @@session.wait_timeout := 60;
just beforeSTART TRANSACTION;
and then restore it after aCOMMIT
orROLLBACK
usingSET @@session.wait_timeout := @old_wait_timeout;
. I hope this works -
ASBai over 7 years@qsoft My understand: wait_timeout is a inactive timeout. Means it will be cleared and restart after every activation, e.g: table scan, row insert, network package received, and etc. So under a transaction, we can set this option to a smaller value (e.g.: 5 seconds). That would be more suitable for error detection and failure recovery.
-
Qiulang almost 3 years@AndreasWederbrand my case is the first client crashes unexpectedly (due to bug in it) is it the same physically disconnecting a client from mysql point of view ? Do I need to wait
wait_timeout
before the first transaction to be rollback ? -
Rick James almost 3 years@Qiulang - My Answer hopefully addresses your last Comment.
-
Qiulang almost 3 yearsThanks for the answer. I probably need to point out that what you described here is more likely happened with mysql client not with a running program. Your answer serves more for a DBA problem than for a programming problem. BTW, the default value for wait_timeout is 8 hours. I feel it is too long for a running thread/process
-
Rick James almost 3 yearsIt should be
innodb_lock_wait_timeout
, which defaults to 50 seconds. -
Qiulang almost 3 yearsI will write some code to verify it. It should be easy to verify.
-
Qiulang almost 3 yearsHi I wrote a simple python script to test our discussion. But to my surprise when the first process returns without closing the DB connection explicitly (to emulate crash),the 2nd process always notice immediately. Can you take a look ? github.com/qiulang/mysql
-
Rick James almost 3 years@Qiulang - I don't see anything equivalent to
START TRANSACTION
in thecrash_test.py
-
Qiulang almost 3 yearsHi I think the transaction is started automatically (by mysql driver) so I just to call commit(). I will double check that.
-
Qiulang almost 3 yearsHi please refer to here stackoverflow.com/a/52723551/301513
-
Rick James almost 3 years@Qiulang - I have some quibbles with the Answer in that SO; I added another Answer. That Q&A does not deal with auto-reconnect; your Q and my A (here) does deal with it.
-
Qiulang almost 3 yearsBut regarding to my code, were you concerned that doesn't actually use a transaction ? From my experience and from what I see I think it starts a transaction. But my question is when the 1st process holds the lock doesn't close the connection, the 2nd process gets the lock immediately. How can the 2nd process get the lock?
-
Rick James almost 3 years@Qiulang - There are (at least) two kinds of locks. The stronger one is "exclusive" (
X
); it prevents reads and writes. Search formysql locks exclusive shared intention read write
-
Qiulang almost 3 yearsOK I found dev.mysql.com/doc/refman/8.0/en/… here ”SELECT ... FOR UPDATE sets an intention exclusive lock“. If it is an ix lock then I can't understand what I saw.
-
Qiulang almost 3 yearsBTW, it is easy to observe innodb_lock_wait_timeout from script that is another reason I believe they are run in a transaction.
-
Rick James almost 3 years@Qiulang - Since innodb_lock_wait_timeout can be set as GLOBAL and SESSION, you can't actually see the value in effect anywhere but your own connection. (Sure, it is rare for anyone ot change that setting.)
-
Qiulang almost 3 yearsLet us continue this discussion in chat.