Articles / Go

930 words

  • go
  • postgresql
  • distributed-locks
  • pgx
  • concurrency
  • database

Postgres Advisory Locks as a Cheap Distributed Lock for Go

If you already run Postgres, you probably don't need etcd, ZooKeeper or a Redis lock library to make a job run on exactly one instance. Postgres has had advisory locks since version 8.2, and they fit this problem perfectly.

You know the jobs I mean: a nightly reconciliation, a cache warmer, a "only the leader does this" cron task. Every backend grows a few, and the textbook answer is to bolt on a coordination service. Most people reading this already have Postgres, which makes that textbook answer unnecessary.

What an advisory lock actually is

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 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 and transaction locks behave differently

Postgres 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. They return immediately with a boolean instead of blocking, which is what you want for "skip this run if someone else already has it" rather than "queue up and wait".

Connection pooling quietly breaks session locks

This is the bit that catches people out, and it's entirely about how Go talks to Postgres rather than Postgres itself. database/sql, and pgx's pool by extension, lends you a connection for the duration of a query and then returns it to the pool.

So a session lock on one borrowed connection is worthless if your next query, including the unlock call, gets a different connection. You'll "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:

  1. Use the transaction-scoped variant. It ties the lock's lifetime to a transaction rather than a physical connection, so it plays nicely with pooling by construction.
  2. 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 cron jobs, the transaction lock is simpler

For the far more common "run this 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. A crashed goroutine or a dropped connection cannot leave the lock dangling.

Hang on, why is that safer than a "real" distributed lock? Because Postgres notices when a backend disconnects and releases every lock it was holding, session or transaction scoped. You don't get the classic failure 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.

Turning a job name into a key

Advisory lock keys are just integers, so 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 aren't something to lose sleep over.

If you're running many distinct locks, though, keep a short, deliberate list of the strings you hash rather than generating them dynamically. That way a typo doesn't silently create a second lock namespace.

Where advisory locks stop being enough

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. Picture the sequence:

  1. A process holds the lock.
  2. It gets suspended for an unusually long GC pause or scheduling delay.
  3. Postgres decides the connection is dead and releases the lock.
  4. A second process acquires it, while the first resumes still convinced it's the leader and still running.

For "just make sure the cron job doesn't double-run" and "elect a leader among a handful of app instances", that's an acceptable risk. If two writers touching the same resource simultaneously is genuinely dangerous, that's the point to look at proper consensus systems rather than the database that happens to already be there.

Where it does fit, the appeal is what you don't get: 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.

← All articles