I have a question about MySQL InnoDB. For example: I have thread A to start a transaction:
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update user set name = "Jim" where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 And then, I just leave the thread A as it is. I started another thread B to do this:
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update user set name = "Tom" where id = 1; And I got the error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction It makes sense, because thread A put a 'X' lock on that row.
And then I used thread B to do this:
mysql> start transaction; Query OK, 0 rows affected (0.01 sec) mysql> select * from user where id = 1; +----+------+ | id | name | +----+------+ | 1 | wlq3 | +----+------+ 1 row in set (0.00 sec) Here I'm confused. Like I just said thread A put a 'X' lock on that row. why thread B can read this row. In my opinion, reading data need a share lock, but exclusive lock is already on that data. Is there anyone can help me ,thanks!
By the way, the isolation level of thread A is repeatable read, and thread B is read committed.