Oracle多粒度封鎖機制研究二(zt)

1.1.1 v$locked_object檢視

v$locked_object檢視列出當前系統中哪些物件正被鎖定，其主要欄位說明如下：

欄位名稱	型別	說明
XIDUSN	NUMBER	回滾段號；
XIDSLOT	NUMBER	槽號；
XIDSQN	NUMBER	序列號；
OBJECT_ID	NUMBER	被鎖物件標識；
SESSION_ID	NUMBER	持有鎖的會話（SESSION）標識；
ORACLE_USERNAME	VARCHAR2（30）	持有該鎖的使用者的Oracle使用者名稱；
OS_USER_NAME	VARCHAR2（15）	持有該鎖的使用者的作業系統使用者名稱；
PROCESS	VARCHAR2（9）	作業系統的程式號；
LOCKED_MODE	NUMBER	鎖模式，取值同表三中的LMODE；

表五：v$locked_object檢視欄位說明

1.2 監控指令碼

根據上述系統檢視，可以編制指令碼來監控資料庫中鎖的狀況。

1.2.1 showlock.sql

第一個指令碼showlock.sql，該指令碼通過連線v$locked_object與all_objects兩檢視，顯示哪些物件被哪些會話鎖住：

/* showlock.sql */

column o_name format a10

column lock_type format a20

column object_name format a15

select rpad(oracle_username,10) o_name,session_id sid,

decode(locked_mode,0,'None',1,'Null',2,'Row share',

3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,

object_name ,xidusn,xidslot,xidsqn

from v$locked_object,all_objects

where v$locked_object.object_id=all_objects.object_id;

1.2.2 showalllock.sql

第二個指令碼showalllock.sql，該指令碼主要顯示當前所有TM、TX鎖的資訊；

/* showalllock.sql */

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_type,request,ctime,block

from v$lock

where TYPE IN('TX','TM');

2 Oracle 多粒度封鎖機制示例

以下示例均執行在Oracle 8.1.7上，資料庫版本不同，其輸出結果也可能有所不同。首先建立3個會話，其中兩個（以下用SESS#1、SESS#2表示）以SCOTT使用者連入資料庫，以操作Oracle提供的示例表（DEPT、EMP）；另一個（以下用SESS#3表示）以SYS使用者連入資料庫，用於監控；

2.1 操作同一行資料引發的鎖阻塞

SESS#1：

SQL> select * from dept for update;

DEPTNO DNAME LOC

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

10 account 70

20 research 8

30 sales 8

40 operations 8

SESS#3：

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 17 Row share DEPT 8 2 5861

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

17 TX 524290 5861 Exclusive 0 761 0

17 TM 32970 0 Row share 0 761 0

如第一個指令碼showlock所示，執行完SELECT…FOR UPDATE 語句後， SESS#1（SID為17）在DEPT表上獲得Row share鎖；如第二個指令碼showalllock所示，SESS#1獲得的TX鎖為Exclusive，這些都驗證了上面的理論分析。另外，我們可以將TX鎖的ID1按如下方法進行分解：

SQL> select trunc(524290/65536) xidusn,mod(524290,65536) xidslot from dual;

XIDUSN XIDSLOT

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

8 2

分解結果與第一個指令碼直接查出來的XIDUSN與XIDSLOT相同，而TX鎖的ID2（5861）與XIDSQN相同，可見當LOCK TYPE為TX時，ID1實際上是該事務所佔用的回滾段段號與事務表中的槽（SLOT）號的組合，ID2即為該槽被重用的次數，而這三個值實際上可以唯一地標識一個事務，即TRANSACTION ID，這三個值從系統表v$transaction中也可查到。

另外， DEPT表中有4條記錄被鎖定，但TX鎖只有1個，這也與上面的理論分析一致。繼續進行操作：

SESS#2：

SQL> update dept set loc=loc where deptno=20;

該更新語句被阻塞，此時再檢視系統的鎖情況：

SESS#3：

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 17 Row share DEPT 8 2 5861

SCOTT 19 Row Exclusive DEPT 0 0 0

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

17 TX 524290 5861 Exclusive 0 3462 1

17 TM 32970 0 Row share 0 3462 0

19 TM 32970 0 Row Exclusive 0 7 0

19 TX 524290 5861 None 6 7 0

在DEPT表上除了SESS#1（SID為17）持有Row share鎖外，又增加了SESS#2（SID為19）持有的Row Exclusive鎖，但還沒有為SESS#2分配回滾段（XIDUSN、XIDSLOT、XIDSQN的值均為0）；而從第二個指令碼看到，SESS#2的TX鎖的LOCK_TYPE為None，其申請的鎖型別（REQUEST）為6（即Exclusive），而其ID1、ID2的值與SESS#1所持有的TX鎖的ID1、ID2相同，SESS#1的TX鎖的阻塞域（BLOCK）為1，這就說明了由於SESS#1持有的TX鎖，阻塞了SESS#2的更新操作（SESS#2所更新的行與SESS#1所鎖定的行相沖突）。還可以看出，SESS#2先申請表級的TM鎖，後申請行（事務）級的TX鎖，這也與前面的理論分析一致。

下面，將SESS#1的事務進行回滾，解除對SESS#2的阻塞，再對系統進行監控。

SESS#3：

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 19 Row Exclusive DEPT 2 10 5803

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

19 TX 131082 5803 Exclusive 0 157 0

19 TM 32970 0 Row Exclusive 0 333 0

可以看到，SESS#1的事務所持有的鎖已經釋放，系統為SESS#2的事務分配了回滾段，而其TX鎖也已經獲得，並且ID1、ID2是其真正的Transaction ID。再將會話2的事務進行回滾。

SESS#2：

SQL> rollback;

Rollback complete.

檢查系統鎖的情況：

SESS#3：

SQL> @showlock

no rows selected

SQL> @showalllock

no rows selected

可以看到，TM與TX鎖已全部被釋放。

2.2 實體完整性引發的鎖阻塞

DEPT（部門）表有如下欄位DEPTNO（部門編號），DNAME（部門名稱），LOC（部門位置）；其中DEPTNO列為主鍵。

SESS#1

SQL> INSERT INTO DEPT(DEPTNO) VALUES(50);

1 row created.

SESS#3

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 7 Row Exclusive DEPT 6 88 29

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TX 393304 29 Exclusive 0 6 0

7 TM 3574 0 Row Exclusive 0 6 0

向DEPT表中插入一條DEPTNO為50的記錄後，SESS#1（SID為7）在DEPT表上獲得Row Exclusive鎖，並且由於進行了資料插入，該事務被分配了回滾段，獲得TX鎖。

SESS#2

INSERT INTO DEPT(DEPTNO) VALUES(50);

這時，SESS#2（SID為8）也向DEPT表中插入一條DEPTNO為50的記錄，該語句被阻塞，檢查鎖情況：

SESS#3

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 8 Row Exclusive DEPT 7 75 30

SCOTT 7 Row Exclusive DEPT 6 88 29

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TX 393304 29 Exclusive 0 92 1

7 TM 3574 0 Row Exclusive 0 92 0

8 TX 458827 30 Exclusive 0 22 0

8 TM 3574 0 Row Exclusive 0 22 0

8 TX 393304 29 None 4 22 0

SESS#2在DEPT表上也獲得了Row Exclusive鎖，同樣也獲得了回滾段的分配，得到TX鎖，但是由於其插入的記錄與SESS#1插入的記錄的DEPTNO均為50，該語句成功與否取決於SESS#1的事務是提交還是回滾，所以SESS#2被阻塞，表現為SESS#2以Share方式（REQUEST=4）等待SESS#1所持有的TX鎖的釋放。

這時，如果SESS#1進行回滾：

SESS#1

SQL> ROLLBACK;

Rollback complete.

SESS#2

1 row created.

SESS#3

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 8 Row Exclusive DEPT 7 75 30

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

8 TX 458827 30 Exclusive 0 136 0

8 TM 3574 0 Row Exclusive 0 136 0

SESS#2的阻塞將被解除，SESS#2只持有原先已有的TM與TX鎖，其等待的TX鎖（由SESS#1持有）也消失了。

如果SESS#1提交而不是回滾，在SESS#2上將會出現如下提示：

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_DEPT) violated錯誤。

即發生主鍵衝突，SESS#1與SESS#2的所有鎖資源均被釋放。

2.3 參照完整性引發的鎖阻塞

EMP（員工）表有如下欄位：EMPNO（員工編號），ENAME（員工姓名），DEPTNO（員工所在部門編號），其中DEPTNO列為外來鍵，其父表為DEPT。

SESS#1

SQL> insert into dept(deptno) values(60);

1 row created.

SESS#3

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 7 Row Exclusive DEPT 2 6 33

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TX 131078 33 Exclusive 0 148 0

7 TM 3574 0 Row Exclusive 0 148 0

SESS#1（SID為7）在DEPT表中先插入一條DEPTNO為60的記錄，SESS#1獲得了DEPT表上的Row Exclusive鎖，及一個TX鎖。

SESS#2

insert into emp(empno,deptno) values(2000,60)；

被阻塞

SESS#3

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 7 Row Exclusive DEPT 2 6 33

SCOTT 8 Row Exclusive EMP 3 20 31

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TX 131078 33 Exclusive 0 228 1

7 TM 3574 0 Row Exclusive 0 228 0

8 TX 196628 31 Exclusive 0 9 0

8 TM 3576 0 Row Exclusive 0 9 0

8 TX 131078 33 None 4 9 0

SESS#2（SID為8）向EMP表中出入一條新記錄，該記錄DEPT值為60（即SESS#1剛插入，但還未提交的記錄的DEPTNO值），SESS#2獲得了EMP表上的Row Exclusive鎖，另外由於插入記錄，還分配了回滾段及一個TX鎖，但由於SESS#2的插入語句是否成功取決於SESS#1的事務是否進行提交，所以它被阻塞，表現為SESS#2以Share（REQUEST=4）方式等待SESS#1釋放其持有的TX鎖。這時SESS#1如果提交，SESS#2的插入也將執行成功，而如果SESS#1回滾，由於不符合參照完整性，SESS#2將報錯：

SESS#2

insert into emp(empno,deptno) values(2000,60)

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not

Found

SESS#2持有的鎖也被全部釋放。

2.4 外來鍵未加索引引發的鎖阻塞

EMP表上的DEPTNO列為外來鍵，但沒有在該列上建索引。

SESS#1

SQL> delete emp where 0=1;

0 rows deleted.

SESS#3：

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 7 Row Exclusive EMP 0 0 0

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TM 3576 0 Row Exclusive 0 10 0

首先SESS#1（SID為7）做了一個刪除操作，但由於條件（0=1）為永假，所以實際上並沒有一行被刪除，從監控指令碼可以看出SESS#1在EMP表上獲得Row Exclusive鎖，但由於沒有實際的行被刪除，所以並沒有TX鎖，也沒有為SESS#1分配回滾段。

SESS#2：

SQL> delete dept where 0=1;

該語句雖然也不會刪除實際資料，但卻被阻塞，檢視系統的鎖情況：

SESS#3：

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 8 None EMP 0 0 0

SCOTT 7 Row Exclusive EMP 0 0 0

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TM 3576 0 Row Exclusive 0 31 1

8 TM 3576 0 None 4 12 0

SESS#2申請在EMP表上加SHARE鎖（REQUEST=4），但該申請被SESS#1阻塞，因為SESS#1已經在EMP表上獲得了Row Exclusive鎖，與SHARE鎖不相容。

下面我們對SESS#1進行回滾後，再進行監控。

SESS#3：

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 8 Share EMP 0 0 0

SCOTT 8 Row Exclusive DEPT 0 0 0

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

8 TM 3574 0 Row Exclusive 0 16 0

8 TM 3576 0 Share 0 16 0

SESS#2在EMP表上獲得Share鎖後，又在DEPT表上獲得Row Exclusive鎖，由於沒有實際的行被修改，SESS#2並沒有獲得TX鎖。

在Oracle8中，如果子表的外來鍵上沒有加索引，當在父表上刪除記錄時，會先在子表上申請獲得Share鎖，之後再在父表上申請Row Exclusive鎖。由於表級Share鎖的封鎖粒度較大，所以容易引起阻塞，從而造成效能問題。

當在外來鍵上建立索引後，在父表上刪除資料將不再對子表上加Share鎖，如下所示：

SESS#1：

SQL> create index i_emp_deptno on emp(deptno);

Index created.

SQL> delete dept where 0=1;

0 rows deleted.

SQL>

SQL> @showlock

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

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

SCOTT 7 Row Exclusive DEPT 0 0 0

SQL> @showalllock

SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

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

7 TM 3574 0 Row Exclusive 0 9 0

可以看到，在EMP表DEPTNO列上建立索引後，在DEPT表上執行DELETE操作，不再要求在EMP表上加Share鎖，只是在DEPT表上加Row Exclusive鎖，封鎖的粒度減小，引起阻塞的可能性也減小。

3 Oracle 多粒度封鎖機制總結

Oracle通過具有意向鎖的多粒度封鎖機制進行併發控制，保證資料的一致性。其DML鎖（資料鎖）分為兩個層次（粒度）：即表級和行級。通常的DML操作在表級獲得的只是意向鎖（RS或RX），其真正的封鎖粒度還是在行級；另外，在Oracle資料庫中，單純地讀資料（SELECT）並不加鎖，這些都極大地提高了系統的併發程度。

在支援高併發度的同時，Oracle利用意向鎖及資料行上加鎖標誌位等設計技巧，減小了Oracle維護行級鎖的開銷，使其在資料庫併發控制方面有著明顯的優勢。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242518/，如需轉載，請註明出處，否則將追究法律責任。