!!!! Problématique
C’est quelque chose qu’on rencontre régulièrement en production: une session idle in transaction qui a un verrou sur quelque chose, qui bloque une autre sesssion qui veut acquérir un verrou dessus. Cette autre session a elle même un verrou sur un autre objet, qui bloque une troisième session, et ainsi de suite.
À résoudre à la main, c’est toujours pénible. Pas compliqué, mais ça prend du temps, dans un contexte où on a souvent quelques utilisateurs bloqués qui vous râlent dans les oreilles, voire un chef qui vous souffle dans le cou, pour les plus malchanceux.
!!!! Solution
On doit donc pouvoir résoudre ça avec une requête qui, pour chaque session bloquée, remonte les verrous jusqu’à la source du problème. –En espérant ne pas m’être trompé, voici une requête qui fait exactement ça (testée sur un cas raisonnablement simple en 9.2):– En m’étant bien trompé, voici une version améliorée (j’espère que cette fois-ci c’est bon, j’ai eu un cas réel pour la tester).
///
with recursive conflicting_locks(lock,conflicts) as
(
values ('AccessShareLock','{"AccessExclusiveLock"}'::text[]),
('RowShareLock','{"AccessExclusiveLock","ExclusiveLock"}'),
('RowExclusiveLock','{"AccessExclusiveLock","ExclusiveLock","ShareRowExclusiveLock","ShareLock"}'),
('ShareUpdateExclusiveLock','{"AccessExclusiveLock","ExclusiveLock","ShareRowExclusiveLock","ShareLock","ShareUpdateExclusiveLock"}'),
('ShareLock','{"AccessExclusiveLock","ExclusiveLock","ShareRowExclusiveLock","ShareUpdateExclusiveLock","RowExclusiveLock"}'),
('ShareRowExclusiveLock','{"AccessExclusiveLock","ExclusiveLock","ShareRowExclusiveLock","ShareLock","ShareUpdateExclusiveLock","RowExclusiveLock"}'),
('ExclusiveLock','{"AccessExclusiveLock","ExclusiveLock","ShareRowExclusiveLock","ShareLock","ShareUpdateExclusiveLock","RowExclusiveLock","RowShareLock"}'),
('AccessExclusiveLock','{"AccessExclusiveLock","ExclusiveLock","ShareRowExclusiveLock","ShareLock","ShareUpdateExclusiveLock","RowExclusiveLock","RowShareLock","AccessShareLock"}')
),
tmplocks(pid,lockingpid,lock,granted) as
(
select distinct l.pid, rl.pid as lockingpid, coalesce(l.relation::text,l.virtualxid::text,l.transactionid::text) as lock ,rl.granted
from pg_locks rl
join pg_locks l
on (coalesce(rl.relation::text,rl.virtualxid::text,rl.transactionid::text)=coalesce(l.relation::text,l.virtualxid::text,l.transactionid::text)
and rl.pid<>l.pid)
where l.granted
and not rl.granted
and l.locktype <> ‘tuple’ and rl.locktype <> ‘tuple’
and exists (SELECT 1 FROM conflicting_locks WHERE conflicting_locks.lock=l.mode AND rl.mode=ANY(conflicting_locks.conflicts))
),
locks (pid,lockingpid,tree) as
(
select pid,lockingpid,'{}'::int[]||pid from tmplocks where not granted
UNION ALL
select tmplocks.pid,tmplocks.lockingpid,tree || tmplocks.pid from tmplocks join locks on (tmplocks.pid=locks.lockingpid)
)
| select tree |
|
lockingpid as wholockswho from locks limit 1000000 |
///
Pour les curieux, voila comment elle fonctionne:
La première CTE, conflicting_locks, définit quel lock est en conflit avec un tableau des autres
La deuxième CTE, tmplocks, retourne tous les verrous non accordés (not granted), le pid de celui qui est bloqué, le pid de celui qui possède le verrou, et le verrou. On utilise conflicting_locks pour vérifier que les verrous sont en conflits, et on ignore les verrous de type type (ils sont temporaires, et ne devraient pas engendrer de conflit).
La troisième fait la récursion: pour chaque session verrouillée, on détermine qui la verrouille. Si celle qui verrouille est elle même verrouillée, on continue la récursion. On stocke dans «tree» les pid de la récursion.
Le select final rajoute à tree le pid final (qui n’a pas été stocké durant la récursion, puisque lui n’est pas bloqué).
Voila le résultat que j’obtiens dans mon environnement de test:
///
—————+————
///
On voit que le pid 23390 en gène un bon paquet d’autres. C’est un bon candidat à l’extermination (c’est une session IDLE in transaction…)
Si vous voyez des cas que j’ai raté, ou une amélioration de cette requête, n’hésitez pas à poster en-dessous…