西工大数据库实验报告7

《数据库原理》实验报告题目：实验七事务与并发控制学号姓名班级日期2015302327马俊超100115012017．11.51.1.实验目的1.掌握事务机制，学会创建事务。2.理解事务并发操作所可能导致的数据不一致性问题，用实验展现四种数据不一致性问题：丢失修改、读脏数据、不可重复读以及幻读现象。3.理解锁机制，学会采用锁与事务隔离级别解决数据不一致的问题。4.了解数据库的事务日志。1.2.实验内容1.实验内容、步骤以及结果假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号cardid即为学生的学号：icbc_card(studcardid,icbcid,balance)//校园卡ID，工行卡ID，银行卡余额campus_card(studcardid,balance)//校园卡ID，校园卡余额创建数据库的代码如下：createtablecampus_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)可以看出，执行插入数据的语句后，数据库相关的表里成功出现插入的数据:针对以上数据库按照要求完成下列实验：1.编写一个事务处理(begintran)实现如下的操作：某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。（15分）创建该事务的代码如下，执行结果如图：begintransactiondeclare@xdecimal(10,2)select@x=balancefromicbc_cardwherestudcardid='20150032'set@x=@x-200if(@x0)beginupdateicbc_cardsetbalance=@xwherestudcardid='20150032'updatecampus_cardsetbalance=balance+200wherestudcardid='20150032'committransactionendelsebeginprint'余额不足，交易失败'rollbacktransactionend然后执行查询语句，查看是否成功转账，数据是否发生更改：执行如下语句，查询结果如图：select*fromicbc_cardwherestudcardid='20150032'select*fromcampus_cardwherestudcardid='20150032'可以看出，转账成功执行，学号为20150032的学生的校园卡余额从5元增加了200元变为250元，而银行卡余额也成功减少200元，从1000元变为800元。2.针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读(删除和插入)。（40分，每种数据不一致10分）第一种情况：丢失修改事务A代码如下，执行结果如图：begintransactionadeclare@balance1decimal(10,2)select@balance1=balancefromicbc_cardwherestudcardid='20150031'set@balance1=@balance1+50updateicbc_cardsetbalance=@balance1wherestudcardid='20150031'committransactionagoselect*fromicbc_cardwherestudcardid='20150031'学号为20150031的学生的银行卡里原先的余额是1000元，在执行完事务A后，可以看到，表里的数据更改为事务A所更新的余额增加50元，成为1050元。事务B代码如下，执行结果如图：begintransactionbdeclare@balancedecimal(10,2)select@balance=balancefromicbc_cardwherestudcardid='20150031'waitfordelay'00:00:05'set@balance=@balance+100updateicbc_cardsetbalance=@balancewherestudcardid='20150031'committransactionbgoselect*fromicbc_cardwherestudcardid='20150031'可以看到，在执行完事务B后，由于设置了waitfordelay所以事务B对学号为20150031的记录的修改增加余额100元的结果为1100元，覆盖了事务A的修改，即产生了丢失修改，该过程也是一个并行执行的过程，属于并发控制。第二种情况：读脏数据代码如下，执行结果如图：begintranadeclare@xdecimal(10,2)select@x=balancefromicbc_cardwherestudcardid='20150033'set@x=@x+100updateicbc_cardsetbalance=@xwherestudcardid='20150033'begintranbselect*fromicbc_cardwherestudcardid='20150033'committranbwaitfordelay'00:00:08'rollbackselect*fromicbc_cardwherestudcardid='20150033'可以看出，在执行完事务A后，由于事务A更新了学号为20150033的学生的银行卡的余额，给其余额增加100元，所以执行事务B查询学号为20150033的学生的银行卡的余额结果变为1100元，可是事务A在等待了8秒后执行rollback回滚操作，所以数据库中学号为20150033的学生的银行卡的余额变为1000元，可是事务B读取到的学号为20150033的学生的银行卡的余额结果仍然是1100元，这就叫读脏数据。第三种情况：不可重复读不可重复读的代码如下：begintransactionselect*fromicbc_cardbegintransactiondeclare@bdecimal(10,2)select@b=balancefromicbc_cardwherestudcardid='20150032'updateicbc_cardsetbalance=@b*2wherestudcardid='20150032'commitselect*fromicbc_cardcommit执行结果如下图：可以看出，在第一个事务读取表icbc_card的全部数据后，显示的结果为上面的结果，然后第二个事务执行了更新操作，更新了学号为20150032的学生的余额字段，把其余额乘以2，提交第二个事务。然后第一个事务执行查询表icbc_card的全部数据，结果显示学号为20150032的学生的余额字段的数据不再是原先的350元，而是700元，原因是第二个事务更新了这个数据，这就是不可重复读。第四种情况：幻读（删除和插入）删除的幻读：begintransactionselect*fromicbc_cardbegintransactiondeletefromicbc_cardwherestudcardid='20150033'commitselect*fromicbc_cardcommit执行结果如下图：可以看出，在第一个事务读取表icbc_card的全部数据后，显示的结果为上面的结果，然后第二个事务执行了更新操作，删除了学号为20150033的学生的记录，提交第二个事务。然后第一个事务执行查询表icbc_card的全部数据，结果显示没有学号为20150033的记录，原因是第二个事务删除了这个数据，这就是删除幻读。插入的幻读：begintransactionselect*fromicbc_cardbegintransactioninsertintoicbc_cardvalues('20150034','2015003401',1000)commitselect*fromicbc_cardcommit可以看出，在第一个事务读取表icbc_card的全部数据后，显示的结果为上面的结果，然后第二个事务执行了更新操作，插入了学号为20150033的学生的记录，提交第二个事务。然后第一个事务执行查询表icbc_card的全部数据，结果显示增加了学号为20150033的记录，原因是第二个事务插入了这个数据，这就是插入幻读。3.利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）1）解决丢失修改通过设置X锁事务A代码如下：begintransactionadeclare@bdecimal(10,2)select@b=balancefromicbc_cardwith(xlock)wherestudcardid='20150032'waitfordelay'00:00:05'updateicbc_cardsetbalance=@b+200wherestudcardid='20150032'commitselect*fromicbc_card执行结果如图：可以看出，事务A更新了icbc_card表里的学号为20150032的学生的余额字段，给其增加200，查询结果为1200，然后我们执行事务B:begintransactionbdeclare@adecimal(10,2)select@a=balancefromicbc_cardwith(xlock)wherestudcardid='20150032'updateicbc_cardsetbalance=@a+100wherestudcardid='20150032'commitselect*fromicbc_card执行结果如下：可以看出，事务B更新了icbc_card表里的学号为20150032的学生的余额字段，给其增加100，查询结果为1300，解决了丢失修改的问题，通过设置X锁。2）解决读脏数据通过设置数据库隔离级别和设置holdlock保持锁来解决这个这个问题代码如下：begintransactionupdateicbc_cardwith(xlock)setbalance=balance-200wherestudcardid='20150032'waitfordelay'00:00:05'rollbackbegintransactionsettranisolationlevelreaduncommittedselect*fromicbc_cardwith(holdlock)wherestudcardid='20150032'waitfordelay'00:00:05'select*fromicbc_cardwith(holdlock)wherestudcardid='20150032'执行结果如下图:可以看