Parameter · Lock Management
max_pred_locks_per_transaction
Sets the maximum number of predicate locks per transaction. The shared predicate lock table is sized on the assumption that at most "max_pred_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time.
- Default:
64 - Type: integer
- Range: 10 … 2147483647
- Applies: Restart required
In plain words
SERIALIZABLE transactions record what they read as "predicate locks" (they never block anyone; they are used to detect dangerous patterns). These live in a shared table sized like the normal lock table: this value × the number of backends and prepared transactions. If it fills up you get out of shared memory.
Only matters if you use SERIALIZABLE.
For DBAs and operators
Size of the SIREAD lock table: max_pred_locks_per_transaction × (MaxBackends + max_prepared_transactions). To save space, PostgreSQL promotes fine-grained locks to coarser ones: many tuple locks on one page become a page lock (max_pred_locks_per_page), many page/tuple locks in one relation become a relation lock (max_pred_locks_per_relation). Coarser locks use less memory but cause more false-positive serialization failures.
When the table is full:
ERROR: out of shared memory
HINT: You might need to increase "max_pred_locks_per_transaction".
Predicate locks can outlive their transaction (until all overlapping serializable transactions finish), so long-running serializable transactions, or idle-in-transaction sessions, make the table fill faster. Inspect with SELECT locktype, count(*) FROM pg_locks WHERE mode = 'SIReadLock' GROUP BY 1;.
How to change it
Change in postgresql.conf or ALTER SYSTEM, then restart the server.
See it in context
Isolation levels Locking and deadlocks Serializable Snapshot Isolation
Related parameters
max_pred_locks_per_relation max_pred_locks_per_page max_locks_per_transaction default_transaction_isolation