Why can not update a row that already updated by another transaction in MySQL

Question

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?

Answer 1

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.