--- title: "A lock that held a connection: advisory locks and a pool of four" summary: "An advisory lock keeps a pooled connection busy while held. Four locks and a pool of four starved one instance. Leases in a table fixed it." author: "Samuel Krauss" author_title: "Founder" publisher: "Elchi Studios" published: 2026-10-03 updated: 2026-10-03 url: https://elchi.dev/en/journal/a-lock-that-held-a-connection language: en tags: ["postgresql","go","pgx","advisory-locks","connection-pool","background-jobs","leases"] words: 1145 --- # A lock that held a connection: advisory locks and a pool of four *By Samuel Krauss, Founder. Published 3 October 2026.* > An advisory lock keeps a pooled connection busy while held. Four locks and a pool of four starved one instance. Leases in a table fixed it. Right after we moved the backend to our own cluster, one of its two application nodes dropped out of rotation and the other served everything. The instance on that node was alive, answered `/up`, and failed its readiness probe because it could not get a database connection. It had four, and all four were holding locks. Here is how that happens, and what replaced the locks: leases in a table, which hold no connection at all. ## The background work Four kinds of work in the backend must not run on every instance at once: - the hourly sweep, which removes passkey challenges nobody answered, expired single sign-on requests and whatever our retention rules keep only for a while; - the alert evaluation for uptime monitors, every 30 seconds; - the mail security checks, which look every 15 minutes for domains not checked in the last 23 hours; - the uptime checks themselves, every 10 seconds, once per node, so that two instances on one node never count a check twice. Each round started the same way. The old `TryExclusive` took a connection from the pool, called `pg_try_advisory_lock` on it, and kept that connection until the work was done: ```go conn, err := p.Acquire(ctx) // ... err = conn.QueryRow(ctx, `SELECT pg_try_advisory_lock(hashtext($1))`, name).Scan(&ok) // release: pg_advisory_unlock, then conn.Release() ``` The instance that got the lock did the round, the others skipped it. It is the textbook pattern. ## Why a lock is a connection The PostgreSQL documentation says it in one sentence: once acquired at session level, an advisory lock is held until explicitly released or the session ends. A session is a connection. To keep the lock you keep the connection, and a connection you keep is one the pool cannot hand to anybody else. On the cluster every instance gets four connections, from a budget every application on the cluster shares: three roles on two nodes, counted twice for the overlap during a deploy, take 48 of 120. Now count the locks. Three are cluster-wide: sweep, alerts, mail checks. The fourth is the node's own uptime lock. An instance that won all three cluster-wide locks, plus its own node's lock, had four connections out of the pool. Then: 1. The work inside each lock asked the pool for a connection to run its queries, and waited. 2. Requests asked the pool for a connection, and waited. 3. The readiness probe ran `SELECT 1` with a one-second bound, did not get a connection, and reported the database as failing. The instance answered `/health/ready` with 503, and the edges took it out of rotation. Since the work was waiting for a connection that its own lock was holding, the locks were never released. Not slow: stuck. `pg_locks` makes this visible once you know to look. Advisory locks appear with `locktype = 'advisory'`, and a join with `pg_stat_activity` shows where each holder connects from: ```sql SELECT l.pid, a.client_addr, a.application_name, a.state FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE l.locktype = 'advisory'; ``` All four locks belonged to backends of one node. Those backends show as idle, because the lock call returned long ago, which is exactly why nobody suspects them. ## The first fix, and why it was not the fix The quick fix was a pool of eight. It worked, took 96 of the 120 connections, and made the pool size a function of how many kinds of background work exist: add a fifth job and the failure comes back. A lock should not cost a connection at all, so I took the lock out of the connection. ## Leases in a table The replacement is a table with one row per kind of work: ```sql CREATE TABLE leases ( name text PRIMARY KEY, holder text NOT NULL, taken_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NOT NULL ); ``` Taking a lease is one statement: ```sql INSERT INTO leases (name, holder, taken_at, expires_at) VALUES ($1, $2, now(), now() + make_interval(secs => $3)) ON CONFLICT (name) DO UPDATE SET holder = excluded.holder, taken_at = excluded.taken_at, expires_at = excluded.expires_at WHERE leases.expires_at < now() RETURNING true ``` If nobody holds the name, the row is inserted. If somebody holds it and the lease has run out, it is taken over. If somebody holds it and it has not run out, the `WHERE` is false, PostgreSQL returns no row, and the caller skips the round. `ON CONFLICT DO UPDATE` guarantees one of the two outcomes atomically, even under concurrency, so two instances asking at the same moment cannot both win. A lease lasts two minutes. While the work runs, a goroutine renews it every 30 seconds, a quarter of that, with one `UPDATE ... WHERE name = $1 AND holder = $2`. When the work ends, a `DELETE` with the same condition gives it back. Each statement borrows a connection for a moment. Three details matter: - **Each call is its own holder.** The holder is the host name, the process id and 64 random bits, new for every call. Two goroutines in one process exclude each other as well, and a release that arrives late can never delete a lease somebody else has taken since. - **Only the database's clock counts.** Every time comes from `now()` on the server, so the nodes' clocks can disagree without consequence. - **A transaction-level lock would not have helped.** `pg_try_advisory_xact_lock` needs a transaction open for the whole round: a connection held again, which our `idle_in_transaction_session_timeout` of 30 seconds would end mid-round. The pool went back to four. ## What it costs **A crashed holder delays the next round by up to two minutes.** Nobody releases its lease, so it has to run out. For the hourly sweep that changes nothing. For the alert evaluation, which runs every 30 seconds, it can mean up to four rounds without one. **Work can run twice.** If the holder loses the database for longer than two minutes while another instance can still reach it, the lease runs out and the other instance takes it. The first finds out when its next renewal touches no row; it logs a warning, and the round it is in carries on. There is no fencing token here, the mechanism Martin Kleppmann describes for exactly this case. I accept that because none of the four jobs is harmed by running twice. The sweep deletes what has expired, and deleting it again deletes nothing. An extra uptime check is one more result. The alert evaluation compares what it sees with the state it stored, and the mail checks only take domains not checked for 23 hours, so a run that comes second finds the work recorded and does nothing; only two runs at the very same moment could send one notice twice. A job where a second run does damage would need a fencing token or a unique constraint on its result. None of these does. ## The test `TestLeasesHoldNoConnection` reproduces the failure in its smallest form: a pool of one connection, four leases taken (sweep, alerts, mail checks, uptime runner), and then the write the readiness probe makes, which must commit within three seconds. With the old `TryExclusive` the test never gets that far: the second lock waits for the connection the first one is holding. Two more check that a lease has one holder, that a lease nobody renews is taken over, and that one being renewed is not. ## Where advisory locks still are Migrations still take one at start, before the instance reports ready, and short transactions take transaction-level ones. The rule is narrower: no lock is held across long work on a pooled connection. In PgBouncer's transaction pooling the question does not come up, since session-level advisory locks do not work there at all. ## Sources 1. [PostgreSQL documentation: Explicit Locking, Advisory Locks](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS), PostgreSQL Global Development Group, read 2026-10-03 2. [PostgreSQL documentation: Advisory Lock Functions](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS), PostgreSQL Global Development Group, read 2026-10-03 3. [PostgreSQL documentation: pg_locks](https://www.postgresql.org/docs/current/view-pg-locks.html), PostgreSQL Global Development Group, read 2026-10-03 4. [PostgreSQL documentation: pg_stat_activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW), PostgreSQL Global Development Group, read 2026-10-03 5. [PostgreSQL documentation: INSERT, ON CONFLICT clause](https://www.postgresql.org/docs/current/sql-insert.html), PostgreSQL Global Development Group, read 2026-10-03 6. [PostgreSQL documentation: Client Connection Defaults (idle_in_transaction_session_timeout)](https://www.postgresql.org/docs/current/runtime-config-client.html), PostgreSQL Global Development Group, read 2026-10-03 7. [pgxpool package documentation](https://pkg.go.dev/github.com/jackc/pgx/v5/pgxpool), pkg.go.dev, read 2026-10-03 8. [PgBouncer features: pooling modes and what they support](https://www.pgbouncer.org/features.html), PgBouncer, read 2026-10-03 9. [How to do distributed locking](https://martin.kleppmann.com/2016/02/08/how-to-do-distributed-locking.html), Martin Kleppmann, read 2026-10-03