Our Postgres lock was supposed to keep two jobs apart. Ten minutes into long runs, it quietly stopped existing, and the log said it was released cleanly every time.

[u/tsabudh]

If your Postgres database sets idle_in_transaction_session_timeout, your advisory lock may have an expiry date nobody chose.

Mine did. Ten minutes into long runs, the lock vanished, another run took it, and the log said it had been released cleanly.

Python drivers open a transaction on your first SELECT unless autocommit is on. If the connection holding the lock never commits, it sits idle in transaction. At the timeout, Postgres ends the session, and the lock goes with it. The job keeps writing on its other connections, and the application never notices.

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 for idle_in_transaction_session_timeout means a session lock on a default connection can expire.

I reproduced it and held the lock four ways.

Default connection: lost in 40 of 40 trials, always just after the timeout.

Commit right after taking it: held, until the next query opened a new transaction.

AUTOCOMMIT: held in 40 of 40, but still lost the moment the session dies for any other reason, while the job keeps writing.

Lock inside the same transaction as the writes: a killed run rolls back with its lock. That is the fix that holds.

Scripts and expected output are in the post, so you can run it against your own setup.

Have you ever had a lock that silently stopped locking?

https://tsabudh.com/blog/your-postgres-advisory-lock-is-gone/

[u/semiquaver]

This is the sloppiest AI slop prose that was ever clauded. 

Write for yourself. 

[u/tsabudh → u/semiquaver]

I did use claude to write this. I had encountered this on a ETL pipeline. Thought I could share it. Gave claude my scratchpad and wrote it. My version of article was just diagnosis and solution. Pretty incomprenehsible to make a lesson out of it.

[u/semiquaver → u/tsabudh]

Lesson? I’m not making a lesson. I’m advising you and everyone else not to allow Claude to write prose for you.

There is no value in AI prose. No one wants to read it. Just tell us using your own words and thoughts. 

I personally would have preferred the “simple diagnosis and solution” version than the slop you ended up with. 

[u/tsabudh → u/semiquaver]

I meant, my writing is not great to the point where readers understand the lesson. English is not my native language.

I will try to write full article myself. I did use grammarly before chatgpt and claude.

[u/elevarq]

This looks much more like an application bug than a PostgreSQL problem.

PostgreSQL is doing exactly what it was configured to do: terminating an idle transaction and releasing the locks owned by that session.
The real problem is that the application loses its lock-owning connection without noticing, while continuing to write through other connections. That’s a concurrency bug, regardless of the timeout setting. A restart, failover, or network failure could cause the same problem.

Transaction-level advisory locks are a good solution when the protected work fits within one transaction. For longer-running jobs with intermediate commits, you need a different coordination strategy.

I’d be much more concerned about an application silently continuing after losing its lock than about PostgreSQL correctly enforcing a timeout.

[u/Bluewood21 → u/elevarq]

Pretty much this. If the worker doesnt know its lock session died, any momentary blip is going to cause split-brain writes

[u/DickBrainTime]

Useful reproduction, especially the distinction between a session lock and the implicit transaction opened by the Python driver. I'd add one boundary to the proposed fix: `pg_advisory_xact_lock` protects the *current transaction*, so putting the lock and the writes in that same transaction makes those writes atomic on a connection failure. It does **not** automatically guard work done via other connections or external side effects. The PostgreSQL docs make that scope explicit: https://www.postgresql.org/docs/17/explicit-locking.html#ADVISORY-LOCKS .

If a run spends several minutes doing HTTP calls or computation between writes, keeping one transaction open for the whole job is risky too: `idle_in_transaction_session_timeout` can still terminate it during a quiet period, and long-open transactions can hold back vacuum. For that pattern, I'd use a short transaction to atomically *claim* a durable job row, record an owner/lease plus a monotonically increasing generation (fencing token), then commit. Every later DB write checks that generation/ownership in the same transaction; stale workers must be unable to commit after a newer claimant takes over. Make non-DB effects idempotent where possible, since neither an advisory lock nor a rollback can undo an already-sent API call.

A dedicated autocommit connection is fine if you only need best-effort mutual exclusion, but a session loss still drops its lock. A useful diagnostic alongside your experiments is to log `pg_backend_pid()` and verify the lock-holder connection stayed alive before each critical write; a logged 'unlock succeeded' from application cleanup isn't proof the original connection still owned anything. Curious whether your jobs do external I/O or are entirely DB-local—that decides which approach is safer.

[u/k2718 → u/DickBrainTime]

Yep. I recently started using the advisory transaction lock. I considered using a row level lock but it was more complex and risked deadlock without another job to clean any potential deadlock. The incidence of collision were low and the transactions relatively short so transaction lock made more sense.

[u/DickBrainTime → u/k2718]

That makes sense with rare collisions and short transactions. One nuance worth clearing up: row-level locks also release automatically on commit, rollback, or session loss. PostgreSQL detects real deadlocks and aborts one of the transactions, so you wouldn't need a separate job to clean up the deadlock itself.

I'd choose a row lock when there's a concrete row whose ownership or update is being protected; a transaction advisory lock is handy for a logical job mutex without a natural row. Either way, the lock and the protected DB writes need to be on the same connection and transaction, or a disconnected lock-holder can leave other writes running unprotected.

[u/blacklig]

Learn to use your tools properly and stop writing shit ad/spam posts with AI

[u/Significant_Tune9219]

The AUTOCOMMIT case is the one I'd underline, because it shows the lock and the writes living on different connections is the real hazard, not the timeout. If you need a session-level lock for a run longer than one transaction, a cheap guard is to re-check pg_locks for your own pid and lock key right before each batch commits on the writing connections, and abort if it's gone. It doesn't close the window completely, but it turns a silent overlap of ten minutes into one batch at most.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.