数据库实验报告事务与并发控制

1、.专业.专注.数据库原理实验报告题目：实验七事务与并发控制学号姓名班级日期2013302478纪昌宇100113012015.11.141 .实验七：事务与并发控制1.1. 实验目的1 .掌握事务机制，学会创建事务。2 .理解事务并发操作所可能导致的数据不一致性问题，用实验展现四种数据不一致性问题：丢失修改、读脏数据、不可重复读以及幻读现象。3 .理解锁机制，学会采用锁与事务隔离级别解决数据不一致的问题。4 .了解数据库的事务日志。1.2. 实验内容假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表其中校园卡编号cardid即为学生的学号：icbc_card(st

2、udcardid,icbcid,balance)/校园卡ID,工行卡ID,银行卡余额campus_card(studcardid,balance)校园卡ID,校园卡余额数据创建的代码：usestudentcreatetablecampus_card(studcardidChar(8),balanceDecimal(10,2)createtableicbc_card(studcardidChar(8),icbcidChar(10),balanceDecimal(10,2)田 Q SPJstudentI*数据本关系圉F二表+13至跳表+FileTable5田口dbo.campii5_card+db

3、dC表出口dboJcbccard3口dbdSC：表3HbdS表mLnaiklFlMlinsertinsertinsertinto campus_card values ('20150032' , 50 )into campus_card values ('20150033' , 70 )insertinto icbc_card values ('20150031' ,'2015003101'insertinsertinto icbc_card values ('20150032' ,'2015003201&

4、#39;intoicbc cardvalues ('20150033','2015003301',1000 ),1000 ),1000 )intocampus_cardvalues('20150031',30)PC-1,student-dborcjmpu5_cardXstudcardidbalance0150032015003250.002015003370,00*NULLNULLstudcardidicbcid>L015OD31H20150031011000.002015003220150032011000.002O15W3J2015g3

5、3Q工LCXW8NULLNULLNULLPC-1,student-dbcampus.针对以上数据库按照要求完成下列实验：1,编写一个事务处理(begintran)实现如下的操作：某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。(15分)settransactionisolationlevelrepeatablereadbegintransactionusestudentgodeclarexdecimal(10,2)selectx=balancefromicbc_cardwherestudcardid='20150032's

6、etx=x-200if(x>=0)beginupdateicbc_cardsetbalance=xwherestudcardid='20150032'updatecampus_cardsetbalance=balance+200wherestudcardid='20150032committranendelsebeginprint余额不足，不能转账rollbacktranendstudcardidbalance30,0020L5M3225O.OT2015003370.00.NULLNULLPC-1,atudent-dbo.campu1rtudeardidirbei

7、dbalance20150031011000.002015003220153033；D15003；010150033018OT.001000.00*NULLNULLNULL2.针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复t和幻读(删除和插入)。(40分，每种数据不一致10分)(1) .丢失修改-事务1begintrandeclarebalancedecimal(10,2)selectbalance=balancefromcampus_cardwherestudcardid='20150033'waitfordelay'00:

8、00:05'setbalance=balance-10updatecampus_cardsetbalance=balancewherestudcardid='20150033'committrangoselectbalancefromcampus_cardwherestudcardid='20150033'-事务2begintrandeclarebalance1decimal(10,2)selectbalance1=balancefromcampus_cardwherestudcardid='20150033'waitfordelay&#

9、39;00:00:05'setbalance1=balance1-20updatecampus_cardsetbalance=balance1wherestudcardid='20150033'committrangoselectbalancefromcampus_cardwherestudcaiao-起结果|口消息balance1&0.00事务1】更改了数据，结果为60,但是没有被读到。最终事务2】的结果50覆盖了事务1】的更改值，结果不是期望值40studcardidbalanceSOW20150032250.002015003350.00NULLNULLP

10、C*1.student-dbo.campu5_card(2) .读脏数据事务1SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED-READUNCOMMITTED执行脏读，不发出共享锁，也不接受排他锁begintrandeclarebalancedecimal(10,2)selectbalance=balancefromcampus_cardwherestudcardid='20150032'updatecampus_cardsetbalance=balance+100wherestudcardid='20150032waitforde

11、lay'00:00:05'rollbacktran-回滚goselectbalancefromcampus_cardwherestudcardid='20150032'事务2SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTEDbegintrandeclarebalancedecimal(10,2)selectbalance=balancefromcampus_cardwherestudcardid='20150032'updatecampus_cardsetbalance=balance+50wherestudc

12、ardid='20150032'committrangoselectbalancefromcampus_cardwherestudcardid='20150032'100里结事.工消息balance1j4g00""nir事务1】更改了数据，康务2】读取了表中更改后的值再进行操作，康务1】回滚。最终的表存储了错误结果。(3)不可重复读-事务1SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='2015

13、0031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20150031'committran事务2SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranupdatecampus_cardsetbalance=balance+200wherestudcardid='20150031'committranword可编辑1 两次读取事务1】读取了数据，博务2】更改数据，年务1】再读取数据。事务都不一样！(4)

14、.幻读插入-事务1SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20150031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20150031'committran-事务2SETTRANSACTIONISOLATIONLEVELSERIALIZABLEbegintraninsertintocampus_cardval

15、ues('20150031',30)committran删除-事务1SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDbegintranselectbalancefromcampus_cardwherestudcardid='20150031'waitfordelay'00:00:05'selectbalancefromcampus_cardwherestudcardid='20150031'committran-事务2settranisolationlevelrepeatablereadbegin

16、trandeletefromcampus_cardwherestudcardid='20150031'2100*J菇果消息committran23cbalancebalance3 .利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读（或者幻读）的数据不一致问题。（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）修改隔离级别以却确定数据的正确性：丢失修改，在SQL语句前加未提交读：settranisolationlevelreaduncommitted读脏数据，在SQL语句前已提交读：settranisolatio

17、nlevelreadcommitted不可重复读，在SQL语句前可重复读：settranisolationlevelrepeatableread幻读在SQL语句前加可串行读：settranisolationlevelserializable4 .构造一个出现死锁的情形。（10分）首先将锁的级别改为提交可读settranisolationlevelreadcommitted事务1begintrandeclarereadintselectread=gradefromscwheresno='95003'waitfordelay'00:00:10updatescsetgrade

18、=read-1wheresno='95003'-事务2begintrandeclarereadintselectread=gradefromscwheresno='95003'updatescsetgrade=read-1wheresno='95003'itLiEMrfi8".5 .利用dbcclog命令查看student数据库的事务日志。(5分)DBCClog('student',TYPE=2)-结再_4CurreriL5MUbQTStGhJsrtSXlTra-EBdcriOLasBackljmerftDnTag出logflnoQflmMflL*4hUgHelotsLerihPhJI；加COCO*gMUSMjLJDF_0£G*l_Ci(PTLOUNUUL0CkoooaKa20000(:01,”门,戈31LO