[Oracle, PostgreSQL]DB TABLE Lock

• target:  DB TABLE Lock 확인 및 해제

 

---

• method: 

1. Oracle

오라클 LOCK 걸린 개체 확인 및 LOCK 해제

 

오라클이나 다른 DBMS 제품을 사용하는 중에 LOCK 이 걸려본 경험은 다들 한두번씩 있을거라고 봅니다.

일반적으로 TOAD 나 ORANGE 같은 상용제품을 사용할 때는 모니터링툴이 있으니 바로 열어서 확인해볼 수 있지만, SQL Developer 같은 무료제품을 사용할 때에는 어떻게 해야 하는지 알 수 없습니다.

 

사실 모니터링툴도 다 DBMS 에서 관리하고 있는 시스템인데 SQL 로 조회해보면 됩니다.

자주 사용하는 sql 정도는 몇개 저장해두고 사용하면 편하게 쓸 수 있습니다.

 

일단 LOCK 걸린 객체를 확인해보겠습니다.

 

1) LOCK 걸린 개체 확인

SELECT OBJECT_ID
     , SESSION_ID		-- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT
;


OBJECT_ID       SESSION_ID       ORACLE_USERNAME       OS_USER_NAME
----------------------------------------------------------------
163990          401              SCOTT                 js
163966          401              SCOTT                 js

이걸로는 자세한 내용을 아직 알기 어렵습니다.

그러면 다음으로 해당 sid와 serial 번호로 락걸린 object name을 확인해보겠습니다.

 

2) 해당 sid와 serial 번호로 락걸린 object name을 확인

SELECT A.SID
     , A.SERIAL#
     , object_name
     , A.SID || ', ' || A.SERIAL# AS KILL_TASK
  FROM V$SESSION A
 INNER JOIN V$LOCK B
    ON A.SID = B.SID
 INNER JOIN DBA_OBJECTS C
    ON B.ID1 = C.OBJECT_ID
 WHERE B.TYPE = 'TM'
;


SID     SERIAL#     OBJECT_NAME     KILL_TASK
---------------------------------------------------
401     12761       EMP             401, 12761
401     12761       EMP             401, 12761

 

자 이제 뭔가 OBJECT_NAME이 보이면서 이 TABLE이 LOCK이 걸렸구나 라는 것을 확인할 수 있습니다.

 

그럼 LOCK이 걸린것은 확인했고 LOCK을 해제하려면 어떻게 해야 할까요?

정답은 마지막에 SID와 SERIAL#로 조회된 NUMBER로 해당되는 SESSION을 KILL 시키면 됩니다.

 

3) sid와 시리얼 번호로 세션 해제

 

ALTER SYSTEM KILL SESSION '401, 12761'
;

 

뒤에 입력된 숫자인 401, 12761는 위에서 말씀드린 SID와 SERIAL#에 대한 값입니다.

편하게 KILL_TASK 라는 값을 복사해서 사용할 수 있게 미리 만들어주었습니다.

 

(ALTER 명령은 별도의 COMMIT이 필요없는 거 다들 아시리라 생각합니다.)

 

이 단계까지 왔으면 LOCK 걸린 테이블의 SESSION 은 해제되었을 것이라고 봅니다.

그런데 위에서 조회된 세션이 단순히 LOCK 걸린 것이 아니라 아직 작업중인 SQL 일수도 있다는 생각이 들수도 있습니다.

그럴때는 LOCK을 발생시킨 SQL이 뭔지 확인해볼 수 있습니다.

 

4) 락 발생 사용자 및 OBJECT 조회 + 어떤 sql을 실행중하여 lock을 걸고 있는지 확인

SELECT DISTINCT T1.SESSION_ID
     , T2.SERIAL#
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
  FROM V$LOCKED_OBJECT T1
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
 WHERE 1=1
   AND T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
 ORDER BY T3.ADDRESS
     , T3.PIECE
;


SESSION_ID  SERIAL#  OBJECT_NAME  MACHINE        TERMINAL  PROGRAM          ADDRESS    PIECE  SQL_TEXT
------------------------------------------------------------------------------------------
401         12761    EMP          machine_com1   unknown   JDBC Thin Client 230134658  0      INSERT /*+ APPEND */ INTO EMP
401         12761    EMP          machine_com1   unknown   JDBC Thin Client 230134658  1      , EMPNO
401         12761    EMP          machine_com1   unknown   JDBC Thin Client 230134658  2      , EMPNM
401         12761    EMP          machine_com1   unknown   JDBC Thin Client 230134658  3      , DEPTNO

위와 같이 조회되면 맨 우측에 SQL_TEXT를 조회해보면 어떤 SQL이 실행되었는지 알 수 있습니다.

 

---

2. PostgreSQL

테이블 lock 조회 및 kill하기

 

1) 현재 수행중인 SQL 전체 조회

select datname
     , pid
     , usename
     , application_name
     , client_addr
     , client_port
     , backend_start
     , query_start
     , wait_event_type
     , state
     , backend_xmin
     , query
  from pg_stat_activity;

위 쿼리 실행시 현재 DB에서 돌고 있는 SQL 전체를 조회할 수 있다.

[그림 1] 현재 DB에서 실행중인 SQL

COLUMN NAME	DATA_TYPE	DESC
datid	oid	데이터베이스oid
datname	name	데이터베이스 이름
pid	integer	프로세스id
usesysid	oid	사용자고유번호
usename	name	사용자이름
application_name	text	응용프로그램 이름
client_addr	inet	접속ip
client_hostname	text	접속 호스트 이름
client_port	integer	접속한 TCP 포트
backend_start	timestamp	서버 접속시간
xact_start	timestamp	트랜잭션 시작 시간
query_start	timestamp	쿼리 시작 시간
state_change	timestamp	state 마지막 변경 시간
waiting	boolean	대기중 = true
state	text	상태 정보 (하단 참조)
query	text	state=active인 row에 대해 실행중인 쿼리

※ state(상태정보)

 - active: 쿼리 실행중

 - idle: 새로운 명령 대기중

 - idle in transaction: 트랜잭션은 있지만 현재 실행중인 쿼리 없음

 - idle in transaction (aborted): 트랜잭션은 있고 실행중인 쿼리는 없으나, 트랜잭션에 오류가 발생

 - fastpath function call: 함수 실행중

 - disabled: track_activites 무효

 

2) LOCK 걸린 테이블 확인

SELECT t.relname
     , l.locktype
     , page
     , virtualtransaction
     , pid
     , mode
     , granted
  FROM pg_locks l
     , pg_stat_all_tables t
 WHERE l.relation = t.relid
 ORDER BY relation ASC;

현재 테이블에 lock을 확인 할 수 있다. 기본적으로 대부분의 lock 테이블은 괜찮으나 RowExclusiveLock이 검색된다면 해당 테이블에 접근이 지연되어 다른 쿼리에도 영향을 미칠 수 있으므로 반드시 잡고있는 트랜잭션이나 서버 상태등을 점검하여 lock을 해제 해주는 작업이 필요하다.

[그림 2] 테이블 lock 확인

 

3) 해당 작업(process) kill

-- 해당 pid만 종료
SELECT pg_cancel_backend([pid]);

-- 해당 pid와 연계된 모든 상위 쿼리 종료
SELECT pg_terminate_backend([pid]) FROM pg_stat_activity;

pg_cancel_backend는 해당 PID만 중지시키고, pg_terminate_backend는 해당 PID와 연계된 모든 상위 쿼리 프로세스를 종료시킨다. 따라서 pg_cancel_backend로 해당 작업이 종료 되는지 먼저 체크한 뒤, 중지되지 않는다면 pg_terminate_backend를 수행하여 해당 프로세스를 종료시키도록 한다.

 

---

• source:

1) Oracle

https://hello-nanam.tistory.com/23

오라클 LOCK 걸린 개체 확인 및 LOCK 해제

 

2) PostgreSQL

https://kwomy.tistory.com/80

[PostgreSQL] 테이블 lock 조회 및 kill하기