Your Postgres Lock Has a Hidden Expiry Date

A production job of mine rebuilt per-company tables. It took a Postgres advisory lock so that two rebuilds never overlapped. Then, about ten minutes into a long run, the lock disappeared and another run took it. pg_locks showed no advisory lock while the first run was still writing, and a release step that swallowed errors logged that the lock was released, cleanly.

I can't share that job's data, so everything below is reproduced locally. A lock lost this way stays lost for the rest of the run, and a full run takes hours.

Nothing raises an error. If anything sets idle_in_transaction_session_timeout on your database (a DBA, a migration, a role setting, a provider default), a session advisory lock taken on a default connection from psycopg2 or SQLAlchemy expires with it, and your application never finds out.

Check yours in 30 seconds:

select name, setting, source
from pg_settings
where name in (
  'idle_in_transaction_session_timeout',
  'idle_session_timeout')
order by name;

A value above 0 in the first row means a session lock on a default connection can expire. The checklist says what to do about it.

On a local Postgres (scripts and expected output at the end), I held the same lock four ways, with a second process trying to take it the whole time. Here is what the second process saw:

method	what the second process saw (local, scaled runs)
1. session lock, default connection (idle in transaction)	Lost in 40 of 40 timing trials (20 cron, 20 event)
2. commit right after taking it (idle until the next query)	Held, then lost after one more query
3. session lock, AUTOCOMMIT, the immediate fix (idle)	Held in 40 of 40 trials (20 cron, 20 event). Lost if the session ends another way
4a. lock inside the transaction, the robust fix: kill test	A killed run rolls back with its lock
4b. the same lock: overlap test, two rebuilds of one company at once	250 rows with the lock, 500 without

Each method fixes the biggest cost left by the one before it:

1. Default connection. The driver opens a transaction you never asked for. The database ends the idle transaction at the timeout, and the lock goes with the session.
2. Commit after acquiring. Closing the transaction saves the lock, until the next query quietly opens another one.
3. AUTOCOMMIT. No transaction stays open between statements, so that timeout never applies. The lock still dies with its session for any other reason, and the run keeps writing.
4. Lock inside the transaction. The lock and the writes fail together, so a run that loses its lock cannot keep writing.

The rest of this post covers each method in turn: what I ran, what I measured, and what broke. If you hold a session advisory lock today, the checklist is near the end, and every result can be reproduced.

Three terms first

• Session advisory lock: a lock your application defines, taken with pg_advisory_lock or pg_try_advisory_lock, that belongs to one database session. Postgres keeps it until the session ends or you unlock it. It does not need an open transaction.
• Idle in transaction: a session state, visible in pg_stat_activity, where a transaction is open and the session is doing nothing.
• idle_in_transaction_session_timeout: a database setting that ends any session that stays idle in a transaction longer than the limit. It exists to clean up forgotten transactions. Ending a session releases every lock the session holds.

How I measured

Every number below comes from a script you can run. Setup:

• One throwaway local Postgres, version 18.6.
• Python 3.13, SQLAlchemy 2.0.51, psycopg2 2.9.13.
• The holder sets idle_in_transaction_session_timeout on its own session: 3 seconds in the step-by-step cases, 10 seconds in the timing runs. One real minute becomes one second. The timeout is a plain timer, so multiplying every interval by 60 keeps every ratio.
• A second session tries for the same lock. Lost means the second session got the lock while the holder still believed it held it.

Before trusting that detector, I tested it on a fault of known size. I held the lock on an AUTOCOMMIT connection, so the timeout played no part, killed the holder's session with pg_terminate_backend at 2.00 seconds, and polled for the lock every 50 ms from a second session:

run 0: killed at 2.00s, second session got the lock at 2.008s
run 1: killed at 2.00s, second session got the lock at 2.052s
run 2: killed at 2.00s, second session got the lock at 2.006s
run 3: killed at 2.00s, second session got the lock at 2.053s
run 4: killed at 2.00s, second session got the lock at 2.004s

The detector sees a loss within one poll.

Why the job takes a lock

A full run rebuilds every company and takes hours. An incremental run rebuilds a handful, soon after new data lands. Each company's delete and insert already runs in one transaction, so no reader sees a half-deleted company. That does not stop two runs rebuilding the same company at once: both commit, and the company ends up with both runs' rows. So every run starts with one shared lock:

conn = engine.connect()
got = conn.execute(text("select pg_try_advisory_lock(hashtextextended('rebuild:run', 0))")).scalar()
if not got:
    sys.exit(0)  # another run holds it; leave quietly

Three choices are packed into those lines.

• A session lock, on its own connection. A run is hours of work in many short transactions, and every lock except a session advisory lock ends with its transaction. The connection comes from the pool, and the run holds it until the end.
• One lock for the whole run. It is the simplest rule for "one run at a time".
• Try and exit, not wait. An incremental run that waited behind a full run would sit idle for hours, then redo work. Exiting is safe only if the refused work is retried: keep the queue entry, or commit no Kafka offset until the company is done.

Method 1: A session lock on a default connection

The idea: take the lock once at the start of the run, on its own connection, and hold it until the end. This is the code in the previous section: one pg_try_advisory_lock on a connection of its own, with no commit.

Method 1 result: lost every time

With a 3-second timeout, one run of case A from reproduce_cases.py (how to run it):

A. default (autobegin)
  acquired: True, holder state: idle in transaction
  t=1s other process gets lock: False
  t=5s other process gets lock: True
  holder's next query fails: OperationalError

In the timing runs, a long run holds the lock under a 10-second timeout, and short runs try for it on a 5-second cron grid or on random events about once a second. 20 trials per row:

cron/default:      20/20 trials overlapped; min 10.19s, max 14.98s; 4 within 1s of the timeout
cron/autocommit:   0/20 trials overlapped
event/default:     20/20 trials overlapped; min 10.06s, max 12.67s; 11 within 1s of the timeout
event/autocommit:  0/20 trials overlapped

Cron trials got in across the next 5-second tick, because each short run waits for the tick after the lock dies. Events bunched just after the timeout. None got in before 10 seconds. The loss is deterministic because the timeout is a plain clock, so 20 of 20 is the expected result, not a failure rate. Trials differ only in when the second process happens to poll.

Why the lock vanished: three harmless pieces

1. Drivers open transactions for you. psycopg2 and psycopg 3 begin a transaction before the first statement, even a plain SELECT, unless autocommit is on. SQLAlchemy 2.0 calls this autobegin. The lock code ran one SELECT and never committed, so the connection sat idle in transaction for the whole run.
2. A 10-minute idle_in_transaction_session_timeout on the database fits every symptom. Set with ALTER DATABASE, such a timeout never appears in application config. Ask the database: select setting, source from pg_settings where name = 'idle_in_transaction_session_timeout'.
3. Ending a session releases its session locks. That is the documented contract.

About 10 minutes after the run took the lock, the database (or something in front of it) ended its session and the lock went with it. The run did its real work on other pooled connections, so it kept writing. The next incremental run got true from pg_try_advisory_lock and started. If it was the database's timeout, it did what it was built to do: end forgotten transactions.

Two things said something, and neither helped. At the end of the run, the release step called pg_advisory_unlock on a session the server had ended long ago. That raised an error (case A above: the holder's next query fails). The code caught it with except Exception: pass, then logged that the lock was released: a clean release for a lock that was lost. Postgres writes this to the server log for every session it ends this way:

FATAL:  terminating connection due to idle-in-transaction timeout

One message goes to a log nobody watches. The other was false.

How to tell it from a lookalike

The timing runs above give the signature. No overlap begins before the timeout, and the first begins at the next trigger after it, so on a 5-minute cron grid the overlaps cluster between minute 10 and minute 15. A race in the lock code would land at any minute. A timer does not. Ten minutes could be another timer, so each lookalike has to be ruled out:

lookalike	what rules it out
SQLAlchemy pool_recycle	It acts only at checkout, and the lock connection stays checked out.
idle_session_timeout	It ends only sessions outside a transaction, and a default connection is inside one. Check with show idle_session_timeout.
NAT or firewall dropping idle connections	The server notices only when keepalive probes fail: over two hours with Linux defaults, so no hard floor at 10 minutes. Check tcp_keepalives_*.
Load balancer or proxy	It would also end the AUTOCOMMIT connection, which idles just as long. The reproduction has none.
A pooler timeout (pgbouncer idle_transaction_timeout)	Check where the timeout lives: the database logs the FATAL line above, a pooler logs its own. The reproduction has no pooler. In our case I could not see the deployed connection path, so this is the one lookalike I can't rule out.

A lock that works for 10 minutes beats no lock, but it does harm: everyone believes runs are serialised, so nobody adds another guard or looks for overlaps.

Method 2: Commit right after taking the lock

The idea: the lock was lost because a transaction stayed open, so close it. A session lock survives a commit.

got = conn.execute(text("select pg_try_advisory_lock(hashtextextended('rebuild:run', 0))")).scalar()
conn.commit()

Method 2 result: held, then lost after one more query

Case C commits right after acquiring. Case D adds one select 1 on the lock connection after the commit:

C. commit right after acquire
  acquired: True, holder state: idle
  t=1s other process gets lock: False
  t=5s other process gets lock: False
  holder: alive
D. commit, then one more query
  acquired: True, holder state: idle in transaction
  t=1s other process gets lock: False
  t=5s other process gets lock: True
  holder's next query fails: OperationalError

Case C holds. Case D loses the lock again.

Why: a commit closes one transaction, and the connection opens a new one on its next statement. The likeliest next query is a mid-run "is my lock still held?" check, which brings the failure back.

Method 3: AUTOCOMMIT, the immediate fix

The idea: make the lock connection unable to leave a transaction open.

- conn = engine.connect()
+ conn = engine.connect().execution_options(isolation_level="AUTOCOMMIT")

Each statement commits on its own, so the connection is plain idle between statements. The option is set on the connection, so no query added later can leave a transaction open.

Method 3 result: held

B. AUTOCOMMIT
  acquired: True, holder state: idle
  t=1s other process gets lock: False
  t=5s other process gets lock: False
  holder: alive

In the timing runs, 0 of 40 trials (20 cron, 20 event) let a second run in during 30 seconds, three times the timeout. Method 3 removes the cause the timing pointed at.

What it does not cover

Remember the calibration: a lock on an AUTOCOMMIT connection was gone within about 50 ms of its session ending. The first gap is a second guard. idle_session_timeout ends sessions that are idle outside a transaction, which is exactly where AUTOCOMMIT leaves the lock connection. Case E sets it to 3 seconds on an AUTOCOMMIT holder:

E. AUTOCOMMIT, with idle_session_timeout
  acquired: True, holder state: idle
  t=1s other process gets lock: False
  t=5s other process gets lock: True
  holder's next query fails: OperationalError

That is one way a session lock ends while the run keeps writing. The others, and what to do about each:

what ends or traps the lock	what happens	what to do
Failover, restart, a terminated connection, a middlebox dropping an idle one	The lock is released and the run reconnects on pooled connections and carries on (the calibration above). A check before each company only detects it afterwards. Read pg_locks for your own pid: calling pg_try_advisory_lock after a loss takes the lock afresh on a new session and returns true, which looks like success.	See Method 4.
The run's host loses its network	The server keeps the session until its keepalive probes (server-side settings) give up: about 2 hours 11 minutes with Linux defaults (7,200 s, then 9 probes 75 s apart). Every new run is refused.	Set keepalives. 60 s, 10 s and 3 probes notice in 90 seconds but lose the lock on a longer blip. Not tested on a real network drop.
A hung run with a healthy lock connection	The lock is held until someone notices. Method 1's timeout would have freed it (reasoned, not measured).	Alert on how long the lock has been held.
close() on a pooled connection	The connection goes back to the pool with its session and locks alive. The pool's reset is a rollback, which leaves session locks alone.	Unlock in finally and raise on failure, or call conn.invalidate().
pgbouncer in transaction mode	Session advisory locks are unsupported, by its documentation. I did not run it.	Use transaction-level locks.

Why it is not enough: the lock and the writes live on different sessions, so ending one does not end the other. No setting on the lock connection changes that.

Method 4: The lock inside the transaction, the robust fix

The idea: rebuild each company in its own short transaction, and take a transaction-level lock for that company at the start of it:

begin;
select pg_advisory_xact_lock(hashtextextended('rebuild:company:' || :company_id, 0));
delete from company_rows where company_id = :company_id;
insert into company_rows ...;
commit;

Two properties come from two places. No half-written company comes from transaction atomicity. One writer per company comes from the lock, which lives in the same transaction as the writes. If the session dies, the transaction rolls back and the lock is released at the same moment, so a writer that lost its lock cannot get its writes accepted. Martin Kleppmann gets that guarantee from fencing tokens (numbers the storage checks, to reject a writer whose lock has lapsed) in How to do distributed locking. His lock service and storage are separate systems. Here they are one transaction.

Method 4 result: 250 rows, and a killed run leaves nothing behind

The transaction alone is not enough. Under READ COMMITTED, the second rebuild's delete waits for the first, then finds its old rows already gone and skips them, so it never sees the new rows (the manual describes the skip). company_design.py rebuilds a 250-row company twice at once:

no lock, READ COMMITTED:
  run1: committed, waited 0.0s, deleted 250
  run2: committed, waited 0.0s, deleted 0
  rows afterwards: 500
xact lock, READ COMMITTED:
  run1: committed, waited 0.0s, deleted 250
  run2: committed, waited 0.7s, deleted 250
  rows afterwards: 250

Without the lock, 500 rows. With it, 250.

The test AUTOCOMMIT failed. A run takes a company's lock, deletes its 250 rows and keeps working. A second run for the same company waits. I end the first run's session after its delete and before its commit:

  holder: OperationalError
  waiter: committed, waited 0.6s, deleted 250
  row counts the observer ever saw: [250]; final rows: 250

The waiter got the lock only when the holder's transaction rolled back, then rebuilt the company once. The dead run's work vanished together with its lock. I tested one kill point.

What it costs. company_perf.py rebuilds 6,000 synthetic companies (1.32 million rows, indexed):

A. 6000 companies, 1,320,000 rows: delete + insert each company
   one transaction for all companies, no lock                  14.0s
   one transaction per company, no lock                        21.6s
   one transaction per company + xact lock                     21.8s
   50 companies per transaction + xact locks, sorted           15.8s
   one transaction per company + xact lock, 4 workers           6.1s
   one transaction per company + xact lock, 8 workers           3.5s

The lock adds no measurable cost in this run: 21.8 s with it against 21.6 s without (a single run, so compare the saved output). The cost is one commit per company, and 50 companies per transaction, locked in sorted order, brings the total back to the cost of one giant transaction. Runs on different companies do not wait for each other, unless their keys collide. A full run just takes each company's lock in turn, and a run-level lock is needed only to save duplicated work.

Rules it depends on

1. READ COMMITTED, with the lock in its own statement, so the delete runs on a fresh snapshot after the wait. With the lock, REPEATABLE READ fails the delete after the wait with could not serialize access due to concurrent delete.
2. Take the lock first, then read what you rebuild from.
3. Set lock_timeout. A waiter is active, not idle, so no idle guard ends it. Only lock_timeout or statement_timeout does. Retry the company when it fires. A run that sits idle inside the transaction is ended by the idle timeout and now rolls back with its lock, so the failure is loud and the rows are safe.
4. Lock several companies in sorted order, so two runs cannot deadlock, and keep batches near 50.
5. Every writer takes the lock. An advisory lock only excludes code that takes it. For text keys use hashtextextended: hashtext is 32-bit and can collide.

All four under the same faults

fault	1. default	2. commit after acquiring	3. AUTOCOMMIT	4. lock inside the transaction
Lock connection idle past the idle-in-transaction timeout	Lost	Held	Held	Run fails and rolls back, rows intact
One more query, then idle past that timeout	Lost (follows)	Lost	Held (follows)	No session lock, and the transaction rolls back with its lock
Lock connection idle past idle_session_timeout	Not affected: it is in a transaction (follows)	Lost (follows: it is plain idle)	Lost	No session lock, and the transaction rolls back with its lock
Holder's session killed	Lost within one poll (follows)	Lost within one poll (follows)	Lost within one poll	Lock and writes roll back together
Writes stop when the lock is lost	No	No	No	Yes

Cells marked "follows" come from the state the connection is in, not from a script here. The rest are measured by the scripts above. The last row follows from where the writes run: only a lock in the same transaction as the writes can stop them.

Checklist

If you hold a session advisory lock:

1. Check the lock connection's state: select state from pg_stat_activity where pid = <holder pid>. idle in transaction means you have this bug.
2. Check both idle guards in pg_settings, with source: idle_in_transaction_session_timeout and idle_session_timeout. Database and role settings never show up in application config.
3. Put the lock on its own AUTOCOMMIT connection. That removes this bug, not the others (item 8).
4. Release in finally, and make the release tell the truth. Swallowed errors plus a success log make a lost lock look released.
5. Test against a real Postgres with a shrunken timeout. A mocked engine cannot fail this way.
6. Behind pgbouncer in transaction mode, use transaction-level locks only.
7. Set keepalives for what a dropped connection should do. Defaults can hold a lock for two hours. Tight ones lose it on a short blip.
8. Tie the lock's lifetime to the writes it protects. On different sessions, no fix to the lock alone makes them safe.

Why not just...?

alternative	why not
SET idle_in_transaction_session_timeout = 0, on the session or a role	It works, but it exempts the session from a guard the owner set on purpose and leaves the transaction open.
A heartbeat select 1	It proves the connection is alive, not the run. The writes use other sessions, so a stalled run keeps writing, or a hung run keeps the lock. A watchdog that checks pg_locks only detects the loss afterwards.
Raise the timeout	It hides every other leaked transaction, and a run of hours needs a value that is no longer a guard.
One giant transaction	It holds a transaction id for hours (vacuum cannot clean up behind it), keeps row locks, and loses all work on failure.
Redis, etcd or a lease row	The holder can lose the lock and keep writing, so the database must check a fencing token. A lease row does, at an extra write per company and one hot row.
A row lock or a unique constraint	A row lock needs a parent row for every company and blocks foreign-key inserts. A unique constraint is a backstop, not exclusion.
SERIALIZABLE with retry	It works (250 rows). Without the lock, REPEATABLE READ misses a company's first build and leaves 500. But 8 different companies at once failed 7 of 8 with no index, 6 of 8 with an index and adjacent ids, and 0 of 8 with an index and spread-out ids. The same 10 companies took 8.8 s with 101 failed attempts, against 5.4 s and none for the lock (single runs).
A queue, an idempotent rebuild, the lock on the writes' connection, Kafka partitioning	Each solves a neighbouring problem. The overlap was two sources wanting the same company, runs wrote different outputs, and a full run is not keyed to one company. A lock on the writes' connection costs parallelism. The last two are reasoned, not tested.

Run it yourself

You need Docker (or any Postgres you can throw away), Python 3, and about five minutes.

1. Start a throwaway Postgres. max_connections must be at least 200 for the timing script.

   docker run --rm -d --name lockdemo -e POSTGRES_PASSWORD=pw -p 55433:5432 postgres:18 -c max_connections=200

   The postgres:18 image was 18.6 when I ran this.
2. Install the dependencies.

   python -m venv .venv && . .venv/bin/activate && pip install "sqlalchemy==2.0.51" psycopg2-binary

3. Point the scripts at it.

   export DSN="postgresql+psycopg2://postgres:pw@localhost:55433/postgres"

4. Download the scripts, which live in one folder (start with its README), then run each and compare its output with the saved output next to it.
5. Clean up: docker stop lockdemo.

script	shows	time	compare with
reproduce_cases.py	Cases A to E	30 s, locks only	reproduce_cases.txt, line for line
timing_sim.py	The timing runs and chart	30 s, locks only	timing_sim.txt: default 20 of 20 overlapped, none before 10 s; AUTOCOMMIT 0 of 20
calibrate.py	The detector check	11 s, locks only	calibrate.txt: each within about 50 ms of 2.00 s
company_design.py	Method 4: 500 against 250 rows, the kill test	25 s, two small tables, dropped after	company_design.txt, apart from the waits
company_perf.py	Cost per company, SERIALIZABLE false conflicts with adjacent and spread-out ids, SERIALIZABLE against the lock	1 min, two tables of 1.3 million rows, dropped after	company_perf.txt: seconds move; 7, 6 and 0 of 8 failures; retries against none

Three runs that measured the wrong thing

• The mocked tests. They had no transaction state and no server timeout, so they passed every time and could never have failed. A test on a real Postgres with a 1-second timeout fails on the old code in about a second.
• The first case D. It reported the lock held when it should have been lost. Case C's connection had been "closed" with close(), went back to the pool with its session alive, and kept holding the lock under the same key. Now every case uses its own key and ends its session with conn.invalidate().
• The first SERIALIZABLE run. 6 of 8 different companies failed, which I read as SERIALIZABLE confusing unrelated companies. The 8 ids were adjacent, so their index entries shared pages. With spread-out ids, 0 of 8 failed. I had measured my test data.

What I did not test, and caveats

• A real network drop or a failover. I ended sessions with pg_terminate_backend, the closest local stand-in. The two-hour figure comes from the default keepalive settings.
• Method 4 beyond one kill point. I ended the session after the delete and before the commit, and ran several companies per transaction as a cost test, not a failure test.
• pgbouncer, other drivers, Kafka partitioning, idempotent rebuilds. pgbouncer is from its documentation, everything ran on psycopg2 under SQLAlchemy 2.0, and the last two are reasoned.
• Timing and cost numbers scale minutes to seconds on one machine, with synthetic rows. Real cron and start-up delays are noisier, and a commit costs more on network storage. Numbers move a little from run to run. The shape does not.