數(shù)據(jù)庫實驗：事務(wù)與并發(fā)控制

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

2、rdid,icbcid,balance) /校園卡ID，工行卡ID，銀行卡余額 campus_card(studcardid,balance) /校園卡ID，校園卡余額數(shù)據(jù)創(chuàng)建的代碼： use studentcreate table campus_card( studcardid Char(8) , balance Decimal(10,2)create table icbc_card( studcardid Char(8), icbcid Char(10), balance Decimal(10,2) ) insert into campus_card values('2015003

3、1', 30)insert into campus_card values('20150032', 50)insert into campus_card values('20150033', 70)insert into icbc_card values('20150031', '2015003101', 1000)insert into icbc_card values('20150032', '2015003201', 1000)insert into icbc_card values(

4、'20150033', '2015003301', 1000) 針對以上數(shù)據(jù)庫按照要求完成下列實驗：1. 編寫一個事務(wù)處理(begin tran)實現(xiàn)如下的操作：某學(xué)號為20150032的學(xué)生要從銀行卡中轉(zhuǎn)賬200元到校園卡中，若中間出現(xiàn)故障則進行rollback。（15分）set transaction isolation levelrepeatable readbegin transactionuse studentgodeclare x decimal(10, 2)select x=balance from icbc_cardwhere studcardi

5、d='20150032'set x=x-200 if(x>=0) begin update icbc_card set balance=x where studcardid='20150032' update campus_card set balance=balance+200 where studcardid='20150032' commit tran end else begin print '余額不足，不能轉(zhuǎn)賬' rollback tran end2. 針對本題的數(shù)據(jù)庫和表，分別用具體的例子展現(xiàn)四種數(shù)據(jù)不一致問題：

6、丟失修改、讀臟數(shù)據(jù)、不可重復(fù)讀和幻讀(刪除和插入)。（40分，每種數(shù)據(jù)不一致10分）(1).丟失修改-事務(wù)1begin trandeclare balance decimal(10,2)select balance=balance from campus_card where studcardid='20150033'waitfor delay '00:00:05'set balance=balance-10update campus_card set balance=balance where studcardid='20150033'comm

7、it trangoselect balance from campus_card where studcardid='20150033'-事務(wù)2begin trandeclare balance1 decimal(10,2)select balance1=balance from campus_card where studcardid='20150033'waitfor delay '00:00:05'set balance1=balance1-20update campus_card set balance=balance1 where st

8、udcardid='20150033'commit trangoselect balance from campus_card where studca 【事務(wù)1】更改了數(shù)據(jù),結(jié)果為60，但是沒有被讀到。最終【事務(wù)2】的結(jié)果50覆蓋了【事務(wù)1】的更改值，結(jié)果不是期望值40（2）.讀臟數(shù)據(jù)-事務(wù)1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -READ UNCOMMITTED 執(zhí)行臟讀，不發(fā)出共享鎖，也不接受排他鎖begin tran declare balance decimal(10,2) select balance=b

9、alance from campus_card where studcardid='20150032' update campus_card set balance=balance+100 where studcardid='20150032' waitfor delay '00:00:05' rollback tran -回滾 goselect balance from campus_card where studcardid='20150032' -事務(wù)2 SET TRANSACTION ISOLATION LEVEL REA

10、D UNCOMMITTED begin tran declare balance decimal(10,2) select balance=balance from campus_card where studcardid='20150032' update campus_card set balance=balance+50 where studcardid='20150032' commit tran goselect balance from campus_card where studcardid='20150032' 【事務(wù)1】更改了數(shù)

11、據(jù)，【事務(wù)2】讀取了表中更改后的值再進行操作，【事務(wù)1】回滾。最終的表存儲了錯誤結(jié)果。 （3）不可重復(fù)讀 -事務(wù)1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran select balance from campus_card where studcardid='20150031' waitfor delay '00:00:05' select balance from campus_card where studcardid='20150031'commit tran -事務(wù)2

12、 SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran update campus_card set balance=balance+200 where studcardid='20150031' commit tran 【事務(wù)1】讀取了數(shù)據(jù)，【事務(wù)2】更改數(shù)據(jù)，【事務(wù)1】再讀取數(shù)據(jù)?！臼聞?wù)1】兩次讀取都不一樣?。?）.幻讀 插入 -事務(wù)1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran select balance from campus_card

13、 where studcardid='20150031' waitfor delay '00:00:05' select balance from campus_card where studcardid='20150031'commit tran -事務(wù)2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE begin tran insert into campus_card values('20150031', 30) commit tran 刪除-事務(wù)1 SET TRANSACTION

14、ISOLATION LEVEL READ COMMITTED begin tran select balance from campus_card where studcardid='20150031' waitfor delay '00:00:05' select balance from campus_card where studcardid='20150031'commit tran -事務(wù)2 set tran isolation level repeatable read begin tran delete from campus_ca

15、rd where studcardid='20150031'commit tran 3. 利用鎖機制、數(shù)據(jù)庫的隔離級別等，設(shè)計方案分別解決上述丟失修改、讀臟數(shù)據(jù)和不可重復(fù)讀(或者幻讀)的數(shù)據(jù)不一致問題。（30分，每種數(shù)據(jù)不一致10分，提示可以用sp_lock系統(tǒng)存儲過程查看當前鎖狀況）修改隔離級別以卻確定數(shù)據(jù)的正確性：丟失修改，在SQL語句前加未提交讀：set tran isolation level read uncommitted讀臟數(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)造一個出現(xiàn)死鎖的情形。（10分） 首先將鎖的級別改為提交可讀set tran isolation level read committed-事務(wù)1begin trandeclare read intselect read=gradefrom scwhere sno='95003'waitfor delay '00:00:10'update s