3

I'm working on an application backed by a MySQL instance, and I've encountered strange behavior involving what seems to be "phantom" inserts.

Given a table with an auto-incrementing integer key, I perform the following:

  1. INSERT ... // generates ID 1
  2. INSERT ... // generates ID 2
  3. INSERT ... // generates ID 3
  4. SELECT * // returns the set of elements 1 and 3

It seems like the second insert generates an ID and returns to the client before the insert is finished, so I can simultaneously know the ID and not be able to select the data yet. This is surprising - I would think that the insert would not return until the row is inserted.

Is there a way to tell MySQL both allocate an ID and insert a row in one atomic operation, so that, once I know of the ID 2, it is guaranteed to come back from a select query?

earldouglas
  • 133

1 Answers1

4

The trick I regularly use to alleviate this problem is to create a stored routine that I make sure is atomic by using the following:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
     [your queries here]
COMMIT;

I guess that you can mimic this behavior programatically.

m4573r
  • 5,631
  • Interesting, I just read about READ COMMITTED in the docs - I'll give these a try. – earldouglas Dec 17 '13 at 22:08
  • Using SERIALIZABLE fixes the problem, but it makes things impractically slow. I also tried READ COMMITTED, but it didn't help. – earldouglas Dec 17 '13 at 23:23
  • If it fixes the problem, it's probably the way to go. In your example, you need anyway to wait until all the inserts are committed before doing your select, there's no way around. The question then is why is it slow... Are only the queries you need to be atomically run in the transaction? Do they use indexes and are they optimized? ... – m4573r Dec 18 '13 at 08:56