[skrause]

PostgreSQL has pg_advisory_xact_lock which releases the lock automatically when the transaction is over.

[m11a]

But then you’d be holding a DB connection for the entire duration of your task (which may include HTTP calls, etc). You might even do asynchronous work in parallel, which doesn’t quite work with txn locks. So the session based locks seem a bit better imo.

[eknkc]

I personally do these in .NET, I obtain a connection dedicated to that operation, start a transaction, obtain lock and go crazy. Upon completion of the async workflow, the transaction closes and lock releases. I know I'm holding up a connection and putting some pressure on postgres by keeping a transaction open but session management might be harder as the underlying connection provider uses pooling and it is easier to use transactions rather than sessions here.

And if you add something like pgBouncer or whatever, this should still work but a session lock would fuck things up.