Parameter · Lock Management
max_pred_locks_per_page
Sets the maximum number of predicate-locked tuples per page. If more than this number of tuples on the same page are locked by a connection, those locks are replaced by a page-level lock.
- Default:
2 - Type: integer
- Range: 0 … 2147483647
- Applies: Reload
In plain words
A SERIALIZABLE transaction records each row it reads as a tiny predicate lock. When it has read more than this many rows on the same 8 kB page, PostgreSQL replaces those row locks with one lock on the whole page. That saves memory, but writes to other rows on the page may now look like conflicts.
For DBAs and operators
Default 2: the third tuple lock on a page triggers promotion to a page-level SIREAD lock. Page locks can in turn be promoted to a relation lock (max_pred_locks_per_relation). Promotion is one-way for the life of the transaction.
Higher values mean fewer false-positive ERROR: could not serialize access due to read/write dependencies among transactions errors, but more entries in the predicate lock table (max_pred_locks_per_transaction). Only relevant for SERIALIZABLE workloads; sequential scans take a relation lock straight away anyway.
SELECT locktype, relation::regclass, page, tuple
FROM pg_locks WHERE mode = 'SIReadLock';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