첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
유용한 소스 코드가 있으면 icodebroker@naver.com으로 보내주시면 감사합니다.
블로그 자료는 자유롭게 사용하세요.

■ 재귀적으로 잠금 프로세스 조회하기

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

 

WITH RECURSIVE

    C(REQUESTED, CURRENT) AS

    (

        VALUES

        ('AccessShareLock'::TEXT         , 'AccessExclusiveLock'::TEXT     ),

        ('RowShareLock'::TEXT            , 'ExclusiveLock'::TEXT           ),

        ('RowShareLock'::TEXT            , 'AccessExclusiveLock'::TEXT     ),

        ('RowExclusiveLock'::TEXT        , 'ShareLock'::TEXT               ),

        ('RowExclusiveLock'::TEXT        , 'ShareRowExclusiveLock'::TEXT   ),

        ('RowExclusiveLock'::TEXT        , 'ExclusiveLock'::TEXT           ),

        ('RowExclusiveLock'::TEXT        , 'AccessExclusiveLock'::TEXT     ),

        ('ShareUpdateExclusiveLock'::TEXT, 'ShareUpdateExclusiveLock'::TEXT),

        ('ShareUpdateExclusiveLock'::TEXT, 'ShareLock'::TEXT               ),

        ('ShareUpdateExclusiveLock'::TEXT, 'ShareRowExclusiveLock'::TEXT   ),

        ('ShareUpdateExclusiveLock'::TEXT, 'ExclusiveLock'::TEXT           ),

        ('ShareUpdateExclusiveLock'::TEXT, 'AccessExclusiveLock'::TEXT     ),

        ('ShareLock'::TEXT               , 'RowExclusiveLock'::TEXT        ),

        ('ShareLock'::TEXT               , 'ShareUpdateExclusiveLock'::TEXT),

        ('ShareLock'::TEXT               , 'ShareRowExclusiveLock'::TEXT   ),

        ('ShareLock'::TEXT               , 'ExclusiveLock'::TEXT           ),

        ('ShareLock'::TEXT               , 'AccessExclusiveLock'::TEXT     ),

        ('ShareRowExclusiveLock'::TEXT   , 'RowExclusiveLock'::TEXT        ),

        ('ShareRowExclusiveLock'::TEXT   , 'ShareUpdateExclusiveLock'::TEXT),

        ('ShareRowExclusiveLock'::TEXT   , 'ShareLock'::TEXT               ),

        ('ShareRowExclusiveLock'::TEXT   , 'ShareRowExclusiveLock'::TEXT   ),

        ('ShareRowExclusiveLock'::TEXT   , 'ExclusiveLock'::TEXT           ),

        ('ShareRowExclusiveLock'::TEXT   , 'AccessExclusiveLock'::TEXT     ),

        ('ExclusiveLock'::TEXT           , 'RowShareLock'::TEXT            ),

        ('ExclusiveLock'::TEXT           , 'RowExclusiveLock'::TEXT        ),

        ('ExclusiveLock'::TEXT           , 'ShareUpdateExclusiveLock'::TEXT),

        ('ExclusiveLock'::TEXT           , 'ShareLock'::TEXT               ),

        ('ExclusiveLock'::TEXT           , 'ShareRowExclusiveLock'::TEXT   ),

        ('ExclusiveLock'::TEXT           , 'ExclusiveLock'::TEXT           ),

        ('ExclusiveLock'::TEXT           , 'AccessExclusiveLock'::TEXT     ),

        ('AccessExclusiveLock'::TEXT     , 'AccessShareLock'::TEXT         ),

        ('AccessExclusiveLock'::TEXT     , 'RowShareLock'::TEXT            ),

        ('AccessExclusiveLock'::TEXT     , 'RowExclusiveLock'::TEXT        ),

        ('AccessExclusiveLock'::TEXT     , 'ShareUpdateExclusiveLock'::TEXT),

        ('AccessExclusiveLock'::TEXT     , 'ShareLock'::TEXT               ),

        ('AccessExclusiveLock'::TEXT     , 'ShareRowExclusiveLock'::TEXT   ),

        ('AccessExclusiveLock'::TEXT     , 'ExclusiveLock'::TEXT           ),

        ('AccessExclusiveLock'::TEXT     , 'AccessExclusiveLock'::TEXT     )

    )

   ,L AS

    (

        SELECT

            (

                LOCKTYPE

               ,DATABASE

               ,RELATION::REGCLASS::TEXT

               ,PAGE

               ,TUPLE

               ,VIRTUALXID

               ,TRANSACTIONID

               ,CLASSID

               ,OBJID

               ,OBJSUBID

            ) AS TARGET

           ,VIRTUALTRANSACTION

           ,PID

           ,MODE

           ,GRANTED

       FROM PG_CATALOG.PG_LOCKS

    )

   ,T AS

    (

        SELECT

            BLOCKER.TARGET AS BLOCKER_TARGET

           ,BLOCKER.PID    AS BLOCKER_PID

           ,BLOCKER.MODE   AS BLOCKER_MODE

           ,BLOCKED.TARGET AS TARGET

           ,BLOCKED.PID    AS PID

           ,BLOCKED.MODE   AS MODE

        FROM L BLOCKER

        JOIN L BLOCKED ON  NOT BLOCKED.GRANTED

                       AND BLOCKER.GRANTED

                       AND BLOCKED.PID != BLOCKER.PID

                       AND BLOCKED.TARGET IS NOT DISTINCT FROM BLOCKER.TARGET

        JOIN C         ON  C.REQUESTED = BLOCKED.MODE

                       AND C.CURRENT   = BLOCKER.MODE

    )

   ,R AS

    (

        SELECT

            BLOCKER_TARGET

           ,BLOCKER_PID

           ,BLOCKER_MODE

           ,'1'::INT AS DEPTH

           ,TARGET

           ,PID

           ,MODE

           ,BLOCKER_PID::TEXT || ',' || PID::TEXT AS SEQ

        FROM T

        UNION ALL

        SELECT

            BLOCKER.BLOCKER_TARGET

           ,BLOCKER.BLOCKER_PID

           ,BLOCKER.BLOCKER_MODE

           ,BLOCKER.DEPTH + 1

           ,BLOCKED.TARGET

           ,BLOCKED.PID

           ,BLOCKED.MODE

           ,BLOCKER.SEQ || ',' || BLOCKED.PID::TEXT

        FROM R BLOCKER

        JOIN T BLOCKED ON BLOCKED.BLOCKER_PID = BLOCKER.PID

        WHERE BLOCKER.DEPTH < 1000

    )

SELECT *

FROM   R

ORDER BY SEQ;

 

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

Posted by 사용자 icodebroker
TAG

댓글을 달아 주세요