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
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
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 each
SELECT ... FOR UPDATEtakes? I know roughly how many rows will be impacted by each
SELECT ... 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 as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited.
This seems to track with the idea that the memory pool is equal to
max_locks_per_transaction * max_connections described earlier, but here it is saying that the max has more to do with the number of tables and not the number of rows. I'm not really sure how to square this with the first quote, or how this relates to the space taken by advisory locks.
Any tips on calculating would be greatly appreciated!