Open up two mysql
command-line clients and connect to your database. In client #1, enter
START TRANSACTION;
In client #2, use SHOW PROCESSLIST
, then
KILL [n];
where n
is the id for client #1's connection. Bam—transaction rolled back. But client #1 doesn't know that. Then from client #1, send some command—say,
UPDATE clients SET important_field = NULL;
You'll get the response:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Query OK, 10000 rows affected (0.05 sec)
Rows matched: 10000 Changed: 10000 Warnings: 0
Oops, you say, better hit ROLLBACK!
Then you realize, to your horror, that you're not in a transaction anymore.
Here's my question: In case I want to KILL
a connection at some point, is there any way I can ensure that this "now you're in a transaction, now you're not" scenario doesn't happen, short of setting autocommit to 0 at the system level?
Note that the above is tested under mysql
5.1—if later versions provide a fix, I'd love to hear about it. I'd also love to see tests done with JDBC, ADO.NET, etc. to see whether they're susceptible to this same issue.
(On a meta note, this question arose from two questions over at Server Fault. I'm really hoping that the DBA community will prove more helpful...)
Update: See my answers below. This issue appears to be unique to the mysql
command-line utility, with its bizarre auto-reconnect "feature." Most likely, any tool or library not built on top of the mysql
utility will not exhibit this behavior. However, you may want to test whatever you're using to be sure, or take drachenstern's suggestion and wrap your transactions in stored procedures.