Right, so you’ve decided you want a lock. Not just any lock, but a specific row. You march up to your database, SELECT ... FOR UPDATE in hand, ready to claim what’s yours. And then you wait. And wait. Because someone else is already holding a lock on that row, and your transaction is now stuck in line, blocked and patient.

That blocking behavior is the sensible, default way to handle concurrency. It preserves serialization and prevents a dozen transactions from suddenly stampeding through the moment a lock is released. But sensible isn’t always what you need. Sometimes, waiting is a luxury you can’t afford. Your application might be a high-throughput queue system where a waiting process grinds everything to a halt. Or maybe you’re building a user-facing feature where spinning forever is a terrible user experience. This is where PostgreSQL gives you two brilliant, slightly dangerous tools to politely decline to wait in line: NOWAIT and SKIP LOCKED.

The Impatient Option: NOWAIT

NOWAIT is for when you have zero interest in waiting. You want the lock on your terms, right now. If it’s available, great, you get it. If it’s not, you don’t get put on hold; you get an error immediately. This is the concurrency equivalent of walking up to a busy bathroom door, trying the handle, and if it’s locked, immediately walking away to find another one instead of leaning against the wall and sighing loudly.

Think of it for scenarios where a delay is a critical failure. You’d use this if you need to provide instant feedback, like checking if a seat is available for booking this very second.

-- Transaction 1: Someone is already looking at a specific product
BEGIN;
SELECT * FROM products WHERE id = 123 FOR UPDATE;
-- ...they are still thinking about it...

-- Transaction 2: You, trying to be impatient
BEGIN;
SELECT * FROM products WHERE id = 123 FOR UPDATE NOWAIT;
-- ERROR:  could not obtain lock on row in relation "products"

The key here is that you must be prepared to handle that error in your application code. It’s not a failure state to be hidden; it’s a first-class outcome of your concurrency logic. You catch the exception and then decide what to do next: retry on a different object, notify the user, or bail out.

The “Move Along” Option: SKIP LOCKED

If NOWAIT is impatient, SKIP LOCKED is pragmatic. It doesn’t get angry and throw an error if a row is locked; it just quietly skips over it and grabs whatever is available. This is the workhorse for building efficient, multi-worker queue systems. You can have a dozen workers all running the same SELECT ... FOR UPDATE SKIP LOCKED query against a table of tasks, and they will naturally and safely divvy up the work without ever stepping on each other’s toes or wasting cycles waiting.

Why is this so powerful? Because it transforms locking from a bottleneck into a coordination mechanism. The locked rows are effectively invisible to your SKIP LOCKED query.

-- Let's model a simple queue table with some tasks
CREATE TABLE task_queue (
    id SERIAL PRIMARY KEY,
    task_type TEXT NOT NULL,
    claimed_by INTEGER -- NULL means unclaimed
);

INSERT INTO task_queue (task_type) VALUES ('email'), ('report'), ('cleanup'), ('backup');

-- Worker 1 starts a transaction and grabs a task
BEGIN;
SELECT * FROM task_queue WHERE claimed_by IS NULL ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
-- Let's say it gets the 'email' task (id=1)

-- Worker 2 runs the *exact same query*, at the same time
BEGIN;
SELECT * FROM task_queue WHERE claimed_by IS NULL ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
-- It does NOT wait for Worker 1. It simply skips the locked row (id=1)
-- and returns the next available row, the 'report' task (id=2).

The Devil’s in the Details: Ordering and Non-Determinism

Here’s the part the manual often glosses over: SKIP LOCKED does not respect the ORDER BY clause in the way you might intuitively think. The database first reads the table (or index) and, for each row it would normally return, it tries to lock it. If it can’t, it skips it. The crucial point is that the ORDER BY is applied to the final result set after the locked rows have been skipped.

This means that if you don’t have an ORDER BY, you are at the mercy of the physical order of the table, which is effectively random. For a queue, this is a disaster. Without ORDER BY id, your workers might constantly grab the newest task instead of the oldest, leading to task starvation. Always use an explicit ORDER BY with SKIP LOCKED to ensure fairness and predictability.

Best Practices and Gotchas

  1. You Need a Where Clause: SKIP LOCKED isn’t a magic wand you wave at a table. You almost always use it with a WHERE clause to filter for the specific work you need (e.g., WHERE status = 'pending'). Otherwise, you’re just skipping locked rows arbitrarily.

  2. Indexes are Non-Negotiable: Your WHERE and ORDER BY clauses must be supported by a good index. Since you’re scanning for unlocked rows, you want that scan to be as efficient as possible. A full table scan with SKIP LOCKED is a performance nightmare waiting to happen.

  3. It’s Not Just for Queues: While the queue pattern is the classic example, think creatively. It’s useful for any batch processing system where you want multiple processes to claim distinct chunks of data without communication.

  4. Understand Isolation Levels: This all works under the Read Committed isolation level (the default). The semantics can get funky under Repeatable Read or Serializable, as those snapshots are taken earlier in the transaction. For SKIP LOCKED patterns, sticking with Read Committed is usually the right choice.

In short, NOWAIT and SKIP LOCKED are your escape hatches from the polite-but-sometimes-infuriating world of blocking locks. Use NOWAIT when you need an immediate yes/no answer. Use SKIP LOCKED when you want to cooperatively share a workload. Just remember: with great power comes the responsibility to handle errors and mind your ORDER BY.