postgres locks pg_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.

• Row-Level Locks
  • SELECT FOR UPDATE : Locks selected rows for updates, preventing other transactions from modifying or locking them until the current transaction completes.
  • SELECT FOR SHARE : Allows other transactions to read the rows but prevents them from acquiring exclusive locks (for example, updates or deletions).
• Table Locks
  • ACCESS EXCLUSIVE : Blocks all other operations, including SELECTs . Used for operations such as DROP TABLE .
  • ACCESS SHARE : Allows other transactions to read the table but blocks schema modifications.
  • SHARE ROW EXCLUSIVE : Used for operations such as CREATE INDEX , blocking other modifications but allowing SELECT queries.

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.

# \d pg_locks
 Vue « pg_catalog.pg_locks » 
Colonne | Type | Collationnement | NULL-able | … 
--------------------+--------------------------+-----------------+-----------+-
locktype | text | | | 
database | oid | | | 
relation | oid | | | 
page | integer | | | 
tuple | smallint | | | 
virtualxid | text | | | 
transactionid | xid | | | 
classid | oid | | | 
objid | oid | | | 
objsubid | smallint | | | 
virtualtransaction | text | | | 
pid | integer | | | 
mode | text | | | 
granted | boolean | | | 
fastpath | boolean | | | 
waitstart | timestamp with time zone | | |

• locktype to typ blokady, najczęściej jest to relationship (tabela lub indeks), transactionid (transakcja), virtualxid (transakcja wirtualna, używana dopóki transakcja nie musiała modyfikować danych, a zatem do przechowywania identyfikatorów transakcji w rekordach);
• database to baza danych, w której ta blokada jest wykonywana;
• relationship to OID relacji docelowej, jeśli locktype to relationship (lub page albo tuple);
• page to numer strony w relacji docelowej (dla blokady page lub tuple);
• tuple to numer rekordu docelowego (gdy blokada tuple);
• virtualxid to numer docelowej transakcji wirtualnej (gdy blokada vir-tualxid);
• transactionid to numer transakcji docelowej;
• classid to numer OID klasy obiektu zablokowanego (innego niż relacja) w pg_class. Wskazuje katalog systemowy, a zatem typ obiektu, którego dotyczy. Używany również do advisory locks ;
• objid to OID obiektu w katalogu systemowym wskazywanym przez classid;
• objsubid odpowiada identyfikatorowi kolumny objid obiektu, którego dotyczy blokada;
• virtualtransaction to numer transakcji wirtualnej, która jest właścicielem blokady (lub próbuje ją uzyskać, jeśli grant ma wartość f);
• pid to PID (identyfikator procesu systemowego) sesji, która jest właścicielem blokady; – mode to żądany poziom blokady;
• grant oznacza, czy blokada została uzyskana, czy nie (a zatem oczekuje);
• fastpath odpowiada informacjom używanym głównie do debugowania (fastpath to mechanizm uzyskiwania najsłabszych blokad);
• ​​waitstart wskazuje, jak długo blokada jest w toku.

Większość blokad ma typ relacyjny, transactionid lub virtualxid. Transakcja początkowa zakłada blokadę virtualxid na swoim własnym virtualxid. Zakłada ona słabe blokady (ACCESS SHARE) na wszystkie wybrane obiekty, aby zapewnić, że ich struktura nie zostanie zmodyfikowana w trakcie trwania transakcji. Gdy tylko konieczna będzie modyfikacja, transakcja zakłada blokadę wyłączną na nowo przypisanym numerze transakcji. Każdy zmodyfikowany obiekt (tabela) zostanie zablokowany blokadą ROW EXCLUSIVE, aby zapobiec niejednoczesnym operacjom CREATE INDEX, a także aby zapobiec ręcznemu zablokowaniu całej tabeli (SHARE ROW EXCLUSIVE).

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();

This 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.

-- Step 1: Add a check constraint (fast with NOT VALID)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint (allows concurrent operations)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: Set NOT NULL (fast since constraint guarantees no nulls)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the redundant check constraint
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

Postgres 18 Update: The upcoming Postgres 18 introduces a simplified form that allows NOT NULL constraints to use the NOT VALID attribute directly:

-- Postgres 18+ - Simplified approach
ALTER TABLE users ALTER COLUMN email SET NOT NULL NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 1: Add foreign key without validation
-- Fast - only updates catalog, doesn't validate existing data
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: Validate existing relationships
-- Can take time but allows concurrent operations
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fk;

Parametr max_locks_per_transaction służy do określania rozmiaru współdzielonej przestrzeni pamięci dla blokad obiektów (zwłaszcza tabel). Liczba blokad wynosi:

max_locks_per_transaction × max_connections

lub raczej, jeśli włączono przygotowane transakcje (i max_prepared_transactions zwiększono powyżej 0):

max_locks_per_transaction × (max_connections + max_prepared_transactions)

Domyślna wartość 64 w zupełności wystarcza w większości przypadków. Zdarzają się jednak sytuacje, gdy konieczne może być jej zamontowanie, na przykład gdy używasz wielu partycji, ale komunikat o błędzie jest oczywisty.

Maksymalna liczba blokad w sesji nie jest ograniczona przez parametr max_locks_per_transaction. Jest to wartość uśredniona. Sesja może uzyskać dowolną liczbę blokad, pod warunkiem że całkowita wewnętrzna tablica skrótów jest wystarczająco duża. Blokady wierszy są przechowywane w wierszach, a zatem ich liczba może być potencjalnie nieskończona.

W przypadku serializacji blokady predykatów mają specyficzne ustawienia. Aby zaoszczędzić pamięć, blokady można grupować według bloku lub relacji (poziom blokady opisano w pg_locks). Odpowiednie ustawienia to:

• max_pred_locks_per_transaction (domyślnie 64);
• max_pred_locks_per_page (domyślnie 2, więc 2 zablokowane wiersze powodują zablokowanie całego bloku, przynajmniej na potrzeby serializacji);
• max_pred_locks_per_relation (szczegóły w dokumentacji).

Jeśli sesja oczekuje na blokadę dłużej niż lock_timeout, żądanie jest anulowane. Często dzieje się tak przed dość inwazyjnym poleceniem, nawet krótkim, w zależności od użycia. Na przykład, należy unikać sytuacji, w której polecenie VACUUM FULL, zablokowane przez dość długą transakcję, samo nie zablokuje wszystkich kolejnych transakcji (zjawisko stosu blokad):

postgres=# SET lock_timeout TO '3s' ;
SET 
postgres=# VACUUM FULL t_grosse_table ;
ERROR: canceling statement due to lock timeout

Oczywiście, później trzeba będzie ponownie spróbować VACUUM FULL, ale produkcja nie jest blokowana na dłużej niż 3 sekundy.

PostgreSQL okresowo sprawdza, czy nie występują impasy między trwającymi transakcjami. Domyślna częstotliwość to 1 s (parametr deadlock_timeout), co w większości przypadków jest wystarczające: impasy zdarzają się dość rzadko, a sprawdzanie jest kosztowne. Jedna z transakcji jest następnie zatrzymywana i wycofywana, aby pozostałe mogły być kontynuowane:

postgres=*# DELETE FROM t_centmille_int WHERE i < 50000;
ERROR: deadlock detected DÉTAIL : Process 453259 waits for ShareLock on transaction 3010357;
blocked by process 453125.
Process 453125 waits for ShareLock on transaction 3010360;
blocked by process 453259.
ASTUCE : See server log for query details.
CONTEXTE : while deleting tuple (0,1) in relation "t_centmille_int" 

• https://docs.postgresql.fr/current/runtime‑config‑locks.html#GUC‑MAX‑PRED‑LOCKS‑PER‑RELATION

Aby śledzić nieco dłuższe oczekiwanie na blokadę, zdecydowanie zaleca się włączenie parametru log_lock_waits (domyślnie jest on wyłączony). Próg jest również ustalany przez parametr deadlock_timeout (domyślnie 1 s). W związku z tym sesja oczekująca na blokadę po upływie tego czasu będzie widoczna w śladach:

LOG: process 457051 still waiting for ShareLock on transaction 35373775 after 1000.121 ms DETAIL: Process holding the lock: 457061. Wait queue: 457051.
CONTEXT: while deleting tuple (221,55) in relation "t_centmille_int" STATEMENT: DELETE FROM t_centmille_int ;

Jeżeli nie jest to impas, transakcja będzie kontynuowana, a moment uzyskania blokady będzie również śledzony:

LOG: process 457051 acquired ShareLock on transaction 35373775 after 18131.402 ms CONTEXT: while deleting tuple (221,55) in relation "t_centmille_int" STATEMENT: DELETE FROM t_centmille_int ;
LOG: duration: 18203.059 ms statement: DELETE FROM t_centmille_int ;