數(shù)據(jù)庫實驗報告四

1、數(shù)據(jù)庫原理實驗報告實驗七： 事務(wù)與并發(fā)控制學(xué)號姓名班級日期楊添文2015.11.14實驗七:事務(wù)與并發(fā)控制 一、 實驗內(nèi)容 假設(shè)學(xué)校允許學(xué)生將銀行卡和校園卡進行綁定，在student數(shù)據(jù)庫中有如下的基本表，其中校園卡編號cardid即為學(xué)生的學(xué)號： icbc_card(studcardid,icbcid,balance) /校園卡ID，工行卡ID，銀行卡余額 campus_card(studcardid,balance) /校園卡ID，校園卡余額針對以上數(shù)據(jù)庫按照要求完成下列實驗：1. 編寫一個事務(wù)處理(begin tran)實現(xiàn)如下的操作：某學(xué)號為的學(xué)生要從銀行卡中轉(zhuǎn)賬200元到校園卡中，若

2、中間出現(xiàn)故障則進行rollback。（15分）（1） 程序代碼如下：declare balance intselect balance=balance-200from icbc_cardwhere studcardid=update icbc_cardset balance=balancewhere studcardid=update campus_cardset balance=balance+200where studcardid=if balance0rollbackcommit;（2）結(jié)果如下：（結(jié)果很明顯，陰影部分即顯示操作已成功。）2. 針對本題的數(shù)據(jù)庫和表，分別用具體的例子展現(xiàn)四

3、種數(shù)據(jù)不一致問題：丟失修改、讀臟數(shù)據(jù)、不可重復(fù)讀和幻讀(刪除和插入)。（40分，每種數(shù)據(jù)不一致10分）（1） 丟失修改：代碼如下：set tran isolation level read uncommitteddeclare balance intselect balance=balancefrom icbc_cardwhere studcardid=waitfor delay 00:00:10update icbc_cardset balance=balance-300where studcardid=select balancefrom icbc_cardwhere studcardid

4、=這段代碼執(zhí)行的時間設(shè)置為10秒，在此期間，重復(fù)執(zhí)行這段代碼（可以增加一個窗口，在10秒內(nèi)執(zhí)行），結(jié)果如下截圖：很明顯，若是成功修改，balance中的數(shù)據(jù)就會是400，而不是700。（2） 讀臟數(shù)據(jù)：代碼如下：（兩個事務(wù)的執(zhí)行時間間隔不超過10秒） 1、先編寫代碼執(zhí)行事務(wù)1（執(zhí)行一次對balance的操作，回滾）set tran isolation level read uncommittedbegin tranupdate icbc_cardset balance=balance+2000where studcardid=select balancefrom icbc_cardwhere

5、studcardid=waitfor delay00:00:10rollback;select balancefrom icbc_cardwhere studcardid=這段代碼的結(jié)果為：（balance的值已經(jīng)返回正常值，為1000）2、執(zhí)行事務(wù)2：set tran isolation level read uncommittedselect balancefrom icbc_cardwhere studcardid=這段代碼結(jié)果為：（結(jié)果不是1000，卻是3000，說明讀了臟數(shù)據(jù)）（3） 不可重復(fù)讀：代碼段為：（兩個事務(wù)的執(zhí)行時間間隔不超過10秒）1、 先執(zhí)行事務(wù)1：declare ba

6、lance int/*第一次讀數(shù)據(jù)*/select balance=balancefrom icbc_cardwhere studcardid=print balancewaitfor delay 00:00:10/*第二次讀數(shù)據(jù)*/select balance=balancefrom icbc_cardwhere studcardid=print balance結(jié)果為：（可以很明顯的看出來，兩次讀數(shù)據(jù)的結(jié)果不一致，原因在于事務(wù)2的中間加入，而事務(wù)1又沒有加鎖導(dǎo)致）2、 再執(zhí)行事務(wù)2：（在第二次讀數(shù)據(jù)之前更新）update icbc_cardset balance=balance*2where

7、 studcardid= 結(jié)果：（4） 幻讀：代碼段為：（兩個事務(wù)的執(zhí)行時間間隔不超過10秒）1、 先執(zhí)行事務(wù)1：select balancefrom icbc_cardwhere studcardid=waitfor delay 00:00:10select balancefrom icbc_cardwhere studcardid=結(jié)果：（因為增加了一條數(shù)據(jù)，兩次讀的結(jié)果就不一致）2、 再執(zhí)行事務(wù)2：（增加一條數(shù)據(jù)）insert into icbc_cardvalues(,2000)結(jié)果： 3. 利用鎖機制、數(shù)據(jù)庫的隔離級別等，設(shè)計方案分別解決上述丟失修改、讀臟數(shù)據(jù)和不可重復(fù)讀(或者幻讀)

8、的數(shù)據(jù)不一致問題。（30分，每種數(shù)據(jù)不一致10分，提示可以用sp_lock系統(tǒng)存儲過程查看當(dāng)前鎖狀況）（1） 丟失修改：事務(wù)1：（對表的操作加鎖）-set tran isolation level read uncommittedbegin trandeclare balance intselect * from icbc_card with(tablockx)select balance=balancefrom icbc_cardwhere studcardid=waitfor delay 00:00:10update icbc_cardset balance=balance-300wher

9、e studcardid=select balancefrom icbc_cardwhere studcardid=commit事務(wù)2與上述一致，間隔10秒，結(jié)果為：（可知沒有丟失修改，事務(wù)1結(jié)果為800，而事務(wù)2結(jié)果為500）事務(wù)1：事務(wù)2：（2） 讀臟數(shù)據(jù)：1、 事務(wù)1：set tran isolation level read committedbegin tranupdate icbc_cardset balance=balance+2000where studcardid=select balancefrom icbc_cardwhere studcardid=waitfor del

10、ay00:00:10rollback;select balancefrom icbc_cardwhere studcardid=2、 事務(wù)2：set tran isolation level read committedselect balancefrom icbc_cardwhere studcardid=3、 結(jié)果：（事務(wù)2 數(shù)據(jù)已經(jīng)正常）（1） 事務(wù)1：（2） 事務(wù)2：（3） 不可重復(fù)讀：1、 事務(wù)1：begin transelect * from icbc_card with (tablockx)declare balance int/*第一次讀數(shù)據(jù)*/select balance=b

11、alancefrom icbc_cardwhere studcardid=print balancewaitfor delay 00:00:10/*第二次讀數(shù)據(jù)*/select balance=balancefrom icbc_cardwhere studcardid=print balancecommit2、 事務(wù)2：set tran isolation level read committedupdate icbc_cardset balance=balance*2where studcardid=3、 結(jié)果：（可以明顯看出可以重復(fù)讀數(shù)據(jù)）事務(wù)1：事務(wù)2：4. 構(gòu)造一個出現(xiàn)死鎖的情形。（1

12、0分）（1） 事務(wù)1： begin tran update campus_card with(xlock) set balance = balance + 100 waitfor delay 00:00:10 select * from icbc_card with(holdlock) rollback tran（2） 事務(wù)2： begin tran update icbc_card with(xlock) set balance = balance + 200 waitfor delay 00:00:10 select * from campus_card with(holdlock) rollback tran（3）結(jié)果為：事務(wù)2有結(jié)果，執(zhí)行