6

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.

Trevor Burnham
  • 171
  • 7

3 Answers3

7

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.

jcolebrand
  • 6,376
  • 4
  • 43
  • 67
1

It turns out that there's a simpler answer than I'd previously suggested: When starting the mysql client, use the --disable-reconnect flag.

The original problem stemmed from the mysql command-line client's bizarre default behavior of automatically reconnecting and sending the given command, no matter how potentially harmful, if the connection has been lost. With --disable-reconnect, here's what happens after it gets killed and I send the query:

UPDATE clients SET important_field = NULL;

The response is

ERROR 2006 (HY000): MySQL server has gone away

and the query doesn't go through. Further queries also yield the same error, until I manually reconnect.

So, if you're working from the mysql command-line client on a non-trivial database, I'd recommend using --disable-reconnect by default.

This also helps to allay my concerns about bindings in Java, Ruby, etc.; while I'd certainly recommend testing any particular binding you use, it seems unlikely that any popular MySQL library would use the "automatically reconnect and resend" behavior. However, coders must be careful that their programs don't have any functions that might inadvertently do this (say, retrying an individual query after an exception is thrown rather than restarting the entire transaction).

Trevor Burnham
  • 171
  • 7
0

Use Ruby. Using the canonical mysql gem:

require 'rubygems'
require 'mysql'

my = Mysql.new '127.0.0.1', 'root', 'root', 'test_db'
my.autocommit 0
sleep 10
st = my.prepare 'insert into foo (name) values (?)'
st.execute 'bar'
my.commit

If a kill is issued during those 10 seconds of sleep, a Mysql::Error is emitted from the next line with the message "MySQL server has gone away," and the insertion doesn't occur. I put ActiveRecord through a similar test; not surprisingly, its behavior was identical, since it's built on top of the mysql gem.

So if you use Ruby's mysql library, you can work without fear of KILL-related catastrophe. This also means you could use an irb session to perform a safe transaction interactively.

I suspect that this is equally true of all popular MySQL language bindings, but this is the only one I've tested personally.

Trevor Burnham
  • 171
  • 7