CODEWITHSUNDEEP
mysqltransactionsisolation-level
Snail
Snail

Reputation: 111

Confused about the Mysql lock

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.

Upvotes: 0

Views: 71

Answers (1)

Namphibian
Namphibian

Reputation: 12221

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?

Upvotes: 1

Related Questions