[DATABASE/POSTGRESQL] 재귀적으로 잠금 프로세스 조회하기

■ 재귀적으로 잠금 프로세스를 조회하는 방법을 보여준다.

▶ 예제 코드 (SQL)


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;

100

101

102

103

104

105

106

107

108

109

110

111

112

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;