Come Identificare e Risolvere i Deadlock e la Contesa di Lock in PostgreSQL

I deadlock e la contesa di lock in PostgreSQL di solito si manifestano come un'applicazione che improvvisamente sembra bloccata. Le richieste si accumulano, i worker rimangono in stato active o idle in transaction, e il database stesso potrebbe ancora avere molta CPU disponibile. Il problema non è la capacità grezza. Una sessione sta aspettando che un'altra sessione rilasci un lock, e a volte si forma un'intera coda dietro di essa.

Il modo più rapido per gestire la situazione è separare due casi. La contesa di lock significa che una sessione è in attesa, ma potrebbe eventualmente proseguire. Un deadlock significa che due o più sessioni si aspettano a vicenda in un ciclo, quindi PostgreSQL deve cancellare una transazione. Si esegue il debug di entrambi con gli stessi strumenti di base, ma la soluzione è spesso diversa.

Basi dei Lock in PostgreSQL

PostgreSQL utilizza i lock per proteggere tabelle, righe, transazioni e altri oggetti interni mentre molte sessioni lavorano contemporaneamente. Utilizza anche MVCC, quindi i normali lettori e scrittori spesso non si bloccano a vicenda. Questo è il motivo per cui PostgreSQL gestisce bene l'alta concorrenza, ma è anche il motivo per cui i problemi di lock possono essere confusi: il problema è di solito uno schema di istruzioni specifico, non "troppi utenti" in generale.

Tipi di Lock

PostgreSQL utilizza vari livelli di lock, ognuno dei quali offre un diverso grado di protezione. Comprendere questi livelli è fondamentale per diagnosticare i problemi:

AccessShareLock: Utilizzato dalle normali SELECT. È in conflitto principalmente con ACCESS EXCLUSIVE, motivo per cui molte letture possono essere eseguite mentre sono in corso scritture.
RowExclusiveLock: Comune per INSERT, UPDATE e DELETE su una tabella. Il nome è facile da fraintendere; non significa che ogni riga della tabella sia bloccata esclusivamente.
ShareUpdateExclusiveLock: Utilizzato da operazioni come VACUUM senza FULL, ANALYZE e alcune operazioni sugli indici. Consente letture e scritture normali ma è in conflitto con diverse operazioni di manutenzione.
ShareLock / ShareRowExclusiveLock / ExclusiveLock: Modalità a livello di tabella più forti utilizzate da specifiche operazioni DDL e relative ai vincoli.
AccessExclusiveLock: Il lock di tabella più restrittivo. ALTER TABLE, DROP TABLE, TRUNCATE e VACUUM FULL possono acquisire questo tipo di lock. Blocca sia le letture che le scritture normali.

I lock a livello di riga sono separati dalle modalità di lock a livello di tabella. Un UPDATE acquisisce un RowExclusiveLock a livello di tabella e lock di riga sulle righe che modifica. Quando si dice "questa riga è bloccata", di solito si intende che un'altra transazione ha modificato o selezionato quella riga con FOR UPDATE e non ha ancora eseguito il commit.

Modalità di Lock

Le modalità di lock indicano il tipo di accesso richiesto da una transazione. Sono spesso rappresentate da nomi come RowExclusiveLock, ShareLock, ExclusiveLock, ecc.

Identificare la Contesa di Lock e le Sessioni Bloccanti

La contesa di lock si verifica quando più transazioni sono in attesa di lock detenuti da altre transazioni. Questo può rallentare significativamente la tua applicazione. La vista di sistema pg_locks è il tuo strumento principale per diagnosticare questi problemi.

Utilizzo di `pg_locks`

La vista pg_locks fornisce informazioni su tutti i lock attivi nel sistema di database. È preziosa per capire quali sessioni detengono lock e quali sono in attesa.

Ecco una query comune per identificare le sessioni bloccanti:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
FROM
    pg_catalog.pg_locks blocked_locks
JOIN
    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks blocking_locks
ON
    blocking_locks.locktype = blocked_locks.locktype AND
    blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND
    blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
    blocking_locks.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

Spiegazione della query:

Uniamo pg_locks con pg_stat_activity due volte: una per il processo bloccato e una per il processo bloccante.
La clausola WHERE NOT blocked_locks.granted filtra per i lock che sono attualmente in attesa.
La condizione blocking_locks.pid != blocked_locks.pid garantisce che non segnaliamo una sessione che blocca se stessa.
Le condizioni di join su pg_locks abbinano i lock sulla stessa risorsa.

Interpretare l'Output

blocked_pid / blocking_pid: I PID (ID di processo) delle sessioni coinvolte.
blocked_user / blocking_user: Gli utenti associati a questi PID.
blocked_statement / blocking_statement: Le query SQL attualmente in esecuzione o in attesa.
blocked_mode / blocking_mode: Le modalità di lock richieste e detenute.

Se questa query restituisce righe, hai una contesa di lock. Il blocking_pid detiene un lock che il blocked_pid sta aspettando.

Comprendere e Risolvere i Deadlock

Un deadlock si verifica quando due o più transazioni sono ciascuna in attesa di un lock detenuto da un'altra transazione nel ciclo, creando una dipendenza circolare che nessuna può risolvere da sola. PostgreSQL rileva i deadlock e li risolve automaticamente interrompendo una delle transazioni, tipicamente quella che sta causando il deadlock e ha fatto meno lavoro.

Scenari Comuni di Deadlock

Due transazioni che aggiornano righe diverse in tabelle diverse in ordine inverso:
- Transazione A: Aggiorna la riga X nella Tabella 1, poi tenta di aggiornare la riga Y nella Tabella 2.
- Transazione B: Aggiorna la riga Y nella Tabella 2, poi tenta di aggiornare la riga X nella Tabella 1. Se la Transazione A blocca la riga X e la Transazione B blocca la riga Y, si bloccheranno a vicenda quando tentano di acquisire il lock detenuto dall'altra.
UPDATE seguito da SELECT ... FOR UPDATE:
- Transazione A: Aggiorna una riga.
- Transazione B: Esegue SELECT ... FOR UPDATE sulla stessa riga. Se l'UPDATE detiene ancora un lock esclusivo di riga quando la SELECT FOR UPDATE tenta di acquisire un lock condiviso, e esistono altre dipendenze, può verificarsi un deadlock.

Rilevare i Deadlock

PostgreSQL registra le informazioni sui deadlock nel suo log del server. Vedrai tipicamente messaggi come:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL sceglie automaticamente una transazione da interrompere in modo che le altre possano continuare. Non fare affidamento su pg_stat_activity dopo il fatto per la storia completa; quando guardi, l'istruzione cancellata potrebbe essere sparita. Il log del server è di solito la migliore registrazione del ciclo.

Risolvere i Deadlock

Quando viene rilevato un deadlock e PostgreSQL lo risolve interrompendo una transazione:

Identifica la Vittima: Controlla i log di PostgreSQL per il messaggio deadlock detected. Specificherà quale processo è stato interrotto.
Riprova la Transazione Interrotta: L'applicazione che riceve SQLSTATE 40P01 (deadlock_detected) dovrebbe riprovare l'intera transazione quando l'operazione è sicura da riprovare. Riprovare solo l'ultima istruzione può lasciare lo stato dell'applicazione incoerente.
Analizza la Causa: La chiave per la risoluzione è prevenire futuri deadlock. Questo implica capire perché si è verificato il deadlock (come descritto negli scenari comuni) e regolare la logica dell'applicazione o il design del database.

Tecniche per Prevenire la Contesa di Lock e i Deadlock

Prevenire è sempre meglio che curare. Implementare strategie per minimizzare la contesa di lock ed evitare situazioni di deadlock è cruciale per un database PostgreSQL ad alte prestazioni.

1. Ordinamento Coerente delle Transazioni

Regola: Accedi e modifica sempre le risorse (tabelle, righe) nello stesso ordine in tutte le transazioni. Se più transazioni devono aggiornare TabellaA e TabellaB, assicurati che aggiornino sempre prima TabellaA e poi TabellaB, o viceversa, in modo coerente.
Esempio: Se una transazione deve aggiornare record in utenti e ordini, esegui sempre prima le operazioni su utenti, poi su ordini. Evita scenari in cui una transazione aggiorna utenti poi ordini, mentre un'altra aggiorna ordini poi utenti.

2. Minimizzare la Durata delle Transazioni

Regola: Mantieni le transazioni il più brevi possibile. Più a lungo una transazione è aperta, più lock detiene, aumentando la probabilità di contesa.
Azione: Esegui solo le operazioni necessarie sul database all'interno di una transazione. Sposta il lavoro non correlato al database (ad esempio, chiamate API esterne, calcoli complessi non dipendenti dallo stato della transazione) al di fuori del confine della transazione.

3. Utilizzare Livelli di Isolamento Appropriati

Regola: Comprendi e scegli il livello di isolamento delle transazioni corretto. PostgreSQL offre:
- READ UNCOMMITTED (simulato da READ COMMITTED in PostgreSQL)
- READ COMMITTED (predefinito)
- REPEATABLE READ
- SERIALIZABLE
Azione: Il READ COMMITTED predefinito offre buone prestazioni prevenendo le letture sporche. REPEATABLE READ e SERIALIZABLE offrono una maggiore consistenza ma possono portare a più errori serialization_failure (che sono essenzialmente deadlock per l'isolamento dello snapshot) e potenzialmente a più contesa di lock. Usali solo quando assolutamente necessario.

4. Ottimizzare Query e Indici

Regola: Le query lente trattengono i lock più a lungo. Assicurati che le tue query siano efficienti e ben indicizzate.
Azione: Usa EXPLAIN ANALYZE per identificare le query lente. Aggiungi indici appropriati per velocizzare il recupero dei dati, specialmente per le clausole WHERE e le condizioni JOIN.

5. Usare `SELECT ... FOR UPDATE` con Parsimonia

Regola: SELECT ... FOR UPDATE blocca le righe per la durata della transazione. È potente per prevenire race condition, ma può anche essere una fonte importante di contesa.
Azione: Usalo solo quando hai veramente bisogno di bloccare le righe per impedire che vengano modificate da altre transazioni prima che la tua transazione completi il suo lavoro. Considera se i lock consultivi potrebbero essere più adatti per determinati scenari.

6. Lock Consultivi (Advisory Locks)

Regola: Per il locking a livello di applicazione o esigenze di sincronizzazione più complesse che non corrispondono direttamente ai lock degli oggetti del database, i lock consultivi di PostgreSQL possono essere uno strumento potente.
Azione: Usa funzioni come pg_advisory_lock(), pg_advisory_lock_shared() e pg_advisory_unlock() per implementare meccanismi di locking personalizzati. Questi lock non vengono rilevati automaticamente dal meccanismo di rilevamento dei deadlock, quindi la logica dell'applicazione deve gestirli con attenzione.

7. Operazioni in Batch

Regola: Invece di emettere molte singole istruzioni UPDATE o DELETE, considera di raggrupparle in una singola istruzione o di usare COPY per il caricamento/aggiornamento bulk dove possibile.
Azione: Una singola istruzione UPDATE potrebbe acquisire i lock in modo più efficiente di un ciclo di singoli UPDATE. Analizza il comportamento di locking delle tue operazioni batch.

Un Flusso di Triage Pratico

Quando un incidente è attivo, inizia con le sessioni in attesa, non con la teoria più vecchia nella tua testa:

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

Se un PID bloccante appare ripetutamente, ispezionalo:

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

La frase da tenere d'occhio è idle in transaction. Quella sessione non sta attivamente svolgendo lavoro utile sul database, ma potrebbe ancora detenere lock. Questo spesso deriva da codice applicativo che apre una transazione, esegue una query, chiama un'API esterna e fa il commit solo dopo che l'API restituisce il risultato. Sposta la chiamata esterna al di fuori della transazione se possibile.

Cancella con cautela. SELECT pg_cancel_backend(pid) chiede alla query corrente di fermarsi. SELECT pg_terminate_backend(pid) uccide la sessione e annulla la sua transazione aperta. In un incidente di produzione, terminare il bloccante potrebbe essere la scelta giusta, ma cattura prima la query e l'età della transazione in modo da poter correggere il percorso del codice in seguito.

Un'altra abitudine aiuta dopo l'incidente: salva la query bloccante, la query bloccata e i confini della transazione dai log dell'applicazione. La sola istruzione SQL spesso non è sufficiente. Un innocuo UPDATE users SET last_seen_at = now() può diventare un bloccante se si trova all'interno di una transazione che aspetta anche un'API di pagamento. Le correzioni dei deadlock di solito vivono a livello del flusso della transazione, non all'interno di un'istruzione isolata.

Le correzioni durature sono di solito semplici: mantieni le transazioni brevi, tocca le risorse condivise in un ordine coerente, aggiungi indici che impediscano agli aggiornamenti di scansionare troppo e fai sì che i tentativi dell'applicazione gestiscano correttamente 40P01 e gli errori di serializzazione. PostgreSQL può rilevare un deadlock, ma non può riprogettare per te lo schema della transazione. Quella parte spetta all'applicazione e alla revisione dello schema dopo che l'incidente si è calmato.