Open up two
mysql command-line clients and connect to your database. In client #1, enter
In client #2, use
SHOW PROCESSLIST, then
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.