檢視oracle鎖相關資訊

oracle鎖的模式

  鎖模式                                  鎖簡稱                 鎖程式碼 

 Row Exclusive                     RX                              3
 Row Shared                         RS                             2
 Share                                     S                                4
 Exclusive                               X                                6
 Share Row Exclusive         SRX                           5
 NULL                                     N/A                            0或者1

1、透過V$LOCK、V$TRANSACTION、DBA_OBJECTS來檢視鎖的資訊、鎖定的物件、鎖定的事務相關資訊

select sid,type,id1,id2,
  decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 
  decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
  from v$lock
  where sid=???（注：此sql透過上面的鎖模式、鎖程式碼對應資訊，使用decode來將v$lock中的鎖程式碼資訊顯示為對應的鎖模式，便於檢視分析）

其中：SID  session的ID號
            TYPE  鎖定的級別，主要關注TX和TM
            LMODE 已經獲得的鎖模式
            REQUEST 正在請求的鎖模式           
            BLOCK    是否阻止了其他使用者獲得鎖定，不等於0說明有堵塞

如：

sessionA：

> select distinct sid from v$mystat;

       SID
----------
       69

> update emp set ename='a' where empno=7900;

已更新 1 行。

在sessionB檢視鎖的情況：

> select sid,type,id1,id2,
  decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 
  decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
  from v$lock
  where sid=69

       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
----------  --      ----------         ---------- ------------------- ------------------- ----------
        69 TM     317492          0 Row Exclusive       None                         0
        69 TX     655381       7756 Exclusive           None                         0

對於TM鎖來說，ID1表示被鎖定的物件的object_ID，ID2始終為0

故：可以透過select object_name from dba_objects where object_id=ID1;來檢視鎖對應的物件

> select object_name from dba_objects where object_id=317492;

OBJECT_NAME
------------------------------------------------------------------------

EMP

對於TX鎖來說，ID1表示事務使用的回滾段編號以及在事務表中對應的記錄編號，ID2表示該記錄編號被重用的次數（wrap）

那麼，拆解ID1即可得到當前鎖對應的事務資訊，如：

select trunc(655381/power(2,16)) as undo_blk#,bitand(655381 ,to_number('ffff','xxxx')) +
 0 as slot#  from dual;

 UNDO_BLK#      SLOT#
---------- ----------
        10         21

說明：UNDO_BLK# 當前事務使用的回滾段的編號

      SLOT#    事務槽號

並且與v$transaction中的資訊是一樣的，如：

select xidusn,xidslot,xidsqn,status from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN STATUS
---------- ---------- ---------- ----------------
        10         21       7756 ACTIVE

       SID
----------
       134

> update emp set ename='a' where empno=7900;

Session C:
>  select distinct sid from v$mystat;

       SID
----------
       198

> update emp set ename='a' where empno=7900;

此時，查詢鎖請求佇列

select sid,type,
 decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
 request_mode
 from v$enqueue_lock
 where sid in(134,198)
/
      SID TY REQUEST_MODE
--------- -- -------------------
       134 TX Exclusive
       198 TX Exclusive
由此看到，session B、session C兩個會話在請求TX鎖，在SESSION A的鎖釋放之前，這2個會話將一直等待

可以透過sql檢視等待時間等資訊，如：

select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,
decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
c.ctime time_waited
from   v$lock b, v$enqueue_lock c, v$session a
where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1
order by time_held, time_waited;

       SID
----------
        69

> update emp set ename='a' where empno=7900;

已更新 1 行。

> roll
回退已完成。

select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,
decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
c.ctime time_waited
from   v$lock b, v$enqueue_lock c, v$session a
where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1
order by time_held, time_waited;

BLOCKER_SID	SERIAL#	BLOCKER_USERNAME	TYPE	LOCK_MODE	TIME_HELD	WAITER_SID	REQUEST_MODE	TIME_WAITED
134	3239	SCOTT	TX	Exclusive	102	198	Exclusive	102

 此時我們看到是134導致198等待{134等待的時間長、故會先獲得鎖定}

 

3、在資料庫中透過V$PARAMETER來檢視可以獲得的TX鎖定和TM鎖定的總個數，可以獲得的TX鎖定的總個數由初始化引數transactions決定，而可以獲得的TM鎖定的個數則由初始化引數dml_locks決定

如：檢視當前庫中允許的TX和TM鎖定的最大個數？

> select name,value from v$parameter where name in('transactions','dml_locks');

        NAME                           VALUE
------------------------------ ------------------------------
dml_locks                      1088
transactions                   272

可以透過v$resource_limit檢視來檢視詳細的資訊

如：

select resource_name as "NAME",current_utilization as "CURRENT",max_utilization as "MAX_
H",initial_allocation as "MAX"
from v$resource_limit
where resource_name in('transactions','dml_locks');
NAME                    CURRENT MAX_H MAX
------------ ---------- ----- --------------------
dml_locks                  2     7       1088
transactions               2     22      272

其中：CURRENT 指當前庫中正在存在的TX和TM個數；MAX_H 指當前庫中TX和TM曾經最大個數值；MAX 庫中允許TX和TM達到的最大個數值 

只要max_h沒有等於max就表明夠用；當然，透過v$resource_limit檢視還可以檢視其它相關資源的情況，以便於確認庫中的某些資源是否需要修改其限制，如：select resource_name as "NAME",current_utilization as "CURRENT",max_utilization as "MAX_H",initial_allocation as "MAX" from v$resource_limit;