Database Activity and Locks – EnterpriseDB

Problem description

Database users access and modify shared objects and data during normal activity.
Relation databases handle these concurrent accesses and modifications using locks.
PostgreSQL has several types of lock, some of them are compatible and some others not.
If users want to access the same objects or data with not compatible locks some of them may need to wait for acquiring it.
For this reason, some maintenance activities or new deployments can involve downtime.

pg_locks View

PostgreSQL has a catalog view called pg_locks which is holding the information about cluster-wide locks.
There are several types of locks reported in that table for different objects like relation, tuple, page etcetera.
All these locks can access the database objects in different modes, some of them are not compatible.
This view is the main source for monitoring locks and waiting time in your cluster.

You may find useful queries to monitor this view and the locks in your PostgreSQL cluster in the Postgres wiki pages reported in the external resources section.

There is also a very useful function which tells you what session is blocking the statements you are monitoring.
The function is pg_blocking_pids(pid) and can be used, for example, in the following way

SELECT 
   locktype, 
   mode,
   (Select datname from pg_database where oid=pg.database::regclass::text),
   relation::regclass::text as relation, 
   granted, 
   pid, 
   pg_blocking_pids(pid) AS blocking_pid
FROM pg_locks pg;

The above query collects the main locks information including the blocking session

PostgreSQL 14 pg_locks version

The following picture describes the pg_locks view structure

Screenshot_2022-01-03_at_15.07.04.png

Lock Matrix

PostgreSQL has eight different modes to acquire a lock.
Below you find the matrix of lock compatibility which you can refer in case or lock conflicts.

	Access Share	Row Share	Row Exclusive	Share Update Exclusive	Share	Share Row Exclusive	Exclusive	Access Exclusive
Access Share
Row Share
Row Exclusive
Share Update Exclusive
Share
Share Row Exclusive
Exclusive
Access Exclusive

= Compatible

= Not Compatible

PostgreSQL 14 statements and locks

The following is a not exhaustive list of statements and their required locks in PostgreSQL 14.
To have the complete list of locks required by each statement please refers to PostgreSQL documentation.
https://www.postgresql.org/docs/14/index.html

Access Share:
SELECT
Row Share:
SELECT FOR UPDATE
SELECT FOR SHARE
Row Exclusive:
INSERT
UPDATE
DELETE
(Also access share on any referenced table)
Share Update Exclusive:
ALTER TABLE SET STATISTICS
ALTER TABLE SET
ALTER TABLE RESET
ALTER TABLE VALIDATE CONSTRAINT
ALTER TABLE SET CLUSTER ON
ALTER TABLE SET WITHOUT CLUSTER
ALTER INDEX RENAME
VACUUM (without FULL)
ANALYZE
CREATE INDEX CONCURRENTLY
DROP INDEX CONCURRENTLY (Many limitations)
REINDEX CONCURRENTLY
CREATE STATISTICS
Share:
CREATE INDEX (without CONCURRENTLY)
Share Row Exclusive:
ALTER TABLE ADD FOREIGN KEY (Also on the referenced table)
ALTER TABLE DISABLE/ENABLE TRIGGER
CREATE TRIGGER
CREATE COLLATION
Exclusive
REFRESH MATERIALIZED VIEW CONCURRENTLY(Letting other users select the view)
Access Exclusive
CLUSTER
ALTER TABLE (unless explicitly indicated)
ALTER INDEX (unless explicitly indicated)
REFRESH MATERIALIZED VIEW (without concurrently)
REINDEX (without concurrently)
DROP TABLE
DROP INDEX (without concurrently)
TRUNCATE

Note that the full list of locks to be acquired may change depending on the PostgreSQL version you are using.

Also note that some statements like attaching and detaching partitions may acquire different type of locks on different tables (parent table, child table, etcetera...)

Also note that some strategy may be required to minimize the downtime of some maintenance activities like adding constraints with NOT VALID attribute and later on validate the constraint using VALIDATE CONSTRAINT.
Refers always to the PostgreSQL documentation to have the exact picture
https://www.postgresql.org/docs/14/index.html

Solution

Before planning any maintenance activity or new deployments in your production environments, check the lock matrix, read carefully the PostgreSQL documentation and test your activities in a production clone environment simulating a usual load

EnterpriseDB Resources

https://www.enterprisedb.com/edb-docs/d/postgresql/reference/manual/13.1/view-pg-locks.html

External Resources

The following wiki pages might help you in monitoring locks