MySQL isolation level repeatable reads and atomic increments in updates

Question

The last hours I've studied documentation about the different SQL transaction isolation levels and found out that MySQL uses the Repeatable Read Isolation by default and did some experiments. As far as I understand this, selects in an ongoing transaction should see the same data unless the same transaction does updates to it. I found a non repeatable read while using atomic increments (e.g. update table set age=age+1 where id=1).

My test table consists of two columns id and age with one entry 1, 20.

Running the following commands in 2 session I get a non repeatable read:

Transaction 1                Transaction 2
---------------              -------------------
begin;                       begin;
select * from test;          select * from test;   
+----+-----+                +----+-----+
| id | age |                | id | age |
+----+-----+                +----+-----+
|  1 |  20 |                |  1 |  20 |
+----+-----+                +----+-----+
update test set \           
age=age+1 where id=1;
select * from test;         select * from test;
+----+-----+                +----+-----+
| id | age |                | id | age |
+----+-----+                +----+-----+
|  1 |  21 |                |  1 |  20 |
+----+-----+                +----+-----+
commit;                     
                            select * from test;
                            -- age = 20

                            update test set age=age+1 where id=1;
                            select * from test;
                            -- Expected age=21
                            -- got age=22 => Non-Repeatable Read 

Why does the update use a different value than a select would return? Imagine I would do a select and increment the returned value by one following an update of the row. I would get different results.

Answer 1

The UPDATE operation from the connection on the right column blocks until the transaction on the left completes. If you want repeatable reads on both connections, you'll need to use BEGIN / COMMIT on both connections.

Answer 2

The proper way to run such code is to use FOR UPDATE on the end of the first SELECT. Without that, you are asking for troubles like you found.

What I think happened is (in the righthand connection):

• the second SELECT on the right did a "repeatable read" and got only 20.
• the UPDATE saw that 21 had been committed, so it bumped it to 22.
• the third SELECT new that you had changed the row, so it reread it, getting 22.