CODEWITHSUNDEEP
mysqlsql
Wei Lyu
Wei Lyu

Reputation: 31

MySQL - record locks on query with multiple conditions

Engine: InnoDB company_id is the primary key in this case. When I run the following sql, it locks only the rows which are selected:

select * from company where company_id = 1 for update

=> 1 row locked

select * from company where company_id in (1, 2) for update

=> 2 rows locked

But when the condition number grows to five, sqls look like these, will lock the entire table:

SELECT
    * 
FROM
    company 
WHERE
    company_id = 1 
    OR company_id = 2 
    OR company_id = 3 
    OR company_id = 4 
    OR company_id = 5
FOR UPDATE

=> table locked

SELECT
    * 
FROM
    company 
WHERE
    company_id IN (1, 2, 3, 4, 5)
FOR UPDATE

=> table locked

Is there any way to lock multiple records with only one query(with multiple conditions)?

===================QUESTION FIXED==============================

I'm sorry that I missed the "for update" while first editing the question.

Upvotes: 3

Views: 1012

Answers (1)

GeorgeS
GeorgeS

Reputation: 99

I don't think select command will lock the rows you select. According the document https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html, You should use SELECT ... FOR UPDATE to lock the rows.

Upvotes: 1

Related Questions