patternsqlMinor
determine maximum advisory locks supported by Postgres
Viewed 0 times
advisorysupportedmaximumpostgresdeterminelocks
Problem
According to the Postgres documentation, the maximum number of regular and advisory locks is limited by a shared memory pool:
Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.
How can I determine the size of this pool? Is this the same thing as shared buffers, which I can see with
I also have a couple more detailed questions:
The documentation is a little confusing because if you look at the documentation for
The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects
Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.
How can I determine the size of this pool? Is this the same thing as shared buffers, which I can see with
show shared_buffers; or is it something different? I am trying to determine roughly how many advisory locks my installation would be able to support because I am doing a ton of locking. My shared_buffers size is 5012MB.I also have a couple more detailed questions:
- If the server was unable to grant an advisory lock when I called
pg_advisory_xact_lock(), would it hang, error out, or fail silently? As long as it doesn't fail silently I'm good, although ideally it would hang and then continue once memory frees up.
- I locking not only with advisory locks but also with
SELECT ... FOR UPDATE.If I know the size of the pool, how can I calculate roughly how much space in the pool each advisory lock takes, and eachSELECT ... FOR UPDATEtakes? I know roughly how many rows will be impacted by eachSELECT ... FOR UPDATE.
The documentation is a little confusing because if you look at the documentation for
max_locks_per_transaction it says:The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects
Solution
The lock table is a shared memory area with room for
This is the limit for advisory and all other locks. Like the documentation says, row locks are stored on the row and not (permanently) in the lock table.
max_locks_per_transaction * (max_connections + max_prepared_transactions) locks. It is allocated at server startup time and never resized.This is the limit for advisory and all other locks. Like the documentation says, row locks are stored on the row and not (permanently) in the lock table.
Context
StackExchange Database Administrators Q#285579, answer score: 3
Revisions (0)
No revisions yet.