Unexpected inconsistent read during a transaction
I've encountered some very unexpected behavior in the InnoDB engine using the REPEATABLE READ isolation level.
My application begins a transaction, reads a single row, updates a field in that row, and then reads the row back a second time to verify the update was successful, in this manner:
START TRANSACTION;
SELECT foo FROM test WHERE id = 1;
UPDATE test SET foo = 10 WHERE id = 1;
SELECT foo FROM test WHERE id = 1; -- foo should now be 10
COMMIT;
(Apologies if there is specific syntax for code blocks; I'm new to these forums.)
When a single instance of this code is run, the value of foo returned by the second SELECT is 10, as expected. The unexpected behavior happens when a second, identical transaction starts before the first transaction has committed. If the second transaction also tries to update foo to 10, the second SELECT in the second transaction does _not_ return 10, it returns the original value of foo before the transactions began.
Even more curiously, this unexpected behavior _only_ happens when the second transaction updates foo to the same value as the first transaction. If the second transaction updates foo to a different value than the first transaction, the second select returns the updated value.
This only happens with InnoDB, and not with MyISAM. It also only happens with the REPEATABLE READ isolation level. With READ COMMITTED, the second select returns the updated value.
This output from a Node.js test script outlines the problem:
Running test with update values 10 and 11 with isolation level REPEATABLE READ
Creating table and setting value to 0
Transaction 1 started
Transaction 2 started
Transaction 2 first read value: 0
Transaction 1 updated value to 10 (Rows matched: 1 Changed: 1 Warnings: 0
Transaction 1 committed
Transaction 2 updated value to 11 (Rows matched: 1 Changed: 1 Warnings: 0
Transaction 2 second read value: 11 (PASS)
Transaction 2 committed
Final read value: 11
Running test with update values 12 and 12 with isolation level REPEATABLE READ
Creating table and setting value to 0
Transaction 1 started
Transaction 2 started
Transaction 2 first read value: 0
Transaction 1 updated value to 12 (Rows matched: 1 Changed: 1 Warnings: 0
Transaction 1 committed
Transaction 2 updated value to 12 (Rows matched: 1 Changed: 0 Warnings: 0
Transaction 2 second read value: 0 (FAIL)
Transaction 2 committed
Final read value: 12
(I won't inline the test script here because it is rather long and I still haven't figured out how to attach files to these messages.)
To be certain that this wasn't an idiosyncrasy of Node.js, I manually executed the commands using the MySQL CLI in two different terminals. The first transaction was committed just before running the update in the second transaction. Only the second transaction is shown:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT foo FROM test WHERE id = 1;
+-----+
| foo |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> update test set foo = 12 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select foo from test where id = 1;
+-----+
| foo |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
After the update, MySQL reports that 0 rows were changed, but selecting that row returns the original value, rather than the updated value. I realize that no rows needed to be changed since the second transaction was updating foo to the same value that was just committed by the first transaction. But, I would still expect the second select to read consistently, so that reads and writes within the transaction are self-consistent.
Is this behavior actually intentional, or is this a bug? If not a bug, I would be interested to learn more about what exactly InnoDB is doing and why it's causing this unexpected behavior.
MySQL version: mysql Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)