설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 55건, 최근 0 건
   

DBA_SQL#6 락 모니터링 쿼리

글쓴이 : power 날짜 : 2014-12-19 (금) 20:26 조회 : 1796
-- DEAD LOCK WAITS
SELECT
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc('second',query_start) AS timestamp) AS query_start,
substr(query,1,25) AS query
FROM 
pg_locks 
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.
relnamespace),
pg_stat_activity
WHERE
NOT pg_locks.pid=pg_backend_pid() AND
pg_locks.pid=pg_stat_activity.pid

-- TRANSACTIN LOCK WAITS
SELECT
locked.pid AS locked_pid,
locker.pid AS locker_pid,
locked_act.usename AS locked_user,
locker_act.usename AS locker_user,
locked.virtualtransaction,
locked.transactionid,
locked.locktype
FROM
pg_locks locked,
pg_locks locker,
pg_stat_activity locked_act,
pg_stat_activity locker_act
WHERE
locker.granted=true AND
locked.granted=false AND
locked.pid=locked_act.pid AND
locker.pid=locker_act.pid AND
(locked.virtualtransaction=locker.virtualtransaction OR
locked.transactionid=locker.transactionid);

   

postgresdba.com