Monitoraggio dei lock e dei conflitti

Immagina di lavorare in un ufficio dove tutte le porte vengono bloccate perché qualcuno si è dimenticato le chiavi dentro una stanza. Ecco, così funzionano i lock in PostgreSQL. Se una query o una transazione blocca una risorsa, tutte le altre operazioni che cercano di accedere alla stessa risorsa devono aspettare che finisca. Questa situazione può causare ritardi, scenari di conflitto e, nei casi peggiori, mandare in tilt il sistema.

Quando si verificano i lock?

I lock in PostgreSQL vengono usati per gestire l’accesso concorrente ai dati. Succedono:

1. Quando fai operazioni di scrittura: UPDATE, DELETE, INSERT.
2. Quando usi transazioni che tengono occupata una risorsa più a lungo del necessario.
3. Quando ci sono conflitti tra transazioni diverse che vogliono la stessa risorsa.

Un database reale è un vero e proprio "campo di battaglia" per le risorse, e anche se pensi che il tuo sistema sia perfetto, basta una transazione sbagliata per "impallare" tutto, proprio come un merge sfortunato su Git.

Strumenti per analizzare i lock: pg_locks

pg_locks è una vista di sistema di PostgreSQL che mostra i lock attuali, sia quelli già presi che quelli in attesa. Risponde alla domanda: "Chi sta tenendo il lock e chi sta aspettando?"

I campi principali di pg_locks:

• locktype: tipo di lock (ad esempio, relation, transaction, page, tuple).
• database: identificatore del database.
• relation: identificatore della tabella (se il lock riguarda una tabella).
• mode: modalità del lock (ad esempio, RowExclusiveLock, AccessShareLock).
• granted: flag che indica se il lock è stato concesso (true) o se la transazione è ancora in attesa (false).

Nota: PostgreSQL usa un "sistema gerarchico di lock". Significa che operazioni diverse possono mettere lock meno restrittivi (tipo AccessShareLock per leggere i dati) o più restrittivi (ExclusiveLock per modificare la struttura della tabella).

Esempio: vedere tutti i lock attuali

SELECT *
FROM pg_locks;

Ma se stampi tutto il contenuto di pg_locks, rischi di vedere solo confusione. Proviamo qualcosa di più utile!

Esempio: lock che non sono ancora stati concessi (cioè le transazioni stanno aspettando)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

Cosa succede qui?

• Filtriamo le righe dove granted = false, cioè il lock non è ancora stato dato.
• relation::regclass converte l’id della tabella nel suo nome, così è più leggibile.

L’output potrebbe essere tipo così:

Queste query ti aiutano a capire quale tabella/risorsa è bloccata e quale transazione potrebbe essere la causa.

Analisi dei conflitti: pg_blocking_pids()

I lock sono solo metà del problema, ma cosa fai se una transazione blocca un’altra? PostgreSQL ti dà un modo comodo per trovare il "colpevole" con la funzione pg_blocking_pids().

La funzione pg_blocking_pids() restituisce una lista di process id (pid) che stanno bloccando l’esecuzione della transazione corrente.

Esempio: trovare le transazioni che bloccano le altre

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Cosa succede qui?

• Usiamo la vista pg_stat_activity per vedere i processi attivi nel sistema.
• La funzione pg_blocking_pids(pid) restituisce la lista dei processi che bloccano ogni pid. Se la lista non è vuota (lunghezza maggiore di 0), vuol dire che il processo è bloccato.

Esempio di risultato:

La transazione con pid = 4567 è bloccata dai processi 1234 e 5678. Abbiamo trovato i nostri "colpevoli".

Terminare i processi che bloccano

Quando hai trovato i processi che bloccano, puoi fermarli con la funzione pg_terminate_backend():

SELECT pg_terminate_backend(1234); -- "Killiamo" il processo 1234

Ma occhio! Terminare forzatamente un processo può causare il rollback dei dati nella transazione corrente. Usa questa "nuke button" solo se proprio non hai alternative.

Applicazione pratica: scenario di analisi dei lock

Immagina di avere un database per un’università con le tabelle students e enrollments. Diverse transazioni cercano di scrivere contemporaneamente nella tabella enrollments e ti ritrovi con dei lock.

1. Identificare i lock:

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. Trovare i processi che bloccano:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Risoluzione dei lock:

Termina forzatamente uno dei processi in conflitto:

SELECT pg_terminate_backend(1234); -- Terminiamo il processo 1234

Nota: Prima di "killare" un processo, cerca di capire perché si è creato il lock. Magari è meglio rivedere la logica delle transazioni.

Errori tipici e come evitarli

I lock spesso nascono da una gestione sbagliata delle transazioni. Per esempio:

Errore: una transazione tiene il lock troppo a lungo senza fare nulla (stato "idle in transaction").

Soluzione: tieni d’occhio lo stato delle transazioni con pg_stat_activity e termina quelle "appese".

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Errore: ti sei dimenticato di usare gli indici nelle query, così si creano lock a livello di tabella intera.

Soluzione: ottimizza le query aggiungendo indici per le condizioni usate spesso.

Tabella "chi sta aspettando chi"

Per rendere la diagnostica più semplice puoi costruire un albero di dipendenze che mostra quale transazione blocca quale:

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Risultato:

Qui si vede che il processo 5678 blocca il processo 1234, che a sua volta blocca 4567.