Advisory Locks in Postgres Used From Go Programs Safely

Not every mutual exclusion problem has a row to lock. "Only one instance should run the nightly report." "Only one process should migrate the schema." "Only one worker should rebuild this customer's index at a time." The rows might not exist yet, or the work spans many tables. PostgreSQL has a tool built for it: advisory locks, named locks on integers of your choosing that the server tracks and releases for you but never interprets. This article uses them from Go 1.25.6 with pgx against PostgreSQL 18.6, and runs the experiments that expose the traps. The most common one is specific to Go's database/sql pool, and it leaves locks held that nothing will ever release.

It belongs in the database cornerstone and pairs with row locks and SELECT FOR UPDATE.

What an advisory lock is

An advisory lock is a lock on a key, either one 64 bit integer or a pair of 32 bit integers. PostgreSQL gives it to one session at a time and makes others wait or fail. It does not protect any table; the "advisory" part is that your code agrees to ask before doing the work. The server cleans up after a dead session, and that is the useful property: a process that crashes while holding the lock cannot leave it held, since the lock lives and dies with the connection.

There are two scopes:

• Session locks (pg_advisory_lock, pg_try_advisory_lock) are held until you unlock them or the connection ends. They survive transactions and commit.
• Transaction locks (pg_advisory_xact_lock, pg_try_advisory_xact_lock) are released at the end of the transaction. There is no unlock function.

Each has a blocking form that waits and a try form that returns true or false at once. Use try for "skip if someone else is already doing it" and the blocking form for "wait my turn".

The lab

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"sync"
	"sync/atomic"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

const jobKey = 612001

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(10)

	// 1. A singleton job: five instances try, one gets the lock. The lock lives on one pinned connection.
	var ran atomic.Int32
	var wg sync.WaitGroup
	for i := 1; i <= 5; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			conn, err := db.Conn(ctx)
			if err != nil {
				log.Fatal(err)
			}
			defer conn.Close()
			var got bool
			if err := conn.QueryRowContext(ctx, "select pg_try_advisory_lock($1)", jobKey).Scan(&got); err != nil {
				log.Fatal(err)
			}
			if !got {
				return
			}
			defer conn.ExecContext(ctx, "select pg_advisory_unlock($1)", jobKey)
			ran.Add(1)
			time.Sleep(150 * time.Millisecond) // the job
		}()
	}
	wg.Wait()
	fmt.Println("1. five instances tried a singleton job, instances that ran it:", ran.Load())

	// 2. The pool trap: the lock is taken on one connection and the unlock lands on another.
	held := func(key int) (n int) {
		_ = db.QueryRowContext(ctx, "select count(*) from pg_locks where locktype = 'advisory' and objid = $1", key).Scan(&n)
		return n
	}
	connA, _ := db.Conn(ctx)
	var locked, unlocked bool
	_ = connA.QueryRowContext(ctx, "select pg_try_advisory_lock($1)", jobKey+1).Scan(&locked)
	connB, _ := db.Conn(ctx) // connA is pinned, so this is a different session
	_ = connB.QueryRowContext(ctx, "select pg_advisory_unlock($1)", jobKey+1).Scan(&unlocked)
	_ = connB.Close()
	_ = connA.Close() // back to the pool, still holding the lock
	fmt.Printf("2. lock taken=%v, unlock from another connection=%v, locks held after both went back to the pool: %d\n",
		locked, unlocked, held(jobKey+1))

	// 3. A transaction level lock releases itself at commit or rollback.
	tx, _ := db.BeginTx(ctx, nil)
	var got bool
	_ = tx.QueryRowContext(ctx, "select pg_try_advisory_xact_lock($1)", jobKey+2).Scan(&got)
	var other bool
	_ = db.QueryRowContext(ctx, "select pg_try_advisory_xact_lock($1)", jobKey+2).Scan(&other)
	tx.Rollback()
	var after bool
	_ = db.QueryRowContext(ctx, "select pg_try_advisory_xact_lock($1)", jobKey+2).Scan(&after)
	fmt.Printf("3. xact lock: holder=%v, second caller while held=%v, after rollback=%v\n", got, other, after)

	// 4. Locks are re-entrant per session and counted.
	conn, _ := db.Conn(ctx)
	defer conn.Close()
	for i := 0; i < 2; i++ {
		_, _ = conn.ExecContext(ctx, "select pg_advisory_lock($1)", jobKey+3)
	}
	var first, second bool
	_ = conn.QueryRowContext(ctx, "select pg_advisory_unlock($1)", jobKey+3).Scan(&first)
	var stillHeld int
	_ = db.QueryRowContext(ctx, "select count(*) from pg_locks where locktype = 'advisory' and objid = $1", jobKey+3).Scan(&stillHeld)
	_ = conn.QueryRowContext(ctx, "select pg_advisory_unlock($1)", jobKey+3).Scan(&second)
	fmt.Printf("4. locked twice on one session: first unlock=%v (locks still held: %d), second unlock=%v\n", first, stillHeld, second)

	// 5. The leaked lock from step 2 disappears when its session ends.
	before := held(jobKey + 1)
	_ = conn.Close()      // step 4 reused the pooled connection that still holds the lock
	db.SetMaxIdleConns(0) // closes the idle connections, ending their sessions
	time.Sleep(200 * time.Millisecond)
	fmt.Printf("5. leaked lock rows before the pool closed its idle connections: %d, after: %d\n", before, held(jobKey+1))
}

1. five instances tried a singleton job, instances that ran it: 1
2. lock taken=true, unlock from another connection=false, locks held after both went back to the pool: 1
3. xact lock: holder=true, second caller while held=false, after rollback=true
4. locked twice on one session: first unlock=true (locks still held: 1), second unlock=true
5. leaked lock rows before the pool closed its idle connections: 1, after: 0

The program runs five experiments, and the output reads in the same order.

A singleton job

Five goroutines stand in for five instances. Each takes a connection with db.Conn(ctx), calls pg_try_advisory_lock(612001), and returns at once if it did not get it. The one that did runs a 150 millisecond job and unlocks. The count of instances that ran the job: 1. The other four saw false and skipped, which is exactly what a scheduled job on several replicas wants.

The pinned connection is the important part. db.Conn takes one connection out of the pool and keeps it until Close, so the lock call, the work and the unlock all go through the same session. That is the first rule: a session lock belongs to a connection, so hold on to the connection.

The pool trap

The second experiment shows what happens when that rule is broken. A lock is taken on connection A. Connection B, a different session, then calls pg_advisory_unlock for the same key. It returned false, which is PostgreSQL saying "you do not hold this lock". After both connections were closed, which in database/sql means returned to the pool, the count of advisory locks for that key was 1: still held.

That is what happens in code that writes db.QueryRow("select pg_advisory_lock(...)") and later db.Exec("select pg_advisory_unlock(...)"). Each call takes whichever idle connection the pool has, so the unlock very often goes to a different one. The false is easy to miss because nothing returns an error. The lock stays held by a session that is sitting idle in the pool, and every future attempt to take it fails, until that connection happens to be closed by the pool's idle or lifetime limits. The symptom is a job that stops running and a database full of healthy looking connections.

Avoid it in three ways:

1. Use db.Conn as the lab does, and unlock on that same *sql.Conn before closing it.
2. Check the boolean returned by pg_advisory_unlock and log a warning on false.
3. Prefer a transaction scoped lock whenever the work fits in a transaction, because the transaction pins the connection for you.

Transaction level locks

The third experiment takes pg_try_advisory_xact_lock inside a transaction: true. A second caller on another connection, while the transaction was open, got false. After Rollback, a new call got true. There is nothing to forget: the lock ends with the transaction, on commit or rollback, including when the process dies. If your exclusive work is database work, put it in the transaction and take the lock first; the data change and the lock then have the same lifetime.

Counting

The fourth experiment locks the same key twice on one session. The first pg_advisory_unlock returned true and one lock was still held. The second unlock returned true and released it. Session locks stack: every successful lock call needs its own unlock. A function that locks and a caller that also locks, both unlocking once, leaves the lock held. Transaction locks stack too, but all release at the end.

Session end

The fifth experiment closes the leaked lock from the second. It was held on an idle pooled connection. After the lab closed the pinned connection that had reused it and told the pool to drop idle connections with SetMaxIdleConns(0), the count went from 1 to 0. The session ended and the server released the lock. This is both the safety net and the reason crashes are harmless, and a cleanup you should not rely on in normal operation.

Choosing the key

Keys are numbers, and the whole database shares one space for them. A few conventions keep that safe:

• Two int4 keys for namespaced locks. pg_advisory_lock(classid, objid) takes two 32 bit integers. Use a fixed class per feature (1 for the report job, 2 for per customer rebuilds) and the entity id as the second.
• Hash a string to a 64 bit number with hash/fnv when the key is a name such as "nightly-report". A collision makes two unrelated jobs exclude each other, which is safe but surprising. Log the name beside the number.
• Keep the constants in one file. Two packages that independently pick 42 will block each other with no trace of why.

In diagnostics, select * from pg_locks where locktype = 'advisory' shows every held lock, the session, and the key split into classid, objid and objsubid.

When to use them

Advisory locks fit coordination across processes, such as singleton jobs, leader election of a sort, schema migration (the lock used in embedding migrations), and serialising work per entity without a row. They are cheap, they need no table, and the server cleans up for you.

They do not fit every case:

• Behind a transaction mode pooler. PgBouncer in transaction mode hands a different server connection to each transaction, so a session lock is taken on a connection you do not keep. Use direct connections for session locks, or transaction locks within one transaction.
• When the thing you protect is a row. for update ties the lock to the data and to the transaction, with deadlock detection that treats advisory locks as ordinary participants.
• For long leadership. A lock held for hours pins a connection for that time. For a leader that must renew and be revoked, use a lease with an expiry in a table, or a coordination service.

Always set a way out. A blocking pg_advisory_lock waits forever unless the context has a deadline, so use QueryRowContext with a timeout, or the try form in a loop with a pause. See query timeouts. The functions are documented in the PostgreSQL manual's chapter on advisory lock functions and the semantics in explicit locking.