Skip locked rows in mysql update to avoid lock timeout

Question

My question is similar to: Ignoring locked row in a MySQL query except that I have already implemented a logic close to what's suggested in the accepted answer. My question is how to set the process id initially. All servers run a query like (the code is in ruby on rails but the resulting mysql query is):

UPDATE (some_table) SET process_id=(some process_id) WHERE (some condition on row_1) AND process_id is null  ORDER BY (row_1) LIMIT 100

Now what happens is all processes try to update the same rows, they get locked and they timeout waiting for the lock. I would like the servers to ignore the rows that are locked (because after the lock is released the process_id won't be null anymore so there is no point for locking here). I could try to randomize the batch of records to update but the problem is I want to prioritize the update based on row_1 as in the query above. So my question is, is there a way in mysql to check if a record is locked and ignore it if it is?

Answer 1

No, there is no way to ignore already-locked rows. Your best bet will be to ensure that nothing locks any row for any extended period of time. That will ensure that any lock conflicts are very short in duration. That will generally mean "advisory" locking of rows by locking them within a transaction (using FOR UPDATE) and updating the row to mark it as "locked".

For example, first you want to find your candidate row(s) without locking anything:

SELECT id FROM t WHERE lock_expires IS NULL AND lock_holder IS NULL <some other conditions>;

Now lock only the row you want, very quickly:

START TRANSACTION;
SELECT * FROM t WHERE id = <id> AND lock_expires IS NULL AND lock_holder IS NULL;
UPDATE t SET lock_expires = <some time>, lock_holder = <me> WHERE id = <id>;
COMMIT;

(Technical note: If you are planning to lock multiple rows, always lock them in a specific order. Ascending order by primary key is a decent choice. Locking out-of-order or in random order will subject your program to deadlocks from competing processes.)

Now you can take as long as you want (less than lock_expires) to process your row(s) without blocking any other process (they won't match the row during the non-locking select, so will always ignore it). Once the row is processed, you can UPDATE or DELETE it by id, also without blocking anything.