[Postgres] LWLock

C’est quoi ces locks. Quand on tape dans la vue pg_locks on ne les voit pas …

requete à lancer pour détecter les LWLock : 

 SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL; 

détect LWLock  

SELECT pid, pg_blocking_pids(pid), wait_event, wait_event_type, query FROM pg_stat_activity WHERE backend_type = 'client backend' AND wait_event_type ~ 'Lock';

to track the lock set : log_lock_waits to on 

 show log_lock_waits;
 log_lock_waits
—————-
 off
(1 row)

alter system set log_lock_waits=’on’;
ALTER SYSTEM

Créer une vue pour monitorier les locks : 

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);


SELECT * from lock_monitor;