數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告_事務(wù)與并發(fā)控制

1、.專業(yè).專注.數(shù)據(jù)庫原理實(shí)驗(yàn)報(bào)告題目：實(shí)驗(yàn)七事務(wù)與并發(fā)控制學(xué)號(hào)姓名班級(jí)日期2013302478紀(jì)昌宇100113012015.11.141.實(shí)驗(yàn)七:事務(wù)與并發(fā)控制1.1. 實(shí)驗(yàn)?zāi)康?. 掌握事務(wù)機(jī)制，學(xué)會(huì)創(chuàng)建事務(wù)。2. 理解事務(wù)并發(fā)操作所可能導(dǎo)致的數(shù)據(jù)不一致性問題，用實(shí)驗(yàn)展現(xiàn)四種數(shù)據(jù)不一致性問題：丟失修改、讀臟數(shù)據(jù)、不可重復(fù)讀以及幻讀現(xiàn)象。3. 理解鎖機(jī)制，學(xué)會(huì)采用鎖與事務(wù)隔離級(jí)別解決數(shù)據(jù)不一致的問題。4. 了解數(shù)據(jù)庫的事務(wù)日志。1.2. 實(shí)驗(yàn)內(nèi)容假設(shè)學(xué)校允許學(xué)生將銀行卡和校園卡進(jìn)行綁定，在student數(shù)據(jù)庫中有如下的基本表其中校園卡編號(hào)cardid即為學(xué)生的學(xué)號(hào)：icbc_card(stu

2、dcardid,icbcid,balanee)/校園卡 ID，工行卡 ID，銀行卡余額campus_card(studcardid,balanee)/ 校園卡 ID，校園卡余額數(shù)據(jù)創(chuàng)建的代碼：use studentcreate table campus_card(studcardid Char (8),balanee Decimal (10,2)create table icbc_card(studcardid Char (8),i+lU spjstudentl*亠I聞産疼圏f n表+i 一I養(yǎng)婉表+ _j| FileTable(± 口 dbo.campii5_card+ dbci.

3、C表jJ 口 dboJcbc card3 '3 dbo.SC3 3 dbo,Stm Lna itnlinsert into campus_cardvalues ('20150031' , 30 )insert into campus_cardvalues ('20150032' , 50 )insert into campus_cardvalues ('20150033' , 70 )in sertintoicbc_cardvalues ('20150031','2015003101',1000 )in s

4、ertintoicbc_cardvalues ('20150032','2015003201',1000 )in sertintoicbc_cardvalues ('20150033','2015003301',1000 )icbcidChar(10),bala neeDecimal (10,2)stud card idicbcid|2015OD3LH20150031011000.002015003220150032011000.002O15IW33C1LOWmNULLNULLNULLXPC-1.student - dbcampu

5、s.針對(duì)以上數(shù)據(jù)庫按照要求完成下列實(shí)驗(yàn)：1. 編寫一個(gè)事務(wù)處理(begin tran)實(shí)現(xiàn)如下的操作：某學(xué)號(hào)為20150032的學(xué)生要從銀 行卡中轉(zhuǎn)賬200元到校園卡中，若中間出現(xiàn)故障則進(jìn)行 rollback。( 15分)set transaction isolation levelrepeatable readbegin transactionuse studentgodeclare x decimal (10,2)select x = balaneefrom icbc_cardwhere studcardid ='20150032'set x=x-200if(x>=

6、0)beginupdate icbc_card set balance = x where studcardid ='20150032'update campus_card set balanee = balanee+200 where studcardid = '20150032' commit tranendelsebeginprint '余額不足，不能轉(zhuǎn)賬rollback tranendstudcardidbalance30,0025O.OT2015003370.00NULLNULLrtudea rdidiebcidbalance201500310

7、11000.00201500328OT.M2015303320150033011000.00NULLAfUUNULLPD1.student - dbo.icbc_cardPC-1.student - dbo.eannpu12. 針對(duì)本題的數(shù)據(jù)庫和表，分別用具體的例子展現(xiàn)四種數(shù)據(jù)不一致問題：丟失修改、讀臟數(shù)據(jù)、不可重復(fù)讀和幻讀(刪除和插入)。(40分，每種數(shù)據(jù)不一致10分)(1) .丟失修改-事務(wù)1beg in trandeclare balanee decimal (10,2) select balance=balanee from campus_card where studeardid =

8、'20150033' waitfor delay '00:00:05'set balanee =balanee -10update campus_card set balance = balanee where studeardid ='20150033' commit tran goselect balanee from campus_card where studeardid = '20150033'-事務(wù)2beg in trandeclare balance1 decimal (10,2)select balancel =b

9、alanee from campus_card where studcardid ='20150033' waitfor delay '00:00:05'set balance1 = balance1 -20update campus_card set balance = balance1 where studcardid = '20150033' commit trangoselect balanee from campus_card where studcaiao -啟結(jié)果|山渭息balance1 &0.00事務(wù)1】更改了數(shù)據(jù)，結(jié)果為

10、60 ,但是沒有被讀到。最終 事務(wù)2】的結(jié)果50覆蓋了事務(wù)1】的更改值，結(jié)果不是期望值40studcardidbalance300020150032250.002015003350.00wuNULLPC *1. stud ent - dbo. tampuic白 rd(2) .讀臟數(shù)據(jù)-事務(wù)1SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED-READ UNCOMMITTED 執(zhí)行臟讀，不發(fā)岀共享鎖，也不接受排他鎖begin trandeclare balanee decimal (10,2)select balance=balanee from ca

11、mpus_card where studcardid ='20150032'update campus_card set balance = balanee +100 where studcardid ='20150032'waitfor delay '00:00:05'rollback tran -回滾goselect balanee from campus_card where studcardid = '20150032'-事務(wù)2SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTEDb

12、egi n trandeclare balanee decimal (10,2)select balance=balanee from campus_card where studcardid ='20150032'update campus_card set balance = balanee +50 where studcardid = '20150032' commit trangoselect balanee from campus_card where studcardid = '20150032'事務(wù)1】更改了數(shù)據(jù)，【事務(wù)2】讀取了表

13、中更改后的值再進(jìn)行操作 最終的表存儲(chǔ)了錯(cuò)誤結(jié)果。（3）不可重復(fù)讀-事務(wù)1SETTRANSACTION ISOLATION LEVEL READ COMMITTEDbegi n transelect balanee from campus_card where studcardid ='20150031' waitfor delay '00:00:05'select balanee from campus_card where studcardid = '20150031' commit tran-事務(wù)2SETTRANSACTION ISOLATI

14、ON LEVEL READ COMMITTEDbegin tranupdate campus_card set balanee = balanee+200 where studcardid曹務(wù)1】回滾。= '20150031'commit tran事務(wù)1】讀取了數(shù)據(jù)，事務(wù)2】更改數(shù)據(jù)，事務(wù)1】再讀取數(shù)據(jù)。事務(wù)1】?jī)纱巫x取都不一樣！(4) 幻讀插入-事務(wù)1SET TRANSACTION ISOLATION LEVEL READ COMMITTEDbeg in tra nselect balanee from campus_card where studcardid ='2

15、0150031'waitfor delay '00:00:05'select balanee from campus_card where studcardid = '20150031'commit tran-事務(wù)2SET TRANSACTION ISOLATION LEVELSERIALIZABLEbeg in tra ninsert into campus_card values ('20150031' , 30) commit tran后結(jié)果應(yīng)肖息bJ盤n亡世IW !> Mil1 230.00 | ! !BTTS!SOSbal

16、ance1 ； 230.002 31.00刪除-事務(wù)1SET TRANSACTION ISOLATION LEVEL READ COMMITTEDbeg in tra nselect balanee from campus_card where studcardid ='20150031'waitfor delay '00:00:05'select balanee from campus_card where studcardid = '20150031'commit tran-事務(wù)2 set tran isolation level repea

17、table readbeg in tra n2口結(jié)杲100"% = *balancedelete from campus_card where studcardid ='20150031' commit tranbalance3. 利用鎖機(jī)制、數(shù)據(jù)庫的隔離級(jí)別等，設(shè)計(jì)方案分別解決上述丟失修改、讀臟數(shù)據(jù)和不可重復(fù)讀（或者幻讀）的數(shù)據(jù)不一致問題。（30分，每種數(shù)據(jù)不一致10分，提示可 以用sp_lock系統(tǒng)存儲(chǔ)過程查看當(dāng)前鎖狀況）修改隔離級(jí)別以卻確定數(shù)據(jù)的正確性：丟失修改，在SQL語句前加未提交讀：set tran isolation level read uncommi

18、tted讀臟數(shù)據(jù)，在SQL語句前已提交讀：set tran isolation level read committed不可重復(fù)讀，在SQL語句前可重復(fù)讀：set tran isolation level repeatable read幻讀在SQL語句前加可串行讀：set tran isolation level serializable4. 構(gòu)造一個(gè)出現(xiàn)死鎖的情形 。（10分）首先將鎖的級(jí)別改為提交可讀set tran isolation level read committed-事務(wù)1begi n trandeclare read intselect read = gradefrom sc

19、where sno = '95003'waitfor delay '00:00:10'update scset grade = read -1where sno = '95003'-事務(wù)2begin trandeclare read intselect read = gradefrom scwhere sno = '95003'update scset grade = read -1where sno = '95003'理：折itLiEMrfi 8'.I MUASQLEXSRE燙 CO.O 泊創(chuàng) | dWi23 <5&) Kud | 洞朋 0 行5. 利用dbcc log命令查看student數(shù)據(jù)庫的事務(wù)日志。(5分)DBCC log ('student' , TYPE=2)CurTEriLNUbQTStGhLgrbsdTra-EBdcri OLasBackljmerftDnTag匕l(fā)og flnoQflmMfl L*4hUg HelotsnphPh1!:ODCOCOI;.朋就心必認(rèn)衛(wèi)J LOF_0LGIH_CKPTIXJCN'JLL