ORACLE基礎之oracle鎖（oracle lock mode）詳解

ORACLE裡鎖有以下幾種模式:

0：none
1：null 空
2：Row-S 行共享(RS)：共享表鎖，sub share
3：Row-X 行獨佔(RX)：用於行的修改，sub exclusive
4：Share 共享鎖(S)：阻止其他DML操作，share
5：S/Row-X 共享行獨佔(SRX)：阻止其他事務操作，share/sub exclusive
6：exclusive 獨佔(X)：獨立訪問使用，exclusive

1.oracle提供的所型別可以根據v$lock_type 中的type來查詢,我們平時接觸的最多的是兩種

程式碼如下

select * from v$lock_type where type in ('TM','TX')

檢視描述,可以大概的得知兩種鎖的資訊.TM是同步訪問物件用的,TX是和事務有關的.

3.要知道的有2個概念:

(1).鎖定資料的鎖,也就是行級鎖,只有一種:排它鎖 exclusive (ROW)
(2).鎖定表上的鎖,即鎖定後設資料的鎖 metadata(table),一共有5種:

2 RS: row share
3 RX: row exclusive
4 S: share
5 SRX: share row exclusive
6 X: exclusive

4.根據oracle聯機文件的concepts的

我們可以從這個表找出至少2個東西,.第一是每種資料庫操作都對應的是什麼樣的鎖(參考中間那一列),第二是每種鎖之間,如果遇到之後是否會產生衝突,所謂衝突就是是否會使當前的資料庫操作夯住.其中Y*,表示如果兩個操作鎖定的是同一行,那麼就會有衝突,後操作的會等待前一個操作完成之後再完成,否則會一直夯在那兒;如果不為同一行,那麼則不會衝突,後操作的不會等待.舉一個例子來說明:假設現在A操作為:對id=1的記錄進行update,而B操作為:對id=2的記錄進行刪除,根據表格說明,在A上操作時在TM級別的鎖會是RX,TX級別只有一個是X,在B上會有一個TM級別的鎖會是RX,TX級別只有一個X,而根據表格說明,當RX遇到RX的時候,如果2個操作非同一條記錄,那麼則不會衝突,故AB兩個操作均會按照各自的先加一個TM鎖,再加一個TX鎖,再順利執行各自的操作,不會夯住。如果將B操作的記錄id換位1,那麼兩個操作記錄為同一條記錄,在TM鎖上會表現出衝突,所以B操作就會等待A操作完成提交後(也就是A的TX鎖釋放了後),B再相應的生成一個TX鎖和一個TM鎖再完成操作,否則的話會一直夯住,等待A釋放TX鎖.

5.常用的動態效能檢視:

select * from v$lock_type where type in ('TM','TX');

程式碼如下

select * from v$lock;
select * from v$transaction;

重點說明一下v$lock檢視:

先設定一個場景:在session A中對一個表的記錄進行更新,更新完後並不提交,在session B中對改表的同一條記錄進行刪除

程式碼如下

Session A:
SQL> create table ttt as select * from dba_objects where rownum<=10;

表已建立。
程式碼如下

SQL> update ttt set object_name='TEST' where object_id=20;

已更新 1 行。
程式碼如下

SQL>
Session B:
SQL> delete from ttt where object_id=20;

此時因為A並沒有提交,所以B會一直夯住再開啟一個session C,查詢相關的v$lock檢視

程式碼如下

SQL> select * from v$lock where type in ('TM','TX');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
CA4244F4 CA424508        147 TX     393242        563          0          6        270          0
C8E7F704 C8E7F71C        140 TM      55156          0          3          0        301          0
C8E7F7C8 C8E7F7E0        147 TM      55156          0          3          0        270          0
C8ED3C38 C8ED3D54        140 TX     393242        563          6          0        301          1

可以很清晰的看到2個sid產生了鎖.對於sid為140的session,產生了一個TM和一個TX鎖,TM的鎖模式(LMODE)=3,(3為RX: row exclusive和表格對照相符,當操作為update的時候,產生RX鎖);對於sid為147的session,也產生了一個TM和一個TX鎖,TM的鎖模式(LMODE)=3 (3為RX: row exclusive和表格對照相符,當操作為delete的時候,產生RX鎖),而TX的鎖模式(LMODE)=0,代表正在等待一個鎖.從v$lock_type的定義上面,我們也可以看出,type為TM的鎖,ID1表示的是object_id,查詢dba_objects可以很容易的得出鎖定的物件是TTT這個obj. 從最後一列BLOCK(該block並不代表塊,而是代表阻塞)=1也可以看出,sid=140的session在生成TX鎖之後,發現之後一個的操作也是修改該條記錄,所以BLOCK+1,表示阻塞其他的操作同時操作這條記錄.

另外,此時,查詢select * from v$transaction;檢視,也可以得到相關聯的資訊

我們從v$lock_type對TX鎖的描述,可以知道TX是和事務有關的.因此檢視之前v$lock上TX鎖的相關資訊,可以看到ADDR的值與v$transaction的值是一樣的.甚至可以根據ID1的值來計算,鎖定的是哪個段:根據TX的ID1去除以和取餘2的16次方,得到相關資訊:

程式碼如下

SQL> select 393242 / 65536, mod(393242, 65536) from dual;

393242/65536 MOD(393242,65536)
------------ -----------------
  6.00039673                26

可以神奇的發現和v$transaction中的XIDUSN和XIDSLOT對應上了!

6.最後補充一個操作,建立索引的時候會生成的鎖操作:

首先將ttt表插入很多資料

程式碼如下

SQL> insert into ttt select * from dba_objects;
SQL> commit;

提交完成。

程式碼如下

SQL> select count(*) from ttt;

  COUNT(*)
----------
   1739045

接著再在改表上建立一個索引

程式碼如下

SQL> create index idx_ttt on ttt(object_id);

建立索引的同時,查詢v$lock表

可以發現在建立索引的會生成2個TM鎖,鎖類別分別為4和3,我們查詢這2個TM分別鎖定的是什麼物件:

根據查詢結果發現lmode=4的object_id為55160的物件對應的是TTT這個表,LMODE=4對應的是TM的S鎖

總結

數字越大鎖級別越高, 影響的操作越多。

1級鎖有：Select，有時會在v$locked_object出現。

2級鎖有：Select for update,Lock For Update,Lock Row Share
select for update當對話使用for update子串開啟一個遊標時，所有返回集中的資料行都將處於行級(Row-X)獨佔式鎖定，其他物件只能查詢這些資料行，不能進行update、delete或select for update操作。

3級鎖有：Insert, Update, Delete, Lock Row Exclusive
沒有commit之前插入同樣的一條記錄會沒有反應, 因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。
4級鎖有：Create Index, Lock Share
locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會提示ora-00054錯誤。

00054, 00000, "resource busy and acquire with NOWAIT specified" 
// *Cause: Resource interested is busy. 
// *Action: Retry if necessary. 

5級鎖有：Lock Share Row Exclusive
具體來講有主外來鍵約束時update / delete ... ; 可能會產生4,5的鎖。

6級鎖有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

---

常用查詢DB中鎖的SQL語句：

select b.owner,b.object_name,l.session_id,l.locked_mode 
from v$locked_object l, dba_objects b 
where b.object_id=l.object_id 

select t2.username,t2.sid,t2.serial#,t2.logon_time  
from v$locked_object t1,v$session t2  
where t1.session_id=t2.sid order by t2.logon_time 

如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：

alter system kill session 'sid,serial#'; 

如果出現了鎖的問題, 某個DML操作可能等待很久沒有反應。

當你採用的是直接連線資料庫的方式，也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num來終止使用者連線，因為一個使用者程式可能產生一個以上的鎖, 殺OS程式並不能徹底清除鎖的問題。