Postgresql – determine maximum advisory locks supported by Postgres

postgresql

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 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 each SELECT ... FOR UPDATE takes? 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!

Best Answer

The lock table is a shared memory area with room for 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.