CODEWITHSUNDEEP
postgresql
Peter Mølgaard Pallesen
Peter Mølgaard Pallesen

Reputation: 1931

When are locks released in Postgres

I have some problems understanding locks. Naturally locks are released when everything goes smoothly. But I'm unsure on the exact logic for when locks are released, when things break down. How long a lock can persist? Can I kill all processes and thereby release all locks? Do I have to explicitly call rollback?

Upvotes: 1

Views: 2352

Answers (1)

pifor
pifor

Reputation: 7882

In general locks are released when transaction ends with COMMIT or ROLLBACK.

There are exceptions:

  1. Once acquired, a lock is normally held till end of transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.

2.

There are two ways to acquire an advisory lock in PostgreSQL: at session level or at transaction level. Once acquired at session level, an advisory lock is held until explicitly released or the session ends.

Killing backend processes should release the locks but should be not the right way to release the locks: it should only be used as last resort if you cannot end the client application in a clean way.

Upvotes: 4

Related Questions