無工具情況下Oracle應急診斷思路（二）

二、鎖表（Lock）導致的系統無響應或慢

在多使用者能夠後臺登入進行維護的系統中，經常會發生某些表被鎖定而直接影響業務的情況，在這樣的情況下我們可以將v$session結合v$locked_object和v$lock檢視，如下為簡單的查詢語句：

select lo.oracle_username,

lo.object_id,

s.sid,

s.serial#,

s.seconds_in_wait

from v$locked_object lo, v$session s

where lo.session_id = s.sid

order by seconds_in_wait

也可以透過以上的OBJECTID和USER_OBJECTS（ALL_OBJECTS、DBA_OBJECTS）的OBJECTS_ID關聯，得到鎖的是哪個物件；

select b.owner, b.object_name, a.session_id, a.locked_mode

from v$locked_object a, dba_objects b

where b.object_id = a.object_id;

[@more@]

根據查詢結果定位導致鎖表的session或Process所對應的使用者，提醒使用者釋放鎖（即放棄自己進行DML操作），如果無法定位使用者，則可以採取如下兩種方式：

（1）釋放鎖的方法：

我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：

alter system kill session 'sid,serial#'; --sid,serial#是上面查詢出來的

（2）系統級殺程式方法釋放鎖：

select spid from v$process where addr = (select paddr

from v$session where sid=&sid);

$ kill -9 spid --spid是上面查詢出來的

--如下是檢視當前是否存在鎖表較複雜的SQL語句：

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,

decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,

o.owner,o.object_id,o.object_name object,o.subobject_name, decode(ls.lmode, 1, null, 2, 'Row Share', 3,

'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

lock_mode, o.owner, ls.sid, ls.serial# seria ,ls.serial# serial_num, ls.id1, ls.id2

from sys.dba_objects o, ( select s.osuser, s.username, l.type,

l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,

v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner

<> 'SYS' order by o.owner, o.object_name

--alter system kill session 'sid,serial';

alter system kill session '1012,7580';

如下為oracle10g中 v$locked_object和 v$lock的描述和定義，可以看出這兩個檢視都來自於檢視

SQL> desc v$locked_object;

Name Type Nullable Default Comments

--------------- ------------ -------- ------- --------

XIDUSN NUMBER Y

XIDSLOT NUMBER Y

XIDSQN NUMBER Y

OBJECT_ID NUMBER Y

SESSION_ID NUMBER Y

ORACLE_USERNAME VARCHAR2(30) Y

OS_USER_NAME VARCHAR2(30) Y

PROCESS VARCHAR2(12) Y

LOCKED_MODE NUMBER Y

SQL> select * from v$fixed_view_definition

where view_name='V$LOCKED_OBJECT';

------------------------------------------------------------

select xidusn,xidslot,xidsqn,object_id,session_id,oracle_username, os_user_name,process,locked_mode from gv$locked_object where inst_id = USERENV('Instance')

SQL> desc v$lock;

Name Type Nullable Default Comments

------- ----------- -------- ------- --------

ADDR RAW(8) Y

KADDR RAW(8) Y

SID NUMBER Y

TYPE VARCHAR2(2) Y

ID1 NUMBER Y

ID2 NUMBER Y

LMODE NUMBER Y

REQUEST NUMBER Y

CTIME NUMBER Y

BLOCK NUMBER Y

select * from v$fixed_view_definition

where view_name='V$LOCK';

------------------------------------------------------------

select ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK from GV$LOCK where inst_id = USERENV('Instance')

ORACLE裡鎖有以下幾種模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表鎖
3：Row-X 行專用(RX)：用於行的修改
4：Share 共享鎖(S)：阻止其他DML操作
5：S/Row-X 共享行專用(SRX)：阻止其他事務操作
6：exclusive 專用(X)：獨立訪問使用
數字越大鎖級別越高, 影響的操作越多。
一般的查詢語句如select ... from ... ;是小於2的鎖, 有時會在v$locked_object出現。
select ... from ... for update; 是2的鎖。
當對話使用for update子串開啟一個遊標時，
所有返回集中的資料行都將處於行級(Row-X)獨佔式鎖定，
其他物件只能查詢這些資料行，不能進行update、delete或select...for update操作。
insert / update / delete ... ; 是3的鎖。
沒有commit之前插入同樣的一條記錄會沒有反應,
因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。
建立索引的時候也會產生3,4級別的鎖。
locked_mode為2,3,4不影響DML(insert,delete,update,select)操作,
但DDL(alter,drop等)操作會提示ora-00054錯誤。
有主外來鍵約束時 update / delete ... ; 可能會產生4,5的鎖。

DDL語句時是6的鎖。