SQL Server深入理解“鎖”機(jī)制

相比于 SQL Server 2005（比如快照隔離和改進(jìn)的鎖與死鎖監(jiān)視），SQL Server 2008 并沒(méi)有在鎖的行為和特性上做出任何重大改變。SQL Server 2008 引入的一個(gè)主要新特性是在表級(jí)控制鎖升級(jí)行為的能力。新的LOCK_ESCALATION表選項(xiàng)允許你啟用或禁用表級(jí)鎖升級(jí)。這個(gè)新特性能夠減少鎖競(jìng)爭(zhēng)并且改善并發(fā)性，特別是對(duì)于分區(qū)表（partitioned tables）。
SQL Server 2008 的另一個(gè)改變是不再支持Locks configuration設(shè)定。同樣不再被支持的還有timestamp數(shù)據(jù)類型，它已被rowversion數(shù)據(jù)類型取代。

為什么需要鎖？ 在任何多用戶的數(shù)據(jù)庫(kù)中，必須有一套用于數(shù)據(jù)修改的一致的規(guī)則。對(duì)于真正的事務(wù)處理型數(shù)據(jù)庫(kù)，當(dāng)兩個(gè)不同的進(jìn)程試圖同時(shí)修改同一份數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）負(fù)責(zé)解決它們之間潛在的沖突。

任何關(guān)系數(shù)據(jù)庫(kù)必須支持事務(wù)的ACID屬性，即原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、永久性（Durability）。ACID屬性確保數(shù)據(jù)庫(kù)中的數(shù)據(jù)更改被正確地收集到一起，并且數(shù)據(jù)將保持在與所采取動(dòng)作相一致的狀態(tài)。

鎖的主要作用是提供事務(wù)所需的隔離。隔離確保事務(wù)之間不會(huì)相互干擾，即，一個(gè)給定的事務(wù)不會(huì)讀取或修改正在被另一個(gè)事務(wù)修改的數(shù)據(jù)。此外，鎖提供的隔離性有助于保證事務(wù)間的一致性。沒(méi)有鎖，一致的事務(wù)處理是不可能的。

SQL Server 中的事務(wù)隔離級(jí)別 隔離級(jí)別決定了一個(gè)事務(wù)中正被訪問(wèn)或修改的數(shù)據(jù)受保護(hù)并免于被他事務(wù)修改的程度。理論上，每個(gè)事務(wù)都應(yīng)該完全與其他事務(wù)隔離開(kāi)來(lái)。然而，出于可行性和性能方面的原因，實(shí)踐中這幾乎是不可能做到的。在并發(fā)環(huán)境中如果沒(méi)有鎖和隔離，可能發(fā)生以下4種情況：

丟失更新?-- 在這種情況下，事務(wù)與事務(wù)之間沒(méi)有隔離。多個(gè)事務(wù)能夠讀取同一份數(shù)據(jù)并且修改它。最后對(duì)數(shù)據(jù)集做出修改的事務(wù)將勝出，而其他所有事務(wù)所作的修改都丟失了。 臟讀?-- 在這種情況下，一個(gè)事務(wù)能夠讀取正被其他事務(wù)修改的數(shù)據(jù)。被第一個(gè)事務(wù)讀取的數(shù)據(jù)是不一致的，因?yàn)榱硪粋€(gè)事務(wù)可能會(huì)回滾所作的修改。解決方法：把事務(wù)隔離級(jí)別調(diào)整到READ COMMITTED，即SET TRAN ISOLATION LEVEL READ COMMITTED。這時(shí)我們重復(fù)上面的動(dòng)作會(huì)發(fā)現(xiàn)事務(wù)二會(huì)一直等到事務(wù)一執(zhí)行完畢再返回結(jié)果，因?yàn)榇藭r(shí)事務(wù)以已經(jīng)把自己的更改ROLLBACK了，所以事務(wù)二可以返回正確的結(jié)果。 不可重復(fù)讀?-- 這種情況有點(diǎn)類似于沒(méi)有任何隔離，一個(gè)事務(wù)兩次讀取數(shù)據(jù)，但是在第二次讀取發(fā)生前，另一個(gè)事務(wù)修改了該數(shù)據(jù)；因此，兩次讀取所得到的結(jié)果是不同的。因?yàn)樽x操作不能保證每次都是課重復(fù)進(jìn)行的，這種情況被稱作“不可重復(fù)讀”。解決方法：把事務(wù)隔離級(jí)別調(diào)整到REPEATABLE READ。使用SET TRAN ISOLATION LEVEL REPEATABLE READ。這時(shí)我們重復(fù)上面的動(dòng)作會(huì)發(fā)現(xiàn)事務(wù)二會(huì)一直等到事務(wù)一執(zhí)行完畢再返回結(jié)果。 幻讀?--?這種情況類似于不可重復(fù)讀。然而，不是先前被讀取的實(shí)際行在事務(wù)完成前發(fā)生了改變，而是額外的行被添加到了表中，導(dǎo)致第二次讀取返回了不同的行集合。解決方法：把事務(wù)隔離級(jí)別調(diào)整到SERIALIZABLE。使用SET TRAN ISOLATION LEVEL SERIALIZABLE。這時(shí)我們重復(fù)上面的動(dòng)作會(huì)發(fā)現(xiàn)事務(wù)二會(huì)一直等到事務(wù)一執(zhí)行完畢再返回結(jié)果。

SQL Server 2008 支持6種隔離級(jí)別，分別是

Read Uncommitted Read Committed Repeatable Read Serializable Snapshot Read Committed Snapshot

鎖管理器 解決不同用戶進(jìn)程間鎖沖突的職責(zé)落到了SQL Server Lock Manager身上。SQL Server 自動(dòng)地給進(jìn)程分配鎖，以保證資源的當(dāng)前用戶擁有該資源的一致視圖，從某個(gè)特定操作的開(kāi)始至結(jié)束。

Lock Manager 負(fù)責(zé)決定適當(dāng)?shù)逆i類型（如shared, exclusive, update）和鎖粒度（如row, page,table），根據(jù)正在執(zhí)行的操作類型和所影響的數(shù)據(jù)量。

Lock Manager還管理試圖訪問(wèn)同一資源的鎖類型之間的兼容性，解決死鎖，必要時(shí)升級(jí)鎖到一個(gè)更高的級(jí)別。

Lock Manager 為共享數(shù)據(jù)和內(nèi)部系統(tǒng)資源管理鎖。對(duì)于共享數(shù)據(jù)，Lock Manager 管理表以及數(shù)據(jù)頁(yè)、文本頁(yè)、葉級(jí)索引頁(yè)上的行級(jí)鎖、頁(yè)級(jí)鎖和表級(jí)鎖。內(nèi)部地，Lock Manager使用門閂（latch）來(lái)管理索引行和頁(yè)上的鎖控制對(duì)內(nèi)部數(shù)據(jù)結(jié)構(gòu)的訪問(wèn)，以及在某些情況下，用于取回單個(gè)的數(shù)據(jù)行。門閂提供了更好的系統(tǒng)性能，因?yàn)樗幌矜i那般資源密集。門閂也提供了比鎖更好的并發(fā)性。門閂典型地用于像頁(yè)拆分、索引行的刪除、索引中行的移動(dòng)等操作。鎖與門閂之間最主要的區(qū)別在于，鎖在整個(gè)事務(wù)存續(xù)期間都被持有，而門閂僅在需要它的操作存續(xù)期間被持有。鎖用于保證數(shù)據(jù)的邏輯一致性，而門閂用于保證數(shù)據(jù)和數(shù)據(jù)結(jié)構(gòu)的物理一致性。

SQL Server 鎖類型 鎖在SQL Server中是自動(dòng)處理的。Lock Manager 基于事務(wù)類型（如SELECT, INSERT, UPDATE, 或者DELETE)選擇鎖的類型.Lock Manager使用以下的鎖類型：

共享鎖 更新鎖 獨(dú)占鎖 意向鎖 架構(gòu)鎖 大容量更新鎖

除了選擇鎖類型，Lock Manager還基于所執(zhí)行語(yǔ)句的性質(zhì)以及所影響的行數(shù)自動(dòng)地調(diào)整鎖粒度（如row, page, table）。

共享鎖 缺省地，SQL Server 為所有讀操作應(yīng)用共享鎖。顧名思義，共享鎖不是獨(dú)占的。理論上，在任何時(shí)刻，一個(gè)資源上可以持有無(wú)限數(shù)量的共享鎖。此外，默認(rèn)情況下，一個(gè)進(jìn)程僅僅當(dāng)資源正被讀取期間才會(huì)鎖定該資源，這時(shí)也只有唯一的共享鎖存在。比如SELECT * from authors，當(dāng)查詢開(kāi)始時(shí)，先鎖定authors表中的第一行；當(dāng)?shù)谝恍斜蛔x取以后，它上面的鎖被釋放，并且了第二行上的鎖；第二行讀到以后，它上面的鎖被釋放，同時(shí)獲取了第三行上的鎖；以此類推。按此方式，一個(gè)SELECT查詢?cè)试S在讀操作期間修改那些沒(méi)有正在被讀取的數(shù)據(jù)行。這增強(qiáng)了數(shù)據(jù)訪問(wèn)的并發(fā)性。
共享鎖不僅與其他共享鎖兼容，也與更新鎖兼容。共享鎖不會(huì)阻止其他進(jìn)程在一個(gè)給定的行或頁(yè)上獲取額外的共享鎖或更新鎖。任何時(shí)候事務(wù)多個(gè)事務(wù)或進(jìn)程可以持有多個(gè)共享鎖，這些事務(wù)不會(huì)影響數(shù)據(jù)的一致性。然而，共享鎖確實(shí)會(huì)阻止獨(dú)占鎖的獲取。當(dāng)行或頁(yè)上持有共享鎖的時(shí)候，任何試圖修改其數(shù)據(jù)的事務(wù)將被阻塞，直到 所有的共享鎖被釋放。

更新鎖 更新鎖用于鎖定用戶進(jìn)程想要修改的行或頁(yè)。當(dāng)一個(gè)事務(wù)試圖修改某行時(shí)，它必須先讀取該行以確保它正在修改合適的記錄。假如事務(wù)先在資源上加了共享鎖，要修改該記錄，最終它將需要獲取該資源上的獨(dú)占鎖，以防止任何其他事務(wù)修改同一記錄。問(wèn)題是，當(dāng)多個(gè)事務(wù)試圖同時(shí)修改同一資源的時(shí)候這可能導(dǎo)致死鎖。如圖所示。

SQL Server中的更新鎖就是用來(lái)防止此類死鎖場(chǎng)景的。更新鎖是部分獨(dú)占的，就是說(shuō)在任何時(shí)候任何資源上只能獲取唯一的更新鎖。然而，更新鎖兼容于共享鎖，即它們可以同時(shí)被同一資所獲取。事實(shí)上，更新鎖意味著一個(gè)進(jìn)程想要修改某記錄，并且將也想修改該記錄的其他進(jìn)程排除在外。然而，更新鎖允許其他進(jìn)程獲取共享鎖以便讀取數(shù)據(jù)，直到UPDATE或DELETE語(yǔ)句完成被影響記錄的定位。之后，進(jìn)程嘗試將每一個(gè)更新鎖升級(jí)為獨(dú)占鎖。這時(shí)候，進(jìn)程等待該記錄上當(dāng)前被持有的所有共享鎖釋放。當(dāng)共享鎖全部釋放以后，共享鎖就被升級(jí)為獨(dú)占鎖。接著執(zhí)行數(shù)據(jù)修改，獨(dú)占鎖在事務(wù)的余下時(shí)間內(nèi)一直被持有。

獨(dú)占鎖 如前所述，當(dāng)事務(wù)準(zhǔn)備好要修改數(shù)據(jù)時(shí)，獨(dú)占鎖被分配給它。資源上的獨(dú)占鎖確保沒(méi)有其他任何事務(wù)能妨礙被持有獨(dú)占鎖的事務(wù)鎖定的數(shù)據(jù)。SQL Server在事務(wù)結(jié)束時(shí)釋放獨(dú)占鎖。

獨(dú)占鎖與其他的所類型不兼容。如果資源持有了獨(dú)占鎖，那么任何其他進(jìn)程對(duì)該資源的讀取或修改請(qǐng)求都將強(qiáng)制等待直到獨(dú)占鎖釋放為止。同樣地，如果其他進(jìn)程當(dāng)前持有該資源的讀取鎖（共享鎖或更新鎖），獨(dú)占鎖請(qǐng)求也被強(qiáng)制排隊(duì)等待直到資源變得可用為止。

意向鎖 意向鎖并不正真的構(gòu)成一種鎖定方式，而是充當(dāng)一種機(jī)制，用以在較高的粒度級(jí)別上指示在較低（粒度）級(jí)別上所持有的鎖類型。有3種類型的意向鎖（分別對(duì)應(yīng)于之前提到的3種鎖類型）：共享意向鎖、獨(dú)占意向鎖、更新意向鎖。舉個(gè)例子來(lái)說(shuō)，某進(jìn)程持有的表級(jí)共享意向鎖意味著，該進(jìn)程當(dāng)前在該表的行或頁(yè)級(jí)持有共享鎖。意向鎖的存在防止其他事務(wù)獲取與現(xiàn)存的行或頁(yè)級(jí)鎖不兼容的表級(jí)鎖的企圖。

意向鎖提升了SQL Server鎖的性能。它允許在表級(jí)別檢查鎖來(lái)決定在該表的行或頁(yè)級(jí)持有的鎖類型，而不是在表中的行或頁(yè)級(jí)查遍多個(gè)鎖。

當(dāng)監(jiān)視鎖活動(dòng)時(shí)典型地你將看到3種類型的意向鎖：意向共享鎖（IS）、意向獨(dú)占鎖（IX）、意向獨(dú)占共享鎖（SIX）。
IS鎖表明，在低級(jí)別資源（行或頁(yè)）上，進(jìn)程當(dāng)前持有或有意圖持有共享鎖。
IX鎖表明，在低級(jí)別資源上，進(jìn)程當(dāng)前持有或有意圖持有獨(dú)占鎖。
SIX鎖出現(xiàn)在特殊情況下，當(dāng)一個(gè)事務(wù)在資源上持有共享鎖，后來(lái)又需要意向獨(dú)占鎖（IX），這時(shí)候，S鎖被轉(zhuǎn)換成SIX鎖。

架構(gòu)鎖 SQL Server 使用架構(gòu)鎖來(lái)保持表結(jié)構(gòu)的完整性。不像其他提供數(shù)據(jù)隔離的鎖類型，架構(gòu)鎖提供事務(wù)中對(duì)數(shù)據(jù)庫(kù)對(duì)象如表、視圖、索引的schema隔離。Lock Manager提供2種類型的架構(gòu)鎖：

架構(gòu)穩(wěn)定性鎖（Sch-S）- 當(dāng)事務(wù)引用了索引或數(shù)據(jù)頁(yè)時(shí)，SQL Server在對(duì)象上加Sch-S鎖。這確保當(dāng)其他進(jìn)程仍然引用著該對(duì)象時(shí)，沒(méi)有其他事務(wù)能夠修改該對(duì)象的Schema，如刪除索引或刪除、修改存儲(chǔ)過(guò)程或表。

架構(gòu)修改鎖（Sch-M） - 當(dāng)一個(gè)進(jìn)程需要修改某對(duì)象的結(jié)構(gòu)（如修改表，重編譯存儲(chǔ)過(guò)程）時(shí)， Lock Manager在對(duì)象上加Sch-M鎖。在鎖存在期間，沒(méi)有其他任何事務(wù)能夠引用該對(duì)象，直到（對(duì)象結(jié)構(gòu)的）修改完成并提交為止。

大容量更新鎖（BU） 大容量更新鎖是一種特殊類型的鎖，僅用于使用bcp實(shí)用程序或者BULK INSERT命令向表中大容量復(fù)制數(shù)據(jù)時(shí)。僅僅當(dāng)給bcp或BULK INSERT命令指定了TABLOCK提示，或者使用 sp_tableoption 設(shè)置了 table lock on bulk load 表選項(xiàng)時(shí),BU鎖才能用于大容量數(shù)據(jù)復(fù)制操作。大容量更新 (BU) 鎖允許多個(gè) bulk copy 進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時(shí)防止其它不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問(wèn)該表。如果有任何其他進(jìn)程在該表上持有鎖，則不能給該表施加BU鎖。

SQL Server 鎖粒度 所謂所粒度，從本質(zhì)上說(shuō)就是，為了給事務(wù)提供完全的隔離和序列化，作為查詢或更新的一部分被鎖定的數(shù)據(jù)的總量（的大?。?。Lock Manager需要在資源的并發(fā)訪問(wèn)與維護(hù)大量低級(jí)別鎖的管理開(kāi)銷之間取得平衡。比如，鎖的粒度越小，能夠同時(shí)訪問(wèn)同一張表的并發(fā)用戶的數(shù)量就越大，不過(guò)維護(hù)這些鎖的管理開(kāi)銷也越大。鎖的粒度越大，管理鎖需要的開(kāi)銷就越少，而并發(fā)性也降低了。下圖說(shuō)明了鎖的大小與并發(fā)性之間的權(quán)衡取舍。

當(dāng)前，SQL Server通過(guò)在行或更高級(jí)別加鎖來(lái)平衡性能和并發(fā)性?；诟鞣N因素，如key的分布，行的數(shù)量，行的密度，查詢參數(shù)（SARGs）等等，Query Optimizer內(nèi)部地做出鎖粒度選擇，程序員不需要為此擔(dān)心。SQL Server提供了大量T_SQL擴(kuò)展，使你能從鎖的角度來(lái)更好地控制查詢行為。

SQL Server 提供以下的鎖級(jí)別:

DATABASE?-- 無(wú)論何時(shí)當(dāng)一個(gè)SQL Server 進(jìn)程正在使用除master以外的數(shù)據(jù)庫(kù)時(shí)，Lock Manager為該進(jìn)程授予數(shù)據(jù)庫(kù)級(jí)的鎖。數(shù)據(jù)庫(kù)級(jí)的鎖總是共享鎖，用于跟蹤何時(shí)數(shù)據(jù)庫(kù)在使用中，以防其他進(jìn)程刪除該數(shù)據(jù)庫(kù)，將數(shù)據(jù)庫(kù)置為脫機(jī)，或者恢復(fù)數(shù)據(jù)庫(kù)。注意，由于master和tempdb數(shù)據(jù)庫(kù)不能被刪除或置為脫機(jī)，所以不需要在它們之上加鎖。 FILE?-- 文件級(jí)的鎖用于鎖定數(shù)據(jù)庫(kù)文件。 EXTENT?-- Extent鎖用于鎖定extents,通常僅在空間分配和重新分配的時(shí)候使用。一個(gè)extent由8個(gè)連續(xù)的數(shù)據(jù)頁(yè)或索引頁(yè)組成。Extent鎖可以是共享鎖也可以是獨(dú)占鎖。 ALLOCATION_UNIT?-- 使用在數(shù)據(jù)庫(kù)分配單元上。 TABLE?-- 這種級(jí)別的鎖將鎖定整個(gè)表，包括數(shù)據(jù)和索引。何時(shí)將獲得表級(jí)鎖的例子包括在Serializable隔離級(jí)別下從包含大量數(shù)據(jù)的表中選取所有的行，以及在表上執(zhí)行不帶過(guò)濾條件的update或delete。 Heap?or B-Tree (HOBT)?-- 用于堆數(shù)據(jù)頁(yè)，或者索引的二叉樹(shù)結(jié)構(gòu)。 PAGE?-- 使用頁(yè)級(jí)鎖，由8KB數(shù)據(jù)或者索引信息組成的整個(gè)頁(yè)被鎖定。當(dāng)需要讀取一頁(yè)的所有行或者需要執(zhí)行頁(yè)級(jí)別的維護(hù)如頁(yè)拆分后更新頁(yè)指針時(shí)，將會(huì)獲取頁(yè)級(jí)鎖。 Row ID (RID)?-- 使用RID鎖，頁(yè)內(nèi)的單一行被鎖定。無(wú)論何時(shí)當(dāng)提供最大化的資源并發(fā)性訪問(wèn)是有效并且可能時(shí)，將獲得RID鎖。 KEY?-- SQL Server使用兩種類型的Key鎖。其中一個(gè)的使用取決于當(dāng)前會(huì)話的鎖隔離級(jí)別。對(duì)于運(yùn)行于Read Committed 或者 Repeatable Read 隔離模式下的事務(wù)，SQL Server 鎖定與被訪問(wèn)的行相關(guān)聯(lián)的的實(shí)際索引key。（如果是表的聚集索引，數(shù)據(jù)行位于索引的葉級(jí)。行上在這些你看到的是Key鎖而不是行級(jí)鎖。）若在Serializable隔離模式下，通過(guò)鎖定一定范圍的key值從而不允許新的行插入到該范圍內(nèi)，SQL Server防止了“幻讀”。這些鎖因而被稱作“key-range lock”。 METADATA?-- 用于鎖定系統(tǒng)目錄信息（元數(shù)據(jù)）。 APPLICATION?-- 允許用戶定義他們自己的鎖，指定資源名稱、鎖模式、所有者、timeout間隔。

Serialization 與 Key-Range Locking 如前所述， SQL Server 通過(guò)key-range鎖防止了“幻讀”。下面將介紹key-range鎖如何與各種鎖模式一起工作。

Key-Range Locking for a Range Search 在涉及范圍查找的key-range鎖的情況下，SQL Server 在查詢的WHERE子句所包含的數(shù)據(jù)范圍的索引頁(yè)上加鎖。（對(duì)于聚集索引，則是對(duì)表中的實(shí)際數(shù)據(jù)行加鎖。）因?yàn)樵搮^(qū)間被鎖定了，不允許其他事務(wù)往那個(gè)區(qū)間內(nèi)插入新的行。如下圖所示。


Key-Range Locking When Searching Nonexistent Rows 在涉及此種類型的鎖的情況下，如果事務(wù)試圖刪除或讀取數(shù)據(jù)庫(kù)中不存在的行，那么在該事務(wù)的以后階段，該查詢也不應(yīng)該找到任何行。如下圖所示。


行級(jí)鎖與頁(yè)級(jí)鎖之比較 行級(jí)鎖是否優(yōu)于頁(yè)級(jí)鎖的的爭(zhēng)論持續(xù)了多年，在某些圈子里至今仍在繼續(xù)。許多人堅(jiān)持認(rèn)為如果數(shù)據(jù)庫(kù)和應(yīng)用程序經(jīng)過(guò)良好的設(shè)計(jì)和優(yōu)化，行級(jí)鎖是不必要的。這種觀點(diǎn)誕生于行級(jí)鎖甚至還不存在的時(shí)候。（在SQL Server 7.0 之前，能夠鎖定的最小數(shù)據(jù)單元是頁(yè)。）然而，那時(shí)候SQL Server 中頁(yè)的大小只有2KB。隨著頁(yè)大小擴(kuò)大到8KB，單個(gè)頁(yè)中能夠包含更多數(shù)量的行（是先前的4倍）。8KB頁(yè)上的鎖可能導(dǎo)致更多的頁(yè)級(jí)競(jìng)爭(zhēng)，因?yàn)椴煌M(jìn)程請(qǐng)求同一個(gè)頁(yè)上數(shù)據(jù)行的可能性變得更大了。使用行級(jí)鎖將增加數(shù)據(jù)訪問(wèn)的可并發(fā)性。

另一方面，行級(jí)鎖比頁(yè)級(jí)鎖占用更多的資源（內(nèi)存和CPU），因?yàn)楸碇械男斜软?yè)數(shù)量更多。如果進(jìn)程需要訪問(wèn)頁(yè)上的所有行，鎖定整個(gè)頁(yè)比每行獲取一個(gè)鎖更加高效。這將減少Lock Manager需要管理的內(nèi)存中鎖結(jié)構(gòu)的數(shù)量。

哪一個(gè)更優(yōu) -- 更好的并發(fā)性還是較低的管理開(kāi)銷？如前所述，這二者間需要平衡。當(dāng)鎖的粒度變小，并發(fā)性就會(huì)得到提升，但性能會(huì)因額外的開(kāi)銷而降低。隨著鎖粒度變大，性能因管理開(kāi)銷的降低而得到提升，但是并發(fā)性降低了。取決于應(yīng)用程序、數(shù)據(jù)庫(kù)設(shè)計(jì)和數(shù)據(jù)（量的大?。?，行級(jí)鎖與頁(yè)級(jí)鎖哪個(gè)更合適得具體分析。

SQL Server 在運(yùn)行時(shí)自動(dòng)地做出決一開(kāi)始是鎖定行、頁(yè)還是整個(gè)表，基于查詢的性質(zhì)、表的大小、預(yù)計(jì)被影響的行的數(shù)量。一般地，SQL Server 更經(jīng)常地嘗試先應(yīng)用行級(jí)鎖而非頁(yè)級(jí)鎖，以便提供最佳的并發(fā)性。今天有了更快速的CPU和更大內(nèi)存的支持，行級(jí)鎖的管理開(kāi)銷不再像過(guò)去那樣昂貴。然而，當(dāng)查詢進(jìn)程和實(shí)際被鎖定的資源數(shù)量超過(guò)一定的閥值，SQL Server可能會(huì)嘗試從低級(jí)別鎖升級(jí)至適當(dāng)?shù)母呒?jí)別。

鎖競(jìng)爭(zhēng)與死鎖 SQL Server應(yīng)用程序性能問(wèn)題的最可能的原因是糟糕的查詢語(yǔ)句、糟糕的數(shù)據(jù)庫(kù)和索引設(shè)計(jì)、以及鎖競(jìng)爭(zhēng)。前2個(gè)問(wèn)題無(wú)論系統(tǒng)的用戶多少都會(huì)導(dǎo)致糟糕的應(yīng)用程序性能；而鎖競(jìng)爭(zhēng)導(dǎo)致的性能問(wèn)題隨著用戶數(shù)量的增加而顯現(xiàn)出來(lái)，隨著事務(wù)越來(lái)越復(fù)雜或者運(yùn)行時(shí)間越來(lái)越長(zhǎng)而更加趨于復(fù)雜化。

當(dāng)一個(gè)事務(wù)請(qǐng)求的鎖類型與該資源上現(xiàn)存的鎖類型不兼容時(shí)，鎖競(jìng)爭(zhēng)就發(fā)生了。默認(rèn)地，進(jìn)程無(wú)限期地等待鎖資源變得可用。如果客戶端應(yīng)用程序中來(lái)自 SQL Server 的響應(yīng)明顯不足，你應(yīng)該警惕鎖競(jìng)爭(zhēng)（問(wèn)題）。

下圖演示了一個(gè)鎖競(jìng)爭(zhēng)的例子。

設(shè)置鎖超時(shí)間隔 如果你不想讓進(jìn)程無(wú)限期等待鎖變得可用， SQL Server 允許你使用SET LOCK_TIMEOUT命令設(shè)定鎖超時(shí)間隔。你以毫秒為單位指定超時(shí)間隔。比如，如果你想讓進(jìn)程在鎖變得可用前僅等待5秒，那么執(zhí)行以下命令
SET LOCK_TIMEOUT 5000

如果請(qǐng)求鎖資源超時(shí)的話，語(yǔ)句將會(huì)中止，你將得到以下Error Message：
Server: Msg 1222, Level 16, State 52, Line 1
Lock request time out period exceeded.

查看當(dāng)前 LOCK_TIMEOUT 設(shè)置，可以使用系統(tǒng)函數(shù)@@lock_timeout。
select @@lock_timeout
如果你希望當(dāng)不能獲得鎖時(shí)進(jìn)程立即中止，則 set?
LOCK_TIMEOUT 0
如果你想要將timeout重新置為無(wú)限期，則 set?
LOCK_TIMEOUT -1

最小化鎖競(jìng)爭(zhēng) 為了最大化并發(fā)性和應(yīng)用程序性能，你應(yīng)該盡可能最小化進(jìn)程間的鎖競(jìng)爭(zhēng)。下面是一些一般性指導(dǎo)原則：
盡可能然事務(wù)保持運(yùn)行時(shí)間短和簡(jiǎn)潔。事務(wù)持有鎖的時(shí)間越短，鎖競(jìng)爭(zhēng)發(fā)生的機(jī)會(huì)就越少；將不是事務(wù)所管理的工作單元鎖必需的命令移出事務(wù)。

將組成事務(wù)的語(yǔ)句作為一個(gè)的單獨(dú)的批命令處理，以消除 BEGIN TRAN 和 COMMIT ?TRAN 語(yǔ)句之間的網(wǎng)絡(luò)延遲造成的不必要的延遲。

考慮完全地使用存儲(chǔ)過(guò)程編寫事務(wù)代碼。典型地，存儲(chǔ)過(guò)程比批命令運(yùn)行更快。

在游標(biāo)中盡可早地Commit更新。因?yàn)橛螛?biāo)處理比面向集合的處理慢得多，因此導(dǎo)致鎖被持有的時(shí)間更久。

使用每個(gè)進(jìn)程所需的最低級(jí)別的鎖隔離。比如說(shuō)，如果臟讀是可接受的并且不要求結(jié)果必須精確，那么可以考慮使用事務(wù)隔離級(jí)別0（Read Uncommitted）,僅在絕對(duì)必要時(shí)才使用Repeatable Read or Serializable隔離級(jí)別。

在 BEGIN TRAN 和 COMMIT TRAN 語(yǔ)句之間，絕不允許用戶交互，因?yàn)檫@樣做可能鎖被持有無(wú)限期的時(shí)間。

最小化表中的“熱點(diǎn)”。當(dāng)表中的大多數(shù)Update活動(dòng)發(fā)生在少量的頁(yè)中時(shí)，熱點(diǎn)出現(xiàn)了。

死鎖 當(dāng)兩個(gè)進(jìn)程各自都在等在對(duì)方當(dāng)前鎖定的資源時(shí)，死鎖就發(fā)生了。兩個(gè)進(jìn)程在獲得所請(qǐng)求資源上的鎖之前既不能前進(jìn)，也不能釋放當(dāng)前持有的鎖。

SQL Server 中可能發(fā)生2種類型的死鎖：

循環(huán)死鎖?-- 兩個(gè)進(jìn)程請(qǐng)求不同資源上的鎖，每一個(gè)進(jìn)程都需要對(duì)方持有的該資源上的鎖，這時(shí)將發(fā)生循環(huán)死鎖。如下圖。
轉(zhuǎn)換死鎖?-- 兩個(gè)或多個(gè)進(jìn)程都在事務(wù)中持有同一資源上的共享鎖，并且都想把它升級(jí)為獨(dú)占鎖，但是，誰(shuí)也沒(méi)法升級(jí)直到其他的進(jìn)程釋放共享鎖。 如圖所示。

人們經(jīng)常以為死鎖發(fā)生在數(shù)據(jù)頁(yè)級(jí)或數(shù)據(jù)行級(jí)。事實(shí)上，死鎖經(jīng)常發(fā)生在索引頁(yè)級(jí)或索引鍵級(jí)。下圖展示了由于索引鍵級(jí)的競(jìng)爭(zhēng)引發(fā)的死鎖場(chǎng)景。

SQL Server自動(dòng)地偵測(cè)何時(shí)死鎖情況發(fā)生。SQL Server 中一個(gè)獨(dú)立的進(jìn)程叫做LOCK_MONITOR，大約每5秒鐘檢查一次系統(tǒng)是否存在死鎖。

避免死鎖 遵循前文給出的最小化鎖競(jìng)爭(zhēng)指導(dǎo)原則，有助于消除死鎖。此外，當(dāng)設(shè)計(jì)應(yīng)用程序是你還需要遵循下列指導(dǎo)原則：
按照一致的順序訪問(wèn)多個(gè)表的數(shù)據(jù)以避免循環(huán)死鎖。

最小化HOLDLOCK的使用，或者最小化運(yùn)行于Repeatable Read 或者 Serializable 隔離模式下的查詢。這將有助于避免轉(zhuǎn)換死鎖。

明智而審慎地選擇事物隔離級(jí)別。選擇較低的隔離級(jí)別或許能減少死鎖。

Table Hints for Locking 前面提到過(guò)，你可以使用SET TRANSACTION ISOLATION LEVEL 命令為連接設(shè)置隔離級(jí)別。該命令為整個(gè)會(huì)話設(shè)定了全局的隔離級(jí)別，如果你想要為應(yīng)用程序提供一致的隔離級(jí)別，這很有用。然而，有時(shí)候你也想要許為特定的查詢或者單個(gè)查詢中的不同表指定不同的隔離級(jí)別。SQL Server 允許你在 SELECT, MERGE, UPDATE, INSERT, 和 DELETE 語(yǔ)句中使用表提示來(lái)實(shí)現(xiàn)此目的。這樣一來(lái)，你在會(huì)話級(jí)別改變了當(dāng)前的隔離級(jí)別。

用于改變表級(jí)鎖隔離、粒度或者鎖類型的表提示，通過(guò) SELECT, UPDATE, INSERT, 和 DELETE 語(yǔ)句的 WITH 操作符提供。

注意： 盡管許多表提示是可以組合使用的，但是，你不能一次在一個(gè)表上組合超過(guò)一個(gè)隔離級(jí)別或者鎖粒度的提示。另外，NOLOCK, READUNCOMMITTED, 和 READPAST 提示不能用于 INSERT, UPDATE, MERGE, 或 DELETE 語(yǔ)句的目標(biāo)表上。

Transaction Isolation–Level Hints SQL Server 提供了許多提示用于在查詢中改變默認(rèn)的事務(wù)隔離級(jí)別。

HOLDLOCK?-- 在語(yǔ)句執(zhí)行期間，或者在整個(gè)事務(wù)期間（如果語(yǔ)句在事務(wù)中的話）保持共享鎖。該選項(xiàng)等同于Serializable 隔離級(jí)別。 NOLOCK?-- 使用此選項(xiàng)指定不對(duì)資源施加共享鎖。它類似于在0隔離級(jí)別（Read Uncommitted）下運(yùn)行查詢。NOLOCK選項(xiàng)在對(duì)結(jié)果精度要求不嚴(yán)格的報(bào)表工作環(huán)境下很有用。 READUNCOMMITTED?-- 與指定 Read Uncommitted 隔離級(jí)別和NOLOCK提示完全一樣。 READCOMMITTED?-- 與指定 Read Committed 隔離級(jí)別一樣。 READCOMMITTEDLOCK?-- 當(dāng)數(shù)據(jù)被讀取時(shí)獲得共享鎖，讀取完成時(shí)釋放共享鎖，不管是否設(shè)定了 READ_COMMITTED_SNAPSHOT 隔離級(jí)別。 REPEATABLEREAD?-- 與指定 Repeatable Read 隔離級(jí)別一樣，類似于HOLDLOCK提示。 SERIALIZABLE?-- 與指定 Serializable 隔離級(jí)別一樣，類似于HOLDLOCK提示。 READPAST?-- 讓查詢忽略被其他事務(wù)鎖定的行或頁(yè)，僅返回能夠被讀取的數(shù)據(jù)。只能用在運(yùn)行于Read Committed 或 Repeatable Read 隔離級(jí)別下的事務(wù)中。

Lock Granularity Hints 用于改變鎖粒度：

ROWLOCK?-- 強(qiáng)制 Lock Manager 在資源上施加行級(jí)鎖而非頁(yè)級(jí)鎖或表級(jí)鎖。 PAGLOCK?-- 強(qiáng)制 Lock Manager 在資源上施加頁(yè)級(jí)鎖而非行級(jí)鎖或表級(jí)鎖。 TABLOCK?-- 強(qiáng)制 Lock Manager 在資源上施加表級(jí)鎖而非行級(jí)鎖或頁(yè)級(jí)鎖。 TABLOCKX?-- 強(qiáng)制 Lock Manager?在資源上施加表級(jí)獨(dú)占鎖而非行級(jí)鎖或頁(yè)級(jí)鎖。

Lock Type Hints 用于改變SQL Server 使用的鎖類型：

UPDLOCK?-- 類似于HOLDLOCK，不過(guò)HOLDLOCK在資源上應(yīng)用共享鎖，而UPDLOCK是在事務(wù)期間應(yīng)用更新鎖。 XLOCK?-- 在事務(wù)期間在資源上應(yīng)用獨(dú)占鎖。它阻止其他事務(wù)獲取該資源上的鎖。

樂(lè)觀鎖 許多應(yīng)用程序中，客戶端需要讀取數(shù)據(jù)用于瀏覽，然后修改其中的一些行并將修改提交回SQL Server 數(shù)據(jù)庫(kù)。讀取數(shù)據(jù)和提交更改后的數(shù)據(jù)之間的時(shí)間間隔可能很長(zhǎng)（假如用戶讀取數(shù)據(jù)后去吃午飯了）。

在這類應(yīng)用程序中，你不愿使用如SERIALIZABLE或HOLDLOCK鎖模式來(lái)鎖定數(shù)據(jù)，因?yàn)閺挠脩糇x取數(shù)據(jù)到提交更新的期間，沒(méi)有人能更改它。這違背了最小化鎖競(jìng)爭(zhēng)和死鎖的原則--不允許事務(wù)中的用戶交互。在多用戶的OLTP環(huán)境下，由于所阻塞和鎖競(jìng)爭(zhēng)，無(wú)限期持有共享鎖將對(duì)并發(fā)性和應(yīng)用的整體性能有重大影響。

另一方面，如果不在被讀取的行上加鎖，在這期間另一個(gè)進(jìn)程可能會(huì)更新其中某一行數(shù)據(jù)，當(dāng)?shù)谝粋€(gè)進(jìn)程提交它的更新時(shí)，將覆蓋另一個(gè)進(jìn)程先前所做的更改，從而導(dǎo)致Lost Update。

那么，該如何實(shí)現(xiàn)這樣的應(yīng)用程序呢？怎樣讓用戶讀取數(shù)據(jù)而無(wú)需鎖定數(shù)據(jù)并仍能保證不會(huì)發(fā)生Lost Update呢？

樂(lè)觀鎖就是在讀取數(shù)據(jù)與提交更改之間時(shí)間間隔很久的情況下使用的技術(shù)。樂(lè)觀鎖避免了一個(gè)客戶端覆蓋另一個(gè)客戶端對(duì)數(shù)據(jù)的修改并且無(wú)需持有數(shù)據(jù)庫(kù)中的鎖。

實(shí)現(xiàn)樂(lè)觀鎖有2個(gè)辦法，其一是使用rowversion數(shù)據(jù)類型，其二是利用snapshot隔離的樂(lè)觀并發(fā)性特性。

使用rowversion數(shù)據(jù)類型實(shí)現(xiàn)樂(lè)觀鎖 SQL Server 2008 提供了一個(gè)特殊數(shù)據(jù)類型rowversion,它可以用于在應(yīng)用程序中實(shí)現(xiàn)樂(lè)觀鎖。rowversion數(shù)據(jù)類型在樂(lè)觀鎖模式下充當(dāng)版本號(hào)。無(wú)論何時(shí)包含rowversion類型數(shù)據(jù)列的行被插入或更新時(shí)，SQL Server 自動(dòng)為該列生成一個(gè)值。rowversion數(shù)據(jù)類型是8字節(jié)的二進(jìn)制數(shù)據(jù)類型，除了保證值的唯一性和單向增長(zhǎng)外，它的值不具有意義。你不能夠查看它的每個(gè)字節(jié)來(lái)搞懂它是什么意思。

客戶端從表中讀取數(shù)據(jù)，確保返回的結(jié)果集中包含了主鍵和rowversion列，以及其他想要的數(shù)據(jù)列。由于查詢并不運(yùn)行在事務(wù)中，一旦數(shù)據(jù)被讀取，SELECT查詢獲取的鎖即被釋放。當(dāng)一段時(shí)間過(guò)后用戶想要更新某行時(shí)，必須確保在此期間該數(shù)據(jù)沒(méi)有被其他客戶端修改過(guò)。Update語(yǔ)句必須包含WHERE子句用以比較取回的rowversion值與數(shù)據(jù)庫(kù)中該列的當(dāng)前值。如果兩個(gè)值匹配（即相同），說(shuō)明該行記錄在此期間沒(méi)有被修改過(guò)。因此可以放心提交更改。如果不匹配，則說(shuō)明該行記錄已經(jīng)被修改過(guò)。為了避免Lost Update問(wèn)題發(fā)生，不應(yīng)提交本次更新。若要返回?cái)?shù)據(jù)庫(kù)的當(dāng)前行版本值，請(qǐng)使用 @@DBTS。

CREATE?TABLE?ExampleTable2?(PriKey?int?PRIMARY?KEY,?VerCol?rowversion)

下面是一個(gè)完整實(shí)現(xiàn)的示例代碼。



使用Snapshot隔離級(jí)別的樂(lè)觀鎖 SQL Server 2008 的Snapshot隔離模式通過(guò)自動(dòng)的row versioning提供了實(shí)現(xiàn)樂(lè)觀鎖的另一種機(jī)制。當(dāng)Snapshot隔離模式啟用時(shí)，如果一個(gè)進(jìn)程在事務(wù)中讀取數(shù)據(jù)，當(dāng)前版本的數(shù)據(jù)行上不會(huì)獲得或持有鎖。進(jìn)程讀取的是查詢發(fā)生時(shí)候的數(shù)據(jù)版本。由于數(shù)據(jù)行沒(méi)有被鎖定，因而不會(huì)導(dǎo)致阻塞，其他進(jìn)程在數(shù)據(jù)被讀取后可以修改它。如果另外的進(jìn)程修改了該數(shù)據(jù)行，就會(huì)產(chǎn)生該行的一個(gè)新版本。如果第一個(gè)進(jìn)程這時(shí)試圖更新該數(shù)據(jù)行，SQL Server 通過(guò)檢查 row version 自動(dòng)地防止了Lost Update問(wèn)題。由于 row version 不同，SQL Server阻止第一個(gè)進(jìn)程修改該數(shù)據(jù)行。如果試圖修改，將出現(xiàn)類似于以下錯(cuò)誤消息：


參考
Microsoft SQL Server 2008 R2 Unleashed