[Oracle] oracle에서 lock object를 조회하는 방법

oracle에서 object가 lock이 걸렸을 떄 조회하는 sql문을 알아보자.

우선 lock에 걸리는 이유에 대해 간단하게 알아보겠습니다.

DB에 A라는 사용자가 연결되었고, 동시에 B라는 사용자도 연결했습니다.

A라는 사용자는 'test_a' 라는 테이블에 데이터를 삭제하는 delete문을 삭제하고 commit을 하지 않았습니다. 즉 DB에 반영이 되지 않았죠

그 상태에서 B사용자가 'test_a'라는 테이블에 데이터를 update를 했을 경우 B사용자는 A사용자가 DB에 작업을 반영할 때 까지 기다리는 일이 발생합니다.

이때 A가 'test_a'라는 테이블의 row resource를 사용하기위해 row lock을 걸었습니다.

이것을 그림으로 보면 아래와 같습니다.

이것은 매우 간단한 경우고 수많은 session이 얽혀 있는 DB에서 lock을 파악하기란 쉽지 않습니다. 

우선 이 간단한 경우를 예시로 어떤 사용자가 lock을 잡고있는 사용자이고,

어떤 사용자가 대기를 하는 사용자인지 확인해보는 sql문을 작성해봅니다.

SELECT 
	CASE 
		WHEN a.xidusn = 0 AND a.xidslot = 0 AND a.xidsqn = 0 THEN 
			'waiting session'
		ELSE 
			'locking table'
	END AS lock_status,
	d.sid,
	d."SERIAL#",
	a.OS_USER_NAME ,
	d.username,
	c.OBJECT_name,
	c.object_type,
	b.ctime,
	a.process	
FROM v$locked_object a , v$lock b, dba_objects c, v$session d
WHERE a.object_id = b.id1
AND a.SESSION_ID  = b.sid
AND a.object_id = c.object_id
AND a.session_id = d.sid
and c.object_type = 'TABLE'
AND b.TYPE = 'TM';

위와 같이 사용할 수 있으며 결과는 아래처럼 나옵니다.

v$locked_object에서 xidusn, xidslot, xidsqn 이 0인 값을 가진 session은 트랜잭션을 시작하지 않았기 때문에 waiting 사용자인것을 확인 할수 있습니다.
그리고 ctime은 현재 session이 몇초정도 지났는지를 알 수 있습니다.