西北工业大学数据库实验报告7

《数据库原理》实验报告题目：实验一数据库和表的创建与管理学号姓名班级日期2014302692孟玉军1001140216.11.10一.实验内容、步骤以及结果假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号cardid即为学生的学号：icbc_card(studcardid,icbcid,balance)//校园卡ID，工行卡ID，银行卡余额campus_card(studcardid,balance)//校园卡ID，校园卡余额创建数据库代码如下：usestudentcreatetablecampus_card(studcardidChar(8),balanceDecimal(10,2))createtableicbc_card(studcardidChar(8),icbcidChar(10),lanceDecimal(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)针对以上数据库按照要求完成下列实验：1.编写一个事务处理(begintran)实现如下的操作：某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。（15分）代码：usestudentbegintransactionzhuanzhanggodeclare@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结果显示：2.针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读(删除和插入)。（40分，每种数据不一致10分）1).丢失修改：执行两段代码begintransactiondeclare@adecimal(10,2)select@a=balancefromicbc_cardwherestudcardid='20150032'waitfordelay'00:00:05'updateicbc_cardsetbalance=@a+1wherestudcardid='20150032'commitselect*fromicbc_card-------------------------------------begintransactiondeclare@bdecimal(10,2)select@b=balancefromicbc_cardwherestudcardid='20150032'waitfordelay'00:00:05'updateicbc_cardsetbalance=@b+2wherestudcardid='20150032'commitselect*fromicbc_card两段代码执行后的结果：出现了丢失修改2).读脏数据：begintransactiondeclare@bdecimal(10,2)select@b=balancefromicbc_cardwherestudcardid='20150032'updateicbc_cardsetbalance=@b*2wherestudcardid='20150032'begintransactionselect*fromicbc_cardcommitrollbackselect*fromicbc_card结果显示：3).不可重复读：begintransactionselect*fromicbc_cardbegintransactiondeclare@bdecimal(10,2)select@b=balancefromicbc_cardwherestudcardid='20150032'updateicbc_cardsetbalance=@b*2wherestudcardid='20150032'commitselect*fromicbc_cardcommit结果显示：4).幻读删除：begintransactionselect*fromicbc_cardbegintransactiondeletefromicbc_cardwherestudcardid='20150032'commitselect*fromicbc_cardcommit结果显示：插入：begintransactionselect*fromicbc_cardbegintransactioninsertintoicbc_cardvalues('20150034','2015003401',1000)commitselect*fromicbc_cardcommit结果显示：3.利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）1).丢失修改：begintransactiondeclare@bdecimal(10,2)select@b=balancefromicbc_cardwith(xlock)wherestudcardid='20150032'waitfordelay'00:00:05'updateicbc_cardsetbalance=@b+2wherestudcardid='20150032'commitselect*fromicbc_cardbegintransactiondeclare@adecimal(10,2)select@a=balancefromicbc_cardwith(xlock)wherestudcardid='20150032'waitfordelay'00:00:05'updateicbc_cardsetbalance=@a+1wherestudcardid='20150032'commitselect*fromicbc_card成功加3，解决了丢失修改2).读脏数据begintranupdatecampus_cardwith(xlock)setbalance=balance-20wherestudcardid='20150032'waitfordelay'00:00:05'rollbackbegintransettranisolationlevelreaduncommittedselect*fromcampus_cardwith(holdlock)wherestudcardid='20150032'waitfordelay'00:00:05'select*fromcampus_cardwith(holdlock)wherestudcardid='20150032'结果显示：没有出现脏数据3).不可重复读begintransettranisolationlevelreaduncommitteddeclare@b1Decimal(10,2)declare@b2Decimal(10,2)select@b1=balancefromcampus_cardwith(holdlock)wherestudcardid='20150032'print('第一次数据：'+convert(varchar,@b1))waitfordelay'00:00:05'select@b2=balancefromcampus_cardwith(holdlock)wherestudcardid='20150032'print('第一次数据：'+convert(varchar,@b2))begintranupdatecampus_cardwith(xlock)setbalance=balance+100wherestudcardid='20150032'waitfordelay'00:00:03'commit结果显示：消除了重复读错误4.构造一个出现死锁的情形。（10分）事务一：begintransactionselect*fromcampus_cardwith(tablockx)waitfordelay'00:00:05'select*fromicbc_cardwith(tablockx)commit事务二：begintransactionselect*fromicbc_cardwith(tablockx)waitfordelay'00:00:05'select*fromcampus_cardwith(tablockx)commit结果：出现了死锁5.利用dbcclog命令查看student数据库的事务日志。（5分）dbcclog(student)二.实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)除了标题内容以外，该部分内容中还可以写对于实验的一些感受，建议，意见等。学会了一些事务处理的操作，理解了利用锁机制、数据库的隔离级别去解决问题。批阅者：批阅日期：实验成绩：批注：