Postgres Advisory Locks in Go: Cheaper Than a Distributed Lock Service

At some point every backend accumulates a job that must run on exactly one instance: a nightly reconciliation, a cache warmer, a "only the leader does this" cron task. The textbook answer is to reach for etcd, ZooKeeper or a Redis-based lock library. If you already run Postgres, and most people reading this do, none of that is necessary. Postgres has had advisory locks since version 8.2, and they are perfectly suited to exactly this problem.

An advisory lock is a lock that Postgres tracks but does not attach to any row, table or transaction automatically. You decide what it protects. You pick a 64-bit integer (or a pair of 32-bit integers) as the lock key, and Postgres gives you mutual exclusion on that key across every connection to the database. It costs nothing to set up: no extra service, no extra port to firewall, no separate failure domain to reason about during an incident.

Session locks vs transaction locks

Postgres actually gives you two flavours, and mixing them up is the most common way to get this wrong.

• pg_advisory_lock(key) and pg_advisory_unlock(key) are session-scoped. The lock is held until you explicitly release it or the underlying database connection closes, regardless of how many transactions come and go on that connection in between.
• pg_advisory_xact_lock(key) is transaction-scoped. It releases automatically at COMMIT or ROLLBACK, no explicit unlock call, no way to hold it longer than the surrounding transaction.

There are also pg_try_advisory_lock and pg_try_advisory_xact_lock variants that return immediately with a boolean instead of blocking, which is what you want for "skip this run if someone else already has it" semantics rather than "queue up and wait".

The pooling trap

Here is the bit that catches people out, and it is entirely about how Go talks to Postgres rather than about Postgres itself. database/sql, and pgx's pool by extension, hands you a connection from a pool for the duration of a query and then returns it to the pool afterwards. A session-level advisory lock taken with pg_advisory_lock on one borrowed connection is worthless if your next query, including the unlock call, gets a different connection from the pool. You will "unlock" a lock nobody holds on that connection, and the original lock sits there until that specific backend connection eventually closes.

There are two clean ways round this. Either use the transaction-scoped variant, which ties the lock's lifetime to a transaction rather than a physical connection and so plays nicely with pooling by construction, or explicitly pin a single connection for the session-lock case. With pgx's pool, that means calling Acquire to get a dedicated *pgxpool.Conn and running both the lock and unlock on that same object.

func withAdvisoryLock(ctx context.Context, pool *pgxpool.Pool, key int64, fn func(context.Context) error) (bool, error) {
	conn, err := pool.Acquire(ctx)
	if err != nil {
		return false, fmt.Errorf("acquire connection: %w", err)
	}
	defer conn.Release()

	var got bool
	if err := conn.QueryRow(ctx, "SELECT pg_try_advisory_lock($1)", key).Scan(&got); err != nil {
		return false, fmt.Errorf("try advisory lock: %w", err)
	}
	if !got {
		return false, nil
	}
	defer func() {
		if _, err := conn.Exec(context.Background(), "SELECT pg_advisory_unlock($1)", key); err != nil {
			log.Printf("advisory unlock failed for key %d: %v", key, err)
		}
	}()

	return true, fn(ctx)
}

Note the unlock uses context.Background() rather than the caller's ctx. If the job's context is cancelled or times out partway through, you still want the unlock to fire on the same connection before it goes back to the pool; releasing a pooled connection that's still holding a session lock the caller thinks is gone is exactly the bug this whole approach is meant to avoid.

For the far more common "run this cron job at most once across all replicas, skip if someone's already on it" case, the transaction-scoped version is genuinely simpler and needs no connection pinning at all:

func runIfLeader(ctx context.Context, pool *pgxpool.Pool, key int64, fn func(context.Context) error) (bool, error) {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return false, fmt.Errorf("begin: %w", err)
	}
	defer tx.Rollback(ctx)

	var got bool
	if err := tx.QueryRow(ctx, "SELECT pg_try_advisory_xact_lock($1)", key).Scan(&got); err != nil {
		return false, fmt.Errorf("try advisory xact lock: %w", err)
	}
	if !got {
		return false, nil
	}

	if err := fn(ctx); err != nil {
		return true, err
	}
	return true, tx.Commit(ctx)
}

The lock is released the moment the transaction ends, one way or another, so a crashed goroutine or a dropped connection cannot leave the lock dangling. That's the property that makes this safer than most people expect: Postgres notices when a backend disconnects and releases every lock it was holding, session or transaction scoped. You don't get the classic distributed-lock failure mode where a client dies mid-critical-section and the lock has to be recovered by a lease timeout, because the database itself is the thing detecting the disconnect.

Picking the key

Advisory lock keys are just integers, which means you need a convention for turning "the nightly billing reconciliation job" into a 64-bit number without collisions. Hashing a string identifier is the usual approach:

func lockKey(name string) int64 {
	h := fnv.New64a()
	h.Write([]byte(name))
	return int64(h.Sum64())
}

A 64-bit hash gives you enough space that accidental collisions between unrelated job names are not something you need to lose sleep over, but if you're running many distinct locks it's worth keeping a short, deliberate list of the strings you hash rather than generating them dynamically, precisely so a typo doesn't silently create a second lock namespace.

When this isn't the right tool

Advisory locks live entirely inside one Postgres instance. If your database is sharded, or you run separate primaries per region, a lock taken against one of them says nothing about the others. They also don't give you fencing tokens: if a process holds the lock, gets suspended for an unusually long GC pause or scheduling delay, and only resumes after Postgres has already decided the connection is dead and released the lock, a second process can acquire it while the first is still convinced it's the leader and still running. For most "just make sure the cron job doesn't double-run" and "elect a leader among a handful of app instances" problems that's an acceptable risk, but if you're protecting something where two writers touching the same resource simultaneously is genuinely dangerous, that's the point to look at proper consensus systems rather than reaching for the database that happens to already be there.

Where it does fit, though, the appeal is that there's no new infrastructure, no new client library with its own reconnection semantics to debug, and no additional thing to monitor at 3am. It's one query.