Parameter · Lock Management
max_pred_locks_per_relation
Sets the maximum number of predicate-locked pages and tuples per relation. If more than this total of pages and tuples in the same relation are locked by a connection, those locks are replaced by a relation-level lock.
- Default:
-2 - Type: integer
- Range: -2147483648 … 2147483647
- Applies: Reload
In plain words
The next step up from max_pred_locks_per_page: when one SERIALIZABLE transaction holds more than this many page and row predicate locks in the same table, they are replaced by a single lock on the whole table. Negative values mean "max_pred_locks_per_transaction divided by this number": the default -2 is half of 64 = 32.
For DBAs and operators
Values ≥ 0 are an absolute limit; a negative value -n means max_pred_locks_per_transaction / n, which keeps the pre-PostgreSQL 10 behaviour. With the defaults, the 33rd page/tuple lock in one relation promotes everything to a relation-level SIREAD lock.
Relation locks are cheap in memory but make any write to that table by a concurrent serializable transaction a potential conflict, raising the rate of
ERROR: could not serialize access due to read/write dependencies among transactions
If a SERIALIZABLE workload reads many rows from big tables via index scans and suffers false positives, raise this (and max_pred_locks_per_transaction to give the table room).
How to change it
Change in postgresql.conf or ALTER SYSTEM, then run SELECT pg_reload_conf().
See it in context
Isolation levels Locking and deadlocks Serializable Snapshot Isolation