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.