开发者

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

开发者 https://www.devze.com 2023-02-27 10:56 出处:网络
This question is a logical continuation of this one: How many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?

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 a开发者_开发知识库ll 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?


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

Lock rows matching only both conditions

0

精彩评论

暂无评论...
验证码 换一张
取 消