數據庫實驗報告-事務和并發(fā)控制

./《數據庫原理》實驗報告題目：實驗七事務與并發(fā)控制學號姓名班級日期2013302478紀昌宇100113012015.11.14實驗七:事務與并發(fā)控制實驗目的掌握事務機制,學會創(chuàng)建事務。理解事務并發(fā)操作所可能導致的數據不一致性問題,用實驗展現(xiàn)四種數據不一致性問題：丟失修改、讀臟數據、不可重復讀以及幻讀現(xiàn)象。理解鎖機制,學會采用鎖與事務隔離級別解決數據不一致的問題。了解數據庫的事務日志。實驗內容假設學校允許學生將銀行卡和校園卡進行綁定,在student數據庫中有如下的基本表,其中校園卡編號cardid即為學生的學號： icbc_card<studcardid,icbcid,balance>//校園卡ID,工行卡ID,銀行卡余額campus_card<studcardid,balance>//校園卡ID,校園卡余額數據創(chuàng)建的代碼：usestudentcreatetablecampus_card<studcardidChar<8>,balanceDecimal<10,2>>createtableicbc_card<studcardidChar<8>,icbcidChar<10>,balanceDecimal<10,2>>insertintocampus_cardvalues<'20150031',30>insertintocampus_cardvalues<'20150032',50>insertintocampus_cardvalues<'20150033',70>insertintoicbc_cardvalues<'20150031','2015003101',1000>insertintoicbc_cardvalues<'20150032','2015003201',1000>insertintoicbc_cardvalues<'20150033','2015003301',1000>針對以上數據庫按照要求完成下列實驗：編寫一個事務處理<begintran>實現(xiàn)如下的操作：某學號為20150032的學生要從銀行卡中轉賬200元到校園卡中,若中間出現(xiàn)故障則進行rollback?！?5分settransactionisolationlevelrepeatablereadbegintransactionusestudentgodeclare@xdecimal<10,2>select@x=balancefromicbc_cardwherestudcardid='20150032'set@x=@x-200if<@x>=0>beginupdateicbc_cardsetbalance=@xwherestudcardid='20150032'updatecampus_cardsetbalance=balance+200wherestudcardid='20150032'committranendelsebeginprint'余額不足,不能轉賬'rollbacktranend針對本題的數據庫和表,分別用具體的例子展現(xiàn)四種數據不一致問題：丟失修改、讀臟數據、不可重復讀和幻讀<刪除和插入>。〔40分,每種數據不一致10分<1>.丟失修改--事務1begintrandeclare@balancedecimal<10,2>select@balance=balancefromcampus_cardwherestudcardid='20150033'waitfordelay'00:00:05'set@balance=@balance-10updatecampus_cardsetbalance=@balancewherestudcardid='20150033'committrangoselectbalancefromcampus_cardwherestudcardid='20150033'--事務2begintrandeclare@balance1decimal<10,2>select@balance1=balancefromcampus_cardwherestudcardid='20150033'waitfordelay'00:00:05'set@balance1=@balance1-20updatecampus_cardsetbalance=@balance1wherestudcardid='20150033'committrangoselectbalancefromcampus_cardwherestudca[事務1]更改了數據,結果為60,但是沒有被讀到。最終[事務2]的結果50覆蓋了[事務1]的更改值,結果不是期望值40〔2.讀臟數據--事務1SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED--READUNCOMMITTED執(zhí)行臟讀,不發(fā)出共享鎖,也不接受排他鎖begintrandeclare@balancedecimal<10,2>select@balance=balancefromcampus_cardwherestudcardid='20150032'updatecampus_cardsetbalance=@balance+100wherestudcardid='20150032'waitfordelay'00:00:05'rollbacktran--回滾goselectbalancefromcampus_cardwherestudcardid='20150032'--事務2SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTEDbegintrandeclare@balancedecimal<10,2>select@balance=balancefromcampus_cardwherestudcardid='20150032'updatecampus_cardsetbalance=@balance+50wherestudcardid='20150032'committrangoselectbalancefromcampus_cardwherestudcardid='20150032'[事務1]更改了數據,[事務2]讀取了表中更改后的值再進行操作,[事務1]回滾。最終的表存儲了錯誤結果?！?不可重復讀--事務1SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20150031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20150031'committran--事務2SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranupdatecampus_cardsetbalance=balance+200wherestudcardid='20150031'committran[事務1]讀取了數據,[事務2]更改數據,[事務1]再讀取數據。[事務1]兩次讀取都不一樣！〔4.幻讀插入--事務1SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20150031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20150031'committran--事務2SETTRANSACTIONISOLATIONLEVELSERIALIZABLEbegintraninsertintocampus_cardvalues<'20150031',30>committran刪除--事務1SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20150031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20150031'committran--事務2settranisolationlevelrepeatablereadbegintrandeletefromcampus_cardwherestudcardid='20150031'committran利用鎖機制、數據庫的隔離級別等,設計方案分別解決上述丟失修改、讀臟數據和不可重復讀<或者幻讀>的數據不一致問題?！?0分,每種數據不一致10分,提示可以用sp_lock系統(tǒng)存儲過程查看當前鎖狀況修改隔離級別以卻確定數據的正確性：丟失修改,在SQL語句前加未提交讀：settranisolationlevelreaduncommitted讀臟數據,在SQL語句前已提交讀：settranisolationlevelreadcommitted不可重復讀,在SQL語句前可重復讀：settranisolationlevelrepeatableread幻讀在SQL語句前加可串行讀：settranisolationlevelserializable構造一個出現(xiàn)死鎖的情形。〔10分首先將鎖的級別改為提交可讀settranisolationlevelreadcommitted--事務1begintrandeclare@readintselect@read=gradefromscwheresno='95003'waitfordela