Oracle基礎 鎖

標籤：

一、鎖

　　資料庫是一個多使用者使用的共用資源。當多個使用者並發地儲存資料時，資料庫中就會產生多個事務同時存取同一資料的情況。若對並行作業不加控制就可能會讀取和儲存不正確的資料，破壞資料庫的一致性。

　　鎖是實現資料庫並發控制的一個非常重要的技術。當事務在對某個資料對象進行操作前，先向系統發出請求，對其加鎖。加鎖後事務就對該資料對象有了一定的控制，在該事務釋放鎖之前，其他的事務不能對此資料對象進行更新操作。

 

二、鎖機制

　　當兩個使用者希望持有對象的資源時就會發生死結，即兩個使用者相互等待對方釋放資源時，Oracle認為為產生死結，在這種情況下，將以犧牲一個使用者為代價，另一個使用者繼續執行，犧牲的使用者事務將復原。

　　 例如：

　　（1）使用者1對A表進行update操作，沒有提交。

　　（2）使用者2對B表進行update操作，沒有提交。

　　此時雙方不存在資源共用的問題。

　　（3）如果使用者2此時對A表進行update操作，則會發生阻塞，需要等待使用者1的事務結束。

　　（4）如果使用者1此時又對B表進行UPDATE操作，則會產生死結。

　　此時Oracle會選擇其中一個使用者進行復原，使另一個使用者繼續進行操作。在Oracle系統中自動探索死結，並選擇代價最小的，即完成工作量最少的事務予以撤銷，釋放該事務所擁有的資源，讓其他事務繼續執行下去。

　　

　　通過以下注意事項可以避免死結：

　　1、對於UPDATE和DELETE操作，應只鎖要改動的行，在完成修改後立即提交。

　　2、當多個事務正利用共用更新的方式進行更新，則不要使用共用鎖定，而應採用共用更新鎖定。

　　3、儘可能對一個表的操作的並發事務施加共用更新鎖定，從而可提高並行性。

　　4、在應用負荷較高的期間，不宜對基礎資料結構（表、索引、簇和視圖）進行修改。

 

三、Oracle鎖的分類

　　1、按使用者與系統劃分。

　　（1）自動鎖：當進行一項資料庫操作時，預設情況下，系統自動為此資料庫操作獲得所有有必要的鎖。自動鎖又分為DML鎖、DDL鎖、內部鎖和閂。

　　（2）顯示鎖：在某些情況下，需要使用者顯示的鎖定資料庫操作要用到的資料，使資料庫操作執行得更好。顯示鎖是使用者為資料庫物件設定的。

　　2、按鎖層級劃分。

　　（1）獨佔鎖定：當資料對象被加上排它鎖時，其他的事務不能對它讀取和修改；

　　（2）共用鎖定：加了共用鎖定的資料對象可以被其他事務讀取，但不能修改。

　　3、按操作劃分。　　

（1）DML lock（data locks，資料鎖）：用於保護資料的完整性；
（2）DDL lock（dictionary locks，字典鎖）：用於保護資料庫物件的結構（例如表、視圖、索引的結構定義）；
（3）Internal locks 和latches（內部鎖與閂）：保護內部資料庫結構；
（4）Distributed locks（分布式鎖）：用於OPS（並行伺服器）中；
（5）PCM locks（並行快取管理鎖）：用於OPS（並行伺服器）中。

　　

四、DML鎖

　　在Oracle中最主要的鎖是DML鎖（也可稱為data locks，資料鎖）。DML鎖的目的在於保證並發情況下的資料完整性。在Oracle資料庫中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TX鎖稱為事務鎖或行級鎖。

　　

　　1、TM鎖（表級鎖）

　　TM鎖（表級鎖）類型共有5種，分別稱為共用鎖定（S鎖）、排它鎖（X鎖）、行級共用鎖定（RS鎖）、行級排它鎖（RX鎖）、共用行級排它鎖（SRX鎖）

　　

表1 Oracle的TM鎖類型
鎖模式	鎖描述	解釋	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行級共用鎖定，其他對象只能查詢這些資料行	Select for update、Lock for update、Lock row share
3	SX(Row-X)	行級排它鎖，在提交前不允許做DML操作	Insert、Update、Delete、Lock row share
4	S(Share)	共用鎖定	Create index、Lock share
5	SSX(S/Row-X)	共用行級排它鎖	Lock share row exclusive
6	X(Exclusive)	排它鎖	Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

　　當事務獲得行鎖後，此事務也將自動獲得該行的表鎖（共用鎖定），以防止其他事務通過DDL語句影響記錄行的更新。事務也可以再進行過程中獲得共用鎖定和獨佔鎖定，只有當事務顯示使用LOCK TABLE 語句定義一個獨佔鎖定時，事務才會獲得表上的獨佔鎖定，也可以使用LOCK TABLE顯示定義一個標記的共用鎖定。

　　文法：

　　LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT];

　　lock_mode是鎖定的模式：

　　表級鎖的模式包括以下內容。　　

1）共用鎖定（Share Table Lock，S）：
加鎖文法：Lock Table TableName In Share Mode;
允許的操作：一個共用鎖定由一個事務控制，僅允許其它事務查詢被鎖定的表。一個有效共用鎖定明確地用Select … For update形式鎖定行，或執行Lock Table TableName In Share Mode文法鎖定整個表，不允許被其它事務更新。
禁止的操作：一個共用鎖定由一個事務來控制，防止其它事務更新該表或執行下面的語句：
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;

 

2）排它鎖（Exclusive Table Lock，X）：
排它鎖是在鎖機制中限制最多的一種鎖類型，允許加排它鎖的事務獨自控制對錶的寫入權限。
加鎖文法：Lock Table TableName In Exclusive Mode;
允許的操作：在一個表中只能有一個事務對該表實行排它鎖，排它鎖僅允許其它的事務查詢該表。
禁止的操作：擁有獨佔鎖定的事務禁止其它事務執行其它任何DML類型的語句或在該表上加任何其它類型的鎖。
定義排它鎖的文法：
LOCK TABLE TableName IN EXCLUSIVE MODE;

 

3）行級鎖（Row Share Table Lock，RS）：
一個行級鎖（有時稱為Subshare Table Lock，簡稱SS，子共用鎖定）需要該事務在被鎖定行的表上用update的形式加鎖。當有下面語句被執行的時候行級鎖自動加在操作的表上。
SELECT . . . FROM TableName. . . FOR UPDATE OF . . . ;
LOCK TABLE TableName IN ROW SHARE MODE;
行級鎖（Row Share Table Lock）在鎖類型中是限制最少的，也是在表的並發程度中使用程度最高的。
允許的操作：行級共用鎖定由一個事務控制，允許其它事務查詢、插入、更新、刪除或同時在同一張表上鎖定行。因此其它事務可以同時在同一張表上得到行級鎖、共用行級排它鎖、行級排它鎖、排它鎖。
禁止的操作：擁有行級鎖的事務不允許其它事務執行排它鎖，即：
Lock Table TableName In Exclusive Mode;

 

4.）行級排它鎖（Row Exclusive Table Lock，RX）：
行級排它鎖（亦稱為Subexclusive Table Lock，簡稱SX，子排它鎖）通常需要事務擁有的鎖在表上被更新一行或多行。當有下面語句被執行的時候行級排它鎖被加在操作的表上。
INSERT INTO TableName. . . ;
UPDATE TableName. . . ;
DELETE FROM TableName. . . ;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
行級排它鎖比行級鎖稍微多一些限制。
允許的操作：行級排它鎖由一個事務擁有允許其它事務執行查詢、修改、插入、刪除或同時在同一張表上鎖定行。執有行級排它鎖的事務允許其它事務在同一張表上同時得到共用鎖定和行級排它鎖。
禁止的操作：行級排它鎖由一個事務擁有防止其它事務手動鎖定表來排除其它事務的讀寫權。因此，其它事務不允許在同一張表上使用以下的語句來執行鎖事務。
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE

 

5）共用行級排它鎖（Share Row Exclusive Table Lock，SRX）：
共用行級排它鎖有時也稱共用子排它鎖（Share Subexclusive Table Lock，SSX），它比共用鎖定有更多限制。定義共用行級排它鎖的文法為：
Lock Table TableName In Share Row Exclusive Mode;
允許的操作：僅允許一個事務在某一時刻得到行級排它鎖。擁有行級排它鎖事務允許其它事務在被鎖定的表上執行查詢或使用Select … From TableName For update…來準確在鎖定行而不能更新行。
禁止的操作：擁有行級排它鎖的事務不允許其它事務有除共用鎖定外的其它形式的鎖加在同一張表上或更新該表。即下面的語句是不被允許的：
LOCK TABLE TableName IN SHARE MODE;
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN EXCLUSIVE MODE;

　　當Oracle資料庫發生TX鎖等待時，如果不及時處理常常會引起Oracle資料庫掛起，或導致死結的發生，產生ORA-60的錯誤。

 

　　2、TX鎖（事務鎖或行級鎖）

Oracle資料庫中一般使用行級鎖。

 

當Oracle檢測到死結產生時，中斷並復原死結相關語句的執行，報ORA-00060的錯誤並記錄在資料庫的記錄檔alertSID.log中。同時在user_dump_dest下產生了一個追蹤檔案，詳細描述死結的相關資訊。

 

在日常工作中，如果發現在記錄檔中記錄了ora-00060的錯誤資訊，則表明產生了死結。這時需要找到對應的追蹤檔案，根據追蹤檔案的資訊定位產生的原因。

 

表2 資料字典視圖說明
視圖名	描述	主要欄位說明
v$session	查詢會話的資訊和鎖的資訊。	sid,serial#：表示會話資訊。 program：表示會話的應用程式資訊。 row_wait_obj#：表示等待的對象，和dba_objects中的object_id相對應。
v$session_wait	查詢等待的會話資訊。	sid：表示持有鎖的會話資訊。 Seconds_in_wait：表示等待持續的時間資訊 Event：表示會話等待的事件。
v$lock	列出系統中的所有的鎖。	Sid：表示持有鎖的會話資訊。 Type：表示鎖的類型。值包括TM和TX等。 ID1：表示鎖的對象標識。 lmode,request：表示會話等待的鎖模式的信 息。用數字0－6表示，和表1相對應。
dba_locks	對v$lock的格式化視圖。	Session_id：和v$lock中的Sid對應。 Lock_type：和v$lock中的type對應。 Lock_ID1： 和v$lock中的ID1對應。 Mode_held,mode_requested：和v$lock中 的lmode,request相對應。
v$locked_object	只包含DML的鎖資訊，包括復原段和會話資訊。	Xidusn,xidslot,xidsqn：表示復原段資訊。和 v$transaction相關聯。 Object_id：表示被鎖對象標識。 Session_id：表示持有鎖的會話資訊。 Locked_mode：表示會話等待的鎖模式的信 息，和v$lock中的lmode一致。

 

解鎖及Kill Session:

 

使用下面的文法查出鎖並殺掉Session。

 

SELECT A.SID,A.SERIAL#,A.USERNAME,B.TYPE FROM V$SESSION A,V$LOCK B WHERE A.SID=B.SID;

 

ALTER SYSTEM KILL SESSION ‘SID,SERIAL#‘;

 

Oracle基礎 鎖