postgres locks - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/view-pg-locks.html
• https://wiki.postgresql.org/wiki/Lock_Monitoring
• https://postgres-locks.husseinnasser.com/
• https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES

PostgreSQL generally uses pessimistic locking (although it’s also possible to use optimistic locking), which means lockable resources like tables or rows are locked upfront before they’re modified. When queries try to access lockable resources, depending on the query type, it will require either a shared lock or exclusive lock.

Locks can be created:

• Explicit: https://www.postgresql.org/docs/current/explicit-locking.html
• Implicit: Mapping lock to command - https://pglocks.org/

A “lock” or “mutex” (short for “mutual exclusion”) ensures only one client can do something dangerous at a time.

how they conflict (X means they are conflicting):

For example consider the following for a single table:

For example consider the following for a single table:

Statements requesting access for a lock type are put into a queue where they wait in order. If transaction B is waiting to acquire the lock type that transaction A holds, B must wait for A. The pids for the backend processes are logged, including the statement holding a lock type and the statements waiting to acquire it. Some helpful lock parameters to set are log_lock_waits to on and setting a value for deadlock_timeout to gain visibility into locks or blocked queries. When these timeouts cause cancellations, the events and queries will be logged to postgresql.log.

log_lock_waits (boolean) # Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is off. Only superusers and users with the appropriate SET privilege can change this setting.

log_lock_waits = on:

pgBadger organizes lock-related query information from your postgresql.log. The lock information is put into categories like “Most frequent waiting queries” and “Queries that waited the most,” which can help your investigations.Using it is straightforward; just run it and supply it with the path to your postgresql.log file.

1 SELECT
2 pg_stat_activity.pid, 
3 pg_class.relname, 
4 pg_locks.transactionid, 
5 pg_locks.granted , 
6 age(now() ,pg_stat_activity.query_start) AS "age" 
7 FROM pg_stat_activity,pg_locks left 
8 OUTER JOIN pg_class 
9 ON (pg_locks.relation = pg_class.oid) 
10 WHERE pg_stat_activity.query. <> '<insufficient privilege>' 
11 AND pg_locks.pid = pg_stat_activity.pid 
12 AND pg_stat_activity.pid <> pg_backend_pid() 
13 ORDER BY age DESC LIMIT 10; 

• https://shiroyasha.io/selecting-for-share-and-update-in-postgresql.html This statement acquires a ROW SHARE LOCK lock Mode. Taking an exclusive lock on rows means that even reads, like other SELECT statements, are blocked. This is the most restrictive and potentially harmful lock type. Concurrent readers or modification statements will be in a waiting state for the SELECT...FOR UPDATE transaction to COMMIT or ROLLBACK and be blocked until that happens. The referenced rows from other tables are also locked.

Sometimes, applications read data from the database, process the data, and save the result back in the database. This is a classic example where the select for update can provide additional safety.

BEGIN;
SELECT * FROM purchases WHERE processed = false FOR UPDATE;
-- * application is now processing the purchases *

UPDATE purchases SET ...;
COMMIT;

The select ... for update acquires a ROW SHARE LOCK on a table. This lock conflicts with the EXCLUSIVE lock needed for an update statement, and prevents any changes that could happen concurrently.

select ... for update nowait - prevent blocking calls to our database. With NOWAIT keyword, the statement won’t wait if it can’t acquire the lock immediately.

The select ... for update skip locked is a statement that allows you to query rows that have no locks.

process A: SELECT * FROM purchases
process A:   WHERE processed = false FOR UPDATE SKIP LOCKED;
process B: SELECT * FROM purchases
process B:   WHERE created_at < now()::date - interval '1w';
process B:   FOR UPDATE SKIP LOCKED;
-- process A selects and locks all unprocess rows
-- process B selects all non locked purchases older than a week

process A: UPDATE purchases SET ...;
process B: UPDATE purchases SET ...;

Both Process A and Process B can process data concurrently.

A weaker form of select for update is the select for share query. It is an ideal for ensuring referential integrity when creating child records for a parent.

Other processes could delete the user in the moments between selecting the user and inserting the purchase:

process A: BEGIN;
process A: SELECT * FROM users WHERE id = 1 FOR SHARE;
process B: DELETE FROM users WHERE id = 1;
-- process B blocks and must wait for process A to finish

process A: INSERT INTO purchases (id, user_id) VALUES (1, 1);
process A: COMMIT;
-- process B now unblocks and deletes the user

Select for share prevented other processes from deleting the user, but does not prevent concurrent processes from selecting users. This is the major difference between select for share and select for update.

The select for share prevents updates and deletes of rows, but doesn’t prevent other processes from acquiring a select for share. On the other hand, select for updatealso blocks updates and deletes, but it also prevents other processes from acquiring aselect for update`` lock.

There are two more locking clauses in PostgreSQL introduces from version 9.3. The select for no key updates and select for key share.

The select for no key updates behaves similarly to the select for update locking clause but it does not block the select for share. It is ideal if you are performing processing on the rows but don’t want to block the creation of child records.

The select key share is the weakest form of the with lock clause, and behaves similarly to the select for share locking clause. It prevents the deletion of the rows, but unlike select for share it does not prevent updates to the rows that do not modify key values.

• https://xata.io/blog/migrations-and-exclusive-locks
• https://joinhandshake.com/blog/our-team/postgresql-and-lock-queue/

SELECT *, pg_sleep(30) FROM users; -- simulate a long-running query that acquires an ACCESS SHARE lock on the users table
ALTER TABLE users ADD COLUMN AGE integer --  DDL statement blocks while trying to acquire an ACCESS EXCLUSIVE lock on the same table

This ALTER TABLE statement attempts to acquire an ACCESS EXCLUSIVE lock on the users table but is unable to do so until the SELECT statement completes and releases its ACCESS SHARE lock.

The problem is that any other statements that require a lock on the users table are now queued behind this ALTER TABLE statement, including other SELECT statements that only require ACCESS SHARE locks.

This means that the table is effectively blocked for reads and writes until the ALTER TABLE statement completes. SELECTs and UPDATEs will queue up behind it, unable to execute. If there is a long-running query that prevents the ALTER TABLE from acquiring the lock, then reads and writes will be blocked for the duration of that query.

To identify this we can use the pg_blocking_pids function in combination with pg_backend_pid to find the process ID of the blocked processes.

Postgres provides the lock_timeout setting to control how long statements should wait to acquire locks before giving up.

By setting a lock_timeout on the ALTER TABLE statement it's possible to prevent other queries from queueing behind it for an unacceptable length of time

SET lock_timeout TO '1000ms'

ALTER TABLE users ADD COLUMN age INTEGER

DDL statements in migration sessions should always set lock_timeout to an appropriate value for the application; values of less than 2 seconds are common. This ensures that reads and writes won't queue behind a blocked DDL statement and cause application downtime.

-- Session 1 

-- start a transaction    
BEGIN ;   
LOCK  trips  IN  ACCESS EXCLUSIVE  MODE ;

-- session 2
-- set a transaction level lock_timeout    
BEGIN ;   
SET   LOCAL  lock_timeout =  '5s' ;   
-- Run the modification    
-- It should hang since the table is locked for exclusive access    
-- But it should get canceled after 5s    
ALTER   TABLE  trips  ADD   COLUMN  city_id  INTEGER ;    
-- In psql2 notice the statement is canceled    
-- ERROR: canceling statement due to lock timeout
ROLLBACK;

With the timeout in place, the statement will be canceled after waiting the maximum amount of time. This adds a safeguard that prevents your transactions from waiting forever.

• https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

The statement_timeout[116] can be set from the client application to set a maximum allowed time for statements. When the time is reached, the statements are canceled.

Number of locks that are not granted currently

SELECT COUNT(*) FROM pg_locks WHERE NOT granted

if is lot of locks currently being held - identify which connection is causing the problems:

SELECT pid, pg_blocking_pids(pid), wait_event, wait_event_type, query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND wait_event_type ~ 'Lock'

SELECT * FROM pg_locks;
select relation::regclass, * from pg_locks;
SELECT pid,  MODE,  locktype,  relation::regclass,  page,  tuple  FROM pg_locks  WHERE pid in ('508499',  '508335');

select pid, state, usename, query, query_start 
from pg_stat_activity 
where pid in (  select pid from pg_locks l 
  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r'  where t.relname = 'log_operacji');

select nspname, relname, l.* 
from pg_locks l 
    join pg_class c on (relation = c.oid) 
    join pg_namespace nsp on (c.relnamespace = nsp.oid)
where pid in (select pid 
              from pg_stat_activity
              where datname = current_database() 
                and query != current_query());

• https://github.com/postgres/postgres/blob/master/src/backend/storage/lmgr/README Lightweight locks and these are typically used for controlling access to data structures in shared memory. Shared memory is the memory area in Postgres that's shared between different connections.It is helpful to understand which resource in Postgres is busy, and which part the system is doing a lot of work.

LWLock DataRead,BufferContent LWLock - Postgres internal locks

Lock contention occurs when multiple transactions compete for access to the same database resource (like a table or row), and at least one transaction has to wait because it requires a lock that conflicts with locks held by other transactions. In PostgreSQL, this commonly happens during schema modifications (DDL operations) that require exclusive locks, or during heavy concurrent DML operations on the same rows. When contention occurs, transactions form a queue, waiting for their turn to acquire the needed lock. High lock contention can lead to decreased throughput, increased latency, and in severe cases, application timeouts or downtime.

• Use CONCURRENTLY commands Commands like CREATE INDEX CONCURRENTLY or ALTER TABLE DETACH PARTITION CONCURRENTLY acquire less-restrictive locks compared to the same statements without CONCURRENTLY, allowing other operations to proceed. However, these commands:
  • Take longer to complete.
  • Are non-transactional (can't be in transaction block, can’t be rolled back).
  • Require additional care to handle failures, which can leave partial changes (there are commands like FINALIZE to clean up or finish the work).
• Split complex operations

ALTER TABLE mytable ADD COLUMN newcol timestamptz NOT NULL DEFAULT clock_timestamp();

his single command requires an ACCESS EXCLUSIVE lock and will rewrite the entire table. For large tables, this can lead to significant downtime as it:

Blocks all concurrent access (even SELECTs)
Holds the lock for the entire duration of the table rewrite
Can take minutes or hours for large tables

Instead of a single heavy operation we chose above, we can break it into three less-blocking steps:

ALTER TABLE mytable ADD COLUMN newcol timestamptz DEFAULT clock_timestamp();
UPDATE mytable SET newcol = clock_timestamp() WHERE newcol IS NULL;
ALTER TABLE mytable ALTER COLUMN newcol SET NOT NULL;

This approach has several advantages:

• The initial column addition is very quick and requires only a brief ACCESS EXCLUSIVE lock
• The data population can be done with normal ROW EXCLUSIVE locks, allowing concurrent operations
• Each step can be rolled back if something goes wrong It is always a good idea to do the batch updates for large tables to avoid long-running transactions. - pgroll Don't forget to set appropriate lock_timeout values to make sure transactions don't end up waiting forever.

Other way

ALTER TABLE mytable ADD CONSTRAINT mytable_newcol_not_null CHECK (newcol IS NOT NULL) NOT VALID;
ALTER TABLE mytable VALIDATE CONSTRAINT mytable_newcol_not_null;
ALTER TABLE mytable ALTER COLUMN newcol SET NOT NULL; --optional
ALTER TABLE mytable DROP CONSTRAINT mytable_newcol_not_null; --optional

Even better

ALTER TABLE mytable ADD COLUMN newcol int NOT NULL DEFAULT 1;

This command still requires an ACCESS EXCLUSIVE lock and blocks other operations. However, in modern PostgreSQL versions, it executes very quickly because Postgres recognizes that a constant default value (like 1) can be stored as metadata without rewriting the table. The lock duration is minimal, making this operation much less disruptive in production.