[ORACLE] LOCK 구조, 경합 쿼리

1. TM Lock

• Table을 보호하는 락
• 경합 발생시 enq:TM - contention 대기

2. TM Lock MODE

 

구분	모드	Ex
LEVEL 2	RS(ROW Shared Table Locks), SS(Subshare Table Locks)	LOCK TABLE EMP IN ROW SHARE MODE;
LEVEL 3	RX(ROW Exclusive Table Locks), SX(Subexclusive Table Locks)	LOCK TABLE EMP IN ROW EXCLUSIVE MODE;
LEVEL 4	S(Share Table Locks), SS(Subshare Table Locks)	LOCK TABLE EMP IN SHARE MODE;
LEVEL 5	SRS(Share Row Exclusive Table Locks), SSX(Share-Subexclusive Table Locks)	LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE;
LEVEL 6	X(Exclusive Table Locks)	LOCK TABLE EMP IN EXCLUSIVE MODE;

 

3. 요청되는 Lock Mode

 

[Oracle]

select lpad(' ',3*(level-1)) || waiting_session waiting_session,
        lock_type,
        mode_requested,
        mode_held,
        lock_id1,
        lock_id2
from 
(
	select l.waiting_session,
		l.holding_session,
		l.lock_type,
		l.mode_held,
		l.mode_requested,
		l.lock_id1,
		l.lock_id2	   
		from ( 
		select w.session_id waiting_session,
				h.session_id holding_session,
				w.lock_type lock_type,
				h.mode_held mode_held,
				w.mode_requested mode_requested,
				w.lock_id1 lock_id1,
				w.lock_id2 lock_id2
		from sys.dba_lock w, sys.dba_lock h
		where h.blocking_others =  'Blocking'
		and  h.mode_held      !=  'None'
		and  h.mode_held      !=  'Null'
		and  w.mode_requested !=  'None'
		and  w.lock_type       =  h.lock_type
		and  w.lock_id1        =  h.lock_id1
		and  w.lock_id2        =  h.lock_id2
		) l
	union all
	select holding_session, null, 'None', null, null, null, null
	from 
	(
		select l.waiting_session,
		l.holding_session,
		l.lock_type,
		l.mode_held,
		l.mode_requested,
		l.lock_id1,
		l.lock_id2	   
		from ( 
		select w.session_id waiting_session,
				h.session_id holding_session,
				w.lock_type lock_type,
				h.mode_held mode_held,
				w.mode_requested mode_requested,
				w.lock_id1 lock_id1,
				w.lock_id2 lock_id2
		from sys.dba_lock w, sys.dba_lock h
		where h.blocking_others =  'Blocking'
		and  h.mode_held      !=  'None'
		and  h.mode_held      !=  'Null'
		and  w.mode_requested !=  'None'
		and  w.lock_type       =  h.lock_type
		and  w.lock_id1        =  h.lock_id1
		and  w.lock_id2        =  h.lock_id2
		) l
	)
	minus
	select waiting_session, null, 'None', null, null, null, null
	from 
	(
		select l.waiting_session,
		l.holding_session,
		l.lock_type,
		l.mode_held,
		l.mode_requested,
		l.lock_id1,
		l.lock_id2	   
		from ( 
		select w.session_id waiting_session,
				h.session_id holding_session,
				w.lock_type lock_type,
				h.mode_held mode_held,
				w.mode_requested mode_requested,
				w.lock_id1 lock_id1,
				w.lock_id2 lock_id2
		from sys.dba_lock w, sys.dba_lock h
		where h.blocking_others =  'Blocking'
		and  h.mode_held      !=  'None'
		and  h.mode_held      !=  'Null'
		and  w.mode_requested !=  'None'
		and  w.lock_type       =  h.lock_type
		and  w.lock_id1        =  h.lock_id1
		and  w.lock_id2        =  h.lock_id2
		) l
	)
) lock_holders
connect by  prior waiting_session = holding_session
  start with holding_session is null;