PostgreSQL Locking

Il locking e' fondamentale per garantire la consistenza dei dati. PostgreSQL utilizza diversi livelli di lock su tabelle, righe, pagine, ... che cerchero' di riassumere in modo semplice in questa paginetta.

PostgreSQL Locks

PostgreSQL ha una sofisticata gestione dei lock per garantire la consistenza delle transazioni e per proteggere gli oggetti durante le operazioni di modifica dei dati (DML) e di modifica della struttura dei dati (DDL).
E' importante ricordare che in PostgreSQL non solo le DML (INSERT, UPDATE, DELETE, MERGE) ma anche le DDL (eg. CREATE INDEX, ALTER TABLE) possono far parte di una transazione e quindi venir confermate con un COMMIT o rigettate con un ROLLBACK.

E' possibile ottenere l'elenco dei lock attivi interrogando la vista pg_locks [NdA alcune query utili sui lock PostgreSQL sono riportate in questa paginetta]. La vista pg_locks e' molto semplice e contiene tutti i riferimenti per otterere i dettagli delle sessioni e degli oggetti coinvolti. Per comprendere i contenuti e' pero' necessario conoscere i tipi di lock utilizzati da PostgreSQL.

La documentazione ufficiale riporta gia' tutti i dettagli... quindi ho copiato le tabelle dal sito ufficiale aggiungendo qualcosa per renderle piu' semplici, almeno spero.

Relation Locks

Il primo tipo di lock e' quello a livello di tabella, e' un lock piuttosto grossolano, abbastanza facile da ottenere ed ha molteplici lock mode. I lock vengono liberati al momento del commit o del rollback della transazione.
Dal punto di vista dell'implementazione PostgreSQL i table lock vengono mantenuti in memoria in un area dedicata.

La figura seguente indica quale lock richiedono i vari comandi SQL e quali sono gli eventuali conflitti. Per rendere piu' semplice la lettura sono stati aggiunti gli statement SQL interessati [NdE l'elenco completo e' riportato sulle colonne].
Ad esempio se vi e' un VACUUM in corso su una tabella oppure una REINDEX CONCURRENTLY (colonna SHARE UPDATE EXCLUSIVE) e' possibile effettuare un UPDATE (riga ROW EXCLUSIVE)? Si! Perche' l'intersezione finisce su una casella verde!
Analizzando in dettaglio la figura si puo' notare che e' possibile lanciare una CREATE INDEX anche se ve ne e' una gia' in corso. Mentre e' possibile lanciare solo una CREATE INDEX CONCURRENTLY alla volta. Di converso una CREATE INDEX blocca le UPDATE, mentre una CREATE INDEX CONCURRENTLY consente le UPDATE.

Conflicting Table-Level Locks

Requested Lock Mode Existing Lock Mode
ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE
SQL SELECT SELECT FOR UPDATE UPDATE, DELETE, INSERT, MERGE VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, REINDEX CONCURRENTLY, ALTER TABLE (some), ALTER INDEX (some) CREATE INDEX CREATE TRIGGER, ALTER TABLE (some) REFRESH MATERIALIZED VIEW CONCURRENTLY DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW, ALTER TABLE (most), ALTER INDEX (most), LOCK TABLE, DROP INDEX
ACCESS SHARE SELECT               X
ROW SHARE SELECT FOR UPDATE             X X
ROW EXCLUSIVE UPDATE, ...         X X X X
SHARE UPDATE EXCLUSIVE VACUUM, ...       X X X X X
SHARE CREATE INDEX     X X   X X X
SHARE ROW EXCLUSIVE CREATE TRIGGER, ...     X X X X X X
EXCLUSIVE REFRESH MAT. CONC.   X X X X X X X
ACCESS EXCLUSIVE DROP TABLE, ... X X X X X X X X

In generale uno statement ALTER TABLE acquisisce un pesante ACCESS EXCLUSIVE lock escluse le seguenti eccezioni:

Anche uno statement ALTER INDEX acquisisce un ACCESS EXCLUSIVE lock eccetto con:

Nel caso di tabelle partizionate vi sono casi ulteriori (eg. ALTER TABLE... ATTACH PARTITION richiede un SHARE UPDATE EXCLUSIVE sulla tabella partizionata ed un ACCESS EXCLUSIVE sulla partizione) ma tra indici, costraint, subpartition, ... le combinazioni sono troppe per una paginetta semplice come questa e rimandiamo alla documentazione ufficiale!

Tuple Locks

I lock a livello di riga riguardano solo le attivita' di scrittura e non le letture: due transazioni diverse non possono mantenere un lock sulla stessa riga contemporaneamente. Al contrario le attivita' in sola lettura non vengono mai messe in attesa da un lock di riga, anche se e' possibile che vedano righe diverse per l'MVCC (MultiVersion Concurrency Control).
Anche i lock a livello di riga vengono liberati al momento del commit o del rollback della transazione.

Dal punto di vista dell'implementazione PostgreSQL un lock a livello di riga viene scritto sulla tupla stessa [NdA campo T_XMAX], quindi non richiede memoria e non c'e' un limite massimo al numero di lock di riga. Per altro sulle UPDATE e sulle DELETE questo non comporta un overhead perche' comunque la riga viene modificata dalle istruzioni stesse direttamente nel blocco in memoria. Una SELECT FOR UPDATE opera nello stesso modo quindi fa segnare come dirty i blocchi interessati richiedendo scritture su disco (asincrone). In PostgreSQL non e' possibile ottenere l'elenco di tutti row-level lock attivi [NdA in realta' e' possibile con l'extension pgrowlocks che pero' presenta alcuni limiti].

Eventuali lock in attesa sono comunque visibili nella vista pg_locks con tutti i dettagli necessari per risalire alle sessioni ed agli oggetti coinvolti.

La figura seguente indica quale lock richiedono i vari comandi SQL e quali sono i conflitti.
In generale non e' mai possibile che due transazioni modifichino la stessa riga, il lock impedira' la modifica fino a che non verra' rilasciato da un COMMIT o da un ROLLBACK. Si possono pero' notare che sono disponibili diverse clausole di SELECT che possono condividere i lock a livello di riga.

Conflicting Row-Level Locks

Requested Lock Mode Current Lock Mode
FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE FOR UPDATE
SQL SELECT KEY SHARE SELECT FOR SHARE UPDATE(NO KEY), SELECT FOR NO KEY UPDATE DELETE, UPDATE(KEY), SELECT FOR UPDATE
FOR KEY SHARE SELECT FOR KEY SHARE       X
FOR SHARE SELECT FOR SHARE     X X
FOR NO KEY UPDATE UPDATE(NO KEY), ...   X X X
FOR UPDATE DELETE, ... X X X X

Lock in SELECT

Un normale statement di SELECT non viene mai posto in attesa su un lock di riga. Una SELECT puo' venire sospesa solo dai lock di tabella piu' pesanti (ACCESS ESCLUSIVE: ad esempio per una DROP TABLE).

Nel caso in cui venga utilizzata la clausola SELECT... FOR UPDATE pero' la select si comporta in pratica come un'UPDATE richiedendo i lock di riga sulle righe interessate.
Come abbiamo visto nella figura precedente in PostgreSQL sono disponibili ulteriori forme della clausola FOR UPDATE: FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE con livelli di locking di riga differenti. Queste sintassi alternative utilizzate solo in casi particolari.

Una possibilita' invece molto utile della clausola FOR UPDATE e' SKIP LOCKED che consente di selezionare solo le righe che non hanno lock attivi [NdA disponibile dalla versione PG 9.5].

Lock in INSERT

Una INSERT non attende mai per un lock a livello di riga... perche' e' la INSERT stessa ad inserire la riga e nessuna altra transazione potra' vederla fino a che non viene eseguito il COMMIT.

Tuttavia vi sono dei casi in cui anche le INSERT possono essere in attesa!
Gli indici UNIQUE impediscono di inserire righe con chiavi duplicate. Una INSERT con valori di chiave duplicata riceve immediatamente un errore. Il lock si presenta se e' gia' attiva una transazione che ha eseguito una INSERT ma non ha ancora eseguito il COMMIT, una seconda INSERT eseguita sulla stessa chiave deve aspettare il termine della prima transazione in modo da sapere se deve restituire l'errore di chiave duplicata oppure effettuare l'inserimento.

Una nota curiosa: gli indici univoci in PostgreSQL... non sono univoci! Dal punto di vista fisico quando una riga viene modificata da un'UPDATE viene creata una nuova tupla risultano due tuple con differenti valori nei campi T_XMIN e T_XMAX per fare in modo che le transazioni precedenti possano continuare a "vedere" la riga precedente. La vecchia e la nuova tupla possono essere ospitate nello stesso blocco [NdA come avviene in caso di un Hot-Update] oppure in blocchi diversi e convivono per un certo tempo fino a che il VACUUM non provvedera' a cancellare la dead tuple; in questo periodo di tempo vi saranno nell'indice univoco due entry diverse per la stessa chiave.

Monitoring Locks

Come si controllano i lock?
Con la vista pg_locks!

La vista pg_locks contiene tutti i dettagli dei lock che abbiamo descritto (ed anche di quelli che per semplicita' non abbiamo descritto). La colonna locktype riporta il tipo di lock: per i Table-Level locks il contenuto sara' relation mentre per l'attesa della transazione per il controllo dell'univocita' di un indice il contenuto sara' transationid. La colonna mode riporta la modalita': AccessShareLock per una SELECT, RowExclusiveLock per INSERT/UPDATE/DELETE/MERGE, ... Importantissima e' la colonna granted che vale false per i lock che sono in attesa. Infine e' utile la colonna waitstart [NdA disponibile dalla versione 14] che indica da quanto tempo il lock e' in attesa.

Ma non e' tutto... in realta' la vista pg_locks controlla solo i Table-Level lock ed un lock di riga non sara' visibile a meno ch generino un'attesa. L'estensione pgrowlocks consente di analizzare anche i Row-Level. Generalmente non e' necessario spingersi a questo livello ma e' comunque possibile con Postgres raggiungere il dettaglio di ogni singolo lock.

Le query piu' utili per controllare i lock si trovano in questa paginetta.

Deadlock

Si possono verificare catene di lock per cui una transazione non puo' terminare perche' e' in attesa che venga liberato un lock e nello stesso tempo blocca, direttamente o meno, la transazione che dovrebbe liberarlo. Questa e' una situazione di deadlock perche' nessuna transazione e' in grado di terminare; senza un intervento esterno tutte le transazioni sono bloccate perche' ciascuna attende che un'altra liberi un lock.

In questi casi PostgreSQL termina una delle transazioni coinvolte risolvendo automaticamente il deadlock.

Dal punto di vista applicativo e' opportuno evitare che si verifichino deadlock effettuando in modo ordinato le richieste di lock.
Ad esempio le le transazioni debbono richiedere due lock: un lock sulla tabella A ed un lock sulla tabella B, e' opportuno che entrambe richiedano prima il lock su una tabella e poi sull'altra; in questo modo gli eventuali lock vengono risolti con semplici attese senza generare deadlock.

Lock Sizing

PostgreSQL utilizza un area di memoria per mantenere i Table-Level lock. L'area e' dimensionata basandosi sui parametri max_connections (default: 100), max_locks_per_transaction (default: 64) e max_pred_locks_per_transaction (default: 0) che porta ad un totale massimo di 6.400 lock [NdA ma spesso max_connections viene configurato con valori piu' alti]. Il limite non e' per transazione (nonostante i nome dei parametri) ma e' totale per tutte le sessioni attive. Si tratta di un numero elevato anche perche' non debbono essere conteggiati i Row-Level lock che non vengono mantenuti in memoria ma direttamente sulla tupla nella heap (e quindi i lock a livello di riga non hanno limiti in PostgreSQL).

Nella pratica e' difficile superare tale limite, ma nel caso in cui avvenga l'errore e' il seguente:

ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction.

Quando si opera su tabelle con un gran numero di partizioni per PostgreSQL ciascuna partizione e' una tabella a tutti gli effetti ed i Table-Level lock vengono richiesti per ciascuna partizione interessata: in questi casi e' possibile raggiungere il limite dei lock e ricevere l'errore. Effettuare un pruning corretto nelle condizioni di solito risolve il problema. Un altro caso d'errore frequente e' l'uso del comando pg_dump su database con un numero molto elevato di oggetti.
Se la ragione non e' dovuta ad un over-partitioning o ad un mancato pruning si puo' aumentare il valore del parametro max_locks_per_transaction.

Parametri

Da ultimo l'elenco dei parametri di tuning di interesse per i lock: 

              name              |  context   | setting | unit |   min_val   |  max_val   | source  |                    category                     
--------------------------------+------------+---------+------+-------------+------------+---------+-------------------------------------------------
 lock_timeout                   | user       | 0       | ms   | 0           | 2147483647 | default | Client Connection Defaults / Statement Behavior
 deadlock_timeout               | superuser  | 1000    | ms   | 1           | 2147483647 | default | Lock Management
 max_locks_per_transaction      | postmaster | 64      |      | 10          | 2147483647 | default | Lock Management
 max_pred_locks_per_page        | sighup     | 2       |      | 0           | 2147483647 | default | Lock Management
 max_pred_locks_per_relation    | sighup     | -2      |      | -2147483648 | 2147483647 | default | Lock Management
 max_pred_locks_per_transaction | postmaster | 64      |      | 10          | 2147483647 | default | Lock Management
 log_lock_waits                 | superuser  | off     |      |             |            | default | Reporting and Logging / What to Log

Le impostazioni di default sono adeguate per la maggioranza dei casi.
A volte e' necessario aumentare il valore del parametro max_locks_per_transaction, la modifica richiede il riavvio dell'istanza. Utile e' anche l'impostazione ad on del parametro log_lock_waits che non richiede alcun riavvio ma che puo' essere eseguita solo dall'amministratore.

Varie ed eventuali

In questa semplice paginetta non abbiamo descritto i page-level locks e gli advisory locks perche' generalmente meno interessanti per le applicationi.

Il comando di backup pg_dump utilizza un lock ACCESS SHARE (lo stesso lock utilizzato dalla SELECT) su tutti gli oggetti su cui deve eseguire il backup.

Come sempre la documentazione ufficiale e' la fonte piu' completa ed affidabile delle informazioni.

Titolo: PostgreSQL Locking
Livello: Avanzato (3/5)
Data: 1 Aprile 2024
Versione: 1.0.1 - 31 Ottobre 2024
Autore: mail [AT] meo.bogliolo.name