Oracle Lock機制

Oracle的鎖機制歸納總結

2010-06-09 作者：wallimn 來源：wallimn的blog

鎖是防止在兩個事務操作同一個資料源（表或行）時交互破壞資料的一種機制。Oracle採用封鎖技術保證併發操作的可串列性。Oracle的鎖分為兩大類：資料鎖（也稱DML鎖）和字典鎖。字典鎖是Oracle DBMS內部用於對字典表的封鎖。字典鎖包括語法分析鎖和DDL鎖，由DBMS在必要的時候自動加鎖和釋放鎖，用戶無機控制。 Oracle主要提供了5種資料鎖：共用鎖（Share Table Lock，簡稱S鎖）、排它鎖（Exclusive Table Lock，簡稱X鎖）、行級鎖（Row Share Table Lock，簡稱RS鎖）、行級排它鎖（Row Exclusive Table Lock，簡稱RX鎖）和共用行級排它鎖（Share Row Exclusive Table Lock，簡稱SRX鎖）。其封鎖粒度包括行級和表級。 1. 共用鎖（Share Table Lock，S）： 加鎖語法：Lock Table TableName In Share Mode; 允許的操作：一個共用鎖由一個事務控制，僅允許其他事務查詢被鎖定的表。一個有效的共用鎖明確地用Select … For update形式鎖定行，或執行Lock Table TableName In Share Mode語法鎖定整個表，不允許被其他事務更新。允許多個事務在同一個表上加共用鎖，這種情況下不允許在該表上加鎖的事務更新表（即使有一個事務控制的是形如Select Row … for update這樣行鎖也是不被允許的）。因此，僅有一個事務的一個共用鎖可以更新該表如果其他事務也有相同的事務在該表上的話。 禁止的操作：一個共用鎖由一個事務來控制，防止其他事務更新該表或執行下面的語句： 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; DML語句獲得的DML自動鎖（Oracle隱含鎖） DML自動鎖的相容矩陣圖： X：排它鎖、RS：行級共用鎖、RX：行級排它鎖、S、共用鎖、RSX：行級共用排它鎖 DML語句 有行級鎖 鎖類型 SELECT ... FROM table INSERT INTO table ... X RX UPDATE table ... X RX DELETE FROM table ... X RX SELECT ... FROM table ... FOR UPDATE OF ... X RS LOCK TABLE table IN ... ROW SHARE MODE RS ROW EXCLUSIVE MODE RX SHARE MODE S SHARE EXCLUSIVE MODE SRX EXCLUSIVE MODE X 資料字典中與鎖有關的表或視圖 V_$LOCKED_OBJECT V_$LOCKS_WITH_COLLISIONS V_$LOCK_ACTIVITY V_$LOCK_ELEMENT V_$_LOCK DBMS_LOCK V$DLM_ALL_LOCKS V$DLM_LOCKS V$ENQUEUE_LOCK V$GLOBAL_BLOCKED_LOCKS V$LOCK V$LOCKED_OBJECT V$LOCKS_WITH_COLLISIONS V$LOCK_ACTIVITY V$LOCK_ELEMENT V$_LOCK 解鎖及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#';