[Postgresql] Lock Session 확인 및 정리 가이드

* Lock Session 확인 및 정리 가이드

 

 

세션(1) pid=3516 (Blocking Session)

kyh=# select pg_backend_pid();

pg_backend_pid

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

3516

kyh=# begin;

kyh=# update kyh1 set test='kyh' where test='kyh2';

UPDATE 26

 

세션(2) pid=6532 (Wait Session)

pg_backend_pid

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

6532

kyh=# begin;

kyh=# update kyh1 set test='kyh3' where test='kyh2';

~ lock발생

 

 (1) blocking세션으로 인한 wait session확인하기

 

kyh=# select pid, waiting, state, query from pg_stat_activity where waiting = 't';

 

pid | waiting | state | query

------+---------+--------+------------------------------------------------

6532 | t | active | update kyh1 set test='kyh3' where test='kyh2';

(1 row)

 

 

 2) blocking세션, wait세션 확인하기

-> pid=6532가 blocking세션이고, pid=3516은 wait 세션

 

select bl.pid as blocked_pid, a.usename as blocked_user, ka.query as blocking_statement,

TO_CHAR(now() -ka.query_start, 'HH24:MI:SS') as blocking_duration,

kl.pid as blocking_pid, ka.usename as blocking_user,

a.query as blocked_statement,

TO_CHAR(now() -a.query_start, 'HH24:MI:SS') as blocked_duration

from pg_catalog.pg_locks bl

join pg_catalog.pg_stat_activity a on bl.pid= a.pid

join pg_catalog.pg_locks kl

join pg_catalog.pg_stat_activity ka

on kl.pid= ka.pid

on bl.transactionid= kl.transactionid and bl.pid!= kl.pid

where bl.granted= 'f';

 

blocked_pid | blocked_user | blocking_statement | blocking_duration | blocking_pid | blocking_user | blocked_statement | blocked_duration

-------------+--------------+-----------------------------------------------+-------------------+--------------+---------------+------------------------------------------------+------------------

6532 | postgres | update kyh1 set test='kyh' where test='kyh2'; | 00:14:08 | 3516 | postgres | update kyh1 set test='kyh3' where test='kyh2'; | 00:10:34

 

 

 3) blocking세션 kill시키기

select pg_terminate_backend(6532);

kyh=# select pg_terminate_backend(6532);

pg_terminate_backend

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

t

(1 row)

 

 4) 현재 lock정보 확인 쿼리

->현재 pid=3516세션은 commit ,rollback 명령어가 없이 대기 상태

 

select pg_stat_activity.pid,locktype, virtualtransaction, transactionid, nspname, relname, mode, granted,

cast(date_trunc('second',query_start) as timestamp) as query_start, substr(query,1,50) as query

from pg_locks left outer join pg_class on (pg_locks.relation= pg_class.oid)

left outer join pg_namespace on (pg_namespace.oid= pg_class.relnamespace),

pg_stat_activity

where not pg_locks.pid=pg_backend_pid()

and pg_locks.pid=pg_stat_activity.pid

order by pg_stat_activity.pid;

 

pid | locktype | virtualtransaction | transactionid | nspname | relname | mode | granted | query_start | query

------+---------------+--------------------+---------------+---------+---------+------------------+---------+---------------------+-----------------------------------------------

3516 | relation | 3/35 | | public | kyh1 | RowExclusiveLock | t | 2014-05-22 14:28:48 | update kyh1 set test='kyh' where test='kyh2';

3516 | virtualxid | 3/35 | | | | ExclusiveLock | t | 2014-05-22 14:28:48 | update kyh1 set test='kyh' where test='kyh2';

3516 | transactionid | 3/35 | 739 | | | ExclusiveLock | t | 2014-05-22 14:28:48 | update kyh1 set test='kyh' where test='kyh2';

 

 5) 세션(1)에서 commit 명령어 수행

kyh=# commit;

 

 

-> commit명령어 이후 lock정보는 없는 상태

select pg_stat_activity.pid,locktype, virtualtransaction, transactionid, nspname, relname, mode, granted,

cast(date_trunc('second',query_start) as timestamp) as query_start, substr(query,1,50) as query

from pg_locks left outer join pg_class on (pg_locks.relation= pg_class.oid)

left outer join pg_namespace on (pg_namespace.oid= pg_class.relnamespace),

pg_stat_activity

where not pg_locks.pid=pg_backend_pid()

and pg_locks.pid=pg_stat_activity.pid

order by pg_stat_activity.pid;

 

pid | locktype | virtualtransaction | transactionid | nspname | relname | mode | granted | query_start | query

-----+----------+--------------------+---------------+---------+---------+------+---------+-------------+-------

(0 rows)

 

 

by. 김용하(2014.07.23)