oracle 資料庫的鎖

oracle 資料庫中的鎖可以分為三類，TX，TM和user defined locks(UL).這裡只介紹TX和TM。

Row Locks(TX)

行級鎖也叫做TX 鎖。該種鎖放置在一個表中的行級上。當在進行insert,updatedetele,merge 或者select ... for update 的時候事務會請求鎖住相應的行。當顯式或者隱式發出commit，rollback 的時候才會解除事務鎖定的行。行級鎖其實是一種佇列機制，用來防止兩個事務同時修改相同的行。oracle 資料庫總是互斥的鎖住需要修改的行，直到事務終結（commit 或者rollback）。行級鎖是粒度最小的鎖，所以能夠提高資料庫系統的併發性和吞吐量。

如果事務請求鎖定表中的某些行，事務也需要在該表上加一個鎖。這個表級鎖的目的是防止衝突的DDL 操作，DDL操作將會影響到表的結構，從而影響當前正在對該表進行操作的事務。行級鎖TX和表級鎖TM同屬於oracle DML鎖，都是由oracle主動根據事務的需要新增的。行級鎖的資訊是儲存在oracle 資料庫 block header中的。通常在表級有兩種鎖模式：X 鎖也就是所謂的互斥鎖。 S 鎖也就是所謂的共享鎖。但是在oracle 中在表級有7 種鎖模式，null和沒有鎖也算是鎖模式。所以oracle

資料庫為了保證在併發情況資料的一致性和完整性所實施的鎖機制是比較複雜的。

X 鎖：用於互斥的訪問資源。同時阻止其他會話在相同的資源上放置S 鎖或者 X鎖。

S 鎖：使用者共享的訪問資源。可以允許多個會話在同一個資源上面放置多個S 鎖，但是不允許任何會話在加有S 鎖的資源上面放置 X 鎖。

因此當我們需要對資料進行修改的時候我們需要一個X 鎖，當我們需要對讀取資料同時並且保證沒有人在併發的修改資料我們需要一個S 鎖。在這種只有S和X鎖的情況下當一個會話修改了資料我們必須等到事務提交或者回滾以後才能查詢，這是可以選擇等待或者撤銷讀操作。而oracle 對鎖的實現則靈活很多。

oracle 資料庫中表級鎖的模式如下：

1、Row Exclusive Table Lock (RX)也稱作subexclusive table lock (SX)，rows-X.該鎖的含義是在（sub）行級放置一個X鎖。

通常表明持有這種鎖的事務是已經更新了表中的相應記錄或者是由於select ... for update引起的。SX 鎖允許其他事務進行查詢，插入，更新，刪除，或者併發的鎖住表中的記錄。因此SX鎖允許多個事務同時在相同的表上獲得SX 和SS 鎖。注：這裡的允許是由條件的，不能是已經被RX的行。

SQL> select sid from V$mystat where rownum = 1;

SID

----------

1

#該會話標記為session1.

SQL> select sid,type,id1,id2,lmode

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE

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

1 AE 100 0 4

使用select ... for update 語句根據where 子句鎖定相應的行。

SQL> select * from hr.employees

2 where employee_id < 105

3 for update;

SQL> select sid,type,id1,id2,lmode

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE

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

1 AE 100 0 4

1 TM 67516 0 3

1 TX 589849 1083 6

可見在發出select ...for update 語句以後oracle 在表employees 上放置了一個TM為RX的鎖。表示的含義是select ...for update 中的記錄加了一個X鎖。在另外一個會話中（session2）我們發出相同的select ... for update 語句。同時在session1 中查詢v$lock。

SQL> select sid from V$mystat where rownum = 1;

SID

----------

39

#該會話標記為session2.

SQL> select * from hr.employees

2 where employee_id < 105

3 for update;

^Cselect * from hr.employees

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

該操作會被阻塞，所以一直等待，我這裡把它cancel 了。

在session2 中發出select ...for update 的時候，session 2 中的查詢。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

39 AE 100 0 4 0 0

39 TX 589849 1083 0 6 0

39 TM 67516 0 3 0 0

1 TM 67516 0 3 0 0

1 TX 589849 1083 6 0 1

6 rows selected.

我們可以從查詢中看到session2 請求一個行級的互斥鎖（X）。並且session 1阻塞了一個會話，這個會話就是 session2，因為session2 請求的行級的X鎖已經被sesion2 持有了，所以session2 不得不等待。

注：不帶for update 的select語句是在執行的時候oracle 不會新增任何的鎖。

2、Row Share （RS），也稱作subshare table lock(SS)，row-S.該鎖的含義是在(sub)行級上放置一個S鎖。在oracle 9i以前 select ...for update 語句使用的就是這種鎖，這種鎖也可以達到查詢資料的時候不允許併發的修改。

3、update,insert,delete 在表級放置的也都是RX 鎖，也就是lock mode 等於3的那種鎖。這個查詢的目的是說明初始化環境。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

39 AE 100 0 4 0 0

上面的執行的select ... for update 語句我已經rollback了。

夏天到了公司給員工加高溫補貼。不提交。

SQL> update employees

2 set salary = salary + 200

3 where employee_id = 100;

1 row updated.

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

1 TM 67516 0 3 0 0

1 TX 524298 1353 6 0 0

把上面的操作回滾掉。建立employees 的副本，用來執行detele 和 insert 操作。

SQL> create table emp as select * from employees;

Table created.

檢視一下初始化環境。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

現在假如employee_id =145 的員工辭職不幹了，hr要把他的資料資訊從emp表中刪除。不提交。

SQL> delete from emp

2 where employee_id = 145;

1 row deleted.

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

1 TM 69469 0 3 0 0

1 TX 196625 1146 6 0 0

可見detele 操作在在表級加的也是RX鎖，因為要互斥使用資源，所有需要加X鎖。現在假如來了一個新員工，hr要往emp 表中插入必要的資訊。不提交。

SQL> insert into emp(employee_id,last_name,email,hire_date,job_id)

2 values(321,'zhangfei','12345678@qq.com',sysdate,'IT_sup');

1 row created.

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

1 TM 69469 0 3 0 0

1 TX 327692 1161 6 0 0

查詢的結果和沒有執行detele 操作的時候是相同的，這可以證明一下兩點：oracle 實施餓鎖機制是靈活的，你寫你的我寫我的，雖然是同一張表，但是只要是不同的列，是不會相互衝突或者干擾的。因為X鎖放在了行級。這樣有利於提高資料系統的併發性。

把上面兩個事務都回滾掉。再次確認初始化環境。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

4、Share TableLock（S）。

持有這種鎖的事務允許其他事務對錶進行查詢（不允許select...for update 方式）。只有當一個事務持有S 鎖的時候才允許更新。因為多個事務可能併發的持有S 鎖。持有這種鎖不足以保證事務能夠修改表中的記錄。我們顯式的發出對emp 表的一個S 鎖定操作。

SQL> lock table emp in share mode;

Table(s) Locked.

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

1 TM 69469 0 4 0 0

由查詢可以知道我們知道oracle在表emp 上放置了一個lock mode 為4 的鎖，也就是share tablelock 鎖。其他會話就不能對該表新增任何X 鎖了。在session 2 中發出下面delete 操作。

SQL> delete from emp;

該操作會被阻塞在，那邊一直等待。在session1 中進行查詢會話相關的鎖資訊。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

39 AE 100 0 4 0 0

39 TM 69469 0 0 3 0

1 TM 69469 0 4 0 1

我們可以知道session 2 想在表上加一個lmode=3 的RX 鎖。但是因為session 1已經把整個表都share lock 了，所以這種互斥鎖新增不了。下面我撤銷session 2

中的delete 操作。在繼續試驗。

SQL> delete from emp;

^Cdelete from emp

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

在session 2 中往emp 在家一把S 鎖是OK的。

SQL> lock table emp in share mode;

Table(s) Locked.

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

39 AE 100 0 4 0 0

39 TM 69469 0 4 0 0

1 TM 69469 0 4 0 0

在session 1 中發出如下的update 語句。

SQL> update emp

2 set salary = salary + 200

3 where employee_id = 100;

會一直阻塞在那邊，因為在emp 上有兩個S鎖。在session 2中進行如下的查詢。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

39 AE 100 0 4 0 0

39 TM 69469 0 4 0 1

1 TM 69469 0 4 5 1

我們可以知道 session 1 在請求一個 lmode 為 5 的鎖，但是被該會話自身和session 2 所阻塞。現在釋放掉session 鎖持有的S 鎖。我們可以檢視到session 1 中的update 語句執行成功。

SQL> update emp

2 set salary = salary + 200

3 where employee_id = 100;

1 row updated.

我們把上述的操作都回滾掉並驗證下初始化環境，繼續我們是實驗。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

39 AE 100 0 4 0 0

5、Exclusive Table Lock (X)。

這種鎖是限制性最嚴格的，不允許其他事務執行任何型別的DML操作或者在表上放置任何型別的鎖。當然單純的select 語句還是允許的因為select 語句不需要任何的鎖。可以顯式的給一張表加一個S 鎖，也可以顯式的給一張表加一個X 鎖。

SQL> lock table emp in exclusive mode;

Table(s) Locked.

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

1 TM 69469 0 6 0 0

剛才我們執行的lock table 語句的結果就是在emp 表中放置了一個lmode =6 的鎖，即X 鎖。雖然emp 被加上了X 鎖但是單純的select 查詢還是可以的。

SQL> select last_name from emp

2 where employee_id = 100;

LAST_NAME

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

test

注：這個test 是我以前對employees 所做的一個更新，所以成了現在我們看到的這個樣子。

接下來在session 2 中執行DML 操作。來看看會發生什麼情況。

SQL> delete from emp

2 where employee_id = 145;

在session 2 中執行的這條 delete 操作會一直阻塞在那邊，因為整張表已經被上了X 鎖。下面在session 1 中進行如下的查詢。

SQL> select sid,type,id1,id2,lmode,request,block

2 from V$lock

3 where sid = 1;

where sid in (1,39);

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 AE 100 0 4 0 0

1 TM 69469 0 6 0 1

session 2 會話的資訊沒有出現在查詢結果中，但是我們可以看到session 1 阻塞了一個會話，這個會話就是session 2.我現在回滾掉sesion 1 中的lock table 操作，釋放session 1所持有的 X 鎖。我們可以看到session 2 中的那條原本正在等待的delete 資訊馬上返回執行成功。

SQL> delete from emp

2 where employee_id = 145;

1 row deleted.

6、Share Row Exclusive Table Lock (SRX)。

這種鎖，也叫做share-subexclusive table lock (SSX)。是限制性比S 鎖更嚴格的一種鎖。在一張表中一次只能允許一個事務持有SSX 鎖。持有SSX 鎖的事務允許別的事務進行查詢該表（不允許select...for update 方式）。並且持有該鎖的會話不更新該表。

oracle 資料庫的鎖

相關文章