4

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.

Sebi2020
  • 1,966
  • 1
  • 23
  • 40

2 Answers2

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I use begin on both connections... I knew that the update blocks, but that would also happen with non atomic updates. But the result would be different. (e.g. 21 after the second transaction is commited). Meaning the second transaction uses data of first transaction which it shouldn't use because of repeatable read isolation. That normally should only happens if the second transaction begins after the first transaction has already been commited. – Sebi2020 Oct 17 '20 at 20:02
1

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.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Do you know any official source describing this behaviour? Are you sure `FOR UPDATE` changes anything? As far as I know `FOR UPDATE` just locks the row until an update occurs, but you can't be sure that the `SELECT` on the right side will be executed after the initial `SELECT` on the left side. – Sebi2020 Oct 21 '20 at 20:10
  • The lock sticks until `COMMIT`. Without an explicit `BEGIN`, autocommit will do a `COMMIT` at the end of the single SQL. – Rick James Oct 21 '20 at 20:25
  • I use begin. It does so if I update the row with update. The second update (on then right side) also blocks until I commit the first transaction. – Sebi2020 Oct 21 '20 at 21:54