Waiting for 9.5 – Implement SKIP LOCKED for row-level locks

On 7th of October, Alvaro Herrera committed patch:

Implement SKIP LOCKED for row-level locks
 
This clause changes the behavior of SELECT locking clauses in the
presence of locked rows: instead of causing a process to block waiting
for the locks held by other processes (or raise an error, with NOWAIT),
SKIP LOCKED makes the new reader skip over such rows.  While this is not
appropriate behavior for general purposes, there are some cases in which
it is useful, such as queue-like tables.
 
Catalog version bumped because this patch changes the representation of
stored rules.
 
Reviewed by Craig Ringer (based on a previous attempt at an
implementation by Simon Riggs, who also provided input on the syntax
used in the current patch), David Rowley, and Álvaro Herrera.
 
Author: Thomas Munro

This is great. Some time ago I wrote about picking tasks to work on, from a queue. To make it work sensibly, we had to use advisory locks. This is not a big problem, but it's something that most people don't use a lot, so it's very likely that it will get skipped when searching for solutions.

We have ability to lock rows, but never had the ability to just ignore whatever is locked and proceeding.

Now, finally, we can write simple queuing system without advisory locks, and one that performs great.

Let's see a test case:

CREATE TABLE jobs ( id serial PRIMARY KEY, priority int4 NOT NULL, payload text);
CREATE TABLE
 
INSERT INTO jobs (priority, payload) SELECT random() * 100, 'payload #' || i FROM generate_series(1,100) i;
INSERT 0 100

Data looks like:

SELECT * FROM jobs LIMIT 10;
 id | priority |   payload   
----+----------+-------------
  1 |       51 | payload #1
  2 |       88 | payload #2
  3 |       65 | payload #3
  4 |       76 | payload #4
  5 |       87 | payload #5
  6 |       90 | payload #6
  7 |       91 | payload #7
  8 |       65 | payload #8
  9 |       72 | payload #9
 10 |       48 | payload #10
(10 ROWS)

Assuming we want to process tasks based on their priority (higher priority = first to process), getting single task would be:

SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1;
 id | priority |   payload   
----+----------+-------------
 67 |       99 | payload #67
(1 ROW)

Since we don't want to have the same job handled by many workers, we should lock it. For example like this:

psql-1: $ BEGIN;
BEGIN
 
psql-1: *$ SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE;
 id | priority |   payload   
----+----------+-------------
 67 |       99 | payload #67
(1 ROW)

But now, other psql sessions can't get any row, as the select … for update, will lock – waiting for psql-1 to finish its transaction.

Of course, we could have added “NOWAIT", but this is not really a solution:

psql-2: $ BEGIN;
BEGIN
psql-2: *$ SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE nowait;
ERROR:  could NOT obtain LOCK ON ROW IN relation "jobs"

We got error, transaction got rolled back, not good.

But, with the new, great patch, we can:

psql-2: $ BEGIN;
BEGIN
 
psql-2: *$ SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE skip locked;
 id | priority |   payload
----+----------+-------------
 94 |       99 | payload #94
(1 ROW)

and even more:

psql-3: $ BEGIN;
BEGIN
 
psql-3: *$ SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE skip locked;
 id | priority |   payload   
----+----------+-------------
 91 |       98 | payload #91
(1 ROW)

This is great. Of course this leads to simple question – what will happen if there are no unlocked rows?

psql-4: $ WITH x AS (SELECT id FROM jobs FOR UPDATE skip locked) DELETE FROM jobs WHERE id IN (SELECT id FROM x);
DELETE 97
 
psql-4: $ BEGIN;
BEGIN
 
psql-4: *$ SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE skip locked;
 id | priority | payload 
----+----------+---------
(0 ROWS)

All great. No error, no problem, just empty result set – after all, all jobs are being worked on.

Thanks guys, it's great thing.