MySQL and the Missing Rows

I was doing some multi-threaded, multi-transactional testing for the backend of the MySQL Enterprise Monitor. I came across a weird failure, where it appeared I was able to successfully insert a row, and then (in the same transaction), a select from the same table did not return any rows.

Consider the following transactions:

mysql> create table t1 (id integer primary key auto_increment, name varchar(32) unique) engine=innodb;

a> begin;

b> begin;
b> select * from t1;

a> insert into t1 values(null, 'oldag');

b> insert into t1 values(null, 'oldag) on duplicate key update id=LAST_INSERT_ID(id);
(b blocks)

a> commit;

(b is released)
...
Query OK, 0 rows affected (0.00 sec)
b> select * from t1;
Empty set (0.00 sec)

what, what, what?!?!

So, the gist is that the insert appears to succeed (notice the ok, no warnings or errors), but it doesn't affect any rows. Surely, this cannot be correct. So, let's perform a thought experiment.

B begins a transaction, and selects from t1. He locks in his transaction read view (repeatable read isolation level) at whatever it was right then. A then makes a modification in another transaction. B then does the insert, but it is *not* actually an insert. A normal insert would have failed with a duplicate key constraint failure. But the lovely "on duplicate key update" feature turns that into an update request on the existing row with the constraint. But wait -- notice the update is designed to essentially be a no-op. The LAST_INSERT_ID trick is used so that hibernate gets the correct id for the now-not-inserted row (call it poor man's lazy create). Additionally, MySQL has the optimization that, if an update really wouldn't change anything, then by god -- don't change anything! Hence, the "0 rows affected."

Where does that leave us? We have a frozen transaction read view. We have an apparently successful insert (data manipulation) that should be visible in the same transaction. Due to internal trickery, no data manipulation actually occurred. Thusly, the 'B' transaction still has the same frozen read view as before the insert, and no rows appear in the select.

So, you can argue with me all day about the technical internals of what the database engine is doing, and why it is correct, and why it would be hard to change. But I'll argue back that the client experience is unexpected and therefore wrong. An insert with no errors (or warnings) is furthermore not visible immediately after a successful return. I don't care that there were zero rows affected. That is accurate. There WERE zero rows affected. So what. Insert. Select. Show me my rows!

So, what actually may make it impossible... Consider if transaction A had modified/inserted a lot more rows. But then B only inserts a subset of those. How do you make only those rows visible (that B insert/updated) but not the ones that B didn't 'touch?' Granted, it's a hard problems, and in pure transactional database land, it might just be impossible. And, "on duplicate key update" is a mysql-ism that throws a kink in the whole works.

Ok, now that I've told you it is impossible, I will tell you how to make it work. ;)

There is a workaround. Remember how I said that the update is a no-op if nothing is actually updated? Well, what if we actually FORCE some kind of update to happen. In this case, I added a 'dummy' insert_count column, starting at zero. I then changed it to "on duplicate key update id=LAST_INSERT_ID(id), insert_count = insert_count + 1". This forces an update to occur. The data manipulation is recognized, and the row becomes visible in the transaction. It occurred to me soon after that, that I should probably be doing hibernate 'versioning' on the objects with optimistic locking anyway, and maybe that would play nicely with this.