■ 재귀적으로 잠금 프로세스를 조회하는 방법을 보여준다.
▶ 예제 코드 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; |