[ PostgreSQL ] Lock Session 확인

PostgreSQL에서 Lock Session을 할 때 사용하는 SQL 쿼리 

• 기본적으로 Postgresql의 Transaction isolation은 read-committed이다. ( Oracle DB의 격리 레벨과 동일 )
• Autocomit이 on으로 되어있다.
• Lock 테스트는 총 3개의 Session으로 진행할 예정입니다.

 

■ Autocommit 비활성화

- Lock 테스트를 하기위해 Session의 Autocommit을 비활성화하였습니다.

session1 > lee=# \set AUTOCOMMIT off

session2 > lee=# \set AUTOCOMMIT off

 

■ Update Table

session1 > lee=# update lee set a=2 where b='asd';
UPDATE 1

session2 > lee=# update lee set a=2 where b='asd';
---> WAIT

■ Lock 조회

session3 > postgres=#  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
   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;
 blocked_pid | blocked_user | blocking_pid | blocking_user |         blocked_statement         | current_statement_in_blocking_process 
-------------+--------------+--------------+---------------+-----------------------------------+---------------------------------------
       90078 | postgres     |        90383 | postgres      | update lee set a=2 where b='asd'; | update lee set a=2 where b='asd';

 

■ Blocking Session Kill

SELECT pg_terminate_backend([pid])

session3 > postgres=#  select pg_terminate_backend(90383);
 pg_terminate_backend 
----------------------
 t
(1 row)


session2 > lee=# update lee set a=2 where b='asd';
UPDATE 1
---> WAIT 풀림


session1 > lee=# select * from lee;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
---> session이 죽어서 reset session 된것을 볼 수 있다.