PostgreSQL Advisory Locks in 2026: Distributed Locking, Cron Jobs, and Race Condition Prevention

Advisory locks are PostgreSQL's built-in mechanism for application-level coordination. Unlike row locks (SELECT FOR UPDATE) which lock database rows, advisory locks lock arbitrary integers you define—perfect for "only one server should run this cron job" or "only one request should process this payment" scenarios.

The best part: if you're already using PostgreSQL, you get distributed locking for free. No Redis Redlock, no ZooKeeper, no additional infrastructure.

---

Advisory Lock Fundamentals

PostgreSQL provides two scopes and two modes:

Scope:

• Session-level: lock persists for the entire database session; must be explicitly released or connection close
• Transaction-level: lock automatically released when the transaction commits or rolls back

Mode:

• Exclusive: only one holder at a time (most common)
• Shared: multiple shared holders, but blocks exclusive holders

Key functions:

-- Session locks (must release manually)
SELECT pg_advisory_lock(key);           -- Wait until acquired
SELECT pg_try_advisory_lock(key);       -- Return true/false immediately
SELECT pg_advisory_unlock(key);         -- Release

-- Transaction locks (auto-released on commit/rollback)
SELECT pg_advisory_xact_lock(key);      -- Wait until acquired
SELECT pg_try_advisory_xact_lock(key);  -- Return true/false immediately
-- No unlock needed — automatic on transaction end

-- Shared locks
SELECT pg_advisory_lock_shared(key);
SELECT pg_try_advisory_lock_shared(key);

Keys are 64-bit integers. Use consistent hashing of strings to generate them:

// lib/db/advisory-lock.ts
import { createHash } from "crypto";

/**
 * Convert a string key to a 64-bit integer for advisory locks.
 * Uses the first 8 bytes of SHA-256 — collision probability is negligible.
 */
export function lockKey(name: string): bigint {
  const hash = createHash("sha256").update(name).digest();
  // Read first 8 bytes as a signed 64-bit integer
  return hash.readBigInt64BE(0);
}

// Examples:
// lockKey("billing-cron") → -3456789012345678901n
// lockKey("payment-xyz") → 1234567890123456789n

---

Pattern 1: Distributed Cron Lock

Prevent duplicate cron job execution across multiple server instances:

// lib/cron/advisory-lock.ts
import { Pool, PoolClient } from "pg";
import { lockKey } from "@/lib/db/advisory-lock";

interface CronLockOptions {
  jobName: string;
  fn: () => Promise<void>;
  pool: Pool;
  timeoutMs?: number;  // How long to wait for lock (0 = don't wait)
}

/**
 * Run fn() exclusively across all server instances.
 * If another instance is already running this job, skip.
 */
export async function withCronLock({
  jobName,
  fn,
  pool,
  timeoutMs = 0,
}: CronLockOptions): Promise<{ ran: boolean; durationMs?: number }> {
  const key = lockKey(`cron:${jobName}`);
  const client = await pool.connect();

  try {
    // Try to acquire session lock (non-blocking)
    const result = await client.query<{ acquired: boolean }>(
      `SELECT pg_try_advisory_lock($1) as acquired`,
      [key]
    );

    const acquired = result.rows[0].acquired;

    if (!acquired) {
      console.log(`Cron job ${jobName}: skipping — already running on another instance`);
      return { ran: false };
    }

    console.log(`Cron job ${jobName}: acquired lock, running...`);
    const start = Date.now();

    try {
      await fn();
      const durationMs = Date.now() - start;
      console.log(`Cron job ${jobName}: completed in ${durationMs}ms`);
      return { ran: true, durationMs };
    } finally {
      // Always release the lock, even if fn() throws
      await client.query(`SELECT pg_advisory_unlock($1)`, [key]);
    }
  } finally {
    client.release();
  }
}

Usage in a cron handler:

// app/api/cron/send-digest-emails/route.ts
import { NextRequest, NextResponse } from "next/server";
import { withCronLock } from "@/lib/cron/advisory-lock";
import { pool } from "@/lib/db";

export async function GET(req: NextRequest) {
  const authHeader = req.headers.get("Authorization");
  if (authHeader !== `Bearer ${process.env.CRON_SECRET}`) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const { ran, durationMs } = await withCronLock({
    jobName: "send-digest-emails",
    pool,
    fn: async () => {
      const users = await getUsersForDigest();
      await sendDigestEmails(users);
    },
  });

  return NextResponse.json({
    ran,
    durationMs,
    timestamp: new Date().toISOString(),
  });
}

---

Pattern 2: Transaction-Level Resource Lock

For within-request locking (e.g., "only process this payment once"):

// lib/payments/process.ts
import { db } from "@/lib/db";
import { lockKey } from "@/lib/db/advisory-lock";

/**
 * Process a payment exactly once — even if two requests arrive simultaneously.
 * Uses transaction-level advisory lock (auto-released on commit/rollback).
 */
export async function processPaymentOnce(paymentId: string) {
  const key = lockKey(`payment:${paymentId}`);

  return db.$transaction(async (tx) => {
    // Try to acquire transaction lock — non-blocking
    const result = await tx.$queryRaw<[{ acquired: boolean }]>`
      SELECT pg_try_advisory_xact_lock(${key}::bigint) as acquired
    `;

    if (!result[0].acquired) {
      // Another transaction is processing this payment right now
      throw new Error("PAYMENT_ALREADY_PROCESSING");
    }

    // Lock acquired — we're the only one processing this payment
    const payment = await tx.payment.findUnique({ where: { id: paymentId } });

    if (!payment) throw new Error("Payment not found");
    if (payment.status !== "pending") {
      // Already processed (by a previous request)
      return payment;
    }

    // Process the payment
    const stripeResult = await chargeStripe(payment);

    const updated = await tx.payment.update({
      where: { id: paymentId },
      data: {
        status: "succeeded",
        stripeChargeId: stripeResult.id,
        processedAt: new Date(),
      },
    });

    // Lock auto-released when transaction commits
    return updated;
  });
}

---

Pattern 3: Leader Election

For background workers where exactly one instance should be "leader":

// lib/workers/leader-election.ts
import { Pool } from "pg";
import { lockKey } from "@/lib/db/advisory-lock";

export class LeaderElection {
  private client: any = null;
  private isLeader = false;
  private heartbeatInterval: ReturnType<typeof setInterval> | null = null;

  constructor(
    private readonly pool: Pool,
    private readonly role: string,
    private readonly onBecomeLeader: () => void,
    private readonly onLoseLeadership: () => void
  ) {}

  async start() {
    await this.tryBecomeLeader();

    // Retry leadership every 30 seconds
    this.heartbeatInterval = setInterval(() => {
      this.tryBecomeLeader().catch(console.error);
    }, 30_000);

    // Release on shutdown
    process.on("SIGTERM", () => this.stop());
    process.on("SIGINT", () => this.stop());
  }

  private async tryBecomeLeader() {
    if (this.isLeader) {
      // Already leader — keep the connection alive with a ping
      try {
        await this.client.query("SELECT 1");
      } catch {
        // Connection died — lost leadership
        this.isLeader = false;
        this.client = null;
        this.onLoseLeadership();
      }
      return;
    }

    const key = lockKey(`leader:${this.role}`);
    const client = await this.pool.connect();

    try {
      const result = await client.query<{ acquired: boolean }>(
        `SELECT pg_try_advisory_lock($1) as acquired`,
        [key]
      );

      if (result.rows[0].acquired) {
        this.client = client;
        this.isLeader = true;
        console.log(`Became leader for role: ${this.role}`);
        this.onBecomeLeader();
        // Don't release client — hold the lock
      } else {
        client.release(); // Not the leader — release immediately
      }
    } catch (err) {
      client.release();
      throw err;
    }
  }

  async stop() {
    if (this.heartbeatInterval) {
      clearInterval(this.heartbeatInterval);
    }
    if (this.client) {
      const key = lockKey(`leader:${this.role}`);
      try {
        await this.client.query(`SELECT pg_advisory_unlock($1)`, [key]);
      } finally {
        this.client.release();
        this.client = null;
        this.isLeader = false;
      }
    }
  }
}

// Usage
const election = new LeaderElection(
  pool,
  "report-generator",
  () => {
    console.log("Starting report generation worker...");
    startReportWorker();
  },
  () => {
    console.log("Lost leadership — stopping report worker");
    stopReportWorker();
  }
);

await election.start();

---

Pattern 4: Per-Tenant Operation Serialization

Prevent concurrent operations for the same tenant from conflicting:

// lib/billing/subscription-manager.ts

/**
 * Serialize subscription changes per team.
 * Prevents race conditions when two requests try to upgrade/downgrade simultaneously.
 */
export async function changeSubscription(
  teamId: string,
  newPlan: string
) {
  const key = lockKey(`subscription:${teamId}`);

  return db.$transaction(async (tx) => {
    // Acquire lock for this specific team
    await tx.$executeRaw`
      SELECT pg_advisory_xact_lock(${key}::bigint)
    `;
    // This WAITS (blocking) if another transaction holds the lock
    // Transaction lock is released automatically on commit/rollback

    const current = await tx.subscription.findFirst({
      where: { teamId, status: "active" },
    });

    if (!current) throw new Error("No active subscription found");
    if (current.plan === newPlan) return current; // Already on this plan

    // Safe to change — no concurrent modification possible
    const updated = await tx.subscription.update({
      where: { id: current.id },
      data: { plan: newPlan, updatedAt: new Date() },
    });

    await tx.subscriptionHistory.create({
      data: {
        teamId,
        fromPlan: current.plan,
        toPlan: newPlan,
        changedAt: new Date(),
      },
    });

    return updated;
  });
}

---

Monitoring Lock Contention

-- See currently held advisory locks
SELECT
  pid,
  usename,
  application_name,
  classid,
  objid,
  locktype,
  granted,
  now() - query_start AS held_duration
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE locktype = 'advisory'
ORDER BY held_duration DESC;

-- Find long-running advisory lock holders
SELECT
  pid,
  usename,
  left(query, 80) AS query_snippet,
  now() - query_start AS query_duration,
  classid,
  objid
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE locktype = 'advisory'
  AND now() - query_start > INTERVAL '1 minute'
ORDER BY query_duration DESC;

---

TypeScript Helper Library

// lib/db/advisory-lock.ts (complete)
import { Pool, PoolClient } from "pg";
import { createHash } from "crypto";

export function lockKey(name: string): bigint {
  const hash = createHash("sha256").update(name).digest();
  return hash.readBigInt64BE(0);
}

type LockFn = (
  client: PoolClient,
  key: bigint
) => Promise<boolean>;

/**
 * Generic advisory lock helper for session-level locks.
 */
export async function withAdvisoryLock<T>(
  pool: Pool,
  name: string,
  fn: () => Promise<T>,
  options: { blocking?: boolean } = {}
): Promise<{ result: T | null; acquired: boolean }> {
  const key = lockKey(name);
  const client = await pool.connect();

  try {
    const lockFn = options.blocking
      ? `SELECT pg_advisory_lock($1)`
      : `SELECT pg_try_advisory_lock($1) as acquired`;

    const lockResult = await client.query(lockFn, [key]);

    const acquired = options.blocking
      ? true
      : lockResult.rows[0].acquired as boolean;

    if (!acquired) {
      return { result: null, acquired: false };
    }

    try {
      const result = await fn();
      return { result, acquired: true };
    } finally {
      await client.query(`SELECT pg_advisory_unlock($1)`, [key]);
    }
  } finally {
    client.release();
  }
}

/**
 * Transaction-level advisory lock (auto-released on commit/rollback).
 * Use inside a Prisma or pg transaction.
 */
export async function acquireTransactionLock(
  tx: { $executeRaw: Function },
  name: string,
  blocking: boolean = false
): Promise<boolean> {
  const key = lockKey(name);

  if (blocking) {
    await tx.$executeRaw`SELECT pg_advisory_xact_lock(${key}::bigint)`;
    return true;
  }

  const result = await tx.$queryRaw<[{ acquired: boolean }]>`
    SELECT pg_try_advisory_xact_lock(${key}::bigint) as acquired
  `;
  return result[0].acquired;
}

---

When Not to Use Advisory Locks

For most SaaS products (< 1M req/day), advisory locks handle coordination perfectly. Switch to Redis when you see lock wait times consistently above 100ms.

---

Cost and Timeline

Advisory locks cost $0 (no additional infrastructure). The engineering cost is purely implementation time.

---

See Also

• PostgreSQL Row-Level Security — Row-level data isolation
• PostgreSQL Partitioning — Data partitioning for large tables
• Redis Advanced Patterns — Redlock for cross-database distributed locking
• AWS SQS Worker Pattern — Queue-based serialization alternative

---

Working With Viprasol

We build concurrency-safe backend systems for SaaS products—from distributed lock patterns through full event sourcing architectures. Our engineers have implemented advisory lock systems for financial applications where double-processing would have serious consequences.

What we deliver:

• Advisory lock helpers integrated into your existing PostgreSQL setup
• Cron job deduplication across multi-instance deployments
• Idempotent payment processing with lock-based guarantees
• Lock contention monitoring and alerting
• Architecture review for race condition vulnerabilities

Explore our web development services or contact us to discuss your concurrency requirements.