A table as a queue

SKIP LOCKED is an optional portion of the SELECT clause in an SQL statement. It makes it possible to build a queryable task queue in newer RDBMs. It works exactly as the name implies. When selecting with SKIP LOCKED the selected rows are locked, and any rows that may already be locked are skipped.

The table

Starting with a table to act as a queue.

CREATE TABLE "queueable" (
  "id" int NOT NULL PRIMARY KEY,
  "payload" varchar(50) NOT NULL
)
;

Enqueue

Clients enqueue items by running INSERT statements on the queueable table.

INSERT INTO "queueable"
VALUES
  ( 1, 'hello 1' ),
  ( 2, 'hello 2' ),
  ( 3, 'hello 3' )
;

Dequeue

Clients (workers typically) dequeue items by using DELETE and SELECT statements with SKIP LOCKED. Let's look at a situation where 2 clients are attempting to dequeue an item at the same time. The first client attempts the SELECT with a SKIP LOCKED. It selects and locks row where id = 1. The second client performs the exact same query. However, since the first row is already locked, the second client skips the row where id = 1. It then locks the row where id = 2. Then both items are dequeued via DELETE statements.

BEGIN;

DELETE 
FROM
    "queueable" 
WHERE
    "id" = ( SELECT id FROM "queueable" FOR UPDATE SKIP LOCKED LIMIT 1 );
	
COMMIT;

This is a contrived example of course course, as nothing meaningful is happening. It should, however, demonstrate how a table can act as a queue.