Concurrency is one of PostgreSQL's strongest features. Multiple users can read and modify data simultaneously while PostgreSQL ensures data consistency and correctness through its lock management system.
Most PostgreSQL administrators are familiar with common lock types such as row locks, table locks, and transaction locks. However, PostgreSQL also provides several configuration parameters that control lock management behavior and memory allocation for lock tracking.
Why Lock Management Matters
Whenever a transaction accesses database objects, PostgreSQL acquires locks internally. These locks help prevent data corruption and ensure that concurrent transactions operate safely.
Without an efficient lock management system, PostgreSQL would be unable to:
- Prevent conflicting updates
- Detect deadlocks
- Maintain transaction isolation
- Support Serializable transactions
The Lock Management section of postgresql.conf contains the following parameters:
deadlock_timeout
max_locks_per_transaction
max_pred_locks_per_transaction
max_pred_locks_per_relation
max_pred_locks_per_page
deadlock_timeout
Purpose
This parameter specifies how long PostgreSQL waits on a lock before checking whether a deadlock exists.
Default value:
SHOW deadlock_timeout;
Output:
deadlock_timeout
------------------
1s
(1 row)
PostgreSQL internally stores the value in milliseconds. Check more about this parameter from pg_settings like this
SELECT *
FROM pg_settings
WHERE name = 'deadlock_timeout';
Output:
name | deadlock_timeout
setting | 1000
unit | ms
category | Lock Management
short_desc | Sets the time to wait on a lock before checking for deadlock.
extra_desc |
context | superuser
vartype | integer
source | default
min_val | 1
max_val | 2147483647
enumvals |
boot_val | 1000
reset_val | 1000
sourcefile |
sourceline |
pending_restart | f
The corresponding source code definition is:
{
name => 'deadlock_timeout',
type => 'int',
context => 'PGC_SUSET',
group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the time to wait on a lock before checking for deadlock.',
flags => 'GUC_UNIT_MS',
variable => 'DeadlockTimeout',
boot_val => '1000',
min => '1',
max => 'INT_MAX',
},How Deadlock Detection Works
Consider two transactions.
Create a test table and insert values
create table customers(id int,name text);
insert into customers (id,name) values(1,’marc demo');
insert into customers (id,name) values(2,'jhon');
Transaction A:
BEGIN;
UPDATE customers
SET name = 'Alice'
WHERE id = 1;
Transaction B:
BEGIN;
UPDATE customers
SET name = 'Bob'
WHERE id = 2;
Now Transaction A attempts:
UPDATE customers
SET name = 'Alice'
WHERE id = 2;
At the same time, Transaction B attempts:
UPDATE customers
SET name = 'Bob'
WHERE id = 1;
Both transactions are waiting for each other.
This creates a deadlock.
Result:
ERROR: deadlock detected
DETAIL: Process 141629 waits for ShareLock on transaction 4713; blocked by process 141327.
Process 141327 waits for ShareLock on transaction 4714; blocked by process 141629.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "customers"
PostgreSQL does not immediately perform deadlock detection because the operation is relatively expensive. Instead, it waits for the period defined by deadlock_timeout.
After the timeout expires, PostgreSQL checks for deadlocks and aborts one transaction if necessary.
max_locks_per_transaction
Purpose
This parameter determines the maximum number of lockable objects PostgreSQL expects a transaction to lock simultaneously.
Default value:
SHOW max_locks_per_transaction;
Output:
max_locks_per_transaction
---------------------------
64
(1 row)
Details from pg_settings:
SELECT *
FROM pg_settings
WHERE name = 'max_locks_per_transaction';
Output:
name | max_locks_per_transaction
setting | 64
unit |
category | Lock Management
short_desc | Sets the maximum number of locks per transaction.
extra_desc | The shared lock table is sized on the assumption that at most "max_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time.
context | postmaster
vartype | integer
source | default
min_val | 10
max_val | 2147483647
enumvals |
boot_val | 64
reset_val | 64
sourcefile |
sourceline |
pending_restart | f
Source code definition:
{
name => 'max_locks_per_transaction',
type => 'int',
context => 'PGC_POSTMASTER',
group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of locks per transaction.',
long_desc => 'The shared lock table is sized on the assumption that at most "max_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time.',
variable => 'max_locks_per_xact',
boot_val => '128',
min => '10',
max => 'INT_MAX',
},What Does It Control?
Every time a transaction accesses database objects, PostgreSQL creates lock entries in shared memory.
Examples of lockable objects include:
A transaction touching hundreds or thousands of tables may require more lock entries than the default allocation.
Example
Suppose a transaction modifies hundreds of partition tables:
BEGIN;
UPDATE sales_2025_01 SET amount = amount + 10;
UPDATE sales_2025_02 SET amount = amount + 10;
UPDATE sales_2025_03 SET amount = amount + 10;
-- Hundreds more partitions
COMMIT;
Each table requires lock entries.
If the lock table becomes full, PostgreSQL may produce errors indicating insufficient lock resources.
When to Increase
Consider increasing this parameter if:
- You use thousands of partitions
- Large schema migration scripts are common
- Transactions touch many database objects
A restart is required because this parameter uses the PGC_POSTMASTER context.
Predicate Locks and Serializable Transactions
Before discussing the remaining parameters, it is important to understand predicate locking.
Many PostgreSQL users know about traditional locks, but predicate locks are often misunderstood.
Normal Locks
Normal locks protect existing database objects.
Example:
BEGIN;
UPDATE customers
SET name = 'Alice'
WHERE id = 1;
PostgreSQL acquires:
If another transaction attempts:
UPDATE customers
SET name = 'Bob'
WHERE id = 2;
it must wait.
Normal locks therefore block conflicting operations.
Predicate Locks
Predicate locks are completely different.
They exist primarily for Serializable transactions.
Example:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT *
FROM products
WHERE price > 100;
PostgreSQL records that this transaction has read rows matching:
price > 100
This information is stored as predicate locks.
Unlike normal locks, predicate locks do not block inserts or updates immediately.
Instead, PostgreSQL tracks dependencies between transactions and aborts one transaction if a serialization conflict occurs.
Phantom Read Example
Create a test table and insert values
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
amount NUMERIC
);
INSERT INTO orders(amount)
VALUES
(500),
(1200),
(1500),
(800),
(2000);
Transaction A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*)
FROM orders
WHERE amount > 1000;
Result:
3
Transaction B:
BEGIN;
INSERT INTO orders(amount)
VALUES (2000);
COMMIT;
Without predicate locking, Transaction A could later observe:
11
rows instead of 10.
This phenomenon is known as a phantom read.
Predicate locking allows PostgreSQL to detect such anomalies and maintain true serializable behavior.
During this time, check the pg_locks system catalogues
select * from pg_locks;
Result :
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+-----------------+---------+----------+-----------
relation | 5 | 2684 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 12073 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 2685 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 28853 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 2662 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 28846 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 3455 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 2663 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 2615 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
relation | 5 | 1259 | | | | | | | | 5/16 | 141327 | AccessShareLock | t | t |
virtualxid | | | | | 5/16 | | | | | 5/16 | 141327 | ExclusiveLock | t | t |
relation | 5 | 28846 | | | | | | | | 5/16 | 141327 | SIReadLock | t | f |
(12 rows)
Look at the last row
SIReadLock stands for Serializable Isolation Read Lock, which is PostgreSQL's implementation of predicate locking.
max_pred_locks_per_transaction
Purpose
Controls the maximum number of predicate locks PostgreSQL expects a transaction to acquire.
Default value:
SHOW max_pred_locks_per_transaction;
Output:
64
Check more details about this parameter from pg_settings.
SELECT *
FROM pg_settings
WHERE name = 'max_pred_locks_per_transaction';
Output:
name | max_pred_locks_per_transaction
setting | 64
unit |
category | Lock Management
short_desc | Sets the maximum number of predicate locks per transaction.
extra_desc | 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.
context | postmaster
vartype | integer
source | default
min_val | 10
max_val | 2147483647
enumvals |
boot_val | 64
reset_val | 64
sourcefile |
sourceline |
pending_restart | f
Source code:
{
name => 'max_pred_locks_per_transaction',
type => 'int',
context => 'PGC_POSTMASTER',
group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of predicate locks per transaction.',
variable => 'max_predicate_locks_per_xact',
boot_val => '64',
min => '10',
max => 'INT_MAX',
},When It Matters
This parameter only becomes important when:
- Using SERIALIZABLE isolation level
- Running large analytical queries
- Reading large portions of tables
max_pred_locks_per_relation
Purpose
Controls how many page-level and tuple-level predicate locks can exist on a relation before PostgreSQL promotes them to a relation-level predicate lock.
Default value:
SHOW max_pred_locks_per_relation;
Output:
-2
Check more about this parameter from pg_settings.
SELECT *
FROM pg_settings
WHERE name = 'max_pred_locks_per_relation';
Output:
name | max_pred_locks_per_relation
setting | -2
unit |
category | Lock Management
short_desc | Sets the maximum number of predicate-locked pages and tuples per relation.
extra_desc | 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.
context | sighup
vartype | integer
source | default
min_val | -2147483648
max_val | 2147483647
enumvals |
boot_val | -2
reset_val | -2
sourcefile |
sourceline |
pending_restart | f
Source code:
{
name => 'max_pred_locks_per_relation',
type => 'int',
context => 'PGC_SIGHUP',
group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of predicate-locked pages and tuples per relation.',
variable => 'max_predicate_locks_per_relation',
boot_val => '-2',
},Lock Promotion
Initially PostgreSQL may create:
Tuple Predicate Locks
If too many tuple locks appear in a table, PostgreSQL promotes them to:
Page Predicate Locks
If even more locks accumulate, PostgreSQL promotes them further to:
Relation Predicate Locks
This reduces memory consumption.
max_pred_locks_per_page
Purpose
Controls how many tuple-level predicate locks can exist on a page before PostgreSQL replaces them with a page-level predicate lock.
Default value:
SHOW max_pred_locks_per_page;
Output:
max_pred_locks_per_page
-------------------------
2
(1 row)
Check more about this parameter from pg_settings.
SELECT *
FROM pg_settings
WHERE name = 'max_pred_locks_per_page';
Output:
name | max_pred_locks_per_page
setting | 2
unit |
category | Lock Management
short_desc | Sets the maximum number of predicate-locked tuples per page.
extra_desc | 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.
context | sighup
vartype | integer
source | default
min_val | 0
max_val | 2147483647
enumvals |
boot_val | 2
reset_val | 2
sourcefile |
sourceline |
pending_restart | f
Source code:
{
name => 'max_pred_locks_per_page',
type => 'int',
context => 'PGC_SIGHUP',
group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of predicate-locked tuples per page.',
variable => 'max_predicate_locks_per_page',
boot_val => '2',
},Imagine a transaction scans thousands of rows on the same page.
Creating individual predicate locks for every tuple would consume significant shared memory.
Instead PostgreSQL promotes those locks into a single page-level predicate lock.
This keeps memory usage under control while preserving Serializable isolation guarantees.
PostgreSQL's lock management subsystem is responsible for maintaining consistency in highly concurrent environments. The lock management parameters allow administrators to control how PostgreSQL allocates memory for lock tracking and how it handles deadlock detection.
Among these parameters, deadlock_timeout and max_locks_per_transaction are the most commonly encountered in production environments. The predicate lock parameters exist primarily to support Serializable transactions and protect against anomalies such as phantom reads.
Understanding these settings helps database administrators troubleshoot lock-related issues, size shared memory appropriately, and make informed tuning decisions when building large-scale PostgreSQL deployments.