4

I am trying to understand the repeatable read isolation level of MySQL InnoDB. But it has a behavior that I cannot understand when I tried these 2 transactions.

Here is my initialization for the test

mysql> SHOW CREATE TABLE `test`;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `test`;
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
No Tx1 Tx2
1 START TRANSACTION START TRANSACTION
2 SELECT * FROM test;# Return 1 SELECT * FROM test;# Return 1
3 SELECT * FROM test WHERE ID = 1 FOR UPDATE;# return 1
4 UPDATE test SET ID = 2 WHERE ID = 1; # OK UPDATE test SET ID = 3 WHERE ID = 1; # Lock wait
5 SELECT * FROM test;# Return 2 # Keep waiting
6 COMMIT; # OK # Query OK: rows matched:0, changed: 0, warnings: 0
7 SELECT * FROM test;# Return 2 SELECT * FROM test WHERE ID = 1;# Return 1
8 UPDATE test SET ID = 3 WHERE ID = 1; #Query OK: rows matched:0, changed: 0, warnings: 0
9 SELECT * FROM test;# Return 2 SELECT * FROM test;# Return 1 <-- cannot update 1 to 3 even the row exists
10 UPDATE test SET ID = 3 WHERE ID = 2;#Query OK: rows matched:1, changed: 1, warnings: 0
11 COMMIT; #OK
12 SELECT * FROM test# Return 3 SELECT * FROM test# Return 3

I wonder how MySQL treats ID=1 and ID=2 in Tx2 at lines 8 and 10. And If I use UPDATE test SET ID = 3 WHERE ID = 2 at line 4 of Tx2, lock wait is still required even Tx1 only hold the exclusive lock for ID = 1?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Cụt Cánh
  • 375
  • 2
  • 10

1 Answers1

2

At step 8, there is no longe a locked record, because TX1 has done a COMMIT, and this clears the lock.

There is also no longer a record with ID=1, because it has been updated.

At step 9 and 10, It is not possible for Tx2 to update the record, because of REPEATABLE READ. It cannot see the record with id=2, and also no longer find the record with id=1 (because it is gone/changed).

Only after a COMMIT, Tx2 can see the new/changed data.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • But ID=1 still visible with Tx2 via select. It's so weird if we can select it but cannot update it. Update an ID that not visible is also weird too. – Cụt Cánh Dec 28 '20 at 09:21
  • It is still visible because of REPEATABLE READ, see [this](https://stackoverflow.com/questions/4034976/difference-between-read-commited-and-repeatable-read#:~:text=Repeatable%20read%20is%20a%20higher,unchanged%2C%20and%20available%20to%20read.) great explanation why this data cannot be changed. (NOTE: The read data cannot be changed, but the data in your database can !) – Luuk Dec 28 '20 at 11:23