Oracle Lock 걸린 세션 확인 및 Lock관련 테이블
오라클에서 Lock과 관련된 딕셔너리 뷰는 얼마나 될까요? 티베로와 비교해보는 것도 궁금했습니다. 지난번에는 티베로의 Lock 관련 딕셔너리 뷰를 포스팅했는데, 이번에는 오라클을 포스팅하도록 하겠습니다.
DB : Oracle 12.2.0.1 64bit
오라클은 Lock 관련테이블이 69개가 존재합니다. Synonym도 포함된 값이기때문에 줄어들 수는 있지만, 티베로와 비교하였을때는 많은 테이블의 수를 확인할 수 있습니다.
select * from dict where table_name like '%LOCK%'
TABLE_NAME COMMENTS
-----------------------------------------------------------------------------------------------------------
DBA_BLOCKERS
DBA_DDL_LOCKS
DBA_DML_LOCKS
DBA_HIST_CR_BLOCK_SERVER Consistent Read Block Server Historical Statistics
DBA_HIST_CURRENT_BLOCK_SERVER Current Block Server Historical Statistics
DBA_KGLLOCK
DBA_LOCK
DBA_LOCKDOWN_PROFILES Describes all the rules of all the lockdown profiles
DBA_LOCK_INTERNAL
DBA_RECOVERABLE_SCRIPT_BLOCKS Details about the recoverable script blocks
CDB_BLOCKERS in all containers
CDB_DDL_LOCKS in all containers
CDB_DML_LOCKS in all containers
CDB_HIST_CR_BLOCK_SERVER Consistent Read Block Server Historical Statistics in all containers
CDB_HIST_CURRENT_BLOCK_SERVER Current Block Server Historical Statistics in all containers
CDB_KGLLOCK in all containers
CDB_LOCK in all containers
CDB_LOCKDOWN_PROFILES Describes all the rules of all the lockdown profiles in all containers
CDB_LOCK_INTERNAL in all containers
CDB_RECOVERABLE_SCRIPT_BLOCKS Details about the recoverable script blocks in all containers
DBMS_LOCK_ALLOCATED
DBA_LOCKS Synonym for DBA_LOCK
GV$BLOCKING_QUIESCE Synonym for GV_$BLOCKING_QUIESCE
GV$CACHE_LOCK Synonym for GV_$CACHE_LOCK
GV$CR_BLOCK_SERVER Synonym for GV_$CR_BLOCK_SERVER
GV$CURRENT_BLOCK_SERVER Synonym for GV_$CURRENT_BLOCK_SERVER
GV$DATABASE_BLOCK_CORRUPTION Synonym for GV_$DATABASE_BLOCK_CORRUPTION
GV$DLM_ALL_LOCKS Synonym for GV_$DLM_ALL_LOCKS
GV$DLM_LOCKS Synonym for GV_$DLM_LOCKS
GV$ENQUEUE_LOCK Synonym for GV_$ENQUEUE_LOCK
GV$GES_BLOCKING_ENQUEUE Synonym for GV_$GES_BLOCKING_ENQUEUE
GV$GES_DEADLOCKS Synonym for GV_$GES_DEADLOCKS
GV$GES_DEADLOCK_SESSIONS Synonym for GV_$GES_DEADLOCK_SESSIONS
GV$GLOBAL_BLOCKED_LOCKS Synonym for GV_$GLOBAL_BLOCKED_LOCKS
GV$LIBCACHE_LOCKS Synonym for GV_$LIBCACHE_LOCKS
GV$LOCK Synonym for GV_$LOCK
GV$LOCKED_OBJECT Synonym for GV_$LOCKED_OBJECT
GV$LOCKS_WITH_COLLISIONS Synonym for GV_$LOCKS_WITH_COLLISIONS
GV$LOCK_ACTIVITY Synonym for GV_$LOCK_ACTIVITY
GV$LOCK_ELEMENT Synonym for GV_$LOCK_ELEMENT
GV$LOCK_TYPE Synonym for GV_$LOCK_TYPE
GV$NFS_LOCKS Synonym for GV_$NFS_LOCKS
GV$NONLOGGED_BLOCK Synonym for GV_$NONLOGGED_BLOCK
GV$SESSION_BLOCKERS Synonym for GV_$SESSION_BLOCKERS
GV$_LOCK Synonym for GV_$_LOCK
V$BLOCKING_QUIESCE Synonym for V_$BLOCKING_QUIESCE
V$BLOCK_CHANGE_TRACKING Synonym for V_$BLOCK_CHANGE_TRACKING
V$CACHE_LOCK Synonym for V_$CACHE_LOCK
V$CR_BLOCK_SERVER Synonym for V_$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER Synonym for V_$CURRENT_BLOCK_SERVER
V$DATABASE_BLOCK_CORRUPTION Synonym for V_$DATABASE_BLOCK_CORRUPTION
V$DLM_ALL_LOCKS Synonym for V_$DLM_ALL_LOCKS
V$DLM_LOCKS Synonym for V_$DLM_LOCKS
V$ENQUEUE_LOCK Synonym for V_$ENQUEUE_LOCK
V$GES_BLOCKING_ENQUEUE Synonym for V_$GES_BLOCKING_ENQUEUE
V$GES_DEADLOCKS Synonym for V_$GES_DEADLOCKS
V$GES_DEADLOCK_SESSIONS Synonym for V_$GES_DEADLOCK_SESSIONS
V$GLOBAL_BLOCKED_LOCKS Synonym for V_$GLOBAL_BLOCKED_LOCKS
V$LIBCACHE_LOCKS Synonym for V_$LIBCACHE_LOCKS
V$LOCK Synonym for V_$LOCK
V$LOCKED_OBJECT Synonym for V_$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS Synonym for V_$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY Synonym for V_$LOCK_ACTIVITY
V$LOCK_ELEMENT Synonym for V_$LOCK_ELEMENT
V$LOCK_TYPE Synonym for V_$LOCK_TYPE
V$NFS_LOCKS Synonym for V_$NFS_LOCKS
V$NONLOGGED_BLOCK Synonym for V_$NONLOGGED_BLOCK
V$SESSION_BLOCKERS Synonym for V_$SESSION_BLOCKERS
V$_LOCK Synonym for V_$_LOCK
69 rows selected.
마찬가지로 테스트를 통해 어떤 뷰에 어떤 값들이 보여지는지 확인해보도록 하겠습니다.
세션1
TEST@ysbae> update emp set salary=1111 where employee_id=200;
1 row updated.
세션2 (Lock이 걸린 상태)
TEST@ysbae> update emp set salary=9999 where employee_id=200;
세션3
V$LOCK
v$lock에서 Type인 TX를 기준으로 값을 추출했을 때 두개의 행이 나오며 LMODE와 REQUEST를 통해 어느 세션이 Lock을 잡고있고, 어느 세션이 Lock을 요청했는지 알 수 있습니다. LMODE=6인 59번세션에서는 Lock을 Exclusive 모드로 잡고 있는 세션이며, REQUEST가 6인 1번 세션이 exclusive을 요청하고 대기하고 있는 세션임을 알 수 있습니다.
*참고
LMODE/REQUEST 번호에 따른 값의 의미
Lock mode in which the session holds the lock:
-
0 - none
-
1 - null (NULL)
-
2 - row-S (SS)
-
3 - row-X (SX)
-
4 - share (S)
-
5 - S/Row-X (SSX)
-
6 - exclusive (X)
select * from v$lock where type='TX'
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
-------- ---------- --------- -------- --------- --------- ---------- -------------- ---------- ---------- ------------
000000006E954180 000000006E954200 1 TX 131088 1747 0 6 137 0 0
000000006A2BFEE8 000000006A2BFF70 59 TX 131088 1747 6 0 226837 1 0
DBA_DML_LOCKS
dml으로 인한 lock을 보여주는 dba_dml_locks 테이블에서도 1번세션과 59번 세션을 확인할 수 있습니다. 하지만 이 뷰에서는 어느 세션이 lock을 잡고 있는지는 나오지 않고 있습니다. 59번 세션이 Blocking을 하고 있음에도 불구하고 BLOCKING_OTHERS에서는 Not Blocking으로 나오고 있음을 확인할 수 있습니다. 즉, DML과 관련되어 Lock이 걸려있는 모든 세션을 보여줄때 확인할 수 있는 테이블이라고 확인할 수 있을것 같습니다.
select * from dba_dml_locks
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTED LAST_CONVERT BLOCKING_OTHERS
-------------------- --------- ------ ------------------ --------------- ------------------------ ------------------
1 TEST EMP Row-X (SX) None 274 Not Blocking
59 TEST EMP Row-X (SX) None 227435 Not Blocking
DBA_LOCKS
DBA_LOCKS 테이블에서 LOCK_TYPE을 Transaction으로 조회한 결과 두개의 행이 나오게 되며 MODE_HELD와 MODE_REQUESTED의 값을 통해서 누가 LOCK을 잡고 있는지 알 수 있습니다. MODE_HELD에서 Exclusive값을 보이는 59번 세션이 현재 Lock을 잡고 있는것을 확인할 수 있습니다. 또한 BLOCKING_OTHERS 컬럼에서 Blocking의 값을 확인해도 알 수 있습니다. Lock에 대해 보기 편하게 잘 나타나있는 뷰라고 할 수 있습니다.
select * from dba_locks where LOCK_TYPE='Transaction'
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS CON_ID
-------------------- -------------------------- ----------- ----------------- ---------- --------- ------------------------ ---------------- ------------
1 Transaction None Exclusive 131088 1747 664 Not Blocking 0
59 Transaction Exclusive None 131088 1747 227364 Blocking 0
DBA_BLOCKERS
현재 LOCK을 잡고있는 세션만 확인하고 싶을 경우 간편하게 확인할 수 있는 뷰입니다. 59번 세션이 잡고 있는것을 확인할 수 있습니다.
select * from dba_blockers
HOLDING_SESSION CON_ID
------------------------------ ------------
59 0
V$SESSION
가장 먼저 확인할 수 있고, 가장 많은 정보를 볼 수 있는 테이블입니다. 보통 이 뷰만 사용해서라도 Lock걸린 테이블을 찾아낼 수 있습니다.
SID가 1번인 세션의 BLOCKING SESSION을 확인해보면 59번세션이 BLOCKING하고 있는것을 확인할 수 있으며 그에 따라 enq:TX -row lock contention 이벤트가 발생하고 있는것을 확인할 수있습니다. 뿐만아니라 어떤 프로그램을 사용해서 접속하여 세션이 작업하고 있는지 상세하게 확인할 수 있습니다.
select sid,serial#,username,status,machine,program,module,blocking_session_status,blocking_instance,blocking_session,event from v$session where type <> 'BACKGROUND'
SID SERIAL# USERNAME STATUS MACHINE PROGRAM MODULE BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION EVENT
--------- -------------- ---------- ------------ ----------------- -------------------------- -------------------------------------------------- ------------------------ -------------------- ------------------- ------------------------------
1 57694 TEST ACTIVE ysbae sqlplus@ysbae (TNS V1-V3) SQL*Plus VALID 1 59 enq: TX - row lock contention
24 24830 SYS INACTIVE ysbae sqlplus@ysbae (TNS V1-V3) sqlplus@ysbae (TNS V1-V3) NO HOLDER SQL*Net message from client
25 64616 SYS ACTIVE ysbae oracle@ysbae (J000) DBMS_SCHEDULER NOT IN WAIT PGA memory operation
48 30931 SYS ACTIVE ysbae oracle@ysbae (J001) DBMS_SCHEDULER NO HOLDER PL/SQL lock timer
59 41976 TEST INACTIVE ysbae sqlplus@ysbae (TNS V1-V3) SQL*Plus NO HOLDER SQL*Net message from client
62 57475 SYS INACTIVE WORKGROUP\YSBAE OrangeMainU.exe Orange for ORACLE (Unicode) DBA - Session Monitor NO HOLDER SQL*Net message from client
63 53219 SYS ACTIVE WORKGROUP\YSBAE OrangeMainU.exe Orange for ORACLE (Unicode) DBA NOT IN WAIT SQL*Net message to client
69 51582 ACTIVE ysbae oracle@ysbae (J002) NO HOLDER jobq slave wait
8 rows selected.
해당 세션을 확인해서 BLOCKING 하고 있는 세션을 강제로 킬 하던지, 세션 사용중인 담당자에서 트랜잭션의 완료(commit or rollback)을 하라고 요청해야 합니다.
세션을 킬하는 명령어는 아래와 같습니다.
alter system kill session 'SID,SERIAL#'
alter system kill session '59,41976' ;
명령어 후 V$SESSION을 한번더 조회하면 STATUS에 KILLED라고 바뀐것을 확인할 수 있습니다.
LOCK과 관련된 뷰는 오라클이 다양하게 많이 있고 각 뷰마다 디테일하게 조회할 수 있습니다.