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.
Best Answer
What was wrong with the answers on those two questions? They were 100% accurate, so I'm not sure what more we can do to help you here.
I would suggest that you confirm that you never assume that you're in a transaction. Always check to make sure you are. In TSQL it would be as simple as checking @@TRANCOUNT to be greater than 0. That's rather the same as any threaded situation where you want to check mutexes. What happens if you do it with a stored procedure? It kills the stored procedure, yes? Because SPs are intended to be atomic. What you're demonstrating has nothing to do with atomicity.
To be clear, this behavior is by design! Don't run atomic transactions by hand in the console, put them in a program so if the connection goes away it's gone. This is not something that you can "just hope works right".
If you have something that must be ACID, you must put it in a container that can be made ACID. This means a stored procedure or the like.