■ 잠금 프로세스 조회하기

------------------------------------------------------------------------------------------------------------------------

 

SELECT

    BLOCKED_LOCKS.PID                  AS BLOCKED_PID

   ,BLOCKED_ACTIVITY.USENAME           AS BLOCKED_USER

   ,BLOCKING_LOCKS.PID                 AS BLOCKING_PID

   ,BLOCKING_ACTIVITY.USENAME          AS BLOCKING_USER

   ,BLOCKED_ACTIVITY.QUERY             AS BLOCKED_STATEMENT

   ,BLOCKING_ACTIVITY.QUERY            AS CURRENT_STATEMENT_IN_BLOCKING_PROCESS

   ,BLOCKED_ACTIVITY.APPLICATION_NAME  AS BLOCKED_APPLICATION

   ,BLOCKING_ACTIVITY.APPLICATION_NAME AS BLOCKING_APPLICATION

FROM PG_CATALOG.PG_LOCKS         BLOCKED_LOCKS

JOIN PG_CATALOG.PG_STAT_ACTIVITY BLOCKED_ACTIVITY  ON  BLOCKED_ACTIVITY.PID    = BLOCKED_LOCKS.PID

JOIN PG_CATALOG.PG_LOCKS         BLOCKING_LOCKS    ON  BLOCKING_LOCKS.LOCKTYPE = BLOCKED_LOCKS.LOCKTYPE

                                                   AND BLOCKING_LOCKS.DATABASE      IS NOT DISTINCT FROM BLOCKED_LOCKS.DATABASE

                                                   AND BLOCKING_LOCKS.RELATION      IS NOT DISTINCT FROM BLOCKED_LOCKS.RELATION

                                                   AND BLOCKING_LOCKS.PAGE          IS NOT DISTINCT FROM BLOCKED_LOCKS.PAGE

                                                   AND BLOCKING_LOCKS.TUPLE         IS NOT DISTINCT FROM BLOCKED_LOCKS.TUPLE

                                                   AND BLOCKING_LOCKS.VIRTUALXID    IS NOT DISTINCT FROM BLOCKED_LOCKS.VIRTUALXID

                                                   AND BLOCKING_LOCKS.TRANSACTIONID IS NOT DISTINCT FROM BLOCKED_LOCKS.TRANSACTIONID

                                                   AND BLOCKING_LOCKS.CLASSID       IS NOT DISTINCT FROM BLOCKED_LOCKS.CLASSID

                                                   AND BLOCKING_LOCKS.OBJID         IS NOT DISTINCT FROM BLOCKED_LOCKS.OBJID

                                                   AND BLOCKING_LOCKS.OBJSUBID      IS NOT DISTINCT FROM BLOCKED_LOCKS.OBJSUBID

                                                   AND BLOCKING_LOCKS.PID    != BLOCKED_LOCKS.PID

JOIN PG_CATALOG.PG_STAT_ACTIVITY BLOCKING_ACTIVITY ON  BLOCKING_ACTIVITY.PID =  BLOCKING_LOCKS.PID

WHERE NOT BLOCKED_LOCKS.GRANTED;

 

------------------------------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker
TAG

댓글을 달아 주세요