What SKIP LOCKED Solves, and What It Leaves to You

SKIP LOCKED is an option for SELECT ... FOR UPDATE. It tells the database: if another transaction has already locked a row, don't wait for it. Skip that row and return the next one that is free.

It sounds like a small detail, but it was made for a very specific job: letting many workers take tasks from the same table without blocking each other. Oracle used it internally for its own message queues as early as the late 90s. Postgres added it in version 9.5 (2016), and MySQL in 8.0 (2018). SQL Server has the same idea as the READPAST table hint.

In the last few years it has become popular because of one promise: you may not need Redis or RabbitMQ for background jobs, because your database can be the queue.

I wanted to understand how true that is. Then I noticed that my own side project already used it, and that one of its queues could leave a job stuck forever. The claim used FOR UPDATE SKIP LOCKED, so two workers could never pick up the same job. But nothing handled a worker that crashed after the claim. The job stayed running, workers only looked for queued jobs, and another check refused to create a new job for the same item while one was still running. One crash could block that item for good.

That made me look more closely at what SKIP LOCKED actually guarantees, and what is still left to you.

Two workers, one queue table, a real run at 2x speed. A (left) is too slow, B (right) takes job 777 back as attempt 2, and A's late result is ignored. By the end of this post, every line here will make sense.

Here is what we'll go through:

1. SKIP LOCKED: how one statement stops two workers from taking the same job
2. Lease: how a job comes back after a worker crashes
3. Heartbeat: how a slow worker keeps its job without losing it to a timeout
4. Fencing token: whose result counts when two workers own one job
5. Idempotency key: how to avoid repeating a payment or an email
6. The cost: what the database pays for being your queue
7. The verdict: what I chose for my side project, and when this approach fits

1. SKIP LOCKED: claiming a job

Suppose a worker finds a queued job and then updates its status in a separate statement:

SELECT id FROM jobs WHERE status = 'queued' LIMIT 1;
UPDATE jobs SET status = 'running' WHERE id = 777;

Two workers can read the same row before either of them updates it. Both start processing job 777. If the job is "send a payment", the payment goes out twice.

In Postgres, we can select and claim the job in one statement:

UPDATE jobs
SET status = 'running', started_at = now()
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

The subquery locks the selected row until the transaction ends. If another worker runs the same query at that moment, it skips the locked row and takes the next queued job. Nobody waits, and nobody gets the same job.

This is the part SKIP LOCKED solves, and it solves it well.

2. Lease: recovering after a crash

Here is the detail that is easy to miss. The row lock lives only until the transaction commits. After that, nothing is locked. The only thing that keeps other workers away from the job is its status, running.

So if the worker crashes after the commit, the job stays running forever. No error, no retry, no alert. This is exactly what happened in my side project.

SKIP LOCKED protects the moment of the claim. What happens after that is your responsibility.

A common fix is to give each claim a time limit, often called a lease. The worker owns the job only for a certain time:

WHERE status = 'queued'
   OR (status = 'running' AND started_at < now() - interval '10 minutes')

When the lease expires, another worker can retry the job. The queue recovers on its own, without someone resetting rows by hand. In my case I used one hour and a maximum of three attempts, after which the job is marked failed.

The hard part is choosing the time. A worker waiting on a slow API looks the same as a worker that has crashed. If the lease expires while the first worker is still processing, a second worker starts the same job. Now the job has two owners.

3. Heartbeat: keeping a slow worker's job

A heartbeat reduces this risk. Instead of counting the lease from started_at, the job stores lease_until, and the worker keeps moving it forward while it is alive, for example every 30 seconds:

UPDATE jobs
SET lease_until = now() + interval '1 minute'
WHERE id = 777 AND status = 'running';

Other workers reclaim only jobs where lease_until < now(). A slow worker that is still alive keeps its job, and a crashed worker loses it about a minute later.

But a heartbeat can't remove the risk completely. A long GC pause or a lost connection can stop the heartbeat while the worker is still processing. The lease expires, another worker takes the job, and then the first one wakes up and continues.

4. Fencing token: who owns the result

If two workers can end up with the same job, we need to decide whose result counts. The usual answer is a fencing token, an idea well explained in Martin Kleppmann's Designing Data-Intensive Applications. Each claim gets a number that only grows, and every final write checks that the number is still current:

-- claim: the attempt number goes from 1 to 2
UPDATE jobs SET status = 'running', attempt = attempt + 1 ... RETURNING attempt;

-- finish: worker A still has attempt 1
UPDATE jobs SET status = 'done'
WHERE id = 777 AND attempt = 1;
-- the row already has attempt 2, so 0 rows change

The older attempt updates nothing and knows it was replaced. The newer attempt saves its result.

The cost is discipline. Every write that finishes a job has to check the number, and the result and the status should be saved in one transaction.

5. Idempotency key: when the work leaves the database

A fencing token only protects writes that check it. If the old worker has already called a payment API or sent an email, the database can't undo that.

For external calls you need an idempotency key: the same job always sends the same key, for example payment-777, and the receiving service ignores a second request with that key. Stripe supports this with an Idempotency-Key header.

This is why "exactly once" is mostly a myth in job processing. In practice you get "at least once", and you make repeats harmless: fencing inside the database, idempotency keys outside it.

6. The cost: what the database pays

When the queue lives in your main database, the database carries the load:

• Bloat. In Postgres, every UPDATE creates a new version of the row. A queue table changes all the time, so VACUUM has a lot of work, and a long-running transaction elsewhere can stop it from cleaning up. Brandur Leach wrote a detailed post about a queue that slowed down for exactly this reason.
• Primary only. You can't lock rows on a read replica, so all queue traffic goes to the primary. If the primary is down, so is your queue.
• No strict order. SKIP LOCKED returns the next free row, not the next row in line. If jobs for one customer must run in order, you need extra design.
• Connections. Every worker holds a connection. Shopify used SKIP LOCKED for checkout reservations, and in their case the real limit was connection usage, not locking.
• Throughput. Thousands of jobs per second are fine. Much higher volumes are where Kafka and similar systems make more sense.

7. The verdict

What I chose for my side project

After all this, I had a list: atomic claim, lease, heartbeat, fencing on every final write, idempotency keys. I could have built all of it.

I didn't. My side project runs a single worker, so I enforced that instead. The worker takes a Postgres advisory lock at startup, and a second copy waits until the first one exits. I also added a metric and an alert for jobs that stay running too long.

That choice has its own caveat: if the database connection drops, the advisory lock is lost and isn't taken again. For a side project, that's acceptable. For a payment system, it wouldn't be. But it kept the design close to what the project actually needs, and it doesn't pretend to be safe for ten workers when there is only one.

So, is it a silver bullet?

No. It's a very good tool when you know what comes with it.

A table with SKIP LOCKED fits when	Consider something else when
You already run Postgres or MySQL	You need very high throughput
You have thousands of jobs, not millions	Many services must read the same events
The job must be created in the same transaction as your data	Jobs must run in strict order
You are ready to add leases and alerts for stuck jobs	The primary database is already under pressure

Try it yourself

Everything above fits in one small worker of about 60 lines: a claim with SKIP LOCKED, a 10-second lease, and attempt as the fencing token. I wrote it in Node.js and in C#, and the GIF at the top is a real run of two copies against the same table. The slow one loses its lease, the other one takes the job as attempt 2, and the late write changes 0 rows.

The heart of the C# version (Npgsql), shortened:

// Claim one job: UPDATE ... WHERE id = (SELECT ... FOR UPDATE SKIP LOCKED) RETURNING id, attempt
await using var claim = db.CreateCommand(ClaimSql);
await using var reader = await claim.ExecuteReaderAsync();
if (!await reader.ReadAsync()) return;   // queue is empty
var (id, attempt) = (reader.GetInt32(0), reader.GetInt32(1));

await DoWorkAsync(id);

// Finish only if I still own the job: attempt is the fencing token
await using var finish = db.CreateCommand("UPDATE jobs SET status = 'done' WHERE id = $1 AND attempt = $2");
finish.Parameters.AddWithValue(id);
finish.Parameters.AddWithValue(attempt);
if (await finish.ExecuteNonQueryAsync() == 0)
    Console.WriteLine($"LOST job {id}: someone else owns it now");

The code, the real output and a step-by-step tutorial you can run in two terminals are on GitHub.

Notes for later

1. SKIP LOCKED protects the claim. After the commit, only the status protects the job.
2. A crash after the commit leaves a stuck job. Add a lease.
3. A lease can't tell a dead worker from a slow one. Measure real job durations before you choose the timeout.
4. A lease shorter than the work creates two owners. Use a fencing token on final writes.
5. Fencing stops at the database. For external calls, use idempotency keys.
6. "Exactly once" is really "at least once" plus safe repeats.
7. A single worker is a valid design, as long as you enforce it and write it down.

Have you moved a queue from Redis to your database? What was the first thing that surprised you?

---

The side project in this post is TextStack, a reader for technical books that explains hard terms in the context of the book. It's open source: github.com/mrviduus/textstack.