實驗14 并發(fā)控制實驗

1、德州學(xué)院德州學(xué)院 數(shù)據(jù)庫技術(shù)與應(yīng)用實驗數(shù)據(jù)庫技術(shù)與應(yīng)用實驗 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 預(yù)備知識預(yù)備知識 -事務(wù)運行模式事務(wù)運行模式 SQL Server 2008的事務(wù)運行模式的事務(wù)運行模式v（1）自動提交事務(wù)每條單獨的語句都是一個事務(wù)。每條單獨的語句都是一個事務(wù)。v（2）顯式事務(wù)每個事務(wù)均以每個事務(wù)均以GEBIN TRANSACTION 語句顯式開始，以語句顯式開始，以COMMIT或或ROLLBACK語語句顯式結(jié)束。句顯式結(jié)束。v（3）隱式事務(wù)在前一個事務(wù)完成時，新事務(wù)隱式啟動，但每個事務(wù)仍以在前一個事務(wù)完成時，新事務(wù)隱式啟動，

2、但每個事務(wù)仍以COMMIT或或ROLLBACK語語句顯式完成。句顯式完成。v（4）批處理級事務(wù)只應(yīng)用于多個活動結(jié)果集（只應(yīng)用于多個活動結(jié)果集（MARS），在），在MARS回話中啟動的回話中啟動的T-SQL顯式或隱式事務(wù)顯式或隱式事務(wù)變成批處理級事。當(dāng)批處理完成時沒有提交或回滾的批處理級事務(wù)自動由變成批處理級事。當(dāng)批處理完成時沒有提交或回滾的批處理級事務(wù)自動由SQL Server進行回滾。進行回滾。 預(yù)備知識預(yù)備知識 -事務(wù)控制語句事務(wù)控制語句事務(wù)控制語句事務(wù)控制語句vSQL Server 2008中的事務(wù)控制語句包括以下幾種：中的事務(wù)控制語句包括以下幾種：v（1 1）BEGIN TRANSAC

3、TIONBEGIN TRANSACTION語句語句BEGIN TRANSACTION語句表示事務(wù)的開始，其語法格式為：語句表示事務(wù)的開始，其語法格式為：BEGIN TRANSACTION 事務(wù)名事務(wù)名| 事務(wù)變量名事務(wù)變量名 v其中，事務(wù)變量名是用戶定義的、含有有效事務(wù)名稱的變量，該變其中，事務(wù)變量名是用戶定義的、含有有效事務(wù)名稱的變量，該變量必須是字符型。量必須是字符型。v（2 2）COMMIT TRANSACTIONCOMMIT TRANSACTION語句語句COMMIT TRANSACTION語句標(biāo)志一個成功的事務(wù)（包括顯式和隱式語句標(biāo)志一個成功的事務(wù)（包括顯式和隱式事務(wù)）的結(jié)束。其語法

4、格式為：事務(wù)）的結(jié)束。其語法格式為：COMMIT TRANACTION 事務(wù)名事務(wù)名| 事務(wù)變量名事務(wù)變量名 預(yù)備知識預(yù)備知識 -事務(wù)控制語句事務(wù)控制語句v（3 3）ROLLBACK TRANSACTIONROLLBACK TRANSACTION語句語句ROLLBACK TRANSACTION語句將事務(wù)（包括顯式和語句將事務(wù)（包括顯式和隱式事務(wù)）回滾到事務(wù)的起點或事務(wù)內(nèi)的某個保存點。隱式事務(wù)）回滾到事務(wù)的起點或事務(wù)內(nèi)的某個保存點。其語法格式為：其語法格式為：v ROLLBACK TRANSACTION 事務(wù)名事務(wù)名 | 事務(wù)變量名事務(wù)變量名 | 保存點名保存點名 | 保存點變量名保存點變量名

5、v（4 4）SAVE TRANSACTIONSAVE TRANSACTION語句語句SAVE TRANSACTION語句在事務(wù)內(nèi)部設(shè)置保存點，這語句在事務(wù)內(nèi)部設(shè)置保存點，這樣可以按條件將事務(wù)回滾到保存點，而不是回滾整個事樣可以按條件將事務(wù)回滾到保存點，而不是回滾整個事務(wù)。務(wù)。 預(yù)備知識預(yù)備知識 -鎖鎖v Microsoft SQL Server 的數(shù)據(jù)庫引擎具有多粒度鎖定功的數(shù)據(jù)庫引擎具有多粒度鎖定功能，允許一個事務(wù)鎖定不同類型的資源。為盡量減少鎖能，允許一個事務(wù)鎖定不同類型的資源。為盡量減少鎖定的開銷，數(shù)據(jù)庫引擎會自動將資源鎖定在適合任務(wù)的定的開銷，數(shù)據(jù)庫引擎會自動將資源鎖定在適合任務(wù)的級別

6、上。因此，一般情況下用戶不必關(guān)心加鎖、解鎖問級別上。因此，一般情況下用戶不必關(guān)心加鎖、解鎖問題。同時，題。同時，SQL Server的的SQL語句（語句（SELECT、INSERT、DELETE、UPDATE）支持顯式加鎖。這）支持顯式加鎖。這4個個語句在顯式加鎖的語法上類似，下面僅以語句在顯式加鎖的語法上類似，下面僅以SELECT語句語句為例給出加鎖的簡化語法：為例給出加鎖的簡化語法：SELECT FROM 表名表名 WITH 鎖類型鎖類型 預(yù)備知識預(yù)備知識 -鎖鎖vSQL Server 2008中支持的鎖類型主要有如下幾種：中支持的鎖類型主要有如下幾種：（1）HOLDLOCK，只在該表上保

7、持共享鎖，直到整個事務(wù)結(jié)束，而不，只在該表上保持共享鎖，直到整個事務(wù)結(jié)束，而不是在語句執(zhí)行完立即釋放。是在語句執(zhí)行完立即釋放。（2）NOLOCK：不添加鎖。這個選項只適用于：不添加鎖。這個選項只適用于SELECT語句，并且使語句，并且使用此選項，可能會讀到未提交的數(shù)據(jù)或用此選項，可能會讀到未提交的數(shù)據(jù)或“臟數(shù)據(jù)臟數(shù)據(jù)”。（3）PAGLOCK：指定添加頁面鎖（否則通常可能添加表鎖）。：指定添加頁面鎖（否則通常可能添加表鎖）。（4）ROWLOCK：指定使用行級鎖。：指定使用行級鎖。（5）TABLOCK：指定使用表級鎖，：指定使用表級鎖，SQL Server在該語句執(zhí)行完后立在該語句執(zhí)行完后立即釋放

8、這個鎖，而如果同時指定了即釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這，該鎖一直保持到這個事務(wù)結(jié)束。個事務(wù)結(jié)束。（6）TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務(wù)：指定在表上使用排它鎖，這個鎖可以阻止其他事務(wù)讀或更新這個表的數(shù)據(jù)，直到這個語句或整個事務(wù)結(jié)束。讀或更新這個表的數(shù)據(jù)，直到這個語句或整個事務(wù)結(jié)束。例如：例如：SELECT * FROM StudentInfo WITH (TABLOCK) 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 實驗?zāi)康膶嶒災(zāi)康?v 掌握掌握SQL Server 2008中事務(wù)的定義

9、和作中事務(wù)的定義和作用。用。v 理解理解SQL Server 2008的加鎖機制。的加鎖機制。v 掌握并發(fā)操作帶來的數(shù)據(jù)不一致性的原理掌握并發(fā)操作帶來的數(shù)據(jù)不一致性的原理和封鎖機制。和封鎖機制。v 掌握死鎖形成的原理和死鎖解除方法。掌握死鎖形成的原理和死鎖解除方法。 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 實驗要求實驗要求 v建立適當(dāng)事務(wù)實例，體會事務(wù)的原子性和一致性特建立適當(dāng)事務(wù)實例，體會事務(wù)的原子性和一致性特征。征。v編寫適當(dāng)事務(wù)實例，模擬編寫適當(dāng)事務(wù)實例，模擬“不可重復(fù)讀不可重復(fù)讀”問題。問題。v編寫適當(dāng)事務(wù)實例，驗證編寫適當(dāng)事務(wù)實例，驗

10、證SQL Server 2008的自動的自動加鎖機制是否能避免加鎖機制是否能避免“不可重復(fù)讀不可重復(fù)讀”問題。問題。v編寫適當(dāng)事務(wù)實例，模擬兩個事務(wù)產(chǎn)生死鎖的情景。編寫適當(dāng)事務(wù)實例，模擬兩個事務(wù)產(chǎn)生死鎖的情景。v撰寫實驗報告，并附實驗結(jié)果與相應(yīng)實驗語句。撰寫實驗報告，并附實驗結(jié)果與相應(yīng)實驗語句。 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 實驗步驟實驗步驟v建立一個事務(wù)，實現(xiàn)建立一個事務(wù)，實現(xiàn)SC表轉(zhuǎn)分功能，將任務(wù)編號表轉(zhuǎn)分功能，將任務(wù)編號為為“(2009-2010-2)-140176-09006”、學(xué)號為、學(xué)號為“201001903029”的課程

11、成績減的課程成績減10分，并將分，并將10分分加給學(xué)號加給學(xué)號“201001903051”的學(xué)生。設(shè)置合適數(shù)的學(xué)生。設(shè)置合適數(shù)據(jù)，執(zhí)行此事務(wù)，體會事務(wù)的原子性與一致性。據(jù)，執(zhí)行此事務(wù)，體會事務(wù)的原子性與一致性。（1）為）為SC的的Grade列建立列建立CHECK約束，保證分?jǐn)?shù)在約束，保證分?jǐn)?shù)在0至至100之之間。如果已經(jīng)建立此間。如果已經(jīng)建立此CHECK約束，可以省略此步驟。約束，可以省略此步驟。ALTER TABLE SC ADD CHECK(Grade=0 and Grade=100) 實驗步驟實驗步驟v（2）在查詢窗口中查詢兩位學(xué)生成績的）在查詢窗口中查詢兩位學(xué)生成績的SQL語句，并執(zhí)行

12、。語句，并執(zhí)行。SELECT * FROM SC WHERE TeachTaskID=(2009-2010-2)-140176-09006 AND (Sno =201001903029 OR Sno =201001903051)v查詢結(jié)果如圖查詢結(jié)果如圖14-1所示：所示： 實驗步驟實驗步驟（3）輸入轉(zhuǎn)分功能事務(wù)的）輸入轉(zhuǎn)分功能事務(wù)的SQL語句，并執(zhí)行。語句，并執(zhí)行。BEGIN TRANUPDATE SC SET Grade =Grade -10 WHERE TeachTaskID=(2009-2010-2)-140176-09006 AND Sno =201001903029IF ERROR

13、0 ROLLBACK TRANELSE UPDATE SC SET Grade =Grade +10 WHERE TeachTaskID=(2009-2010-2)-140176-09006 AND Sno =201001903051 IF ERROR=0 COMMIT TRAN ELSE ROLLBACK TRAN 實驗步驟實驗步驟（4）再次執(zhí)行查詢兩位學(xué)生成績的再次執(zhí)行查詢兩位學(xué)生成績的SQL語句，查詢結(jié)果如圖語句，查詢結(jié)果如圖14-2所所示，已經(jīng)成功轉(zhuǎn)分。示，已經(jīng)成功轉(zhuǎn)分。（5）再次執(zhí)行轉(zhuǎn)分功能事務(wù)的）再次執(zhí)行轉(zhuǎn)分功能事務(wù)的SQL語句，消息窗口顯示如下信息：語句，消息窗口顯示如下信息：v

14、(1 行受影響行受影響)v消息消息547，級別，級別16，狀態(tài)，狀態(tài)0，第，第7 行行vUPDATE 語句與語句與CHECK 約束約束CK_SC_Grade_22AA2996沖突。該沖突發(fā)生于數(shù)據(jù)庫沖突。該沖突發(fā)生于數(shù)據(jù)庫ST，表，表dbo.SC, column Grade。v語句已終止。語句已終止。v從消息可以看出，第一個從消息可以看出，第一個UPDATE語句執(zhí)行成功，第二個語句執(zhí)行成功，第二個UPDATE語句因為違反語句因為違反CHECK約束執(zhí)行失敗。約束執(zhí)行失敗。 實驗步驟實驗步驟（6）再次執(zhí)行查詢兩位學(xué)生成績的）再次執(zhí)行查詢兩位學(xué)生成績的SQL語句，語句，查詢結(jié)果仍如圖查詢結(jié)果仍如圖1

15、4-2所示，說明第一個所示，說明第一個UPDATE語句已經(jīng)撤銷。語句已經(jīng)撤銷。 實驗步驟實驗步驟2.將上述轉(zhuǎn)分功能事務(wù)寫成存儲過程形式，存儲于系統(tǒng)中可長將上述轉(zhuǎn)分功能事務(wù)寫成存儲過程形式，存儲于系統(tǒng)中可長期使用，體會事務(wù)的實際應(yīng)用意義。期使用，體會事務(wù)的實際應(yīng)用意義。v（1）在查詢窗口中輸入建立存儲過程的代碼，并執(zhí)行：）在查詢窗口中輸入建立存儲過程的代碼，并執(zhí)行：CREATE PROC TransScore TeachTaskID char(26),Sno1 varchar(12),Sno2 varchar(12)AS BEGINBEGIN TRANUPDATE SC SET Grade =G

16、rade -10 WHERE TeachTaskID=TeachTaskID AND Sno =Sno1 實驗步驟實驗步驟IF ERROR0 ROLLBACK TRAN ELSE UPDATE SC SET Grade =Grade +10 WHERE TeachTaskID=(2009-2010-2)-140176-09006 AND Sno =201001903051 IF ERROR=0 COMMIT TRAN ELSE ROLLBACK TRANEND（2）在窗口中輸入執(zhí)行存儲過程的代碼，并執(zhí)行：）在窗口中輸入執(zhí)行存儲過程的代碼，并執(zhí)行：vEXEC TransScore 2009-20

17、10-2)-140176-09006, 201001903029,201001903051 實驗步驟實驗步驟3.為查詢語句的加鎖方式設(shè)置為不加鎖，做不可重復(fù)為查詢語句的加鎖方式設(shè)置為不加鎖，做不可重復(fù)讀實驗，體會并發(fā)操作的帶來的問題。讀實驗，體會并發(fā)操作的帶來的問題。v（1）打開查詢窗口）打開查詢窗口1，輸入下列，輸入下列SQL語句，并執(zhí)語句，并執(zhí)行，得到行，得到StudentInfo表中的記錄數(shù)為表中的記錄數(shù)為11：BEGIN TRANSACTION SELECT COUNT(*) FROM StudentInfo with (nolock) 實驗步驟實驗步驟v（2）打開查詢窗口）打開查詢窗

18、口2，輸入下列，輸入下列SQL語句，并執(zhí)行，注意設(shè)置合適數(shù)語句，并執(zhí)行，注意設(shè)置合適數(shù)據(jù)，保證插入成功：據(jù)，保證插入成功：BEGIN TRAN INSERT INTO StudentInfo VALUES(201001903070,明梅明梅,女女,1991-03-15,計算機系計算機系,計算機科學(xué)計算機科學(xué)與技術(shù)與技術(shù)(本科本科),4)v（3）切換到查詢窗口）切換到查詢窗口1，輸入下列，輸入下列SQL語句，并執(zhí)行，得到語句，并執(zhí)行，得到StudentInfo表中的記錄數(shù)為表中的記錄數(shù)為12：SELECT COUNT(*) FROM StudentInfo with (nolock) COMMI

19、Tv（4）切換到查詢窗口）切換到查詢窗口2，輸入下列，輸入下列SQL語句，并執(zhí)行：語句，并執(zhí)行：COMMITv一個事務(wù)中的兩次查詢結(jié)果不一致，出現(xiàn)了幻影現(xiàn)象。一個事務(wù)中的兩次查詢結(jié)果不一致，出現(xiàn)了幻影現(xiàn)象。 實驗步驟實驗步驟4.將查詢窗口將查詢窗口1中兩個中兩個SELECT語句后面的語句后面的 with (nolock)去掉，去掉，使用數(shù)據(jù)庫引擎默認(rèn)的鎖，體會鎖的作用，并驗證使用數(shù)據(jù)庫引擎默認(rèn)的鎖，體會鎖的作用，并驗證SQL Server 2008能否避免能否避免“不可重復(fù)讀不可重復(fù)讀”問題。問題。（1）在窗口）在窗口1中，選中下列中，選中下列SQL語句，并執(zhí)行，得到語句，并執(zhí)行，得到Stud

20、entInfo表中的記錄數(shù)為表中的記錄數(shù)為12：BEGIN TRANSACTION SELECT COUNT(*) FROM StudentInfo（2）在窗口）在窗口3中，輸入查詢鎖的中，輸入查詢鎖的SQL語句，并執(zhí)行：語句，并執(zhí)行：SELECT * FROM sys.dm_tran_locksv可以從結(jié)果窗口的查詢結(jié)果中看到，已經(jīng)為可以從結(jié)果窗口的查詢結(jié)果中看到，已經(jīng)為StudentInfo表表加了加了S鎖。鎖。 實驗步驟實驗步驟（3）切換到窗口）切換到窗口2，選中下列，選中下列SQL語句，并執(zhí)行，仍要注意設(shè)語句，并執(zhí)行，仍要注意設(shè)置合適數(shù)據(jù)，保證插入成功。置合適數(shù)據(jù)，保證插入成功。BEG

21、IN TRAN INSERT INTO StudentInfo VALUES(201001903072,明梅明梅,女女,1991-03-15,計算計算機系機系,計算機科學(xué)與技術(shù)計算機科學(xué)與技術(shù)(本科本科),4)（4）切換到窗口）切換到窗口3，再次執(zhí)行查詢鎖的，再次執(zhí)行查詢鎖的SQL語句，可以從結(jié)果語句，可以從結(jié)果窗口中看到，數(shù)據(jù)庫引擎已經(jīng)自動加了窗口中看到，數(shù)據(jù)庫引擎已經(jīng)自動加了X、IX、IS等若干鎖。等若干鎖。 實驗步驟實驗步驟（5）切換到窗口）切換到窗口1，選中下列，選中下列SQL語句，并執(zhí)行語句，并執(zhí)行SELECT COUNT(*) FROM StudentInfoCOMMIT結(jié)果窗口下

22、方出現(xiàn)結(jié)果窗口下方出現(xiàn)“正在執(zhí)行查詢正在執(zhí)行查詢”，說明加鎖操作被阻止。，說明加鎖操作被阻止。（6）切換到窗口）切換到窗口2，選中下列，選中下列SQL語句，并執(zhí)行：語句，并執(zhí)行：COMMIT（7）再次切換到窗口）再次切換到窗口1，結(jié)果窗口中已經(jīng)出現(xiàn)查詢結(jié)果：，結(jié)果窗口中已經(jīng)出現(xiàn)查詢結(jié)果：13。兩次查詢結(jié)果仍不一致，說明兩次查詢結(jié)果仍不一致，說明SQL Server的加鎖機制無法避免的加鎖機制無法避免“不可重復(fù)讀不可重復(fù)讀” 實驗步驟實驗步驟5.設(shè)計死鎖實驗，體會死鎖的形成原理，理解設(shè)計死鎖實驗，體會死鎖的形成原理，理解SQL Server 2008的死鎖解除方法。的死鎖解除方法。（1）在窗口）

23、在窗口1中輸入下列中輸入下列SQL語句并執(zhí)行：語句并執(zhí)行：SET DEADLOCK_PRIORITY LOW -設(shè)置事務(wù)的死鎖優(yōu)先級設(shè)置事務(wù)的死鎖優(yōu)先級為低為低BEGIN TRAN UPDATE StudentInfo SET LengSch =4 WHERE Sno = 201001903051這時，這時，SQL Server自動為自動為StudentInfo表中的表中的Sno = 201001903051記錄加上了寫鎖。記錄加上了寫鎖。 實驗步驟實驗步驟（2）在窗口）在窗口2中輸入下列中輸入下列SQL語句并執(zhí)行：語句并執(zhí)行：BEGIN TRAN UPDATE StudentInfo SET

24、 Depart =紡織系紡織系 WHERE Sno = 201001903057這時，這時，SQL Server自動為自動為StudentInfo表中的表中的Sno = 201001903057記錄加記錄加上了寫鎖。上了寫鎖。（3）切換到窗口）切換到窗口1，輸入下列，輸入下列SQL語句并執(zhí)行：語句并執(zhí)行： UPDATE StudentInfo SET Depart =經(jīng)濟管理系經(jīng)濟管理系 WHERE Sno = 201001903057COMMIT這時，這時，SQL Server請求為請求為StudentInfo表中的表中的Sno = 201001903057記錄加記錄加寫鎖，但步驟（寫鎖，但

25、步驟（2）中語句已經(jīng)為此記錄加了寫鎖，所以只能等待。結(jié)）中語句已經(jīng)為此記錄加了寫鎖，所以只能等待。結(jié)果窗口中出現(xiàn)果窗口中出現(xiàn)“正在執(zhí)行查詢正在執(zhí)行查詢”信息。信息。 實驗步驟實驗步驟（4）切換到窗口）切換到窗口2，輸入下列，輸入下列SQL語句并執(zhí)行：語句并執(zhí)行： UPDATE StudentInfo SET LengSch =3 WHERE Sno = 201001903051COMMIT這時，窗口這時，窗口1中的事務(wù)擁有資源中的事務(wù)擁有資源Sno = 201001903051記錄，而等待資源記錄，而等待資源Sno = 201001903057記錄；而窗口記錄；而窗口2中的事務(wù)擁有資源中的事務(wù)擁有資源S