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

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

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

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

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

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

SQL Server 2008 支持6種隔離級別，分別是

Read Uncommitted Read Committed Repeatable Read Serializable Snapshot Read Committed Snapshot

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Server 提供以下的鎖級別:

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

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

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


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


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

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

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

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

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

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

下圖演示了一個鎖競爭的例子。

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

如果請求鎖資源超時的話，語句將會中止，你將得到以下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)不能獲得鎖時進程立即中止，則 set?
LOCK_TIMEOUT 0
如果你想要將timeout重新置為無限期，則 set?
LOCK_TIMEOUT -1

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

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

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

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

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

在 BEGIN TRAN 和 COMMIT TRAN 語句之間，絕不允許用戶交互，因為這樣做可能鎖被持有無限期的時間。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Lock Granularity Hints 用于改變鎖粒度：

ROWLOCK?-- 強制 Lock Manager 在資源上施加行級鎖而非頁級鎖或表級鎖。 PAGLOCK?-- 強制 Lock Manager 在資源上施加頁級鎖而非行級鎖或表級鎖。 TABLOCK?-- 強制 Lock Manager 在資源上施加表級鎖而非行級鎖或頁級鎖。 TABLOCKX?-- 強制 Lock Manager?在資源上施加表級獨占鎖而非行級鎖或頁級鎖。

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

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

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

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

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

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

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

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

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

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

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

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



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


參考
Microsoft SQL Server 2008 R2 Unleashed