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)容與步驟】15.1. SQL Server數(shù)據(jù)庫事務(wù)基礎(chǔ)知識(shí)1 .事務(wù)的概念(Transaction )所謂事務(wù)是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么都做，要么都不做，是一個(gè)不可分割的工作單位。關(guān)系數(shù)據(jù)庫中，事務(wù)可以是一條 SQL語句、一組SQL語句。開始 結(jié)束 回滾在SQL語言中，定義事務(wù)的語句有三條：Begi n Tran sacti onCommitRollback2.事務(wù)開始：BEGIN TRANSACTION標(biāo)記一個(gè)顯式本地事務(wù)的起始點(diǎn)。BEGIN

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

3、har、varchar、nchar或 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, COMMITTRANSACTION 使得自從事務(wù)開始以來所執(zhí)行的所有數(shù)據(jù)修改成為數(shù)據(jù)庫的永久部分，釋放連接占用的資源，并將TRANCOUNT 減少到 0。如果 TR

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

5、RANSACTION相關(guān)聯(lián)，transaction_name可作為幫助閱讀的一種方法。tran_n ame_variable:是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用char、varchar、nchar或 nvarchar數(shù)據(jù)類型聲明該變量。5 事務(wù)回滾：ROLLBACK TRANSACTION將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點(diǎn)或事務(wù)的某個(gè)保存點(diǎn)。語法結(jié)構(gòu)：ROLLBACK TRAN SACTION tra nsactio n_n ame | tran_n ame_variable| savepo int_n ame | savepo in t_variable 參數(shù)說明：tran

6、saction_name：是給 BEGIN TRANSACTION 上的事務(wù)指派的名稱。transaction_name 必須符合標(biāo)識(shí)符規(guī)則， 但只使用事務(wù)名稱的前 32個(gè)字符。嵌套事務(wù)時(shí)，transaction_name必 須是來自最遠(yuǎn)的 BEGIN TRANSACTION 語句的名稱。tran_n ame_variable:是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用char、varchar、nchar或 nvarchar數(shù)據(jù)類型聲明該變量。savepoint_name : 是來自 SAVE TRANSACTION 語句的 savepoint_name 。 savepo int_n

7、ame必須符合標(biāo)識(shí)符規(guī)則。當(dāng)條件回滾只影響事務(wù)的一部分時(shí)使用 savepo int_n ame。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_lnsUpdate簡單的事務(wù)，并使它正常提交。Begin transaction t_lnsUpdate -t_lnsUpdate 為事務(wù)名Use CPXSInsert into CP（產(chǎn)品編號(hào)，產(chǎn)品名稱，價(jià)格，庫存量）Val

8、ues（'100021','寶馬汽車',456780,39）Update XSSSet負(fù)責(zé)人='飛'Where 客戶編號(hào)='000003'Commit transaction t_lnsUpdate -事務(wù)提交結(jié)束，t_InsUpdate 為事務(wù)名 測試：執(zhí)行語句"select * from xss; ”，看數(shù)據(jù)是否添加到表中？請給出測試結(jié)果：CS-FC.CriS . . ” mnerFl,三生1* 摘要Segin匸lansacticu t_In書U匸id赴二色 -匸_工11啟1口已呂匕總為事実宅Insert into

9、fP (產(chǎn)品編芍產(chǎn)品名稱價(jià)格$庫存量Values ' 100021r f寶馬料車 1 f &67SD, 33;Update XSS沁負(fù)責(zé)人“張飛， ere客戶編號(hào)=WO0003Ccrnmit transaction L_In3Jpda.匚亡口結(jié)果U消息客戶辭號(hào)客戶名騎it醫(yī)負(fù)貴人1j OOMOI廠電公司銅江11111111210ODOT2寡電女司李四77777777730WXM3貨運(yùn)泊祠福建張飛33J422114南京666666665OODQ05廠電普司iES1111111226DODM7中國建設(shè)銀行NULL車世民6543627370D0WS東方航空g司上海NULLHULL&#

10、163;MWCO南總周瑜345672568(2) 在查詢分析器中執(zhí)行以下語句，創(chuàng)建一個(gè)簡單的事務(wù)，并使它回滾BEGIN TRANSACTIONUse CPXS select * from xss;update xssset客戶名稱='理工學(xué)院'where 客戶編號(hào)='000002'select * from xss;Rollback;測試：select * from xss;請給出測試結(jié)果：結(jié)果_J消息U #'U .01 IS占匕 “'J1fflWDOl廣電公司館江張三1111111122無煽季四力7陽"特3黃廷省司福建去飛334422

11、114南京趙六5DDCDO5廠電益司乘三111111122G000007中國建設(shè)NU.李世t543C2737(xxhooa東方航空上琴NULLNULL3000009南京周瑜34M725B客戶慵粵客尸名稱負(fù)貴.J電話1(XKK)O1廣電吿司菌江張三1111111122原門理工學(xué)瞬無錫李四m?7?7773DW003貸運(yùn)益司福建張飛134422114OfXHXM小家電面逐南用趨亢&666S66665廣電公司張三1111111226(HXHX17中國建設(shè)帳廳NU.李世.&54362707東方航空舍司上海NULLMULL3家具公司甫京周晞W5672563思考：比較兩條查詢語句的結(jié)果差異，

12、為什么會(huì)有這樣的差異？執(zhí)行了事務(wù)后，rollback相當(dāng)于取消了事務(wù)，事務(wù)沒有生效。2 事務(wù)的存儲(chǔ)點(diǎn)：事務(wù)的存儲(chǔ)點(diǎn)可以使事務(wù)在發(fā)生回滾的情況下，存儲(chǔ)點(diǎn)前的操作結(jié)果得以保存。執(zhí)行以下語句，創(chuàng)建一個(gè)名為t_ln sertCP，其中包含一個(gè)存儲(chǔ)點(diǎn)。Begi n tran sact ion t_l nsertCP-Use CPXSInsert into CP（產(chǎn)品編號(hào)，產(chǎn)品名稱，價(jià)格，庫存量）Values（'100028','天山雪蓮',456,57）Save transaction t_I nsertCP -存儲(chǔ)點(diǎn)Update CPSet產(chǎn)品名稱='白藥Whe

13、re產(chǎn)品編號(hào)='208729'-此為一個(gè)不存在的編號(hào)，目的是使插入操作出錯(cuò)if error!=0 -error為系統(tǒng)全局變量，錯(cuò)誤號(hào) rollback tran sact ion t_ln sertCPelsecommit transaction t InsertCP測試：使用查詢語句查詢表 CP中數(shù)據(jù)，觀查查詢結(jié)果，看存儲(chǔ)點(diǎn)前的操作結(jié)果是否確實(shí)得以保存。給出相應(yīng)的結(jié)果:C5-PC. CPT£ . . . LQneiryl. sql* 3Hn曲工匸iiiTO CF產(chǎn)品錮號(hào)產(chǎn)品名稱”價(jià)格庫存量Values ( *1C002S * J 天山雪蓮 ” ,456.57Save

14、 tiansaction reinsert匚pUpdate CP業(yè)七產(chǎn)品名稱云南白藥，Wliexe 產(chǎn)品編號(hào)=72怦'1£ 低UHEOH *0iclltficX transaction t_InserrCPelsecoirnut txsaction 匸 InseitCFZ3結(jié)異_j消息產(chǎn)品扁號(hào)產(chǎn)品名稱價(jià)榴庫存量1D221055愛雪VCD232£120?1TO001影電3QK1DS100002洗衣機(jī)1800204KJWOS冰箱18001251000061肛牢調(diào)IBM5E1000071.5匹空調(diào)2400207iodods2肛宇調(diào)39M6&100011MP3135

15、0W91MJ015TCLH寸彩37M211Q100016東風(fēng)小汽車$5.9111DD021寶馬肓車45.3913天血雪蓮45657131MXM2數(shù)碼相機(jī)350022 事務(wù)的實(shí)驗(yàn)練習(xí)：實(shí)驗(yàn)練習(xí)：寫一個(gè)名為pt_CPXSB的存儲(chǔ)過程，含一名為t_lnserCPXSB的事務(wù),用于實(shí)現(xiàn)向CPXSB表中插入一條數(shù)據(jù)時(shí)，檢查產(chǎn)品編號(hào)”字段是否包含有 CP表中，客 戶編號(hào)”是否包含于XSS表中，只要兩者之一為否，撒銷插入操作，否則，則提交數(shù)據(jù)。 給出相應(yīng)的代碼：create proc pt_CPXSB產(chǎn)品編號(hào)char（6）,客戶編號(hào)char（6）,銷售日期datetime,數(shù)量int,銷售額floatas

16、beg inbegi n tran saction t_In serCPXSBinsert into CPXSB （產(chǎn)品編號(hào)，客戶編號(hào)，銷售日期，數(shù)量，銷售額） values （產(chǎn)品編號(hào)，客戶編號(hào)，銷售日期，數(shù)量，銷售額）if （產(chǎn)品編號(hào)in （select產(chǎn)品編號(hào)from CP）and （客戶編號(hào)in （select 客戶編號(hào)from XSS）begincommit tran sacti on t_ln serCPXSBprint'插入一行數(shù)據(jù)成功select * from CPXSBendif （產(chǎn)品編號(hào)not in （select產(chǎn)品編號(hào)from CP）beginprint

17、9;插入數(shù)據(jù)中產(chǎn)品編號(hào)與 CP表中產(chǎn)品編號(hào)不一致'rollback tran saction t_l nserCPXSBendif （客戶編號(hào)not in （select客戶編號(hào)from XSS）beginprint'插入數(shù)據(jù)中客戶編號(hào)與 XSS 表中客戶編號(hào)不一致'rollback tran saction t_l nserCPXSBend end給出測試結(jié)果：15.3. 鎖與并發(fā)控制1530. SQL Server 鎖簡介1. 查看鎖的信息（1）執(zhí)行EXEC SP_LOCK報(bào)告有關(guān)鎖的信息a.勢I為消息spiddbidObjldIn.Ty.Resource '

18、;.lodeStartus1800DBsGRANT2531n.0TAEISGRANTnUE w00DBsj RANT查詢分析器中按Ctrl+2可以看到鎖的信息. 為結(jié)杲'j消息spiddbidObjldIil.Ty .ModeStatus1S00DSsGRAU?1 Ml25111115151D130TABISGRAN3533C-0DEsGRAtJT454500DSsGRAM?2. 如何鎖定數(shù)據(jù)庫對象（1）如何鎖一個(gè)表的某一行（示例）SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM table ROWLOCK WH

19、ERE 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)程懸掛；（3）優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；（4 ）對所有的腳本和 SP都要仔細(xì)測試，在正式版本之前。（5）所有的SP都要有錯(cuò)誤處理（通過 error）（6）一般不要修改SQL SERVER事務(wù)的默認(rèn)級別。不推薦強(qiáng)行加鎖1531. 排它鎖1新建兩個(gè)連接：新建兩個(gè)用戶，并給相應(yīng)的權(quán)限，然

20、后各自登錄到數(shù)據(jù)庫中，分別打開查詢窗口2在第一個(gè)連接中執(zhí)行以下語句beg in tranupdate XSSset客戶名稱='SM城市廣場where 客戶編號(hào)='000003'waitfor delay '00:00:50'-等待 50 秒commit tran3在第二個(gè)連接中執(zhí)行以下語句beg in transelect * from XSSwhere 客戶編號(hào)='000003'commit tran先執(zhí)行以上兩個(gè)語句中的第一個(gè)語句，后執(zhí)行第二個(gè)語句，觀查執(zhí)行的結(jié)果（主要 是執(zhí)行時(shí)間的差異）。4行受影響1包査詢己成功執(zhí)行 , "

21、; _CS-FC 0 0 HTH) 7eKtA (53) fPIS 00:00:50(|練習(xí)：將以上兩個(gè)連接的執(zhí)行順序調(diào)換，觀查執(zhí)行情況。二I錯(cuò)果、消烏客尸濡號(hào)客戶名秫trtix負(fù)責(zé).電話1! :HXK陽？城市廣場福連張飛33442211曲疊詢已成功執(zhí)行-(9.0Testk C53)CPUS 00:00:00看“行受參響)申萱誼已成助瓠行CS-PC 0 RTUJ Tewtl崩)CPXS 00:00.50* 行思考：為什么會(huì)有這樣的結(jié)果？其中一個(gè)用戶對表 XSS中000003客戶編號(hào)加上排它鎖，只允許該用戶自己讀取 和修改，知道該用戶釋放，否則其他用戶不能對其數(shù)據(jù)進(jìn)行讀取和修改。注：若同時(shí)執(zhí)行上

22、述兩個(gè)語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即 要等待50秒1532. 共享鎖1 .在第一個(gè)連接中執(zhí)行以下語句beg in transelect * from XSS with(holdlock) -holdlock 人為加鎖where 客戶編號(hào)='000003'waitfor delay '00:00:50'-等待 50 秒commit tran2在第二個(gè)連接中執(zhí)行以下語句beg in transelect客戶編號(hào)，地區(qū)from XSSwhere客戶名稱='SM城市廣場update XSSset客戶名稱='好又多超市

23、9;where 客戶編號(hào)='000003'commit tran給出執(zhí)行情況:3結(jié)票屜消息I卷尸編號(hào)I客尸名稱地區(qū)負(fù)責(zé).電話1 i OXW03 j 城市廣場上緯33333333刃查C5-PC .0 RTM) CS-PCMom(52)丈 p檔 00:00:501曰結(jié)果園消區(qū)二 客戶編號(hào)地區(qū)I i aid ai aaaiM bei10DD003 i 上得wib "CS-PCg o MMCS-PCM (52)tpsts 00:00:001練習(xí)：將以上兩個(gè)連接的執(zhí)行順序調(diào)換，觀查執(zhí)行情況。兩個(gè)連接都要50s，結(jié)果相同。思考：為什么會(huì)有這樣的結(jié)果？其中一個(gè)用戶對表 XSS中00

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

25、or delay '00:00:10' update XSSset客戶名稱='老虎城where 客戶編號(hào)='000002' commit tran給出執(zhí)行情況：U5-rL.514 LS-rL.tegxn tranupda匸亡 XSS匸客戶名稱手，中山老虎城where 客戶編號(hào) h000002' waitfor delay 、0匚：二口：包。upda.t生 CPaec庫存量=50wriere 產(chǎn)品編號(hào)=,100005' comrni匸 tran |百甬息（1行受家響）（o行受影響）C5-FC CPIS . . .Lj3u*ry6. sqI*

26、 CS-PC. CTIS . . L begin rranjpSate CPM昌匕陣存量'SO產(chǎn)品編號(hào)-'00005 1wai.tf or delay 1 C 1 j :匚 3 :10 1m客戶名稱，中山老虎城*where 客戶輪號(hào) =* 000002 rcommit 匸ran練習(xí)：將以上兩個(gè)連接的執(zhí)行順序調(diào)換，觀查執(zhí)行情況。跟交換之前沒差別。思考：為什么會(huì)有這樣的結(jié)果?154理解兩段鎖協(xié)議通過對比各個(gè)階段的 exec sp_lock，觀察寫鎖和讀鎖的釋放時(shí)間。理解二段式鎖（兩段 鎖）的工作原理。完成以下實(shí)驗(yàn)，思考為什么會(huì)有那樣的實(shí)驗(yàn)結(jié)果。（1）實(shí)驗(yàn)場景新建兩個(gè)連接：使用前面新

27、建的兩個(gè)用戶，各自登錄到數(shù)據(jù)庫中，分別打開查詢窗口， 在兩個(gè)查詢分析窗口中分別執(zhí)行以下操作。連接1：中執(zhí)行以下代碼：begi n transelect * from CP with (UPDLOCK) where產(chǎn)品編號(hào)='100003'連接2:中執(zhí)行以下代碼:select * from CP where產(chǎn)品編號(hào)='100003'update CP set庫存量=庫存量 + 100 where產(chǎn)品編號(hào)='100003' select * from CP where 產(chǎn)品編號(hào)='100003'(2)查看阻塞情況a.通過查看第一個(gè)連接

28、的鎖定情況：exec spock請給出執(zhí)行結(jié)果： spsd dbidObjldInT>peResourceModeStatus1j52i 700D6SGRANT25273095761410P幅1:109IUGRANT3527309576141aRID1:109:1UWAIT4527309576141aTABIXGRAM554309576141aTABIXGRANT6$4309576141aPAS1:109IUGRANT7541QaMP&<1:Q:0)Sch-SGRAM8M70aDBSGRAM954309576141aRID1:109:1UGRANT1055500DBSGRA

29、NT115670QDBsGRAM125E11115151.0TABISGRAMb.打開創(chuàng)菩理文件夾：2000版本 當(dāng)前活動(dòng)”-鎖/進(jìn)程ID2005版本選擇瓏活動(dòng)監(jiān)觀器給出觀查結(jié)果：Xt；7畢T瑁供SB于現(xiàn).申|3cs-rc'TA亍1 嘴 piRiCQCOHAJTDHi <rQL Si-TW'rt-Studi?命152CE-KMwCJHS2Uk <Tt 9Q L S« I"W«-Mi'l f mt S<-U.dj O-111saaw苦0MAITI血匸HDIk占M Ml洌L 卯胖甘込fd：ML SludjAacmll44V<B£1燈ahi肪mmUtcT-oiioi i 58 L Si xr-n- 皿-Q跡辭舌FT AWWMnynSTfiiRipsrSSirrlltfilllf9AAH】科氐A(chǔ)W屯 StrTtr0J)56cs-rcMw0A3TAIII&：- CCJRAHDIk 41応 l S L S*-rwr Uaukg4SVt S tlldhO-MQ乩|詩=ilo-rwE1<1 p：筍監(jiān)dJkTAiTIfc 氏50創(chuàng)SG-Kl2SELECT TBWU i-ct q>£1 SQ L S«-rrrr nt