CODEWITHSUNDEEP
mysqlsqltransactionslockingrow
Songo
Songo

Reputation: 5726

Does select for update lock the whole table or just the returned rows?

My table:

mysql> select * from users;
    +-----+---------+--------+
    | id  | name    | locked |
    +-----+---------+--------+
    |  10 | 1010    |      0 |
    |  11 | xxx     |      0 |
    | 888 | 888     |      0 |
    | 890 | qqq     |      0 |
    | 891 | qqq     |      0 |
    +-----+---------+--------+
CREATE TABLE `NewTable` (
`id`  int NOT NULL ,
`name`  varchar(255) NULL ,
`locked`  tinyint NULL DEFAULT 0 ,
PRIMARY KEY (`id`)
);

Transaction 1 (run in terminal 1):

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name="qqq" and locked <>1 limit 1 for update;
+-----+------+--------+
| id  | name | locked |
+-----+------+--------+
| 891 | qqq  |      0 |
+-----+------+--------+
1 row in set (0.00 sec)

mysql>

Running explain on the query: enter image description here

Transaction 2 (run in terminal 2):

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name="xxx" and locked <>1 limit 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

I'm using a REPEATABLE-READ isolation level. I understand that the rows returned by the select in the 1st transaction will be locked till a commit is issued. However,Why was my select in the 2nd transaction blocked and waiting for the 1st transaction to end although I'm searching for a different name?!

Upvotes: 2

Views: 3555

Answers (1)

ruperik
ruperik

Reputation: 41

Without seeing the DDL for the table, I cannot say for certain. However, my guess would be that your second select is trying to do a full table scan in order to apply your where clause. Are the columns you are searching on included in an index or primary key? If not, then there is no way for MySQL to know what is in the row you returned in the first query while running the second query. For all it knows, it could be a match and should be returned.

Upvotes: 2

Related Questions