CODEWITHSUNDEEP
mysqltransactionsrowlocking
Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29985

SELECT FOR UPDATE and WHERE condition on two indexed columns - what's locking strategy?

This question is a logical continuation of this one:

How many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?

Suppose I have such SELECT:

SELECT id, status FROM job WHERE status = XXX AND id IN (1, 2, 3) FOR UPDATE

id is primary key, status is indexed.

What's MySQL locking strategy in this case?

  1. Lock rows matching only both conditions
  2. Lock all rows matching first condition + lock all rows matching the second condition separately
  3. Lock the whole table

I think the most logical is 2. But... I'm not sure. Can you elaborate?

Upvotes: 4

Views: 1706

Answers (1)

Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29985

Well, I made tests and they show that option 1 is in effect in this case.

Lock rows matching only both conditions

Upvotes: 1

Related Questions