# Lock
###### tags: `Oracle` `SQL`
無解釋特別好的資料
為什麼需要 Locks
因為多筆交易在資料的讀取與寫入的時候，彼此會相互影響，因此為了交易的concurrency與 isolation ，
資料的讀取或是寫入的時候就會被做一個記號，這個記號用來告知該資料正在被讀取或是寫入的狀態，
其他交易根據這個記號來決定是否要等待到該紀錄狀態結束或是直接讀取該資料而該”記號”就是所謂的 Locks

行級鎖：行級鎖只對用戶正在訪問的行進行鎖定。
如果該用戶正在修改某行，那麼其他用戶就可以更新同一表中該行之外的數據。
例如：如果用戶1正在更新Jobs 表中的第一行，則用戶2可以同時修改該表中的第二行。
也就是說除了該表中的第一行，其他用戶可以修改任意行，但是第一行的數據其他用戶只能select。
行級鎖是一種排他鎖，防止其他事務修改此行，但是不會阻止讀取此行的操作。
在使用INSERT、UPDATE、DELETE 和SELECT…FOR UPDATE 等 語句時，Oracle 會自動應用行級鎖鎖定。
SELECT...FOR UPDATE 語句允許用戶每次選擇多行記錄進行更新，這些記錄會被鎖定，且只能由發起查詢的用戶進行編輯。
只有在回滾或提交事務之後，鎖定才會釋放，其他用戶才可以編輯這些記錄。

SELECT...FOR UPDATE 語句的語法如下：
SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT];

其中：
OF 子句用於指定即將更新的列，即鎖定行上的特定列。
WAIT 子句指定等待其他用戶釋放鎖的秒數，防止無限期的等待。
「使用FOR UPDATE WAIT」子句的優點如下：
1.防止無限期地等待被鎖定的行；
2.允許應用程式中對鎖的等待時間進行更多的控制。
3.對於交互式應用程式非常有用，因為這些用戶不能等待不確定

舉例：比如一個用戶在SQL PLUS下輸入這條語句：SQL>SELECT * FROM order_master WHERE vencode='V002' FOR UPDATE;
此時再開啟一個SQL PLUS，以相同的用戶登陸，執行下面的命令。
SQL> SELECT * FROM order_master WHERE vencode='V02' FOR UPDATE WAIT 5;
由於要更新的行已經被鎖定，上述命令在等待5秒鐘之後返回，並給出如下的錯誤信息：

ERROR 位於第1行；
ORA-30006；資源已被占用；執行操作時出現WAIT超時。
PS：再開啟的SQL PLUS 是以相同用戶登陸的，用其他用戶登陸更是不可能。

表級鎖

表級鎖被鎖定的表，暫時放在內存中，不提交不進去資料庫，也就是說，多個用戶在同一時間，
同時修改同一個表的同一行時，同時點提交，但是還是按隨機的先後被提交進資料庫，而不是同時被提交，
而是先隨機存儲後，被再次提交的覆蓋。

表級鎖將保護表數據，在事務處理過程中，表級鎖會限制對整個表的訪問。
可以使用LOCK TABLE 語句顯示地鎖定表。
表級鎖用來限制對表執行添加、更新和刪除等修改操作。鎖定表的語法如下：
LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT];
其中：lock_mode 是鎖定的模式。
NOWAIT 關鍵字用於防止無限期的等待其他用戶釋放鎖。

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

行共享 (ROW SHARE, RS):
允許其他用戶訪問和鎖定該表，但是禁止排他鎖定整個表。
行共享鎖鎖定後，在同一時刻，不同用戶可以對同一個表中的被行共享鎖鎖定後的該行，具備增、刪、改、查的功能。

行排他(ROW EXCLUSIVE, RX):
與行共享模式相同，同時禁止其他用戶在此表上使用共享鎖。
使用SELECT...FOR UPDATE 語句會在表上自動應用排他鎖。被行排他後，其他用戶不能同時修改該行，
但是可以插入行，可以查詢該行，其他用戶也不能再在該表上對此行進行排他。

共享（SHARE, S）:
共享鎖將鎖定表，僅允許其他用戶查詢表中的行，但不允許插入、更新或刪除行。
多個用戶可以同時在同一個表中放置共享鎖，即允許資源共享，因此得名「共享鎖」。
例如，如果用戶每天都需要在結帳時更新日銷售表，則可以在更改該表時使用共享鎖以確保數據的一致性。
也就是說該表只能查，其他用戶想修改表中行的數據，只需要對該表進行共享鎖。

共享行排他（SHARE ROW EXCLUSIVE , SPX）:
執行比共享表更多的限制。防止其他事務在表上應用共享鎖、共享行排他鎖以及排他鎖。
共享行排他是除了該行以外的其他行也不能增、刪、改。只能在此表中加低級表。
要是想在該表中更改其他行的數據，就只有其他用戶對該行進行共享行排他鎖，也僅僅只能修改被這個用戶鎖定的行
，而其他的行也修改不了。

排他（EXCLUSIVE，E）:
對表執行最大限制。除了允許其他用戶查詢該表的記錄，
排他鎖防止其他事務對表做任何更改或在表上應用任何類型的鎖。這個鎖應該叫鎖中之王，
他鎖住了的話，其他用戶就只有查詢的功能了，就別想在該表中干別的事了。

BTW：在能加很多鎖的表中，如果第一個用戶對該表鎖定時，沒有使用「NOWAIT」語句，
是需要第一個用戶對該表COMMIT或ROLLBACK 命令釋放鎖定後，其他用戶才能對該表進行鎖定。
如果其他用戶違反了該條，就會無期限的等待

SQL> LOCK TABLE order_mater IN SHARE MODE;
而使用下面的語句就可以很少的預防這種情況的存在：
SQL> LOCK TBALE order_mater IN SHARE MODE NOWAIT;
下面對行級鎖和表級鎖來個總結，行級鎖鎖定的只是行，而表級鎖是對表的鎖定，
但是在表級鎖鎖定表後，還可以在該表中使用行級鎖。

1.資料庫鎖的基本概念

為了確保並發用戶在存取同一資料庫對象時的正確性（即無丟失修改、可重複讀、不讀「髒」數據），
資料庫中引入了鎖機制。
基本的鎖類型有兩種：排它鎖（Exclusive locks記為X鎖）和共享鎖（Share locks記為S鎖）。

排它鎖：若事務T對數據D加X鎖，則其它任何事務都不能再對D加任何類型的鎖，直至T釋放D上的X鎖；
一般要求在修改數據前要向該數據加排它鎖，所以排它鎖又稱為寫鎖。

共享鎖：若事務T對數據D加S鎖，則其它事務只能對D加S鎖，而不能加X鎖，直至T釋放D上的S鎖；
一般要求在讀取數據前要向該數據加共享鎖，所以共享鎖又稱為讀鎖。

2. Oracle 多粒度封鎖機制介紹

根據保護對象的不同，Oracle資料庫鎖可以分為以下幾大類：
(1) DML lock（data locks，數據鎖）：用於保護數據的完整性；
(2) DDL lock（dictionary locks，字典鎖）：用於保護資料庫對象的結構（例如表、視圖、索引的結構定義）；
(3) internal locks 和l a t c h es（內部鎖與閂）：保護內部資料庫結構；
(4) distributed locks（分布式鎖）：用於OPS（並行伺服器）中；
(5) PCM locks（並行高速緩存管理鎖）：用於OPS（並行伺服器）中。

從封鎖粒度（封鎖對象的大小）的角度看，Oracle DML鎖共有兩個層次，即行級鎖和表級鎖。

2.1 Oracle的TX鎖（行級鎖、事務鎖）

TX的本義是Transaction（事務），當一個事務第一次執行數據更改（Insert、Update、Delete）
或使用SELECT… FOR UPDATE語句進行查詢時，它即獲得一個TX（事務）鎖，
直至該事務結束（執行COMMIT或ROLLBACK操作）時，該鎖才被釋放。
所以，一個TX鎖，可以對應多個被該事務鎖定的數據行。

在Oracle的每行數據上，都有一個標誌位來表示該行數據是否被鎖定。
Oracle不象其它一些DBMS（資料庫管理系統）那樣，建立一個鍊表來維護每一行被加鎖的數據，
這樣就大大減小了行級鎖的維護開銷，也在很大程度上避免了其它資料庫系統使用行級封鎖時經常發生的鎖數量不夠的情況。
數據行上的鎖標誌一旦被置位，就表明該行數據被加X鎖，Oracle在數據行上沒有S鎖。

鎖的模式
v$locked_object中的LOCKED_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級鎖有：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

查看鎖表進程和解鎖
(1)方式一：
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

v$locked_object 視圖中記錄了所有session中的所有被鎖定的對象信息。
v$session 視圖記錄了所有session的相關信息。
dba_objects 為oracle用戶對象及系統對象的集合，通過關聯這張表能夠獲取被鎖定對象的詳細信息。

(2)方式二：
select * from v$session t1, v$locked_object t2
where t1.sid = t2.SESSION_ID;
(3)進程解鎖
如有記錄則表示有鎖，記錄下SID和serial# ，將記錄的ID替換下面的SID, serial，即可解除LOCK
alter system kill session 'SID,serial';