實(shí)驗(yàn)15 事務(wù)與并發(fā)控制

1、實(shí)驗(yàn)十五事務(wù)與并發(fā)控制【實(shí)驗(yàn)?zāi)康呐c要求】1. 掌握數(shù)據(jù)庫事務(wù)的概念2. 熟悉數(shù)據(jù)庫的四個(gè)特性3. 熟練掌握數(shù)據(jù)庫事務(wù)的實(shí)現(xiàn)方法【實(shí)驗(yàn)內(nèi)容與步驟】15.1. SQL Server數(shù)據(jù)庫事務(wù)基礎(chǔ)知識1事務(wù)的概念( Transaction )所謂事務(wù)是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么都做，要么都不做，是一個(gè)不可分割的工作單位。關(guān)系數(shù)據(jù)庫中，事務(wù)可以是一條SQL語句、一組SQL語句。在SQL語言中，定義事務(wù)的語句有三條： Begin Transaction 開始 Commit 結(jié)束 Rollback 回滾2事務(wù)開始：BEGIN TRANSACTION標(biāo)記一個(gè)顯式本地事務(wù)的起始點(diǎn)。BEGIN

2、TRANSACTION將 TRANCOUNT 加 1。語法結(jié)構(gòu)：BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 參數(shù)說明：transaction_name：是給事務(wù)分配的名稱。transaction_name 必須遵循標(biāo)識符規(guī)則，但是不允許標(biāo)識符多于 32 個(gè)字符。僅在嵌套的 BEGIN.COMMIT 或 BEGIN.ROLLBACK 語句的最外語句對上使用事務(wù)名。tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用 char、varchar、nch

3、ar 或 nvarchar 數(shù)據(jù)類型聲明該變量。WITH MARK description：指定在日志中標(biāo)記事務(wù)。Description 是描述該標(biāo)記的字符串。如果使用了 WITH MARK，則必須指定事務(wù)名。WITH MARK 允許將事務(wù)日志還原到命名標(biāo)記。4事務(wù)提交：COMMIT TRANSACTION標(biāo)志一個(gè)成功的隱性事務(wù)或用戶定義事務(wù)的結(jié)束。如果 TRANCOUNT 為 1，COMMIT TRANSACTION 使得自從事務(wù)開始以來所執(zhí)行的所有數(shù)據(jù)修改成為數(shù)據(jù)庫的永久部分，釋放連接占用的資源，并將 TRANCOUNT 減少到 0。如果 TRANCOUNT 大于 1，則 COMMIT T

4、RANSACTION 使 TRANCOUNT 按 1 遞減。語法結(jié)構(gòu)：COMMIT TRAN SACTION transaction_name | tran_name_variable 參數(shù)說明：transaction_name：Microsoft SQL Serve 忽略該參數(shù)。transaction_name 指定由前面的 BEGIN TRANSACTION 指派的事務(wù)名稱。transaction_name 必須遵循標(biāo)識符的規(guī)則，但只使用事務(wù)名稱的前 32 個(gè)字符。通過向程序員指明 COMMIT TRANSACTION 與哪些嵌套的 BEGIN TRANSACTION 相關(guān)聯(lián)，transa

5、ction_name 可作為幫助閱讀的一種方法。tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用 char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型聲明該變量。5事務(wù)回滾：ROLLBACK TRANSACTION將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點(diǎn)或事務(wù)內(nèi)的某個(gè)保存點(diǎn)。語法結(jié)構(gòu)：ROLLBACK TRAN SACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 參數(shù)說明：transaction_name：是給 BEGIN TR

6、ANSACTION 上的事務(wù)指派的名稱。transaction_name 必須符合標(biāo)識符規(guī)則，但只使用事務(wù)名稱的前 32 個(gè)字符。嵌套事務(wù)時(shí)，transaction_name 必須是來自最遠(yuǎn)的 BEGIN TRANSACTION 語句的名稱。tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用 char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型聲明該變量。savepoint_name：是來自 SAVE TRANSACTION 語句的 savepoint_name。savepoint_name 必須符合標(biāo)識符規(guī)則。當(dāng)條件回滾只影響事務(wù)的一部分

7、時(shí)使用 savepoint_name。savepoint_variable：是用戶定義的、含有有效保存點(diǎn)名稱的變量的名稱。必須用 char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型聲明該變量。15.2. SQL Server數(shù)據(jù)庫事務(wù)創(chuàng)建1事務(wù)的創(chuàng)建（1）在查詢分析器中執(zhí)行以下語句，創(chuàng)建一個(gè)名為t_InsUpdate簡單的事務(wù)，并使它正常提交。Begin transaction t_InsUpdate -t_InsUpdate為事務(wù)名Use CPXSInsert into CP(產(chǎn)品編號,產(chǎn)品名稱,價(jià)格,庫存量)Values(100021,寶馬汽車,456780,39)Upd

8、ate XSSSet 負(fù)責(zé)人=張飛Where 客戶編號=000003Commit transaction t_InsUpdate -事務(wù)提交結(jié)束，t_InsUpdate為事務(wù)名測試： 執(zhí)行語句“select * from xss; ”，看數(shù)據(jù)是否添加到表中？請給出測試結(jié)果：（2）在查詢分析器中執(zhí)行以下語句，創(chuàng)建一個(gè)簡單的事務(wù)，并使它回滾BEGIN TRANSACTION Use CPXSselect * from xss; update xss set 客戶名稱=廈門理工學(xué)院 where 客戶編號=000002; select * from xss;Rollback;測試：select * f

9、rom xss;請給出測試結(jié)果：思考：比較兩條查詢語句的結(jié)果差異，為什么會(huì)有這樣的差異？ 執(zhí)行了事務(wù)后，rollback相當(dāng)于取消了事務(wù)，事務(wù)沒有生效。2事務(wù)的存儲點(diǎn)：事務(wù)的存儲點(diǎn)可以使事務(wù)在發(fā)生回滾的情況下，存儲點(diǎn)前的操作結(jié)果得以保存。執(zhí)行以下語句，創(chuàng)建一個(gè)名為t_InsertCP，其中包含一個(gè)存儲點(diǎn)。Begin transaction t_InsertCP-Use CPXSInsert into CP(產(chǎn)品編號,產(chǎn)品名稱,價(jià)格,庫存量)Values(100028,天山雪蓮,456,57)Save transaction t_InsertCP -存儲點(diǎn)Update CPSet 產(chǎn)品名稱=云南

10、白藥Where 產(chǎn)品編號=208729 -此為一個(gè)不存在的編號，目的是使插入操作出錯(cuò)if error!=0 -error為系統(tǒng)全局變量，錯(cuò)誤號rollback transaction t_InsertCPelsecommit transaction t_InsertCP測試：使用查詢語句查詢表CP中數(shù)據(jù)，觀查查詢結(jié)果，看存儲點(diǎn)前的操作結(jié)果是否確實(shí)得以保存。給出相應(yīng)的結(jié)果：2事務(wù)的實(shí)驗(yàn)練習(xí)：實(shí)驗(yàn)練習(xí)：寫一個(gè)名為pt_CPXSB的存儲過程，含一名為t_InserCPXSB的事務(wù),用于實(shí)現(xiàn)向CPXSB表中插入一條數(shù)據(jù)時(shí)，檢查“產(chǎn)品編號”字段是否包含有CP表中，“客戶編號”是否包含于XSS表中，只要兩

11、者之一為否，撒銷插入操作，否則，則提交數(shù)據(jù)。給出相應(yīng)的代碼：createprocpt_CPXSB產(chǎn)品編號char(6),客戶編號char(6),銷售日期datetime,數(shù)量int,銷售額floatasbeginbegintransactiont_InserCPXSBinsertintoCPXSB(產(chǎn)品編號,客戶編號,銷售日期,數(shù)量,銷售額)values(產(chǎn)品編號,客戶編號,銷售日期,數(shù)量,銷售額)if(產(chǎn)品編號in(select產(chǎn)品編號fromCP)and(客戶編號in(select客戶編號fromXSS)begincommittransactiont_InserCPXSBprint插入一行

12、數(shù)據(jù)成功select*fromCPXSBendif(產(chǎn)品編號notin(select產(chǎn)品編號fromCP)beginprint插入數(shù)據(jù)中產(chǎn)品編號與CP表中產(chǎn)品編號不一致 rollbacktransactiont_InserCPXSBendif(客戶編號notin(select客戶編號fromXSS)beginprint插入數(shù)據(jù)中客戶編號與XSS表中客戶編號不一致 rollbacktransactiont_InserCPXSBendend給出測試結(jié)果：15.3. 鎖與并發(fā)控制15.3.0SQL Server鎖簡介1. 查看鎖的信息 (1) 執(zhí)行 EXEC SP_LOCK 報(bào)告有關(guān)鎖的信息 (2)

13、 查詢分析器中按Ctrl+2可以看到鎖的信息 2. 如何鎖定數(shù)據(jù)庫對象 (1) 如何鎖一個(gè)表的某一行 (示例)SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM table ROWLOCK WHERE id = 1 (2) 鎖定數(shù)據(jù)庫的一個(gè)表(示例)SELECT * FROM table WITH (HOLDLOCK)3.軟件開發(fā)中如何盡可能避免死鎖 （1） 使用事務(wù)時(shí)，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)； （2） 設(shè)置死鎖超時(shí)參數(shù)為合理范圍，如：3分鐘-10分種；超過時(shí)間，自動(dòng)放棄本次操作，避免進(jìn)程懸掛；

14、（3） 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)； （4）對所有的腳本和SP都要仔細(xì)測試，在正式版本之前。 （5） 所有的SP都要有錯(cuò)誤處理（通過error） （6） 一般不要修改SQL SERVER事務(wù)的默認(rèn)級別。不推薦強(qiáng)行加鎖 15.3.1排它鎖1.新建兩個(gè)連接：新建兩個(gè)用戶，并給相應(yīng)的權(quán)限，然后各自登錄到數(shù)據(jù)庫中，分別打開查詢窗口 2.在第一個(gè)連接中執(zhí)行以下語句 begin tran update XSS set 客戶名稱=SM城市廣場 where 客戶編號=000003 waitfor delay 00:00:50 -等待50秒 commit tran3.在第二個(gè)連接中執(zhí)行以下語句 begin

15、 tran select * from XSS where 客戶編號=000003 commit tran先執(zhí)行以上兩個(gè)語句中的第一個(gè)語句，后執(zhí)行第二個(gè)語句，觀查執(zhí)行的結(jié)果（主要是執(zhí)行時(shí)間的差異）。練習(xí)：將以上兩個(gè)連接的執(zhí)行順序調(diào)換，觀查執(zhí)行情況。思考：為什么會(huì)有這樣的結(jié)果？ 其中一個(gè)用戶對表XSS中000003客戶編號加上排它鎖，只允許該用戶自己讀取和修改，知道該用戶釋放，否則其他用戶不能對其數(shù)據(jù)進(jìn)行讀取和修改。 注：若同時(shí)執(zhí)行上述兩個(gè)語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待50秒 15.3.2共享鎖1在第一個(gè)連接中執(zhí)行以下語句 begin tran select

16、 * from XSS with(holdlock) -holdlock人為加鎖 where 客戶編號=000003 waitfor delay 00:00:50 -等待50秒 commit tran2.在第二個(gè)連接中執(zhí)行以下語句 begin tran select 客戶編號,地區(qū) from XSS where 客戶名稱=SM城市廣場 update XSS set 客戶名稱=好又多超市 where 客戶編號=000003commit tran給出執(zhí)行情況：練習(xí)：將以上兩個(gè)連接的執(zhí)行順序調(diào)換，觀查執(zhí)行情況。 兩個(gè)連接都要50s，結(jié)果相同。思考：為什么會(huì)有這樣的結(jié)果？ 其中一個(gè)用戶對表XSS中00

17、0003客戶編號加上HOLDLOCK，導(dǎo)致其他用戶只能對這一組數(shù)據(jù)讀取，不能進(jìn)行修改。 15.3.3死鎖1.在第一個(gè)連接中執(zhí)行以下語句 begin tran update XSS set 客戶名稱= 中山老虎城where 客戶編號=000002 waitfor delay 00:00:30 update CP set 庫存量=50 where 產(chǎn)品編號=100005 commit tran 2.在第二個(gè)連接中執(zhí)行以下語句 begin tran update CP set 庫存量=50 where 產(chǎn)品編號=100005waitfor delay 00:00:10 update XSS set

18、客戶名稱= 中山老虎城where客戶編號=000002 commit tran 給出執(zhí)行情況：練習(xí)：將以上兩個(gè)連接的執(zhí)行順序調(diào)換，觀查執(zhí)行情況。 跟交換之前沒差別。 思考：為什么會(huì)有這樣的結(jié)果？15.4. 理解兩段鎖協(xié)議通過對比各個(gè)階段的exec sp_lock，觀察寫鎖和讀鎖的釋放時(shí)間。理解二段式鎖（兩段鎖）的工作原理。完成以下實(shí)驗(yàn)，思考為什么會(huì)有那樣的實(shí)驗(yàn)結(jié)果。（1）實(shí)驗(yàn)場景新建兩個(gè)連接：使用前面新建的兩個(gè)用戶，各自登錄到數(shù)據(jù)庫中，分別打開查詢窗口，在兩個(gè)查詢分析窗口中分別執(zhí)行以下操作。 連接1：中執(zhí)行以下代碼：begin transelect * from CP with (UPDLOCK) where 產(chǎn)品編號=100003連接2：中執(zhí)行以下代碼：select * from CP where 產(chǎn)品編號=100003update CP set 庫存量 = 庫存量 + 100 where 產(chǎn)品編號=100003select * from CP where 產(chǎn)品編號=100003（2）查看阻塞情況a. 通過查看第一個(gè)連接的鎖定情況：exec sp_lock請給出執(zhí)行結(jié)果：b. 打開文件夾:2000版本“當(dāng)前活動(dòng)”-“鎖/進(jìn)程ID”2005版本選擇給出觀查結(jié)果：15.5. 事務(wù)應(yīng)用案例在數(shù)據(jù)庫中創(chuàng)建兩個(gè)表，賬