How I Made Cron Jobs Run Exactly Once Across Instances With Postgres Advisory Locks

A few months ago I noticed something odd in my inbox: two copies of the same daily digest email, sent a few seconds apart. Nothing was broken in the email code. The problem was that my app now ran on two instances, and both of them had happily woken up at 07:00 and run the same scheduled job. Each instance thought it was the only one.

I didn't want to add Redis, a distributed lock service or a full job framework just to say "only one of you should do this". I already had Postgres, and Postgres ships with a feature built for exactly this: advisory locks. In this post I'll show how I use them from Prisma to make a cron job run exactly once across any number of instances, and the one pooling gotcha that almost made me ship a lock that didn't lock anything.

What an advisory lock actually is

Postgres normally takes locks for you: on rows when you update them, on tables when you alter them. Advisory locks are different. They don't protect any data. They're just named mutexes that live inside the database, and their meaning is entirely up to your application. The "name" is a 64-bit integer (or a pair of 32-bit integers), and Postgres guarantees that only one session or transaction can hold an exclusive lock on a given key at a time.

Because every instance of my app already talks to the same database, that database becomes a natural coordinator. Here's the core idea in plain SQL:

-- Session 1
SELECT pg_try_advisory_lock(42);   -- true: I own it now

-- Session 2, at the same moment
SELECT pg_try_advisory_lock(42);   -- false: someone else holds it

The try variants are what make this useful for scheduled jobs. They never wait. They either grab the lock and return true, or return false immediately. So the instance that loses the race simply skips the run instead of queuing up behind the winner and running the job a second time afterwards.

Session locks vs transaction locks

Postgres gives you two flavours, and picking the right one matters more than anything else in this post:

• Session-level (pg_advisory_lock, pg_try_advisory_lock): held until you call pg_advisory_unlock or the database session ends. Rolling back a transaction does not release it.
• Transaction-level (pg_advisory_xact_lock, pg_try_advisory_xact_lock): held until the current transaction commits or rolls back, and released automatically. There is no unlock function for these at all.

My first version used the session-level functions, because that's what most examples show. It worked perfectly on my laptop. Then I thought about how my app actually connects in production.

The connection pool trap

Prisma keeps a pool of connections, and in production I also sit behind PgBouncer in transaction mode. That means the "session" Postgres sees is a pooled backend connection, not my request. If I call pg_try_advisory_lock in one query and pg_advisory_unlock in another, there's no guarantee both queries run on the same connection. The unlock can land on a different backend, which returns false and a warning, while the original connection keeps holding the lock forever, or until the pooler recycles it. Worse, a later, unrelated request that gets that connection now "owns" the lock without knowing it.

Rule I now follow: if there's a connection pooler anywhere between my code and Postgres, I use transaction-level advisory locks inside a single transaction. Never session-level.

Transaction-level locks sidestep the whole problem. Inside a transaction, every query is pinned to one connection, and when the transaction ends the lock disappears with it. If my code throws halfway through, the rollback releases the lock too. There's nothing to forget to clean up.

Turning job names into lock keys

Advisory locks want an integer, but I want to think in job names like "daily-digest". I could hard-code numbers, but that gets messy fast and it's easy to reuse one by accident. Instead I let Postgres hash the name with hashtextextended(text, seed), which returns a bigint and has been available since Postgres 11. Two different job names could in theory collide, but with a 64-bit hash and a handful of jobs that's not something I lose sleep over. The worst case of a collision is that two unrelated jobs can't run at the same moment, not data corruption.

A reusable lock helper with Prisma

Prisma's interactive transactions give me exactly what I need: a callback where every query runs on the same connection inside one transaction. Here's the helper I wrote:

// lib/with-job-lock.ts
import { Prisma } from "@prisma/client";
import { prisma } from "@/lib/prisma";

type LockResult<T> =
  | { ran: true; value: T }
  | { ran: false };

export async function withJobLock<T>(
  jobName: string,
  work: (tx: Prisma.TransactionClient) => Promise<T>,
  timeoutMs = 60_000,
): Promise<LockResult<T>> {
  return prisma.$transaction(
    async (tx) => {
      const [{ locked }] = await tx.$queryRaw<{ locked: boolean }[]>`
        SELECT pg_try_advisory_xact_lock(hashtextextended(${jobName}, 0)) AS locked
      `;

      if (!locked) {
        return { ran: false } as const;
      }

      const value = await work(tx);
      return { ran: true, value } as const;
    },
    { timeout: timeoutMs },
  );
}

A few details are worth calling out:

• I use the tagged-template form of $queryRaw, so jobName is sent as a bound parameter, not concatenated into the SQL string.
• The lock call and the job's work both use tx, never the global prisma client. If the work used prisma directly, it would run on a different connection outside the transaction and the lock would protect nothing.
• I raise timeout. Prisma's interactive transactions default to a 5-second timeout (and a 2-second maxWait to start one), after which the transaction is rolled back. A digest job can easily run longer than that.
• The return type is a small discriminated union, so callers have to handle the "someone else is running it" case explicitly.

Using it in a cron endpoint

My scheduler hits a protected route handler on every instance (or sometimes on whichever instance the load balancer picks, and sometimes twice because of a retry). It doesn't matter anymore, because the handler is wrapped in the lock:

// app/api/cron/daily-digest/route.ts
import { NextResponse } from "next/server";
import { withJobLock } from "@/lib/with-job-lock";

export async function POST(request: Request) {
  if (request.headers.get("authorization") !== `Bearer ${process.env.CRON_SECRET}`) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const result = await withJobLock("daily-digest", async (tx) => {
    const users = await tx.user.findMany({
      where: { digestEnabled: true, lastDigestAt: { lt: startOfToday() } },
      select: { id: true },
    });

    for (const user of users) {
      await queueDigestEmail(user.id);
      await tx.user.update({
        where: { id: user.id },
        data: { lastDigestAt: new Date() },
      });
    }

    return users.length;
  });

  if (!result.ran) {
    return NextResponse.json({ skipped: true, reason: "already running" });
  }

  return NextResponse.json({ processed: result.value });
}

Notice that the lock alone isn't the whole story. It guarantees that two runs never overlap, but it doesn't stop a second run from starting one minute after the first finished. That's why the query also filters on lastDigestAt. The lock handles concurrency, and the state check makes the job idempotent. I want both.

Things that bit me

Long transactions have a cost

Holding a transaction open for a minute means holding a connection for a minute and keeping any row locks taken inside it. For a job that sends thousands of emails I don't keep everything in one giant transaction. The lock transaction does the coordination and marks progress, while the slow external work (like calling an email API) goes to a queue. If your job is genuinely long-running, consider a lightweight "lease" row with an expiry instead, or move the work into a proper job queue.

Don't hold it across network calls you can't bound

If the email provider hangs, my transaction hangs, and Prisma eventually rolls it back at the timeout. That's actually safe, since the lock is released, but it's noisy. I wrap external calls in their own timeouts so the job fails fast and cleanly.

Seeing who holds a lock

When something looks stuck, I check pg_locks. Advisory locks show up with locktype = 'advisory', and joining to pg_stat_activity tells me which connection holds them and for how long:

SELECT l.pid, l.objid, a.application_name, a.state, now() - a.xact_start AS held_for
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'advisory';

When I wouldn't use advisory locks

Advisory locks are great for "only one at a time" coordination: cron jobs, cache rebuilds, one-off migrations at boot, or serialising work per customer by locking on a hashed customer ID. They're not a replacement for a work queue. If I need retries, backoff, visibility into failed jobs or fan-out across many workers, I reach for a queue table with FOR UPDATE SKIP LOCKED or a dedicated tool. And if my instances don't share a single Postgres primary, the whole approach falls apart, because the lock only exists on the server you took it on.

But for the very common case of "my app scaled to two instances and now the nightly job runs twice", it's about twenty lines of code using infrastructure I already had. The duplicate emails stopped the day I shipped it.

Key takeaways

• Postgres advisory locks are application-defined mutexes keyed by a 64-bit integer, perfect for coordinating multiple app instances that share one database.
• Use the try variants for scheduled jobs so the losing instance skips instead of waiting and running the job again.
• Behind Prisma's pool or PgBouncer in transaction mode, use pg_try_advisory_xact_lock inside an interactive transaction. Session-level locks can leak onto other pooled connections.
• Run all of the job's queries through tx, and raise Prisma's default 5-second transaction timeout for longer jobs.
• Pair the lock with an idempotency check (like a lastRunAt column). The lock prevents overlap, and the check prevents repeats.