數(shù)據(jù)庫實驗報告_事務和并發(fā)控制

1、?數(shù)據(jù)庫原理?實驗報告題目：實驗七事務與并發(fā)限制學號姓名班級日期2021302478紀昌宇100113012021.11.141 .實驗七：事務與并發(fā)限制1.1. 實驗目的1 .掌握事務機制,學會創(chuàng)立事務.2 .理解事務并發(fā)操作所可能導致的數(shù)據(jù)不一致性問題,用實驗展現(xiàn)四種數(shù)據(jù)不一致性問題：喪失修改、讀臟數(shù)據(jù)、不可重復讀以及幻讀現(xiàn)象.3 .理解鎖機制,學會采用鎖與事務隔離級別解決數(shù)據(jù)不一致的問題.4 .了解數(shù)據(jù)庫的事務日志.1.2. 實驗內容在student數(shù)據(jù)庫中有如下的根本表,校園卡ID,工行卡ID,銀行卡余額校園卡ID,校園卡余額假設學校允許學生將銀行卡和校園卡進行綁定,其中校園卡編號ca

2、rdid即為學生的學號：icbc_card(studcardid,icbcid,balance)/campus_card(studcardid,balance)/數(shù)據(jù)創(chuàng)立的代碼：usestudentcreatetable(studcardidcampus_cardChar(8),balanceDecimal(10,2)createtableicbc_card(studcardidChar(8),icbcidChar(10),balanceDecimal(10,2)SPJstudentFL_J表3至婉表1_jFileTableGQ口dbo.campus_card3ndbo.CS+)JdboJcb

3、ccard>口dbciSC表32dbd£表PCI.student-dgempii之mLaItiTl為insertintocampus_cardvalues('20210031,30)insertintocampus_cardvalues('20210032,50)insertintocampus_cardvalues('20210033,70)insertintoicbc_cardvalues('20210031,2021003101,1000)insertintoicbc_cardvalues('20210032,2021003201,

4、1000)insertintoicbc_cardvalues('20210033,2021003301,1000)PC-1,student-dbercampus_eardXstudcardidbalance90.0G*202100322.15g33NULL50.0070WNULLstudcardidicbcidbalance20210031011000.002021003220210032011000.002塞L500余2021003301L000.00*NULLNULLNULLPC-1,student-dboJcbc.crdx針對以上數(shù)據(jù)庫根據(jù)要求完成以下實驗:1,編寫一個事務處理(

5、begintran)實現(xiàn)如下的操作：某學號為20210032的學生要從銀行卡中轉賬200元到校園卡中,假設中間出現(xiàn)故障那么進行rollback.(15分)settransactionisolationlevelrepeatablereadbegintransactionusestudentgodeclarexdecimal(10,2)select=balancefromicbc_cardwherestudcardid='20210032'set=x200if(>=0)beginupdateicbc_cardsetbalance=xwherestudcardid='

6、20210032'updatecampus_cardsetbalance=balance+200wherestudcardid='20210032'committranendelsebeginprint'余額缺乏,不能轉賬rollbacktranend1-dtzic.izampuE_cardstiidcardi'dlbalance-sow202100322021003370.00I*NULLNULL1PC-1.student-dbo.campu!studcadidicbcidbalance2O15W31O11000.0020210032202100332

7、021003201015003301800.001000.00NULLNULLNULL2.針對此題的數(shù)據(jù)庫和表,分別用具體的例子展現(xiàn)四種數(shù)據(jù)不一致問題：喪失修改、讀臟數(shù)據(jù)、不可重復讀和幻讀刪除和插入.40分,每種數(shù)據(jù)不一致10分1.喪失修改-事務1begintrandeclarebalancedecimal(10,2)selectbalance=balancefromcampus_cardwherestudcardid='20210033,waitfordelay'00:00:05'setbalance=balance-10updatecampus_cardsetbal

8、ance=balancewherestudcardid='20210033,committrangoselectbalancefromcampus_cardwherestudcardid='20210033,-事務2begintrandeclarebalance1decimal(10,2)selectbalance仁balancefromcampus_cardwherestudcardid='20210033,waitfordelay'00:00:05'setbalance1=balance1-20updatecampus_cardsetbalance=

9、balance1wherestudcardid='20210033,committrangoselectbalancefromcampus_cardwherestudca口結果J消息balance【事務1】更改了數(shù)據(jù),結果為60,但是沒有被讀到.最終【事務2】的結果50覆蓋了【事務1】的更改值,結果不是期望值40PC*1.student-dbo.camp-us_cardxstudcardid30W20210032250.002021003350.00.NULLWU(2).讀臟數(shù)據(jù)- -事務1SETTRANSACTIOIFSOLATIONLEVELREADUNCOMMITTED- -RE

10、ADUNCOMMITTED行臟讀,不發(fā)出共享鎖,也不接受排他鎖begintrandeclarebalancedecimal(10,2)selectbalance=balancefromcampus_cardwherestudcardid='20210032,updatecampus_cardsetbalance=balance+100wherestudcardid='20210032waitfordelay'00:00:05'rollbacktran-回滾goselectbalancefromcampus_cardwherestudcardid='202

11、10032,- -事務2SETTRANSACTIOIFSOLATIONLEVELREADUNCOMMITTEDbegintrandeclarebalancedecimal(10,2)selectbalance=balancefromcampus_cardwherestudcardid='20210032,updatecampus_cardsetbalance=balance+50wherestudcardid='20210032committrangoselectbalancefromcampus_cardwherestudcardid='20210032,【事務1】更

12、改了數(shù)據(jù),【事務2】讀取了表中更改后的值再進行操作,【事務1】回滾.最終的表存儲了錯誤結果.(3)不可重復讀- -事務1SETTRANSACTIONSOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20210031,waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20210031'committran- -事務2SETTRANSACTIONSOLATIONLEV

13、ELREADCOMMITTEDbegintranupdatecampus_cardsetbalance=balance+200wherestudcardid='20210031'committran100%*a口晴果,Ej消息1 I3C.OO,balance1ia:a-230.00；兩次【事務1】讀取了數(shù)據(jù),【事務2】更改數(shù)據(jù),【事務1】再讀取數(shù)據(jù).【事務讀取都不一樣！(4).幻讀插入-事務1SETTRANSACTIOISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid=&

14、#39;20210031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20210031'committran-事務2SETTRANSACTIOISOLATIONLEVELSERIALIZABLEbegintraninsertintocampus_cardvalues('20210031',30)committran理北果即嬴balanceIbbll-M1 230.00:Ubalancey|ii!uF!iii一mimi*1230.00：2 3Q.00刪

15、除-事務1SETTRANSACTIOISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20210031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20210031'committran-事務2settranisolationlevelrepeatablereadbegintrandeletefromcampus_cardwherestudcard

16、id='20210031'committranbalance230.00-3iBo-3bounce3 .利用鎖機制、數(shù)據(jù)庫的隔離級別等,設計方案分別解決上述喪失修改、讀臟數(shù)據(jù)和不可重復讀或者幻讀的數(shù)據(jù)不一致問題.30分,每種數(shù)據(jù)不一致10分,提示可以用sp_lock系統(tǒng)存儲過程查看當前鎖狀況修改隔離級別以卻確定數(shù)據(jù)的正確性：喪失修改,在SQL吾句前加未提交讀：settranisolationlevelreaduncommitted讀臟數(shù)據(jù),在SQL吾句前已提交讀：settranisolationlevelreadcommitted不可重復讀,在SQL吾句前可重復讀：settra

17、nisolationlevelrepeatableread幻讀在SQL語句前加可串行讀：settranisolationlevelserializable4 .構造一個出現(xiàn)死鎖的情形.10分首先將鎖的級別改為提交可讀settranisolationlevelreadcommitted-事務1begintrandeclarereadintselectread=gradefromscwheresno='95003,waitfordelay'00:00:10updatescsetgrade=reac-1wheresno='95003,-事務2begintrandeclarer

18、eadintselectread=gradefromscwheresno='95003,updatescsetgrade=read1wheresno='95003,謔K:同iLLEMijfiS,.|洸LLV8QLEBRE沾&咽|d州胡打攜：陰rtudE|W川觸.行5.利用dbcclog命令查看student數(shù)據(jù)庫的事務日志.5分DBCClog('student',TYPE=2)口站要,蛆CunwtLSNIrmsBctoigLagBackljtrHrADnT«gbusbagftaldL?gHplofcPh11:口a正JL0FJJE50PTLQLNUIjLgCkoooa