How to Skip Locked Rows in MySQL with SKIP LOCKED and NOWAIT
SELECT ... FOR UPDATE waits for a row that someone else holds. Sometimes waiting is the wrong thing to do: you'd rather say "busy" right away, or take the next row instead. NOWAIT and SKIP LOCKED do those two things, and the second one turns a plain table into a job queue.
Prerequisites
- MySQL 8.0 or newer, and two sessions. The examples use MySQL 9.7.4 in Docker, and the
mysqlclient from How to Connect to MySQL in a Docker Container. SELECT ... FOR UPDATE, which both options change.
Step 1: Hold a Row
START TRANSACTION;
SELECT Id, QuantityOnHand FROM products WHERE Id = 3 FOR UPDATE;
Step 2: Refuse to Wait with NOWAIT
SELECT Id, QuantityOnHand FROM products WHERE Id = 3 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
It fails immediately, instead of waiting up to 50 seconds. Your code can answer "someone else is working on this" and move on. Fixed: ERROR 3572 covers the error.
Step 3: Leave Taken Rows Out with SKIP LOCKED
SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4, 5) FOR UPDATE SKIP LOCKED;
+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
| 4 | 50 |
| 5 | 50 |
+----+----------------+
2 rows in set (0.002 sec)
I asked for three rows and got two. Row 3 is locked, so it's simply missing from the result, with no error and no wait. End the first transaction with ROLLBACK.
Step 4: Build a Job Queue
A table of jobs, picked up by several workers, is where this shines. Here's a small one:
CREATE TABLE jobs (
Id int AUTO_INCREMENT PRIMARY KEY,
Name varchar(50) NOT NULL,
Status varchar(20) NOT NULL DEFAULT 'Waiting',
INDEX (Status)
);
INSERT INTO jobs (Name) VALUES ('Send invoice 1'), ('Send invoice 2'), ('Send invoice 3');
Two workers ask for the oldest waiting job at the same moment:
START TRANSACTION;
SELECT Id, Name FROM jobs WHERE Status = 'Waiting' ORDER BY Id LIMIT 1 FOR UPDATE SKIP LOCKED;
+----+----------------+
| Id | Name |
+----+----------------+
| 1 | Send invoice 1 |
+----+----------------+
1 row in set (0.002 sec)
START TRANSACTION;
SELECT Id, Name FROM jobs WHERE Status = 'Waiting' ORDER BY Id LIMIT 1 FOR UPDATE SKIP LOCKED;
+----+----------------+
| Id | Name |
+----+----------------+
| 2 | Send invoice 2 |
+----+----------------+
1 row in set (0.002 sec)
Same statement, different jobs, and neither one waited. Each worker does its job, marks it done and commits:
UPDATE jobs SET Status = 'Done' WHERE Id = 1;
COMMIT;
UPDATE jobs SET Status = 'Done' WHERE Id = 2;
COMMIT;
+----+----------------+---------+
| Id | Name | Status |
+----+----------------+---------+
| 1 | Send invoice 1 | Done |
| 2 | Send invoice 2 | Done |
| 3 | Send invoice 3 | Waiting |
+----+----------------+---------+
3 rows in set (0.000 sec)
If a worker crashes, its transaction rolls back, and the job goes back to being available by itself.
Which One to Use
| You want | Use |
|---|---|
| To wait your turn | FOR UPDATE |
| An error right away, so you can tell the user it's busy | FOR UPDATE NOWAIT |
| Whatever isn't taken, such as jobs in a queue | FOR UPDATE SKIP LOCKED |
MySQL's locking reads page warns: "Queries that skip locked rows return an inconsistent view of the data." That's right for a queue and wrong for a total, so never add up numbers from it.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
Using SKIP LOCKED for a report | Locked rows are silently left out | Use it only when any free row will do |
Forgetting LIMIT 1 in a queue | One worker locks every waiting job | Take one at a time |
| Doing long work with the transaction open | The row stays locked, which is fine, but other locks pile up too | Keep each job's transaction small |
No index on the WHERE column | The search locks more rows than it needs | Index it |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
ERROR 3572 | NOWAIT, and the row is locked | Handle it, or retry later |
SKIP LOCKED returns nothing | Every matching row is taken, or none match | Try again later |
| The same job runs twice | The worker committed before finishing, or the status wasn't updated | Update the status in the same transaction |
FAQ
Does it work with FOR SHARE?
Yes. The page says: "FOR SHARE supports OF table_name, NOWAIT, and SKIP LOCKED options."
Conclusion
Add NOWAIT to fail immediately on a locked row, and SKIP LOCKED to leave locked rows out. With ORDER BY, LIMIT 1 and SKIP LOCKED, several workers can share one table of jobs, without waiting for each other or taking the same job.
Additional Resources
- Locking Reads — both options, and what they guarantee.
