|
相比于 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ù)類(lèi)型，它已被rowversion數(shù)據(jù)類(lèi)型取代。 為什么需要鎖？在任何多用戶的數(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種情況：
SQL Server 2008 支持6種隔離級(jí)別，分別是 （詳情請(qǐng)參考我的另一篇blog：SQL Server 2008 R2 事務(wù)與隔離級(jí)別實(shí)例講解） 鎖管理器解決不同用戶進(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類(lèi)型（如shared, exclusive, update）和鎖粒度（如row, page,table），根據(jù)正在執(zhí)行的操作類(lèi)型和所影響的數(shù)據(jù)量。 Lock Manager還管理試圖訪問(wèn)同一資源的鎖類(lèi)型之間的兼容性，解決死鎖，必要時(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使用門(mén)閂（latch）來(lái)管理索引行和頁(yè)上的鎖控制對(duì)內(nèi)部數(shù)據(jù)結(jié)構(gòu)的訪問(wèn)，以及在某些情況下，用于取回單個(gè)的數(shù)據(jù)行。門(mén)閂提供了更好的系統(tǒng)性能，因?yàn)樗幌矜i那般資源密集。門(mén)閂也提供了比鎖更好的并發(fā)性。門(mén)閂典型地用于像頁(yè)拆分、索引行的刪除、索引中行的移動(dòng)等操作。鎖與門(mén)閂之間最主要的區(qū)別在于，鎖在整個(gè)事務(wù)存續(xù)期間都被持有，而門(mén)閂僅在需要它的操作存續(xù)期間被持有。鎖用于保證數(shù)據(jù)的邏輯一致性，而門(mén)閂用于保證數(shù)據(jù)和數(shù)據(jù)結(jié)構(gòu)的物理一致性。 SQL Server 鎖類(lèi)型鎖在SQL Server中是自動(dòng)處理的。Lock Manager 基于事務(wù)類(lèi)型（如SELECT, INSERT, UPDATE, 或者DELETE)選擇鎖的類(lèi)型.Lock Manager使用以下的鎖類(lèi)型：除了選擇鎖類(lèi)型，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)查詢(xún)開(kāi)始時(shí)，先鎖定authors表中的第一行；當(dāng)?shù)谝恍斜蛔x取以后，它上面的鎖被釋放，并且了第二行上的鎖；第二行讀到以后，它上面的鎖被釋放，同時(shí)獲取了第三行上的鎖；以此類(lèi)推。按此方式，一個(gè)SELECT查詢(xún)?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)防止此類(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ú)占鎖與其他的所類(lèi)型不兼容。如果資源持有了獨(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í)別上所持有的鎖類(lèi)型。有3種類(lèi)型的意向鎖（分別對(duì)應(yīng)于之前提到的3種鎖類(lèi)型）：共享意向鎖、獨(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í)持有的鎖類(lèi)型，而不是在表中的行或頁(yè)級(jí)查遍多個(gè)鎖。 當(dāng)監(jiān)視鎖活動(dòng)時(shí)典型地你將看到3種類(lèi)型的意向鎖：意向共享鎖（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ù)隔離的鎖類(lèi)型，架構(gòu)鎖提供事務(wù)中對(duì)數(shù)據(jù)庫(kù)對(duì)象如表、視圖、索引的schema隔離。Lock Manager提供2種類(lèi)型的架構(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）大容量更新鎖是一種特殊類(lèi)型的鎖，僅用于使用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ù)提供完全的隔離和序列化，作為查詢(xún)或更新的一部分被鎖定的數(shù)據(jù)的總量（的大?。?。Lock Manager需要在資源的并發(fā)訪問(wèn)與維護(hù)大量低級(jí)別鎖的管理開(kāi)銷(xiāo)之間取得平衡。比如，鎖的粒度越小，能夠同時(shí)訪問(wèn)同一張表的并發(fā)用戶的數(shù)量就越大，不過(guò)維護(hù)這些鎖的管理開(kāi)銷(xiāo)也越大。鎖的粒度越大，管理鎖需要的開(kāi)銷(xiāo)就越少，而并發(fā)性也降低了。下圖說(shuō)明了鎖的大小與并發(fā)性之間的權(quán)衡取舍。 當(dāng)前，SQL Server通過(guò)在行或更高級(jí)別加鎖來(lái)平衡性能和并發(fā)性?；诟鞣N因素，如key的分布，行的數(shù)量，行的密度，查詢(xún)參數(shù)（SARGs）等等，Query Optimizer內(nèi)部地做出鎖粒度選擇，程序員不需要為此擔(dān)心。SQL Server提供了大量T_SQL擴(kuò)展，使你能從鎖的角度來(lái)更好地控制查詢(xún)行為。 SQL Server 提供以下的鎖級(jí)別:
Serialization 與 Key-Range Locking如前所述， SQL Server 通過(guò)key-range鎖防止了“幻讀”。下面將介紹key-range鎖如何與各種鎖模式一起工作。Key-Range Locking for a Range Search在涉及范圍查找的key-range鎖的情況下，SQL Server 在查詢(xún)的WHERE子句所包含的數(shù)據(jù)范圍的索引頁(yè)上加鎖。（對(duì)于聚集索引，則是對(duì)表中的實(shí)際數(shù)據(jù)行加鎖。）因?yàn)樵搮^(qū)間被鎖定了，不允許其他事務(wù)往那個(gè)區(qū)間內(nèi)插入新的行。如下圖所示。 Key-Range Locking When Searching Nonexistent Rows在涉及此種類(lèi)型的鎖的情況下，如果事務(wù)試圖刪除或讀取數(shù)據(jù)庫(kù)中不存在的行，那么在該事務(wù)的以后階段，該查詢(xún)也不應(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)銷(xiāo)？如前所述，這二者間需要平衡。當(dāng)鎖的粒度變小，并發(fā)性就會(huì)得到提升，但性能會(huì)因額外的開(kāi)銷(xiāo)而降低。隨著鎖粒度變大，性能因管理開(kāi)銷(xiāo)的降低而得到提升，但是并發(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è)表，基于查詢(xún)的性質(zhì)、表的大小、預(yù)計(jì)被影響的行的數(shù)量。一般地，SQL Server 更經(jīng)常地嘗試先應(yīng)用行級(jí)鎖而非頁(yè)級(jí)鎖，以便提供最佳的并發(fā)性。今天有了更快速的CPU和更大內(nèi)存的支持，行級(jí)鎖的管理開(kāi)銷(xiāo)不再像過(guò)去那樣昂貴。然而，當(dāng)查詢(xún)進(jìn)程和實(shí)際被鎖定的資源數(shù)量超過(guò)一定的閥值，SQL Server可能會(huì)嘗試從低級(jí)別鎖升級(jí)至適當(dāng)?shù)母呒?jí)別。 鎖競(jìng)爭(zhēng)與死鎖SQL Server應(yīng)用程序性能問(wèn)題的最可能的原因是糟糕的查詢(xú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)求的鎖類(lèi)型與該資源上現(xiàn)存的鎖類(lèi)型不兼容時(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ò)程編寫(xiě)事務(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種類(lèi)型的死鎖： 人們經(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 隔離模式下的查詢(xún)。這將有助于避免轉(zhuǎn)換死鎖。 明智而審慎地選擇事物隔離級(jí)別。選擇較低的隔離級(jí)別或許能減少死鎖。 Table Hints for Locking前面提到過(guò)，你可以使用SET TRANSACTION ISOLATION LEVEL 命令為連接設(shè)置隔離級(jí)別。該命令為整個(gè)會(huì)話設(shè)定了全局的隔離級(jí)別，如果你想要為應(yīng)用程序提供一致的隔離級(jí)別，這很有用。然而，有時(shí)候你也想要許為特定的查詢(xún)或者單個(gè)查詢(xún)中的不同表指定不同的隔離級(jí)別。SQL Server 允許你在 SELECT, MERGE, UPDATE, INSERT, 和 DELETE 語(yǔ)句中使用表提示來(lái)實(shí)現(xiàn)此目的。這樣一來(lái)，你在會(huì)話級(jí)別改變了當(dāng)前的隔離級(jí)別。用于改變表級(jí)鎖隔離、粒度或者鎖類(lèi)型的表提示，通過(guò) SELECT, UPDATE, INSERT, 和 DELETE 語(yǔ)句的 WITH 操作符提供。 注意： 盡管許多表提示是可以組合使用的，但是，你不能一次在一個(gè)表上組合超過(guò)一個(gè)隔離級(jí)別或者鎖粒度的提示。另外，NOLOCK, READUNCOMMITTED, 和 READPAST 提示不能用于 INSERT, UPDATE, MERGE, 或 DELETE 語(yǔ)句的目標(biāo)表上。 Transaction Isolation–Level HintsSQL Server 提供了許多提示用于在查詢(xún)中改變默認(rèn)的事務(wù)隔離級(jí)別。
Lock Granularity Hints用于改變鎖粒度：
Lock Type Hints用于改變SQL Server 使用的鎖類(lèi)型：
樂(lè)觀鎖許多應(yīng)用程序中，客戶端需要讀取數(shù)據(jù)用于瀏覽，然后修改其中的一些行并將修改提交回SQL Server 數(shù)據(jù)庫(kù)。讀取數(shù)據(jù)和提交更改后的數(shù)據(jù)之間的時(shí)間間隔可能很長(zhǎng)（假如用戶讀取數(shù)據(jù)后去吃午飯了）。在這類(lèi)應(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ù)類(lèi)型，其二是利用snapshot隔離的樂(lè)觀并發(fā)性特性。 使用rowversion數(shù)據(jù)類(lèi)型實(shí)現(xiàn)樂(lè)觀鎖SQL Server 2008 提供了一個(gè)特殊數(shù)據(jù)類(lèi)型rowversion,它可以用于在應(yīng)用程序中實(shí)現(xiàn)樂(lè)觀鎖。rowversion數(shù)據(jù)類(lèi)型在樂(lè)觀鎖模式下充當(dāng)版本號(hào)。無(wú)論何時(shí)包含rowversion類(lèi)型數(shù)據(jù)列的行被插入或更新時(shí)，SQL Server 自動(dòng)為該列生成一個(gè)值。rowversion數(shù)據(jù)類(lèi)型是8字節(jié)的二進(jìn)制數(shù)據(jù)類(lèi)型，除了保證值的唯一性和單向增長(zhǎng)外，它的值不具有意義。你不能夠查看它的每個(gè)字節(jié)來(lái)搞懂它是什么意思。客戶端從表中讀取數(shù)據(jù)，確保返回的結(jié)果集中包含了主鍵和rowversion列，以及其他想要的數(shù)據(jù)列。由于查詢(xún)并不運(yùn)行在事務(wù)中，一旦數(shù)據(jù)被讀取，SELECT查詢(xún)獲取的鎖即被釋放。當(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)提交本次更新。 下面是一個(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)程讀取的是查詢(xú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)類(lèi)似于以下錯(cuò)誤消息：
|
|
|
來(lái)自： 咸咸咸咸魚(yú)干 > 《鎖知識(shí)》
