Use FOR UPDATE SKIP LOCKED inside a query that also changes the selected row from ready to processing. This lets concurrent workers claim different synthetic article rows, while the saved state prevents another claim after the lock ends.

This walkthrough is for a developer using Terminal on a Mac with a working PostgreSQL connection. You will create three synthetic articles, verify concurrent claims in two database sessions, and run a complete node-postgres example.

Why can two workers select the same article?

A plain SELECT does not reserve its result. Under PostgreSQL’s default Read Committed isolation, two workers can read the same committed ready row before either changes it. Wrapping that plain read and a later update in a transaction does not itself make the selection exclusive. PostgreSQL documents the snapshot behavior in Transaction Isolation.

The claim needs two protections: a row lock while choosing the article, and a state change before releasing that lock. PostgreSQL releases row locks when the transaction ends. Consequently, a standalone locking read followed by processing would leave the row eligible again once that read’s transaction finished. See Explicit Locking.

Use the same claim protocol in every worker. An application path that reads a ready article and starts processing without successfully claiming it can still duplicate work.

Create a small synthetic article queue

Prerequisites are a running PostgreSQL database, the psql command available in Terminal, and an existing connection configuration. For the JavaScript section, also have a currently supported Node.js release and npm installed. This article starts from a working database connection; installation varies by your existing Mac setup.

The required SKIP LOCKED feature arrived in PostgreSQL 9.5. That is the syntax minimum, not a recommendation to run an unsupported release. Use a supported PostgreSQL version; the retrieved current documentation identifies PostgreSQL 18 as current. See the 9.5 release notes and current SELECT documentation.

Open Terminal and run psql using your usual connection settings. It provides an interactive prompt for executing SQL and viewing results, as described in the psql documentation. Run the following setup once in a scratch database where article_claim_demo does not already exist. It deliberately does not replace an existing table.

SHOW server_version;
SHOW transaction_isolation;

CREATE TABLE article_claim_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    state text NOT NULL DEFAULT 'ready'
        CHECK (state IN ('ready', 'processing', 'done')),
    rendered_title text
);

INSERT INTO article_claim_demo (id, title) VALUES
    (1, 'Paper shapes'),
    (2, 'Desk arrangements'),
    (3, 'Color combinations');

The examples assume Read Committed isolation and this simple table without additional triggers. Keep the three sample rows unchanged until you finish the two-session test.

Claim one article with a single SQL statement

Run this statement whenever a worker needs one article. The candidate query locks an available row; the update records its claim and returns its input fields. PostgreSQL’s UPDATE documentation describes this CTE-and-update pattern and confirms that RETURNING reports rows actually updated.

WITH candidate AS (
    SELECT id
    FROM article_claim_demo
    WHERE state = 'ready'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE article_claim_demo AS a
SET state = 'processing'
FROM candidate AS c
WHERE a.id = c.id
  AND a.state = 'ready'
RETURNING a.id, a.title;

The lock belongs inside the candidate CTE. SKIP LOCKED lets another worker pass over a candidate it cannot lock immediately. LIMIT 1 bounds each claim, and the primary-key join identifies one update target. PostgreSQL explicitly documents locking inside a WITH query and the queue-like use of skipped rows in SELECT: The Locking Clause.

Without an explicit BEGIN, PostgreSQL executes the statement in its own transaction. A successful statement commits both the selection’s associated update and its state change before subsequent processing starts. After commit, the row lock is gone, but state = 'ready' excludes that article. See Transactions.

Only process an article returned by the claim statement. An empty result means this attempt obtained no article; it does not establish that all work is complete. Other workers may currently hold eligible rows. Also, SKIP LOCKED bypasses row-lock waits, while table-level locks can still cause waiting. These boundaries are documented in SELECT.

Verify the claim behavior in two Terminal sessions

Use two Terminal windows connected through psql to the same database. Start with the original three ready rows. The following controlled sequence holds each transaction open so you can observe the overlap directly.

  1. In session A, execute BEGIN ISOLATION LEVEL READ COMMITTED;, then paste the complete claim statement above. Leave the transaction open. With the untouched sample data, the returned ID should be 1.
  2. In session B, execute BEGIN ISOLATION LEVEL READ COMMITTED;, then paste the same claim statement. It should return ID 2 while A still holds ID 1.
  3. Execute ROLLBACK; in session B, then ROLLBACK; in session A.
  4. In either session, run the inspection query below. Both claims were rolled back, so all three articles should again be ready.
SELECT id, title, state, rendered_title
FROM article_claim_demo
ORDER BY id;

These are expected results derived from the documented locking and rollback behavior, rather than a reported database test run. Rolling back cancels each state update and releases its locks. If you use COMMIT instead, those articles remain processing and will not be selected by later claims. See Explicit Locking and Transactions.

This test distinguishes two useful observations: session B advances past a currently locked article, and a committed state change keeps an article out of future claims. Test both properties when adapting an existing worker.

Run three workers with node-postgres

After rolling back both test transactions, create a new local project directory in Terminal. Install the package with the documented node-postgres installation command:

mkdir article-claim-demo
cd article-claim-demo
npm init -y
npm install pg

Save the following complete program as workers.mjs. It uses the same database connection environment as psql; node-postgres documents its shared connection variables in Connecting. Each worker attempts one claim, converts the synthetic title to uppercase, and saves the result.

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ max: 3 });

const claimSQL = `
WITH candidate AS (
  SELECT id
  FROM article_claim_demo
  WHERE state = 'ready'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE article_claim_demo AS a
SET state = 'processing'
FROM candidate AS c
WHERE a.id = c.id AND a.state = 'ready'
RETURNING a.id, a.title;
`;

async function worker(label) {
  const claimed = await pool.query(claimSQL);

  if (claimed.rows.length === 0) {
    console.log(`${label}: no row available now`);
    return;
  }

  const article = claimed.rows[0];
  const renderedTitle = article.title.toUpperCase();

  const completed = await pool.query(
    `UPDATE article_claim_demo
     SET rendered_title = $2, state = 'done'
     WHERE id = $1 AND state = 'processing'
     RETURNING id`,
    [article.id, renderedTitle]
  );

  if (completed.rowCount !== 1) {
    throw new Error(`${label}: completion did not update one row`);
  }

  console.log(`${label}: completed article ${article.id}`);
}

try {
  const results = await Promise.allSettled([
    worker('A'),
    worker('B'),
    worker('C')
  ]);

  for (const result of results) {
    if (result.status === 'rejected') {
      console.error(result.reason);
      process.exitCode = 1;
    }
  }
} finally {
  await pool.end();
}

Run node workers.mjs, then run the inspection query again in psql. With three untouched ready articles and no other activity, expect all three states to become done, with uppercase values in rendered_title. Worker labels and console order need not match article order.

Using pool.query is appropriate here because each claim is one complete statement and each completion is a separate statement. The pool manages connection checkout and return; pool.end() closes the pool after the attempts settle. See Pooling.

The completion query passes its values separately using $1 and $2, following Queries. The program checks the returned rows array for an empty claim and checks rowCount for the number of updated completion rows, consistent with pg.Result.

The JavaScript passed a syntax check during preparation. PostgreSQL executables were unavailable in the research environment, so the SQL and concurrent database behavior were inspected against the documentation but were not executed here.

Choose the right transaction boundary and handle unfinished work

The example commits a short claim before doing the title transformation. That keeps the lock’s lifetime short, but creates a deliberate recovery boundary: if a worker stops after claiming and before completion, the article remains processing. Re-running the program will not reclaim it.

For this desktop demonstration, stop all workers and inspect any processing articles before deliberately making them eligible again. Do not automatically reset them while an earlier worker might still finish: the example has no claim-generation field to distinguish an earlier attempt from a later one. Duplicate claim prevention alone does not provide automatic recovery or exactly-once processing.

Use this decision aid when adapting the example:

Your task Transaction approach Consequence
Claim an article, then do longer application work Commit the single-statement claim first Keep a visible processing state and design recovery separately
Perform a small transformation entirely in PostgreSQL Claim and complete within one transaction Database changes can commit or roll back together
Run several SQL statements as one transaction in Node.js Check out one client and use it throughout Keep the same connection through BEGIN, statements, and COMMIT or ROLLBACK

For the last approach, node-postgres requires every statement in the transaction to use the same checked-out client. Separate pool.query calls may use different connections, so they cannot form that transaction. Release the client after finishing or rolling back. See Transactions and Pooling.

Keep the demonstrated isolation assumptions explicit. Repeatable Read and Serializable can produce errors when a row to be locked has changed since the transaction began; applications using those levels need transaction retry handling. PostgreSQL describes that boundary in Explicit Locking.

For this synthetic article task, the essential sequence is concrete: select and lock an eligible row, change its state in the same statement, and process only the returned article after the claim succeeds. Verify the overlap in two sessions, then decide how your application will handle an article whose worker never records completion.

Sources