關於SQLServer的鎖定

由於 SQL Server 的「交易隔離等級」預設是 READ COMMITTED (事務期間別人無法讀取)，加上 SQL Server 的鎖定造成阻塞時，預設是別的進程必須無限期等待 (LOCK_TIMEOUT = -1)。結果這些大量的用戶端 request 無限期等待永遠不會提交或復原的事務，並一直佔用著 connection pool 中的資源，最後造成 connection pooling 串連數目超載。

若我們要查詢 SQL Server 目前會話中的 lock 逾時時間，可用以下的命令：

SELECT @@LOCK_TIMEOUT

預設為 -1，意即欲訪問的對象或記錄被鎖定時，會無限期等待。若欲更改當前會話的此值，可用下列命令：

SET LOCK_TIMEOUT 3000

後面的 3000，其單位為毫秒，亦即會先等待被鎖定的對象 3 秒鐘。若事務仍未釋放鎖，則會拋回如下代號為 1222 的錯誤資訊，可供程式員編程時做相關的逾時處理：

訊息 1222，層級 16，狀態 51，第 3 行
已超過了鎖請求逾時時段。

若將 LOCK_TIMEOUT 設定為 0，亦即當欲訪問對象被鎖定時，完全不等待就拋回代號 1222 的錯誤資訊。此外，此一 SET LOCK_TIMEOUT 命令，影響範例只限當前會話，而非對某個表做永久的設定。

-------------------------------------------------------------------------------------------
接下來我們在 SSMS 中，開兩個會話 (查詢時段) 做測試，會話 A 建立會造成阻塞的事務進程，會話 B 去訪問被鎖定的記錄。

--會話 A
BEGIN TRAN;
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248
--rollback; --故意不提交或復原

--會話 B
SELECT * FROM Orders WHERE OrderID=10248

 分別執行後，因為欲訪問的記錄是同一條，按照 SQL Server 「交易隔離等級」和「鎖」的預設值，會話 B 將無法讀取該條資料，而且會永遠一直等下去 (若在現實項目裡寫出這種代碼，就準備被客戶和老闆臭罵)。

-------------------------------------------------------------------------------------------
若將會話 B 先加上 SET LOCK_TIMEOUT 3000 的設定，如下，則會話 B 會先等待 3 秒鐘，才拋出代號 1222 的「鎖請求已逾時」錯誤資訊：

SET LOCK_TIMEOUT 3000
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248
--SET LOCK_TIMEOUT -1

 執行結果：

訊息 1222，層級 16，狀態 51，第 3 行
已超過了鎖請求逾時時段。
語句已終止。

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

撰寫不當的 SQL 陳述式，會讓資料庫的索引無法使用，造成全表掃描或全叢集索引掃描。例如不當的：NOT、OR 算符使用，或是直接用 + 號做來串接兩個欄位當作 WHERE 條件，都可能造成索引失效，變成全表掃描，除了效能變差之外，此時若這句不良的 SQL 陳述式，是本帖前述會話 B 的語句，由於會造成全表掃描，因此就一定會被會話 A 的事務阻塞 (因為掃描全表時，一定也會讀到 OrderID=10248 的這一條記錄)。 

發生阻塞時，透過以下命令，可看出是哪個進程 session id，阻塞了哪幾個進程 session id，且期間經過了多少「毫秒 (ms)」。如 3 裡 session id = 53 阻塞了 session id = 52 的進程。另透過 SQL Server Profiler 工具，也能看到相同的內容。

SELECT blocking_session_id, wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks

透過以下兩個命令，我們還能看到整個資料庫的鎖定和阻塞詳細資料：

SELECT * FROM sys.dm_tran_locks

EXEC sp_lock 另透過 KILL 命令，可直接殺掉造成阻塞的 process，如下：

KILL 53

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

欲解決無限期等待的問題，除了前述的 SET LOCK_TIMEOUT 命令外，還有更省事的做法，如下，在會話 B 的 SQL 陳述式中，在表名稱後面加上 WITH (NOLOCK) 關鍵字，表示要求  SQL Server，不必去考慮這個表的鎖定狀態為何，因此也可減少「死結 (dead lock)」發生的機率。但 WITH (NOLOCK) 不適用 INSERT、UPDATE、DELETE。

SELECT * FROM Orders WITH (NOLOCK) WHERE OrderID=10248

類似的功能，也可如下，在 SQL 陳述式前，先設定「交易隔離等級」為可「髒讀 (dirty read)」。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Orders WHERE OrderID=10248

兩種做法的效果類似，讓會話 B 即使讀到被鎖阻塞的記錄，也永遠不必等待，但可能讀到別人未提交的資料。雖然說這種做法讓會話 B 不用請求共用鎖定，亦即永遠不會和其他事務發生衝突，但應考慮項目開發實際的需求，若是會話 B 要查詢的是原物料的庫存量，或銀行系統的關鍵資料，就不適合用這種做法，而應改用第一種做法的 SET LOCK_TIMEOUT 命令，明確讓資料庫拋回等候逾時的錯誤代號 1222，再自己寫代碼做處理。

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

歸根究柢，我們在編程時，就應該避免寫出會造成長時間阻塞的 SQL 陳述式，亦即應最小化鎖定爭用的可能性，以下為一些建議：

• 儘可能讓事務輕薄短小、讓鎖定的時間盡量短，例如把不必要的命令移出事務外，或把一個大量更新的事務，切成多個更新較少的事務，以改善並發性。
  
• 將組成事務的 SQL 陳述式，擺到一個「批 (batch) 處理」，以避免不必要的延遲。這些延遲常由 BEGIN TRAN ... COMMIT TRAN 命令之間的網路 I/O 所引起。
  
• 考慮將事務的 SQL 陳述式寫在一個預存程序內。一般來說，預存程序的執行速度會比批處理的 SQL 陳述式快，且預存程序可降低網路的流量和 I/O，讓事務可更快完成。
  
• 儘可能頻繁地認可 Cursor 中的更新，因為 Cursor 的處理速度較慢，會讓鎖定的時間較長。
  
• 若無必要，使用較寬鬆的交易隔離等級，如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED。而非為了項目開發方便，全部使用預設的 READ COMMITTED 層級。
  
• 避免在事務執行期間，還要等待使用者的反饋或互動，這樣可能會造成無限期的持有鎖定，如同本帖一開始提到的狀況，最後造成大量的阻塞和資料庫 connection 被佔用。
  
• 避免事務 BEGIN TRAN 後查詢的資料，可能在事務開始之前先被引用。
• 避免在查詢時 JOIN 過多的表，否則除了效能較差外，也很容易讀到正被鎖定或阻塞中的表和欄位。
• 應注意在一個沒有索引的表上，過量的「行鎖」，或一些鎖定使用了過多的記憶體和系統資源時，SQL Server 為了有效地管理這些鎖定，會嘗試將鎖定擴充為整個表的「表鎖」，此時會很容易造成其他 process 在訪問時的阻塞和等待。