MySQL 'REPEATABLE READ' transaction unexpected behavior

Question

The default isolation level of MySQL transaction is 'Repeatable Read'.

According to another stackoverflow question( Difference between read commit and repeatable read) "Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read."

Here is my test database;

mysql> select * from people;
+------+---------+
| name | howmany |
+------+---------+
| alex |     100 |
| bob  |     100 |
+------+---------+

slow.sql

START TRANSACTION;

SELECT @new_val := howmany FROM people WHERE name = 'alex';
SELECT SLEEP(10);
SET @new_val = @new_val - 5;
UPDATE people SET howmany = @new_val WHERE name = 'alex';

COMMIT;

fast.sql

START TRANSACTION;

SELECT @new_val := howmany FROM people WHERE name = 'alex';
--  SELECT SLEEP(10);
SET @new_val = @new_val - 5;
UPDATE people SET howmany = @new_val WHERE name = 'alex';

COMMIT;

If I run slow.sql, and before it returns I run fast.sql multiple times. fast.sql will print 95, 90, 85....

I think repeatable read isolation level should make fast.sql fail to run or I misunderstand 'repeatable read'.

I'm running MySQL 5.7 from Ubuntu 16.10.

Thanks very much.

Ilion Ilion · Accepted Answer · 2017-03-08T10:08:56

Repeatable read isolation level guarantees consistency within a single transaction. You are executing multiple transactions. For the behaviour your expecting you would need to look into locking reads. See here for more info. https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read

MySQL 'REPEATABLE READ' transaction unexpected behavior

3 Answers