提供兩種做法，可避免在 SQL Server 事務(wù)鎖定時(shí)產(chǎn)生的不正?；蜷L(zhǎng)時(shí)間阻塞，讓用戶和程序也無(wú)限期等待，甚至引起 connection pooling 連接數(shù)超過(guò)容量。
所謂的「阻塞」，是指當(dāng)一個(gè)數(shù)據(jù)庫(kù)會(huì)話中的事務(wù)，正在鎖定其他會(huì)話事務(wù)想要讀取或修改的資源，造成這些會(huì)話發(fā)出的請(qǐng)求進(jìn)入等待的狀態(tài)。SQL Server 默認(rèn)會(huì)讓被阻塞的請(qǐng)求無(wú)限期地一直等待，直到原來(lái)的事務(wù)釋放相關(guān)的鎖，或直到它超時(shí) (根據(jù) SET LOCK_TIMEOUT，本文后續(xù)會(huì)提到)、服務(wù)器關(guān)閉、進(jìn)程被殺死。一般的系統(tǒng)中，偶爾有短時(shí)間的阻塞是正常且合理的；但若設(shè)計(jì)不良的程序，就可能導(dǎo)致長(zhǎng)時(shí)間的阻塞，這樣就不必要地鎖定了資源，而且阻塞了其他會(huì)話欲讀取或更新的需求。遇到這種情況，可能就需要手工排除阻塞的狀態(tài)，而本文接下來(lái)要介紹兩種排除阻塞的做法。
日前公司 server-side 有組件，疑似因撰寫(xiě)時(shí) exception-handling 做得不周全，導(dǎo)致罕見(jiàn)的特殊例外發(fā)生時(shí)，讓 SQL Server 的事務(wù)未執(zhí)行到 cmmmit 或 rollback，造成某些表或記錄被「鎖定 (lock)」。后來(lái)又有大量的 request，要透過(guò)代碼訪問(wèn)這些被鎖定的記錄，結(jié)果造成了嚴(yán)重的長(zhǎng)時(shí)間「阻塞」，最后有大量 process (進(jìn)程) 在 SQL Server 呈現(xiàn)「等待中 (WAIT)」的狀態(tài)。
由于 SQL Server 的「事務(wù)隔離級(jí)別」默認(rèn)是 READ COMMITTED (事務(wù)期間別人無(wú)法讀取)，加上 SQL Server 的鎖定造成阻塞時(shí)，默認(rèn)是別的進(jìn)程必須無(wú)限期等待 (LOCK_TIMEOUT = -1)。結(jié)果這些大量的客戶端 request 無(wú)限期等待永遠(yuǎn)不會(huì)提交或回滾的事務(wù)，并一直占用著 connection pool 中的資源，最后造成 connection pooling 連接數(shù)目超載。
查了一些書(shū)，若我們要查詢 SQL Server 目前會(huì)話中的 lock 超時(shí)時(shí)間，可用以下的命令：
SELECT @@LOCK_TIMEOUT
執(zhí)行結(jié)果默認(rèn)為 -1，意即欲訪問(wèn)的對(duì)象或記錄被鎖定時(shí)，會(huì)無(wú)限期等待。若欲更改當(dāng)前會(huì)話的此值，可用下列命令：
SET LOCK_TIMEOUT 3000
后面的 3000，其單位為毫秒，亦即會(huì)先等待被鎖定的對(duì)象 3 秒鐘。若事務(wù)仍未釋放鎖，則會(huì)拋回如下代號(hào)為 1222 的錯(cuò)誤信息，可供程序員編程時(shí)做相關(guān)的逾時(shí)處理：
消息 1222，級(jí)別 16，狀態(tài) 51，第 3 行
已超過(guò)了鎖請(qǐng)求超時(shí)時(shí)段。
若將 LOCK_TIMEOUT 設(shè)置為 0，亦即當(dāng)欲訪問(wèn)對(duì)象被鎖定時(shí)，完全不等待就拋回代號(hào) 1222 的錯(cuò)誤信息。此外，此一 SET LOCK_TIMEOUT 命令，影響范例只限當(dāng)前會(huì)話 (進(jìn)程)，而非對(duì)某個(gè)表做永久的設(shè)置。
-------------------------------------------------------------------------------------------
接下來(lái)我們?cè)?SSMS 中，開(kāi)兩個(gè)會(huì)話 (查詢窗口) 做測(cè)試，會(huì)話 A 創(chuàng)建會(huì)造成阻塞的事務(wù)進(jìn)程，會(huì)話 B 去訪問(wèn)被鎖定的記錄。
--會(huì)話 A
BEGIN TRAN;
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248
--rollback; --故意不提交或回滾
--會(huì)話 B
SELECT * FROM Orders WHERE OrderID=10248
分別執(zhí)行后，因?yàn)橛L問(wèn)的記錄是同一條，按照 SQL Server 「事務(wù)隔離級(jí)別」和「鎖」的默認(rèn)值，會(huì)話 B 將無(wú)法讀取該條數(shù)據(jù)，而且會(huì)永遠(yuǎn)一直等下去 (若在現(xiàn)實(shí)項(xiàng)目里寫(xiě)出這種代碼，就準(zhǔn)備被客戶和老板臭罵)。
-------------------------------------------------------------------------------------------
若將會(huì)話 B 先加上 SET LOCK_TIMEOUT 3000 的設(shè)置，如下，則會(huì)話 B 會(huì)先等待 3 秒鐘，才拋出代號(hào) 1222 的「鎖請(qǐng)求已超時(shí)」錯(cuò)誤信息：
--會(huì)話 B
SET LOCK_TIMEOUT 3000
SELECT * FROM Orders WHERE OrderID=10248
--SET LOCK_TIMEOUT -1
執(zhí)行結(jié)果：
消息 1222，級(jí)別 16，狀態(tài) 51，第 3 行
已超過(guò)了鎖請(qǐng)求超時(shí)時(shí)段。
語(yǔ)句已終止。
-------------------------------------------------------------------------------------------
另根據(jù)我之前寫(xiě)的文章「30 分鐘快快樂(lè)樂(lè)學(xué) SQL Performance Tuning」所述：
http://www.cnblogs.com/WizardWu/archive/2008/10/27/1320055.html
撰寫(xiě)不當(dāng)?shù)?SQL 語(yǔ)句，會(huì)讓數(shù)據(jù)庫(kù)的索引無(wú)法使用，造成全表掃描或全聚集索引掃描。例如不當(dāng)?shù)模篘OT、OR 算符使用，或是直接用 + 號(hào)做來(lái)串接兩個(gè)字段當(dāng)作 WHERE 條件，都可能造成索引失效，變成全表掃描，除了性能變差之外，此時(shí)若這句不良的 SQL 語(yǔ)句，是本帖前述會(huì)話 B 的語(yǔ)句，由于會(huì)造成全表掃描或聚集索引掃描，因此就一定會(huì)被會(huì)話 A 的事務(wù)阻塞 (因?yàn)閽呙枞頃r(shí)，一定也會(huì)讀到 OrderID=10248 這一條會(huì)話 A 正在鎖定的記錄)。
下方的 SQL 語(yǔ)句，由于 OrderID 字段有設(shè)索引，因此下圖 1 的「執(zhí)行計(jì)劃」，會(huì)以算法中的「二分查找法」在索引中快速查找 OrderID=10250 的記錄。
SELECT * FROM Orders WHERE OrderID=10250
SELECT * FROM Orders WHERE OrderID=10250 AND ShipCountry='Brazil'
圖 1 有正確使用到索引的 SQL 語(yǔ)句，以垂直的方向使用索引。用 AND 算符時(shí)，只要有任一個(gè)字段有加上索引，就能受惠于索引的好處，并避免全表掃描
此時(shí)若我們將這句 SQL 語(yǔ)句，當(dāng)作前述會(huì)話 B 的語(yǔ)句，由于它和會(huì)話 A 所 UPDATE 的 OrderID=10248 不是同一條記錄，因此不會(huì)受會(huì)話 A 事務(wù)未回滾的影響，會(huì)話 B 能正常執(zhí)行 SELECT 語(yǔ)句。
但若我們將會(huì)話 B 的 SQL 語(yǔ)句，改用如下的 OR 算符，由于 ShipCountry 字段沒(méi)有加上索引，此時(shí)會(huì)造成聚集索引掃描 (和全表掃描一樣，會(huì)對(duì)整個(gè)表做逐條記錄的 scan)。如此一來(lái)，除了性能低落以外，還會(huì)因?yàn)樵谥饤l掃描時(shí)，讀到會(huì)話 A 中鎖定的 OrderID=10248 那一條記錄，造成阻塞，讓會(huì)話 B 永遠(yuǎn)呈現(xiàn)「等待中」的狀態(tài)。
SELECT * FROM Orders WHERE OrderID=10250 OR ShipCountry='Brazil'
圖 2 未正確使用索引的 SQL 語(yǔ)句，以水平的方向使用索引。用 OR 算符時(shí)，必須「所有」用到的字段都有加上索引，才能有效使用索引、避免全表掃描
-------------------------------------------------------------------------------------------
發(fā)生阻塞時(shí)，透過(guò)以下命令，可看出是哪個(gè)進(jìn)程 session id，阻塞了哪幾個(gè)進(jìn)程 session id，且期間經(jīng)過(guò)了多少「毫秒 (ms)」。如下圖 3 里 session id = 53 阻塞了 session id = 52 的進(jìn)程。另透過(guò) SQL Server Profiler 工具，也能看到相同的內(nèi)容。
SELECT blocking_session_id, wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks
圖 3 本帖前述會(huì)話 A 的 UPDATE 語(yǔ)句 (53)，阻塞了會(huì)話 B 的 SELECT 語(yǔ)句 (52)
透過(guò)以下兩個(gè)命令，我們還能看到整個(gè)數(shù)據(jù)庫(kù)的鎖定和阻塞詳細(xì)信息：
SELECT * FROM sys.dm_tran_locks
EXEC sp_lock
圖 4 session id = 52 的 process 因阻塞而一直處于等待中 (WAIT)
另透過(guò) KILL 命令，可直接殺掉造成阻塞的 process，如下：
KILL 53
-------------------------------------------------------------------------------------------
欲解決無(wú)限期等待的問(wèn)題，除了前述的 SET LOCK_TIMEOUT 命令外，還有更省事的做法，如下，在會(huì)話 B 的 SQL 語(yǔ)句中，在表名稱(chēng)后面加上 WITH (NOLOCK) 關(guān)鍵字，表示要求 SQL Server，不必去考慮這個(gè)表的鎖定狀態(tài)為何，因此也可減少「死鎖 (dead lock)」發(fā)生的機(jī)率。但 WITH (NOLOCK) 不適用 INSERT、UPDATE、DELETE。
SELECT * FROM Orders WITH (NOLOCK) WHERE OrderID=10248
類(lèi)似的功能，也可如下，在 SQL 語(yǔ)句前，先設(shè)置「事務(wù)隔離級(jí)別」為可「臟讀 (dirty read)」。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Orders WHERE OrderID=10248
兩種做法的效果類(lèi)似，讓會(huì)話 B 即使讀到被鎖阻塞的記錄，也永遠(yuǎn)不必等待，但可能讀到別人未提交的數(shù)據(jù)。雖然說(shuō)這種做法讓會(huì)話 B 不用請(qǐng)求共享鎖，亦即永遠(yuǎn)不會(huì)和其他事務(wù)發(fā)生沖突，但應(yīng)考慮項(xiàng)目開(kāi)發(fā)實(shí)際的需求，若會(huì)話 B 要查詢的是原物料的庫(kù)存量，或銀行系統(tǒng)的關(guān)鍵數(shù)據(jù)，就不適合用這種做法，而應(yīng)改用第一種做法的 SET LOCK_TIMEOUT 命令，明確讓數(shù)據(jù)庫(kù)拋回等候逾時(shí)的錯(cuò)誤代號(hào) 1222，再自己寫(xiě)代碼做處理。
-------------------------------------------------------------------------------------------
歸根究柢，我們?cè)诰幊虝r(shí)，就應(yīng)該避免寫(xiě)出會(huì)造成長(zhǎng)時(shí)間阻塞的 SQL 語(yǔ)句，亦即應(yīng)最小化鎖定爭(zhēng)用的可能性，以下為一些建議：
- 盡可能讓事務(wù)輕薄短小、讓鎖定的時(shí)間盡量短，例如把不必要的命令移出事務(wù)外，或把一個(gè)大量更新的事務(wù)，切成多個(gè)更新較少的事務(wù)，以改善并發(fā)性。
- 將組成事務(wù)的 SQL 語(yǔ)句，擺到一個(gè)「批 (batch) 處理」，以避免不必要的延遲。這些延遲常由 BEGIN TRAN ... COMMIT TRAN 命令之間的網(wǎng)絡(luò) I/O 所引起。
- 考慮將事務(wù)的 SQL 語(yǔ)句寫(xiě)在一個(gè)存儲(chǔ)過(guò)程內(nèi)。一般來(lái)說(shuō)，存儲(chǔ)過(guò)程的執(zhí)行速度會(huì)比批處理的 SQL 語(yǔ)句快，且存儲(chǔ)過(guò)程可降低網(wǎng)絡(luò)的流量和 I/O，讓事務(wù)可更快完成。
- 盡可能頻繁地認(rèn)可 Cursor 中的更新，因?yàn)?Cursor 的處理速度較慢，會(huì)讓鎖定的時(shí)間較長(zhǎng)。
- 若無(wú)必要，使用較寬松的事務(wù)隔離級(jí)別，如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED。而非為了項(xiàng)目開(kāi)發(fā)方便，全部使用默認(rèn)的 READ COMMITTED 級(jí)別。
- 避免在事務(wù)執(zhí)行期間，還要等待用戶的反饋或交互，這樣可能會(huì)造成無(wú)限期的持有鎖定，如同本帖一開(kāi)始提到的狀況，最后造成大量的阻塞和數(shù)據(jù)庫(kù) connection 被占用。
- 避免事務(wù) BEGIN TRAN 后查詢的數(shù)據(jù)，可能在事務(wù)開(kāi)始之前先被引用。
- 避免在查詢時(shí) JOIN 過(guò)多的表 (此指非必要的 JOIN)，否則除了性能較差外，也很容易讀到正被鎖定或阻塞中的表和字段。
- 應(yīng)注意在一個(gè)沒(méi)有索引的表上，過(guò)量的「行鎖」，或一些鎖定使用了過(guò)多的內(nèi)存和系統(tǒng)資源時(shí)，SQL Server 為了有效地管理這些鎖定，會(huì)嘗試將鎖定擴(kuò)展為整個(gè)表的「表鎖」，此時(shí)會(huì)很容易造成其他 process 在訪問(wèn)時(shí)的阻塞和等待。