搜索
《数据库原理》实验报告实验七：事务与并发控制学号姓名班级日期2013302534杨添文100113032015.11.14实验七:事务与并发控制一、实验内容假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号cardid即为学生的学号：icbc_card(studcardid,icbcid,balance)//校园卡ID，工行卡ID，银行卡余额campus_card(studcardid,balance)//校园卡ID，校园卡余额针对以上数据库按照要求完成下列实验：1.编写一个事务处理(begintran)实现如下的操作：某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。（15分）（1）程序代码如下：declare@balanceintselect@balance=balance-200fromicbc_cardwherestudcardid='20150032'updateicbc_cardsetbalance=@balancewherestudcardid='20150032'updatecampus_cardsetbalance=balance+200wherestudcardid='20150032'if@balance0rollbackcommit;（2）结果如下：（结果很明显，阴影部分即显示操作已成功。）2.针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读(删除和插入)。（40分，每种数据不一致10分）（1）丢失修改：代码如下：settranisolationlevelreaduncommitteddeclare@balanceintselect@balance=balancefromicbc_cardwherestudcardid='20150031'waitfordelay'00:00:10'updateicbc_cardsetbalance=@balance-300wherestudcardid='20150031'selectbalancefromicbc_cardwherestudcardid='20150031'这段代码执行的时间设置为10秒，在此期间，重复执行这段代码（可以增加一个窗口，在10秒内执行），结果如下截图：很明显，若是成功修改，balance中的数据就会是400，而不是700。（2）读脏数据：代码如下：（两个事务的执行时间间隔不超过10秒）1、先编写代码执行事务1（执行一次对balance的操作，回滚）settranisolationlevelreaduncommittedbegintranupdateicbc_cardsetbalance=balance+2000wherestudcardid='20150033'selectbalancefromicbc_cardwherestudcardid='20150033'waitfordelay'00:00:10'rollback;selectbalancefromicbc_cardwherestudcardid='20150033'这段代码的结果为：（balance的值已经返回正常值，为1000）2、执行事务2：settranisolationlevelreaduncommittedselectbalancefromicbc_cardwherestudcardid='20150033'这段代码结果为：（结果不是1000，却是3000，说明读了脏数据）（3）不可重复读：代码段为：（两个事务的执行时间间隔不超过10秒）1、先执行事务1：declare@balanceint/*第一次读数据*/select@balance=balancefromicbc_cardwherestudcardid='20150032'print@balancewaitfordelay'00:00:10'/*第二次读数据*/select@balance=balancefromicbc_cardwherestudcardid='20150032'print@balance结果为：（可以很明显的看出来，两次读数据的结果不一致，原因在于事务2的中间加入，而事务1又没有加锁导致）2、再执行事务2：（在第二次读数据之前更新）updateicbc_cardsetbalance=balance*2wherestudcardid='20150032'结果：（4）幻读：代码段为：（两个事务的执行时间间隔不超过10秒）1、先执行事务1：selectbalancefromicbc_cardwherestudcardid='20150032'waitfordelay'00:00:10'selectbalancefromicbc_cardwherestudcardid='20150032'结果：（因为增加了一条数据，两次读的结果就不一致）2、再执行事务2：（增加一条数据）insertintoicbc_cardvalues('20150032','2015003201',2000)结果：3.利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）（1）丢失修改：事务1：（对表的操作加锁）--settranisolationlevelreaduncommittedbegintrandeclare@balanceintselect*fromicbc_cardwith(tablockx)select@balance=balancefromicbc_cardwherestudcardid='20150031'waitfordelay'00:00:10'updateicbc_cardsetbalance=@balance-300wherestudcardid='20150031'selectbalancefromicbc_cardwherestudcardid='20150031'commit事务2与上述一致，间隔10秒，结果为：（可知没有丢失修改，事务1结果为800，而事务2结果为500）事务1：事务2：（2）读脏数据：1、事务1：settranisolationlevelreadcommittedbegintranupdateicbc_cardsetbalance=balance+2000wherestudcardid='20150033'selectbalancefromicbc_cardwherestudcardid='20150033'waitfordelay'00:00:10'rollback;selectbalancefromicbc_cardwherestudcardid='20150033'2、事务2：settranisolationlevelreadcommittedselectbalancefromicbc_cardwherestudcardid='20150033'3、结果：（事务2数据已经正常）（1）事务1：（2）事务2：（3）不可重复读：1、事务1：begintranselect*fromicbc_cardwith(tablockx)declare@balanceint/*第一次读数据*/select@balance=balancefromicbc_cardwherestudcardid='20150031'print@balancewaitfordelay'00:00:10'/*第二次读数据*/select@balance=balancefromicbc_cardwherestudcardid='20150031'print@balancecommit2、事务2：settranisolationlevelreadcommittedupdateicbc_cardsetbalance=balance*2wherestudcardid='20150031'3、结果：（可以明显看出可以重复读数据）事务1：事务2：4.构造一个出现死锁的情形。（10分）（1）事务1：begintranupdatecampus_cardwith(xlock)setbalance=balance+100waitfordelay'00:00:10'select*fromicbc_cardwith(holdlock)rollbacktran（2）事务2：begintranupdateicbc_cardwith(xlock)setbalance=balance+200waitfordelay'00:00:10'select*fromcampus_cardwith(holdlock)rollbacktran（3）结果为：事务2有结果，执行成功：事务1执行时出现死锁：5.利用dbcclog命令查看student数据库的事务日志。（5分）SQL语句：dbcclog(student,4)结果：二、实验反思事务在执行过程中，由于并行的执行特点，经常会因为数据共享而产生数据的各种误操作，例如读脏数据等等，这时候就需要利用到锁机制来平衡这个短板，本节课的内容就是基于这个问题而提出的，实验本身难度不大，在于细心。