Prevent Duplicate Jobs with PostgreSQL Advisory Locks

Two application instances can wake up at the same time, read the same pending work, and both send the same invoice, rebuild the same report, or run the same cleanup. A cron schedule, queue worker, or Kubernetes replica does not automatically guarantee singleton execution.

PostgreSQL advisory locks provide a lightweight coordination primitive for these cases. Your application assigns meaning to a numeric key, and PostgreSQL allows only one session or transaction to hold a conflicting lock for that key. Other workers can wait or immediately learn that another worker owns it.

As of August 13, 2026, PostgreSQL 18.6 is the current stable release and PostgreSQL 19 Beta 3 is available for testing. The advisory-lock functions used here are long-established and work across currently supported PostgreSQL releases.

When advisory locks are the right tool

Advisory locks are useful when the resource you need to coordinate is conceptual rather than a row that can naturally be locked. Common examples include:

  • allowing only one daily settlement job to run;
  • preventing two workers from rebuilding the same customer report;
  • serializing a schema-related maintenance task;
  • ensuring one process refreshes an external cache at a time;
  • protecting a tenant-specific export keyed by tenant ID.

They are advisory because PostgreSQL does not enforce their meaning. Every code path that touches the protected operation must follow the same locking convention. A forgotten code path can still perform the work.

An advisory lock is not a replacement for a uniqueness constraint or idempotency key. If “charge this order once” is a business invariant, store a unique operation identifier and make the database reject a duplicate. Use the lock to reduce concurrent work; use durable constraints to preserve correctness after crashes, timeouts, and retries.

Understand the four choices

PostgreSQL exposes advisory locks along two dimensions:

  1. Transaction or session lifetime. A transaction-level lock is released automatically at commit or rollback. A session-level lock remains until explicitly unlocked or the database session ends.
  2. Wait or try. A blocking function waits for the key. A try function immediately returns false when another process holds it.

The most useful exclusive functions are:

Function Lifetime Behavior when busy
pg_advisory_xact_lock Transaction Waits
pg_try_advisory_xact_lock Transaction Returns false
pg_advisory_lock Session Waits
pg_try_advisory_lock Session Returns false

For a short job whose database work fits in one transaction, start with pg_try_advisory_xact_lock. Automatic release makes it difficult to leak the lock. For a job that performs external API calls or spans several transactions, a session-level lock may be necessary, but it requires a pinned connection and a reliable finally block.

Design stable lock keys

Advisory locks accept either one signed 64-bit integer or two signed 32-bit integers. The two key spaces do not overlap.

Using two integers is readable for application jobs:

namespace = 42     # background jobs
resource  = 1001   # daily settlement

Acquire that key with:

SELECT pg_try_advisory_xact_lock(42, 1001) AS acquired;

Define the mapping in code or configuration, not in tribal knowledge:

export const LOCK_NAMESPACE = {
  BACKGROUND_JOB: 42,
  TENANT_EXPORT: 43,
};

export const JOB_LOCK = {
  DAILY_SETTLEMENT: 1001,
  EXPIRED_SESSION_CLEANUP: 1002,
};

For per-tenant work, the first integer can be a fixed namespace and the second a numeric tenant ID, provided it fits a signed 32-bit integer. If identifiers do not fit, derive a 64-bit key with a documented, consistent algorithm in every language that participates.

Do not use a runtime’s default string hash. Some languages randomize hashes between processes, which would make identical resource names produce different lock keys. Also remember that a hash can collide. If a collision would be unacceptable, maintain an explicit numeric registry.

Protect a job inside one transaction

The following Node.js example uses the pg connection pool. The important detail is that one checked-out client owns the transaction from BEGIN through COMMIT.

import { pool } from './database.js';

const JOB_NAMESPACE = 42;
const DAILY_SETTLEMENT = 1001;

export async function runDailySettlement() {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    const result = await client.query(
      `SELECT pg_try_advisory_xact_lock($1, $2) AS acquired`,
      [JOB_NAMESPACE, DAILY_SETTLEMENT],
    );

    if (!result.rows[0].acquired) {
      await client.query('ROLLBACK');
      return { status: 'skipped', reason: 'already-running' };
    }

    const pending = await client.query(`
      SELECT id, account_id, amount
      FROM settlements
      WHERE status = 'pending'
      ORDER BY id
      FOR UPDATE
    `);

    for (const item of pending.rows) {
      await client.query(
        `UPDATE settlements
         SET status = 'processed', processed_at = now()
         WHERE id = $1`,
        [item.id],
      );
    }

    await client.query('COMMIT');
    return { status: 'completed', count: pending.rowCount };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

The lock disappears automatically when the transaction commits, rolls back, or the connection terminates. There is no transaction-level unlock function.

Keep the transaction short. Do not hold it open while uploading a large file, waiting for a user, or calling a slow third-party API. Long transactions retain database resources and can delay vacuum-related cleanup.

Coordinate work that spans external side effects

Suppose the job must generate a file, upload it, and then record the result. A transaction-level lock cannot safely span several commits. A session-level lock can, but the same physical PostgreSQL session must be used for acquire, work, and release.

export async function buildTenantExport(tenantId) {
  const client = await pool.connect();
  let acquired = false;

  try {
    const result = await client.query(
      `SELECT pg_try_advisory_lock($1, $2) AS acquired`,
      [43, tenantId],
    );

    acquired = result.rows[0].acquired;
    if (!acquired) {
      return { status: 'skipped', reason: 'export-in-progress' };
    }

    const exportFile = await createExportFile(tenantId);
    const objectUrl = await uploadExport(exportFile);

    await client.query(
      `INSERT INTO tenant_exports (tenant_id, object_url, created_at)
       VALUES ($1, $2, now())`,
      [tenantId, objectUrl],
    );

    return { status: 'completed', objectUrl };
  } finally {
    if (acquired) {
      await client.query(
        `SELECT pg_advisory_unlock($1, $2)`,
        [43, tenantId],
      );
    }
    client.release();
  }
}

This prevents concurrent execution while the connection remains alive. It does not make the upload exactly-once. If the process uploads successfully and crashes before recording the URL, a later worker can repeat the upload. Give the external operation a deterministic idempotency key or object name so retries converge on the same result.

Connection proxies matter. Session-level locks are unsafe when a transaction-pooling proxy can move statements between server sessions. Either use transaction-level locks, obtain a session-pinned connection, or choose a different coordination mechanism supported by the proxy.

Wait, skip, or fail fast

Scheduled singleton jobs usually should skip when busy:

SELECT pg_try_advisory_xact_lock(42, 1001);

Interactive operations may wait, but place a bound on that wait:

BEGIN;
SET LOCAL lock_timeout = '5s';
SELECT pg_advisory_xact_lock(42, 1001);
-- protected statements
COMMIT;

Without lock_timeout, a blocking lock request can wait indefinitely while the holder remains connected. Your application timeout alone may not cancel the PostgreSQL statement correctly, so configure database-side limits as well.

Record skipped, acquired, completed, and failed outcomes separately. A skipped job is not a successful job, and repeated skips may indicate that the protected work is slower than its schedule.

Test concurrency intentionally

Open two psql sessions to the same database. In session A:

BEGIN;
SELECT pg_try_advisory_xact_lock(42, 1001);
-- Returns true. Leave the transaction open temporarily.

In session B:

BEGIN;
SELECT pg_try_advisory_xact_lock(42, 1001);
-- Returns false.
ROLLBACK;

Then finish session A:

COMMIT;

Run the statement again in session B. It should now return true. Also test rollback, abrupt client disconnect, job exceptions, and pool exhaustion. Coordination that only works on the happy path is not production-ready.

Monitor advisory locks

PostgreSQL exposes held and waiting locks through pg_locks. Join it with pg_stat_activity to identify the session:

SELECT
  l.pid,
  l.database,
  l.classid,
  l.objid,
  l.objsubid,
  l.mode,
  l.granted,
  l.waitstart,
  a.application_name,
  a.state,
  a.query_start,
  a.query
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a USING (pid)
WHERE l.locktype = 'advisory'
ORDER BY l.granted DESC, l.waitstart NULLS FIRST;

Set a meaningful application_name in every worker’s connection string. Numeric lock keys are otherwise difficult to connect to a deployment or job name during an incident.

Do not poll pg_locks aggressively. It is an operational inspection view, not a high-frequency metrics stream. Export summarized lock wait and job-duration metrics from the application instead.

Common failures and fixes

The lock always returns false

A session-level lock may have been returned to the pool without being released. Find its PID in pg_locks, identify the application, and fix the cleanup path. Terminating a backend releases its session locks, but do that only after understanding the workload.

Two workers still run together

Confirm that both connect to the same database and calculate identical keys. Advisory locks are local to a database. Log the namespace, resource key, database name, and server address at acquisition time.

The lock disappears too early

A transaction-level lock ends at commit or rollback. If protected work continues afterward, move all database work into the transaction or deliberately use a pinned session-level lock.

A job hangs rather than skips

The code likely called the blocking function without a database lock_timeout. Use the pg_try_* form for immediate feedback, or apply a bounded timeout inside the transaction.

Memory usage grows with many locks

Advisory locks share PostgreSQL’s lock-memory pool. Do not acquire a lock for every row in a huge result set or create thousands of session locks that remain open. Lock the smallest meaningful job or resource scope.

A crash causes duplicate external work

The database releases the lock when the session disappears, which is correct. Another worker may then retry. Make the external side effect idempotent and store a durable operation key or state transition.

Production checklist

  • Define and document a stable key namespace.
  • Prefer transaction-level locks when the work fits one transaction.
  • Use pg_try_* when duplicate schedules should skip rather than wait.
  • Set lock_timeout for blocking acquisition.
  • Keep the same physical connection for session-level locks.
  • Release session locks in a finally block.
  • Avoid long-running database transactions.
  • Add durable uniqueness and idempotency for business invariants.
  • Log lock keys, database identity, outcome, and duration.
  • Monitor pg_locks during incidents and application metrics continuously.
  • Test concurrency, rollback, disconnect, and retry behavior.
  • Verify connection-pool and proxy semantics before using session locks.

Official references