資料庫的交易鎖定 Locks
筆者第一年在某知名公司工作時，程式開發人員、資料庫與線上使用者，
討論到程式與資料庫讀取的設計時，
有一位資深工程師跳出來並且在黑板畫著說：
“當有Transaction A 資料寫入 Table A 的時候，其他交易Transaction B就不能讀取Table A ，要一直等到Transaction A交易結束才可以讀取”
(說著就把 Table A, Table B 畫叉叉，最後所有的 Table 都被劃叉叉 )
這樣的說法其實是對 database Locks 的機制有些誤解. 讓我們進一步說明
為什麼需要 Locks
因為多筆交易在資料的讀取與寫入的時候，彼此會相互影響，
因此為了交易的concurrency與 isolation ，
資料的讀取或是寫入的時候就會被做一個記號，
這個記號用來告知該資料正在被讀取或是寫入的狀態，
其他交易根據這個記號來決定是否要等待到該紀錄狀態結束或是直接讀取該資料
而該”記號”就是所謂的 Locks
Lock Granularity (Lock 的層級)
想像一個比較極端的狀況，
資料庫的資料同時只能夠有一個人User A寫入，
因此 User 寫入資料的時候就會 Lock 該資料，其他人 User B 就要寫入資料就必須要等待，
等到User A 資料寫入完畢，Lock 釋放之後，User B 才能進行寫入的動作
實務上，這樣的資料庫並不實用，因為資料庫通常會有多人”同時”寫入與讀取，
因為資料庫就必須設定一定的 Lock 與 Isolation 的層級，達到
- 在多人可以同時存取
- 交易鎖定的資料完整性
為了達到可以增加多人同時存取， 當使用者 User A 寫入Table A的時候，
Lock 就不會針對 整個 Table A or 整個檔案進行鎖定，而是更進一步細分Lock 的最小單位：
• Row (RID)
• Key (KEY)
• Page (PAG)
• Extent (EXT)
• Heap or B-tree (HoBT)
• Table (TAB)
• File (FIL)
• Application (APP)
• MetaData (MDT)
• Allocation Unit (AU)
• Database (DB)
ROW-level Lock
這是資料庫鎖定的最小單位，也就是Table 中每一筆的紀錄，
Row-level 的lock 可以降低因為同時存取需要等待 Locks 的機率
Key-Level Lock
主要發生在 Clustered Index 的資料上，
因為 Clustered Index 的 leaf-node 節點就用來存放資料，
引此該資料於 clustered index 發生 lock 時，就會以 Key-level Locks 的形式進行
Page-Level Lock
當某個交易需要存取多筆資料，因此產生多個 Row-level locks & Key-level Locks的時候，
Lock Manager 就會根據系統資源的狀況，選擇下列之一來執行
- Page-Level lock
- Row-level Locks + Key Level Locks
換句話說，Page Level Locks 相當於 Row + Key locks 取決於系統資源的狀況而定，
Lock Manager 會做出執行上的判斷。
Extent-Level Lock
什麼是 Extent呢? 8 Pages = 1 Extent
例如， Index 重建的時候，整區Extent 比較多的資料需要重建，就會進行 Extent Level Locks
Table-Level Lock
Table Level Lock 會對於整個資料表 and 索引進行 Locks，
Lock Manager 判斷如果 Row Locks, => Extent Locks 等都無法滿足時，
就會自動升級到 Table Locks
- Table locks 優點是可以減少 Locks 的 overhead，對於該執行的 query 存取效率也會比較好。
- Table Locks 缺點是整個資料表 Table 都被 Locks 住，會嚴重影響其他人同步的讀取與寫入。
Lock Escalation
我們知道 Locks 有不同的層級，從
Row Locks —> Key Locks –> Page Locks –> Extents Locks –> Table Locks
Lock Manager 會根據資源的狀況，從最小的 locks 等級慢慢根據需要，提升 Locks 的層級，這個過程就稱為 “Lock Escalation”
當 query 執行的時候，Lock Manager 就會記錄 Locks 資源的狀態與所需要 locks 數量，例如需要 Row Locks x 2，如果該 locks 超過一定的值的話，Lock Manager 就會提升 Locks 到 Page locks並且釋放 Row Locks.
Lock 的狀態
那麼在多人存取的時候，只要資料上有 Locks 是不是別人就不能夠讀取 or 寫入呢?
當然不是.
Lock 主要影響的是同步”寫入”的問題，而不是同步讀取的問題
因此, Lock mode 主要分為下列幾種：
• Shared (S)
• Update (U)
• Exclusive (X)
Shared Mode
主要用在讀取資料，例如：Query1: SELECT name，一旦 name 被讀取，就會進入 Shared locks ，
同時兩筆交易讀取 Name 是被允許的
Query 1: Select Name…..
Query 2: Select Name, CustomerID……
讀取完畢之後就會立刻釋放 Shared locks，不需要等到 Transaction 整個結束
Update (U) Mode
Update Mode 主要與 Update 有關，用在資料的更新或是寫入。
因為是資料的寫入，因此資料庫不會允許同時兩個交易的資料寫入
但是，讀取 Shared Mode 還是可以同步進行。
例如，Query 1 , Query 2 同時執行時
Query 1: Update Name…..(Update Mode locks —> 之後進入 X Locks)
Query 2: Select Name ….
其中Query 1的 Update，Lock 進行的方式為
1. 先讀取 Name 的欄位，因此對 Name進行 U/Locks
2. 當資料要寫入時，將U/Locks 轉換為 X Locks進行寫入的動作
S Lock 與 U Lock 最大不同的地方是，U/Lock對於該資源，同時只能有一筆U/Lock
Exclusive (X) Mode
Insert, Update, DELETE 都會產生 X Locks。
Exclusive Locks主要是用在當資料修改、寫入的時候避免其他交易query 的同步存取。
Insert 與 DELETE 都會在交易的一開始就取得 X Locks
而 update 一開始會先做 U/Locks 之後再轉換為 X/Locks
主要目的為：
- 確保其他交易不會同時”讀取”正在修改的資料
- 確保其他較易不會同時”寫入”正在修改的資料
Intent Shared, Intent Exclusive
Intent lock 的出現，表示下一層的資源也會有相對應的 Locks 出現
例如1： Intent Exclusive (IX) locks 於 Table ====表示===> X locks on Page/Key
例如2: IX Locks on Page =====> X locks on Row
如何知道Lock 的狀態?
可以用 sys.dm_tran_locks這個 DMV 來知道 locks的層級與範圍
SELECT * FROM sys.dm_tran_locks
總結
回到一開始原始的問題，
“當有Transaction A 資料寫入 Table A 的時候，其他交易Transaction B就不能讀取Table A ，要一直等到Transaction A交易結束才可以讀取”
這樣的敘述應該修改為：
1. 當Transaction A資料寫入 Table A 的時候，Transaction A 會對特定的資料範圍
(Row or Page) 進行 U/Locks
2. 寫入的當下U/Locks 轉換為 X/Lock。
3. 一旦寫入完畢，X/Locks 釋放，不會等到 Transaction A 結束。
4. U/Locks 的時候，其他 transaction 還是可以讀取資料的。
介紹幾種 Locks 資源的範圍與 Locks 的層級。
Locks 的層級與 Isolation Levels有關，之後繼續說明。
• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable