Crise de blocages et de verrous

Symptôme

Les requêtes restent bloquées, 'lock wait timeout', de nombreuses sessions attendent mais le CPU est faible.

1. Trouvez la tête de la chaîne de blocage (root blocker)

Déterminez qui bloque qui ; le vrai coupable est la session qui n'attend rien elle-même mais fait attendre les autres. Concentrez-vous sur la session au sommet de la chaîne.

Oracle

SELECT blocking_session, sid, serial#, username, seconds_in_wait, sql_id FROM v$session WHERE blocking_session IS NOT NULL ORDER BY seconds_in_wait DESC;

PostgreSQL

SELECT pid, usename, pg_blocking_pids(pid) blocked_by, state, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;

MySQL

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

SQL Server

SELECT session_id, blocking_session_id, wait_type, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id<>0;

MongoDB

db.currentOp({ waitingForLock: true })

2. Comprenez ce que fait le root blocker

S'agit-il d'une transaction ouverte non validée ? L'application l'a-t-elle laissée 'idle in transaction' ? Est-ce un long batch ? Comprenez de quoi il s'agit avant de tuer à l'aveugle : elle est peut-être sur le point de se terminer.

Oracle

SELECT s.sid, s.prev_sql_id, t.start_time, t.used_ublk FROM v$transaction t JOIN v$session s ON t.ses_addr=s.saddr;

PostgreSQL

SELECT pid, state, xact_start, now()-xact_start age, query FROM pg_stat_activity WHERE pid=<root_pid>;

3. Décidez : attendre, résoudre côté application ou tuer

Si elle est sur le point de se terminer, attendez. Si l'application peut valider/annuler, déclenchez-le. Sinon, si l'activité est paralysée, terminez le root blocker. Avant de tuer, évaluez l'impact (durée du rollback, données).

4. Terminez le root blocker si nécessaire

Ne tuez que la session en tête de chaîne ; les sessions en dessous sont libérées automatiquement.

Oracle

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

PostgreSQL

SELECT pg_terminate_backend(<root_pid>);

MySQL

KILL <root_id>;

SQL Server

KILL <root_session_id>;

MongoDB

db.killOp(<opid>)

5. Corrigez la cause profonde

Un index manquant peut provoquer des verrous étendus ; l'application peut garder des transactions ouvertes trop longtemps ; le niveau d'isolation peut être inadapté. Ajoutez des index, raccourcissez les transactions, définissez un timeout 'idle in transaction' et choisissez un niveau d'isolation approprié.