To prevent deadlocks when concurrent PostgreSQL updates overlap, lock each transaction’s complete target set in the same immutable-key order before updating. Keep the transaction on one node-postgres client and retry eligible failures from the beginning.

This guide is for a developer working in macOS Terminal who needs two concurrent batches to update synthetic article rows completely. The example uses PostgreSQL’s current documentation, which identifies PostgreSQL 18, and the current node-postgres documentation. Its scope is a known list of existing article IDs, with IDs remaining unchanged during concurrent work. PostgreSQL: Explicit Locking

Why overlapping updates can deadlock

Consider two transactions: A updates article 1, then article 2; B updates article 2, then article 1. If each obtains its first row lock before requesting the second, each waits for a lock held by the other. PostgreSQL detects the cycle and aborts one transaction; the application cannot choose which one. Ordinary updates can produce this situation without explicit locking statements. PostgreSQL: Explicit Locking

The useful distinction is between overlap and a cycle. Overlap may require waiting. A cycle prevents either transaction from proceeding. PostgreSQL recommends consistent lock acquisition order across applications, with the strongest required lock mode acquired first. PostgreSQL: Explicit Locking

For this article’s workload, adopt one rule: acquire every requested article row lock in ascending id order before changing any article. Apply that rule across the whole transaction, including any earlier statements that touch articles.

Lock the complete target set before the update

Sorting a JavaScript array does not instruct a bulk SQL update to visit rows in that order. PostgreSQL’s UPDATE syntax has no direct ORDER BY clause. Its documentation demonstrates ordered locking through a selecting query and explains that shared ordering can prevent deadlocks between update operations. PostgreSQL: UPDATE

Use a separate, awaited locking statement inside the transaction. This illustrative SQL transaction targets articles 1, 2 and 3; the setup table appears in the next section.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id
FROM deadlock_demo_articles
WHERE id = ANY(ARRAY[3, 1, 2]::integer[])
ORDER BY id
FOR UPDATE;

UPDATE deadlock_demo_articles
SET revision = revision + 1
WHERE id = ANY(ARRAY[3, 1, 2]::integer[]);

COMMIT;

The first statement establishes the acquisition order. The update then operates on rows already locked by that transaction. Row locks normally remain until transaction end. Keep this interval short: prepare input before beginning, and avoid waiting for desktop input while locks are held. PostgreSQL: Explicit Locking

The following conditions define the example’s ordering contract:

  • Every competing batch uses ascending article id order.
  • IDs remain unchanged during concurrent work.
  • Each batch identifies its entire target set before locking.
  • No earlier statement has already locked article rows in another order.
  • The example table has no triggers or related-table operations introducing additional lock dependencies.

These are prerequisites for applying the reasoning to this workload. A transaction that locks article 9 first and later requests articles 1 through 3 has already departed from the contract, even if its final query contains ORDER BY id.

Run a complete node-postgres example on your Mac

Prerequisites are a running local PostgreSQL server, an existing scratch database, Node.js with npm, and psql available in Terminal. Configure your existing database connection through the PostgreSQL environment variables. node-postgres pools and psql use the same connection variables; set PGHOST explicitly if needed so both commands reach the same server. node-postgres: Connecting

In a new working folder, install the dependency:

npm init -y
npm install pg

Save the following complete setup as setup.sql. It creates a new demonstration table and three synthetic articles. Run it once in the scratch database. It deliberately fails if that table already exists, rather than replacing existing contents.

BEGIN;

CREATE TABLE deadlock_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    revision integer NOT NULL DEFAULT 0
);

INSERT INTO deadlock_demo_articles (id, title)
VALUES
    (1, 'Paper Shapes'),
    (2, 'Color Notes'),
    (3, 'Desk Sketches');

COMMIT;

psql -v ON_ERROR_STOP=1 -f setup.sql

Save the next complete program as update-articles.mjs. All statements belonging to an attempt use the client returned by pool.connect(). node-postgres explicitly requires one client throughout a transaction; dispatching transaction statements through pool.query() does not preserve that requirement. node-postgres: Transactions

import pg from 'pg';
import { setTimeout as sleep } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({ max: 2, connectionTimeoutMillis: 5000 });
pool.on('error', error => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

function normalizeIds(input) {
  if (!Array.isArray(input) || input.length === 0 ||
      input.some(id => !Number.isInteger(id) ||
        id < 1 || id > 2147483647)) {
    throw new Error('Use a nonempty array of positive PostgreSQL integer IDs');
  }
  return [...new Set(input)].sort((a, b) => a - b);
}

async function incrementRevisions(input) {
  const ids = normalizeIds(input);
  const maxAttempts = 3;

  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

      const locked = await client.query(
        `SELECT id
         FROM deadlock_demo_articles
         WHERE id = ANY($1::integer[])
         ORDER BY id
         FOR UPDATE`,
        [ids]
      );

      if (locked.rows.length !== ids.length) {
        throw new Error('A requested article is missing; batch cancelled');
      }

      const updated = await client.query(
        `UPDATE deadlock_demo_articles
         SET revision = revision + 1
         WHERE id = ANY($1::integer[])
         RETURNING id, revision`,
        [ids]
      );

      if (updated.rowCount !== ids.length) {
        throw new Error('Unexpected update count; batch cancelled');
      }

      await client.query('COMMIT');
      return updated.rows.sort((a, b) => a.id - b.id);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new Error('Rollback failed; connection discarded', {
          cause: rollbackError
        });
      }

      const retryable = error.code === '40P01' || error.code === '40001';
      if (!retryable || attempt === maxAttempts) throw error;
    } finally {
      client.release(discard);
    }

    // Illustrative bounded backoff, after releasing the client.
    await sleep(50 * 2 ** (attempt - 1) + Math.floor(Math.random() * 50));
  }
}

try {
  const outcomes = await Promise.allSettled([
    incrementRevisions([3, 1, 2]),
    incrementRevisions([3, 2])
  ]);

  for (const outcome of outcomes) {
    if (outcome.status === 'rejected') throw outcome.reason;
  }

  const result = await pool.query(
    'SELECT id, title, revision FROM deadlock_demo_articles ORDER BY id'
  );
  console.table(result.rows);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

node update-articles.mjs

The array is passed as one parameter and explicitly cast to integer[]; node-postgres documents conversion of JavaScript arrays into PostgreSQL array values. Deduplication makes the requested count mean distinct articles. The database’s ORDER BY, rather than the input array’s order, establishes lock acquisition order. node-postgres: Queries

On a freshly initialized table, with no other writers and both batches successful, the expected revisions are 1 for article 1 and 2 for articles 2 and 3. These totals follow from batch membership, regardless of which batch finishes first. Subsequent script runs increment the existing values again. A missing requested ID cancels that batch instead of silently updating only the rows found.

Each batch is a separate transaction. If one succeeds and the other fails permanently, the successful batch remains committed. Promise.allSettled() lets both attempts finish before the program closes the pool. The final pool.query() runs outside either transaction. The pool API documents releasing checked-out clients, destroying a client when necessary, and ending the pool after script work finishes. node-postgres: Pool

The normalization checks and illustrative revision arithmetic were executed with Node.js. PostgreSQL was unavailable in the research environment, so the SQL and database concurrency behavior were checked against documentation rather than executed.

Preserve the isolation level your task requires

The program explicitly uses READ COMMITTED for fixed-ID updates. At this level, a locking statement may wait for an updater and then lock the updated row version, provided it still matches the condition. Each statement takes a new snapshot. A row deleted before acquisition can therefore be absent; the program handles that with its count check. PostgreSQL: Transaction Isolation

Do not lower an existing transaction’s isolation level merely to use this example. Under REPEATABLE READ, attempting to lock or update a row changed since the transaction snapshot can cause a serialization failure. SERIALIZABLE also requires retry handling and prevents committed serialization anomalies; it does not replace consistent ordering of ordinary row locks. PostgreSQL: Transaction Isolation

Locking known rows does not establish that a predicate’s membership will remain unchanged. For example, this example does not implement “update every article whose title matches a condition, including concurrent additions.” That is a different consistency task. Choose its isolation and coordination requirements before adapting the fixed-ID recipe.

Also keep the ordering column immutable. PostgreSQL documents that under READ COMMITTED, sorting happens before locking; values changed while the statement waits can make returned rows appear out of order. Using unchanged IDs avoids that particular complication. PostgreSQL: SELECT

Retry complete transactions and investigate repeated failures

PostgreSQL identifies deadlock failures as SQLSTATE 40P01 and serialization failures as 40001. Retry the complete transaction, including any database-dependent decisions that determine its statements or values. Retrying only the final update does not meet that requirement. PostgreSQL: Serialization Failure Handling

The example permits three total attempts. Its short exponential delay and jitter are illustrative application choices, not PostgreSQL defaults. It releases the client before delaying, stops immediately on other errors, and reports exhaustion. PostgreSQL cautions that retries do not guarantee eventual completion. PostgreSQL: Serialization Failure Handling

Use this decision aid when adapting the implementation:

Situation Action
Another batch already holds a requested row Allow ordered acquisition to wait if the task requires every requested row.
A requested ID is absent Roll back and report the missing-target condition.
SQLSTATE is 40P01 or 40001 Roll back, then retry the whole eligible transaction within a bound.
Failures continue after ordered locking Inspect earlier statements, other writers, triggers and additional tables for departures from the ordering contract.
The operation touches several tables Define a shared table order as well as row order for all competing paths.
A batch must commit atomically Retain one transaction; independently committed chunks would change the task.

Avoid adding SKIP LOCKED to this all-target batch: it omits rows that cannot be locked immediately. PostgreSQL describes it as useful for queue-like consumers and unsuitable for a general consistent view. NOWAIT reports an error instead of waiting for a conflicting row lock; it changes waiting behavior but does not complete the batch. PostgreSQL: SELECT

The implementation is ready to adapt when you can name the complete target set, keep its ordering keys unchanged, and apply the same acquisition rule across competing transactions. Retain the missing-row check and bounded whole-transaction retries so that contention or incomplete input produces an explicit outcome.

Sources