0

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.

1 Answer 1

1

Well wlq3 is the committed value in the database so transaction B reads this value. The update with Jim has not committed yet.A lock will not block a read it will just block a update this is due to the MVCC locking model which is implemented in InnoDB.

See this link for more information: InnoDB's row locking the same as MVCC Non-Blocking Reads?

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.