ORACLE Tips: 是誰鎖住了我的資料 ?
By Tommy Wu <tommy at teatime.com.tw>

雖然我們都會要求程式設計師在處理資料時, 不能造成資料鎖定過長的時間, 也要求
一定不能忘了要做 commit 的動作. 但是在實務上, 仍然有許多時候會發現, 他們真
的會忘了 commit 這件事.... 這時候, 如果一不小心, 可能你下個指令要去異動這筆
資料時, 就會發現你的程式不動了, 被鎖住等待對方做 commit 或 rollback...

如果發生資料鎖定的情形... 要如何得知是誰造成的, 就很重要了...

ORACLE 有一些 view 可以查詢 lock 的情形, 如 v$lock, v$locked_object 等等,
我們可以透過這些 view 來查詢.

下面這個方法是很直覺的一個方法, 我以前就是用這個方法查詢:

select b.object_name obj_name,
       d.ctime time,
       to_char(d.lmode) l,
       to_char(d.request) r,
       a.os_user_name os_user,
       c.machine machine,
       c.program program,
       a.session_id s_id,
       c.serial# s_serial,
       a.oracle_username ora_user,
       a.object_id obj_id,
       a.process pid
from v$locked_object a, all_objects b, v$session c, v$lock d
where a.object_id=b.object_id
and a.session_id=c.sid
and a.session_id=d.sid
and d.type='TX'
order by b.object_name, d.ctime desc, a.session_id

但是, 後來發現, 這個動作實在是太慢了, 在系統忙碌的時候, 做一次查詢會很慢, 也
會影響到線上的使用者, 所以上網查了一下, 目前使用下面的指令, 可以很快的查詢到
需要的結果, 也不會造成系統資源的過度使用:

select /*+ ordered */
	--b.kaddr,
	c.sid,
        c.serial#,
        b.ctime,
	lock_waiter.waiting_session,
	lock_blocker.holding_session,
	c.program,
	c.osuser,
	c.machine,
	c.process,
	decode(u.name,
		null,'',
		u.name||'.'||o.name
	) object,
	c.username,
	decode
	(
		b.type,
		'BL', 'Buffer hash table instance lock',
		'CF', 'Control file schema global enqueue lock',
		'CI', 'Cross-instance function invocation instance lock',
		'CU', 'Cursor bind lock',
		'DF', 'Data file instance lock',
		'DL', 'direct loader parallel index create lock',
		'DM', 'Mount/startup db primary/secondary instance lock',
		'DR', 'Distributed recovery process lock',
		'DX', 'Distributed transaction entry lock',
		'FS', 'File set lock',
		'IN', 'Instance number lock',
		'IR', 'Instance recovery serialization global enqueue lock',
		'IS', 'Instance state lock',
		'IV', 'Library cache invalidation instance lock',
		'JQ', 'Job queue lock',
		'KK', 'Thread kick lock',
		'LA','Library cache lock instance lock (A..P=namespace);',
		'LB','Library cache lock instance lock (A..P=namespace);',
		'LC','Library cache lock instance lock (A..P=namespace);',
		'LD','Library cache lock instance lock (A..P=namespace);',
		'LE','Library cache lock instance lock (A..P=namespace);',
		'LF','Library cache lock instance lock (A..P=namespace);',
		'LG','Library cache lock instance lock (A..P=namespace);',
		'LH','Library cache lock instance lock (A..P=namespace);',
		'LI','Library cache lock instance lock (A..P=namespace);',
		'LJ','Library cache lock instance lock (A..P=namespace);',
		'LK','Library cache lock instance lock (A..P=namespace);',
		'LL','Library cache lock instance lock (A..P=namespace);',
		'LM','Library cache lock instance lock (A..P=namespace);',
		'LN','Library cache lock instance lock (A..P=namespace);',
		'LO','Library cache lock instance lock (A..P=namespace);',
		'LP','Library cache lock instance lock (A..P=namespace);',
		'MM', 'Mount definition global enqueue lock',
		'MR', 'Media recovery lock',
		'NA', 'Library cache pin instance lock (A..Z=namespace)',
		'NB', 'Library cache pin instance lock (A..Z=namespace)',
		'NC', 'Library cache pin instance lock (A..Z=namespace)',
		'ND', 'Library cache pin instance lock (A..Z=namespace)',
		'NE', 'Library cache pin instance lock (A..Z=namespace)',
		'NF', 'Library cache pin instance lock (A..Z=namespace)',
		'NG', 'Library cache pin instance lock (A..Z=namespace)',
		'NH', 'Library cache pin instance lock (A..Z=namespace)',
		'NI', 'Library cache pin instance lock (A..Z=namespace)',
		'NJ', 'Library cache pin instance lock (A..Z=namespace)',
		'NK', 'Library cache pin instance lock (A..Z=namespace)',
		'NL', 'Library cache pin instance lock (A..Z=namespace)',
		'NM', 'Library cache pin instance lock (A..Z=namespace)',
		'NN', 'Library cache pin instance lock (A..Z=namespace)',
		'NO', 'Library cache pin instance lock (A..Z=namespace)',
		'NP', 'Library cache pin instance lock (A..Z=namespace)',
		'NQ', 'Library cache pin instance lock (A..Z=namespace)',
		'NR', 'Library cache pin instance lock (A..Z=namespace)',
		'NS', 'Library cache pin instance lock (A..Z=namespace)',
		'NT', 'Library cache pin instance lock (A..Z=namespace)',
		'NU', 'Library cache pin instance lock (A..Z=namespace)',
		'NV', 'Library cache pin instance lock (A..Z=namespace)',
		'NW', 'Library cache pin instance lock (A..Z=namespace)',
		'NX', 'Library cache pin instance lock (A..Z=namespace)',
		'NY', 'Library cache pin instance lock (A..Z=namespace)',
		'NZ', 'Library cache pin instance lock (A..Z=namespace)',
		'PF', 'Password File lock',
		'PI', 'Parallel operation locks',
		'PS', 'Parallel operation locks',
		'PR', 'Process startup lock',
		'QA','Row cache instance lock (A..Z=cache)',
		'QB','Row cache instance lock (A..Z=cache)',
		'QC','Row cache instance lock (A..Z=cache)',
		'QD','Row cache instance lock (A..Z=cache)',
		'QE','Row cache instance lock (A..Z=cache)',
		'QF','Row cache instance lock (A..Z=cache)',
		'QG','Row cache instance lock (A..Z=cache)',
		'QH','Row cache instance lock (A..Z=cache)',
		'QI','Row cache instance lock (A..Z=cache)',
		'QJ','Row cache instance lock (A..Z=cache)',
		'QK','Row cache instance lock (A..Z=cache)',
		'QL','Row cache instance lock (A..Z=cache)',
		'QM','Row cache instance lock (A..Z=cache)',
		'QN','Row cache instance lock (A..Z=cache)',
		'QP','Row cache instance lock (A..Z=cache)',
		'QQ','Row cache instance lock (A..Z=cache)',
		'QR','Row cache instance lock (A..Z=cache)',
		'QS','Row cache instance lock (A..Z=cache)',
		'QT','Row cache instance lock (A..Z=cache)',
		'QU','Row cache instance lock (A..Z=cache)',
		'QV','Row cache instance lock (A..Z=cache)',
		'QW','Row cache instance lock (A..Z=cache)',
		'QX','Row cache instance lock (A..Z=cache)',
		'QY','Row cache instance lock (A..Z=cache)',
		'QZ','Row cache instance lock (A..Z=cache)',
		'RT', 'Redo thread global enqueue lock',
		'SC', 'System commit number instance lock',
		'SM', 'SMON lock',
		'SN', 'Sequence number instance lock',
		'SQ', 'Sequence number enqueue lock',
		'SS', 'Sort segment locks',
		'ST', 'Space transaction enqueue lock',
		'SV', 'Sequence number value lock',
		'TA', 'Generic enqueue lock',
		'TS', 'Temporary segment enqueue lock (ID2=0)',
		'TS', 'New block allocation enqueue lock (ID2=1)',
		'TT', 'Temporary table enqueue lock',
		'UN', 'User name lock',
		'US', 'Undo segment DDL lock',
		'WL', 'Being-written redo log instance lock',
		b.type
	) lock_type,
	decode
	(
		b.lmode,
		0, 'None',           /* Mon Lock equivalent */
		1, 'Null',           /* N */
		2, 'Row-S (SS)',     /* L */
		3, 'Row-X (SX)',     /* R */
		4, 'Share',          /* S */
		5, 'S/Row-X (SRX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(b.lmode)
	) mode_held,
	decode
	(
		b.request,
		0, 'None',           /* Mon Lock equivalent */
		1, 'Null',           /* N */
		2, 'Row-S (SS)',     /* L */
		3, 'Row-X (SX)',     /* R */
		4, 'Share',          /* S */
		5, 'S/Row-X (SSX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(b.request)
	) mode_requested
from v$lock b,
     v$session c,
     sys.user$ u,
     sys.obj$ o,
     (select * from sys.dba_waiters) lock_blocker,
     (select * from sys.dba_waiters) lock_waiter
where b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username != 'SYS'
order by ctime desc, object, kaddr, lockwait



本文的最新版本可以由下列的網頁取得:
 
http://www.teatime.com.tw/~tommy/doc/oracle_lock.txt