CODEWITHSUNDEEP
mysqlsqlinnodb
cgwebprojects
cgwebprojects

Reputation: 3472

Locking multiple rows in MySQL (InnoDB)

To lock two rows in MySQL should I be using:

SELECT * FROM <table> WHERE id=? OR id=? FOR UPDATE;

Or:

SELECT * FROM <table> WHERE id=? AND id=? FOR UPDATE;

I obviously am inside a transaction just not sure whether it should be AND or OR?

Upvotes: 4

Views: 2537

Answers (2)

jleahy
jleahy

Reputation: 16855

If your WHERE clause is id=1 AND id=2 then no rows will be eligible for inclusion in the result set and so no rows will be locked.

To lock both rows you need to use id=1 OR id=2, that way both rows will be included in the result set and will be locked.

Upvotes: 7

Kermit
Kermit

Reputation: 34054

I don't think it matters. According to the manual, locks will be acquired only for rows that "qualify for inclusion in the result set."

For SELECT ... FOR UPDATE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause).

Update

You may be able to use SHOW ENGINE INNODB STATUS to check what rows are locked for each type of SELECT.

You can read more about reading the lock here and here.

Upvotes: 4

Related Questions