StackDoc

StackDoc

當前位置: 主頁 > 操作系統 > OS >

防患未然:解析SQL Server的鎖定與阻塞

時間:2010-10-03 01:46來源:互聯網 作者:互聯網 點擊:
日前公司server-side 有組件,疑似因撰寫時exception-handling 做得不周全,導致罕見的特殊例外發生時,讓SQL Server 的事務未執行到cmmmit 或rollback,
日前公司server-side 有組件,疑似因撰寫時exception-handling 做得不周全,導致罕見的特殊例外發生時,讓SQL Server 的事務未執行到cmmmit 或rollback,造成某些表或記錄被「鎖定(lock)」。後來又有大量的request,要透過代碼訪問這些被鎖定的記錄,結果造成了嚴重的長時間「阻塞」,最後有大量process 在SQL Server 呈現「等待中」的狀態。

由於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 的這一條記錄)。

下方的SQL 語句,由於OrderID 字段有設索引,因此下圖1 的「執行計劃」,會以算法中的「二分搜尋法」在索引中快速查找OrderID=10250 的記錄。

SELECT * FROM Orders WHERE OrderID=10250

SELECT * FROM Orders WHERE OrderID=10250 AND ShipCountry='Brazil'

圖1 有正確使用到索引的SQL 語句,以垂直的方向使用索引。用AND 算符時,只要有任一個字段有加上索引,就能受惠於索引的好處,並避免全表掃描

此時若我們將這句SQL 語句,當作前述會話B 的語句,由於它和會話A 所UPDATE 的OrderID=10248 不是同一條記錄,因此不會受會話A 事務未回滾的影響,會話B 能正常執行SELECT 語句。

但若我們將會話B 的SQL 語句,改用如下的OR 算符,由於ShipCountry 字段沒有加上索引,此時會造成全表掃描。如此一來,除了性能低落以外,還會因為在全表掃描時,讀到會話A 中鎖定的OrderID=10248 那一條記錄,造成阻塞,讓會話B 永遠呈現「等待中」的狀態。

SELECT * FROM Orders WHERE OrderID=10250 OR ShipCountry='Brazil'

圖2 未正確使用索引的SQL 語句,以水平的方向使用索引。用OR 算符時,必須「所有」用到的字段都有加上索引,才能真正有效使用索引、避免全表掃描

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

發生阻塞時,透過以下命令,可看出是哪個進程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

圖3 本帖前述會話A 的UPDATE 語句(53),阻塞了會話B 的SELECT 語句(52)

透過以下兩個命令,我們還能看到整個數據庫的鎖定和阻塞詳細信息:

SELECT * FROM sys.dm_tran_locks

EXEC sp_lock

圖4 session id = 52 的process 因阻塞而一直處於等待中(WAIT)

另透過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 在訪問時的阻塞和等待。


本文摘自:http://www.searchdatabase.com.cn/showcontent_38973.htm
頂一下
(0)
0%
踩一下
(0)
0%
------分隔線----------------------------
發表評論
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
評價:
表情:
驗證碼:點擊我更換圖片
欄目列表
推薦內容