SQL Server并發(fā)控制

1、LogoLogo SQL Server并發(fā)控制 Logo SQL Server鎖的粒度及模式 Contents 1 封封鎖協(xié)議鎖協(xié)議 2 事事務(wù)務(wù)隔離隔離 3 死死鎖處鎖處理理 4 Logo 并發(fā)控制 在大型分布式數(shù)據(jù)庫(kù)應(yīng)用程序中，對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪(fǎng)問(wèn) 操作是一個(gè)普遍存在的問(wèn)題。SQL Server使用資源鎖 定的方法管理用戶(hù)的并發(fā)操作。 SQL Server 2000提 供了兩種并發(fā)控制方法： 樂(lè)觀(guān)并發(fā)控制：該方法假想用戶(hù)之間不太可能發(fā)生資 源沖突（事實(shí)上不是不可能），所以允許用戶(hù)在不鎖定 任何資源的情況下執(zhí)行事務(wù)。只有當(dāng)用戶(hù)試圖修改數(shù)據(jù) 時(shí)才檢查資源是否沖突。該方法需要使用游標(biāo)，游標(biāo)中 介紹

2、。 悲觀(guān)并發(fā)控制：該方法根據(jù)需要在事務(wù)的持續(xù)時(shí)間內(nèi) 鎖定資源，從而確保事務(wù)的完整性和數(shù)據(jù)庫(kù)的一致性。 這是SQL Server 2000默認(rèn)的并發(fā)控制方法。下面予 以介紹。 1 SQL Server鎖的粒度及模式鎖的粒度及模式 1、SQL Server 鎖的粒度 1）RID：行標(biāo)識(shí)符，鎖定表中單行數(shù)據(jù)。 2）鍵值：具有索引的行數(shù)據(jù)。 3）頁(yè)面：一個(gè)數(shù)據(jù)頁(yè)面或索引頁(yè)面。 4）區(qū)域：一組連續(xù)的8個(gè)數(shù)據(jù)頁(yè)面或索引頁(yè)面。 5）表： 整個(gè)表，包括其所有的數(shù)據(jù)和索引。 6）數(shù)據(jù)庫(kù)：一個(gè)完整的數(shù)據(jù)庫(kù)。 可以根據(jù)事務(wù)所執(zhí)行的任務(wù)來(lái)靈活選擇所鎖定的資源粒度。 2、資源鎖定模式基本鎖 1）共享鎖：用于只讀數(shù)據(jù)操作

3、，它允許多個(gè)并發(fā)事務(wù) 對(duì)資源鎖定進(jìn)行讀取，但禁止其他事務(wù)對(duì)鎖定資源的修 改操作。 2）排它鎖：它鎖定的資源不能被其它并發(fā)事務(wù)再進(jìn)行 任何鎖定，所以其它事務(wù)不能讀取和修改。鎖定的資源 用于自己的數(shù)據(jù)修改。 一般更新模式是由一個(gè)事務(wù)組成，該事務(wù)先讀取記錄， 要獲取資源的共享鎖，然后修改記錄，此操作要求鎖轉(zhuǎn) 換為排它鎖。如果兩個(gè)事務(wù)都獲得了資源上的共享鎖， 然后試圖同時(shí)更新數(shù)據(jù)，這樣肯定有一個(gè)事務(wù)要將共享 鎖轉(zhuǎn)化為排它鎖，因?yàn)橐粋€(gè)事務(wù)的排它鎖與其它事務(wù)的 共享鎖不兼容，發(fā)生鎖等待。另一個(gè)事務(wù)也會(huì)出現(xiàn)這個(gè) 問(wèn)題，由于兩個(gè)事務(wù)都要轉(zhuǎn)化為排它鎖，并且都等待另 一個(gè)事務(wù)釋放共享鎖，因此發(fā)生死鎖。 3）更新鎖

4、：在修改操作的初始化階段用于鎖定可能被 修改的資源。一個(gè)數(shù)據(jù)修改事務(wù)在開(kāi)始時(shí)直接申請(qǐng)更新 鎖，每次只有一個(gè)事務(wù)可以獲得資源的更新鎖。 使用更新鎖可以避免上述死鎖，因?yàn)橐淮沃挥幸粋€(gè)事務(wù) 可以獲得更新鎖，之后當(dāng)需要繼續(xù)修改數(shù)據(jù)時(shí)，將更新 鎖轉(zhuǎn)換為排它鎖，否則將更新鎖轉(zhuǎn)換為共享鎖。 4）意向鎖 意向鎖表示如果獲得一個(gè)對(duì)象的鎖，說(shuō)明該結(jié)點(diǎn)的下層 對(duì)象正在被加鎖。例如放置在表上的共享意向鎖表示事 務(wù)打算在表中的頁(yè)或行上加共享鎖。 意向鎖可以提高性能，因?yàn)橄到y(tǒng)僅在表級(jí)上檢查意向鎖 而無(wú)須檢查下層。 意向共享鎖：對(duì)一個(gè)對(duì)象加意向共享鎖，表示將要對(duì) 它的下層對(duì)象加共享鎖。 意向排它鎖：對(duì)一個(gè)對(duì)象加意向排它鎖，

5、表示將要對(duì) 它的下層對(duì)象加排它鎖。 意向排它共享鎖：對(duì)一個(gè)對(duì)象加意向排它共享鎖，表 示對(duì)它加共享鎖，再在它的下層對(duì)象加排它鎖。 5）架構(gòu)鎖 架構(gòu)修改鎖：執(zhí)行表的數(shù)據(jù)定義語(yǔ)言（DDL）操作時(shí) 使用。 架構(gòu)穩(wěn)定鎖：編譯查詢(xún)時(shí)使用。它不阻塞任何事務(wù)鎖 ，包括排它鎖。 6）大容量更新鎖： 當(dāng)數(shù)據(jù)大容量復(fù)制到表的時(shí)候使用。 2 封鎖協(xié)議 在運(yùn)用X鎖和S鎖對(duì)數(shù)據(jù)對(duì)象加鎖時(shí)，需要約定一些規(guī) 則：封鎖協(xié)議（Locking Protocol） 何時(shí)申請(qǐng)X鎖或S鎖 持鎖時(shí)間、何時(shí)釋放 不同的封鎖協(xié)議，在不同的程度上為并發(fā)操作的正確 調(diào)度提供一定的保證 常用的封鎖協(xié)議：三級(jí)封鎖協(xié)議 1級(jí)封鎖協(xié)議 2級(jí)封鎖協(xié)議 1級(jí)

6、封鎖協(xié)議+事務(wù)T在讀取數(shù)據(jù)R前必須先加S 鎖，讀完后即可釋放S鎖 2級(jí)封鎖協(xié)議可以防止丟失修改和讀“臟”數(shù)據(jù)。 在2級(jí)封鎖協(xié)議中，由于讀完數(shù)據(jù)后即可釋放S 鎖，所以它不能保證可重復(fù)讀。 3級(jí)封鎖協(xié)議 1級(jí)封鎖協(xié)議 + 事務(wù)T在讀取數(shù)據(jù)R之前必須先 對(duì)其加S鎖，直到事務(wù)結(jié)束才釋放 3級(jí)封鎖協(xié)議可防止丟失修改、讀臟數(shù)據(jù)和不可 重復(fù)讀。 容易造成比較多的死鎖 封鎖協(xié)議小結(jié)封鎖協(xié)議小結(jié) 3 事務(wù)隔離 為了避免產(chǎn)生并發(fā)訪(fǎng)問(wèn)問(wèn)題，SQL Server使用 不同類(lèi)型的鎖對(duì)資源進(jìn)行鎖定，從而限制在一 個(gè)事務(wù)讀取數(shù)據(jù)期間其他事務(wù)鎖執(zhí)行的操作類(lèi) 型，即對(duì)事務(wù)進(jìn)行隔離。 不同的并發(fā)訪(fǎng)問(wèn)問(wèn)題可以通過(guò)設(shè)置不同的事務(wù) 隔

7、離級(jí)別加以解決。事務(wù)的隔離級(jí)別控制一個(gè) 事務(wù)與其他事務(wù)的隔離程度，它決定該事務(wù)在 讀取數(shù)據(jù)時(shí)對(duì)資源所使用的鎖類(lèi)型。 1）事務(wù)隔離級(jí)別）事務(wù)隔離級(jí)別 未提交讀：未提交讀：這是這是4種隔離級(jí)別中限制最低的級(jí)別，它僅能保證種隔離級(jí)別中限制最低的級(jí)別，它僅能保證SQL Server不讀取物理?yè)p壞的數(shù)據(jù)。在這種隔離級(jí)別下，讀數(shù)據(jù)時(shí)不不讀取物理?yè)p壞的數(shù)據(jù)。在這種隔離級(jí)別下，讀數(shù)據(jù)時(shí)不 發(fā)出共享鎖，也不接受排它鎖，事務(wù)可以對(duì)數(shù)據(jù)執(zhí)行臟讀；在事務(wù)發(fā)出共享鎖，也不接受排它鎖，事務(wù)可以對(duì)數(shù)據(jù)執(zhí)行臟讀；在事務(wù) 結(jié)束前可以更改數(shù)據(jù)集內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)結(jié)束前可以更改數(shù)據(jù)集內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)

8、集中或從數(shù) 據(jù)集消失。但可以防止產(chǎn)生丟失修改。（一級(jí)封鎖協(xié)議）據(jù)集消失。但可以防止產(chǎn)生丟失修改。（一級(jí)封鎖協(xié)議） 提交讀：提交讀：它要求在讀取數(shù)據(jù)時(shí)控制共享鎖以避免發(fā)生臟讀，但數(shù)據(jù)它要求在讀取數(shù)據(jù)時(shí)控制共享鎖以避免發(fā)生臟讀，但數(shù)據(jù) 可在事務(wù)結(jié)束前更改，這可能產(chǎn)生不能重復(fù)讀或幻影讀問(wèn)題?？稍谑聞?wù)結(jié)束前更改，這可能產(chǎn)生不能重復(fù)讀或幻影讀問(wèn)題。 （ 二級(jí)封鎖協(xié)議）二級(jí)封鎖協(xié)議） 可重復(fù)讀：可重復(fù)讀：鎖定查詢(xún)中使用的所有數(shù)據(jù)以防止其他用戶(hù)更新，但是鎖定查詢(xún)中使用的所有數(shù)據(jù)以防止其他用戶(hù)更新，但是 其他用戶(hù)可以將新的幻影行插入到數(shù)據(jù)集中，新插入的幻影行將出其他用戶(hù)可以將新的幻影行插入到數(shù)據(jù)集中，新插入的

9、幻影行將出 現(xiàn)在當(dāng)前事物的后續(xù)讀取結(jié)果集中?？芍貜?fù)讀能夠避免產(chǎn)生臟讀和現(xiàn)在當(dāng)前事物的后續(xù)讀取結(jié)果集中?？芍貜?fù)讀能夠避免產(chǎn)生臟讀和 非重復(fù)讀問(wèn)題，但仍可能導(dǎo)致幻影讀問(wèn)題。非重復(fù)讀問(wèn)題，但仍可能導(dǎo)致幻影讀問(wèn)題。 （三級(jí)封鎖協(xié)議）（三級(jí)封鎖協(xié)議） 可串行讀：可串行讀：這是事務(wù)隔離的最高級(jí)別，它使事務(wù)之間完全隔離，所這是事務(wù)隔離的最高級(jí)別，它使事務(wù)之間完全隔離，所 以將導(dǎo)致并發(fā)級(jí)別較低。在這種隔離級(jí)別下，以將導(dǎo)致并發(fā)級(jí)別較低。在這種隔離級(jí)別下，SQL Server在數(shù)在數(shù) 據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶(hù)在事務(wù)完成之前更新數(shù)據(jù)據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶(hù)在事務(wù)完成之前更新數(shù)據(jù) 集或向數(shù)據(jù)集

10、內(nèi)插入數(shù)據(jù)行，從而避免出現(xiàn)臟讀、非重復(fù)讀或幻影集或向數(shù)據(jù)集內(nèi)插入數(shù)據(jù)行，從而避免出現(xiàn)臟讀、非重復(fù)讀或幻影 讀等并發(fā)問(wèn)題。讀等并發(fā)問(wèn)題。 （四級(jí)封鎖協(xié)議）（四級(jí)封鎖協(xié)議） 2）事務(wù)隔離級(jí)別對(duì)不同類(lèi)型的行為 隔離級(jí)別隔離級(jí)別 丟失修改丟失修改 臟數(shù)據(jù)臟數(shù)據(jù) 不可重讀不可重讀 幻影幻影 未提交讀未提交讀 否否 是是 是是 是是 提交讀提交讀 否否 否否 是是 是是 可重復(fù)讀可重復(fù)讀 否否 否否 否否 是是 可串行讀可串行讀 否否 否否 否否 否否 3）設(shè)置事務(wù)隔離級(jí)別 調(diào)用調(diào)用T-SQL中的中的SET TRANSACTION INOLATION LEVEL語(yǔ)句可以調(diào)整語(yǔ)句可以調(diào)整 事務(wù)的隔離級(jí)別，以

11、控制由該連接所發(fā)出的所有事務(wù)的隔離級(jí)別，以控制由該連接所發(fā)出的所有SELECT語(yǔ)句的默認(rèn)事務(wù)鎖定行為語(yǔ)句的默認(rèn)事務(wù)鎖定行為 。該語(yǔ)句的語(yǔ)法格式為：。該語(yǔ)句的語(yǔ)法格式為： SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |READ COMMITTED |REPEATABLE READ |SERIALIZABLE 例如：將事務(wù)隔離級(jí)別設(shè)置為例如：將事務(wù)隔離級(jí)別設(shè)置為REPEATABLE READ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 注意：一旦設(shè)定，系統(tǒng)就會(huì)按這種隔離級(jí)別自動(dòng)進(jìn)行并發(fā)處理。注

12、意：一旦設(shè)定，系統(tǒng)就會(huì)按這種隔離級(jí)別自動(dòng)進(jìn)行并發(fā)處理。 設(shè)置表級(jí)鎖 表級(jí)鎖是由程序員進(jìn)行的，可以對(duì)SELECT、INSERT 、UPDATE、DELETE語(yǔ)句進(jìn)行精確控制。 表級(jí)鎖定不是系統(tǒng)自動(dòng)完成，而是需要程序員自己根據(jù) 事務(wù)的要求進(jìn)行鎖定，然后，系統(tǒng)按程序員在程序中的 鎖定予以執(zhí)行。 一般來(lái)說(shuō)，讀操作需要共享鎖，寫(xiě)操作需要排它鎖。如 果需要更精確，還需要一些其它專(zhuān)用鎖。具體設(shè)置有： 設(shè)置共享鎖 設(shè)置排它鎖 設(shè)置專(zhuān)用鎖 設(shè)置共享鎖 共享鎖用于讀操作。一個(gè)資源可獲得多個(gè)共享鎖。 使用HOLDLOCK設(shè)置共享鎖。 例： USE Northwind GO BEGIN TRANSANCTION T1

13、 SELECT OrderID,OrderDate FROM Orders WITH (HOLDLOCK) SELECT COUNT(OrderID) FROM Orders COMMIT 設(shè)置排它鎖 對(duì)于INSERT、UPDATE和DELETE語(yǔ)句使用排它鎖。 在并發(fā)事務(wù)中，只有一個(gè)事務(wù)能夠獲得資源的排它鎖。 使用TABLOCKX設(shè)置排它鎖。 例： USE Northwind GO BEGIN TRANSANCTION T1 INSERT INTO Orders WITH (TABLOCKX) (CustomerID,OrderDate) VALUES (ALEF,2005-01-01) COMMIT 設(shè)置其它專(zhuān)用鎖 方法同上。 NOLOCK：不要發(fā)出共享鎖和排它鎖。僅適用 SELECT語(yǔ)句。 READPAST：跳過(guò)已經(jīng)鎖定的行。僅適用 SELECT語(yǔ)句。 TABLOCK：使用大容量更新鎖。 PAGLOCK：使用頁(yè)級(jí)鎖。 ROWLOCK：使用行級(jí)鎖，不使用頁(yè)級(jí)和表級(jí) 鎖。 UPDLOCK：讀取表時(shí)使用更新鎖。 XLOCK：適用于不同粒度的排它鎖。 4 死鎖處理 SQL Server能夠自動(dòng)定期搜索和處理