Locking multiple rows in MySQL (InnoDB)

Question

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?

Answer 1

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.