1、数据库实验报告记录事务与并发控制数据库实验报告记录:事务与并发控制 作者: 日期: 数据库原理实验报告题目:实验七事务与并发控制学号姓名班级日期2013302478纪昌宇100113012015.11.141.实验七:事务与并发控制1.1.实验目的1.掌握事务机制,学会创建事务。2.理解事务并发操作所可能导致的数据不一致性问题,用实验展现四种数据不一致性问题:丢失修改、读脏数据、不可重复读以及幻读现象。3.理解锁机制,学会采用锁与事务隔离级别解决数据不一致的问题。4.了解数据库的事务日志。1.2.实验内容 假设学校允许学生将银行卡和校园卡进行绑定,在student数据库中有如下的基本表,其中校
2、园卡编号cardid即为学生的学号: icbc_card(studcardid,icbcid,balance) /校园卡ID,工行卡ID,银行卡余额 campus_card(studcardid,balance) /校园卡ID,校园卡余额数据创建的代码: use studentcreate table campus_card( studcardid Char(8) , balance Decimal(10,2)create table icbc_card( studcardid Char(8), icbcid Char(10), balance Decimal(10,2) ) insert i
3、nto campus_card values(20150031, 30)insert into campus_card values(20150032, 50)insert into campus_card values(20150033, 70)insert into icbc_card values(20150031, 2015003101, 1000)insert into icbc_card values(20150032, 2015003201, 1000)insert into icbc_card values(20150033, 2015003301, 1000) 针对以上数据库
4、按照要求完成下列实验:1.编写一个事务处理(begin tran)实现如下的操作:某学号为*的学生要从银行卡中转账200元到校园卡中,若中间出现故障则进行rollback。(15分)set transaction isolation levelrepeatable readbegin transactionuse studentgodeclare x decimal(10, 2)select x=balance from icbc_cardwhere studcardid=20150032set x=x-200 if(x=0) begin update icbc_card set balanc
5、e=x where studcardid=20150032 update campus_card set balance=balance+200 where studcardid=20150032 commit tran end else begin print 余额不足,不能转账 rollback tran end2.针对本题的数据库和表,分别用具体的例子展现四种数据不一致问题:丢失修改、读脏数据、不可重复读和幻读(删除和插入)。(40分,每种数据不一致10分)(1).丢失修改-事务1begin trandeclare balance decimal(10,2)select balance=
6、balance from campus_card where studcardid=20150033waitfor delay 00:00:05set balance=balance-10update campus_card set balance=balance where studcardid=20150033commit trangoselect balance from campus_card where studcardid=20150033-事务2begin trandeclare balance1 decimal(10,2)select balance1=balance from
7、 campus_card where studcardid=20150033waitfor delay 00:00:05set balance1=balance1-20update campus_card set balance=balance1 where studcardid=20150033commit trangoselect balance from campus_card where studca 【事务1】更改了数据,结果为60,但是没有被读到。最终【事务2】的结果50覆盖了【事务1】的更改值,结果不是期望值40(2).读脏数据-事务1 SET TRANSACTION ISOLA
8、TION LEVEL READ UNCOMMITTED -READ UNCOMMITTED 执行脏读,不发出共享锁,也不接受排他锁begin tran declare balance decimal(10,2) select balance=balance from campus_card where studcardid=20150032 update campus_card set balance=balance+100 where studcardid=20150032 waitfor delay 00:00:05 rollback tran -回滚 goselect balance f
9、rom campus_card where studcardid=20150032 -事务2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED begin tran declare balance decimal(10,2) select balance=balance from campus_card where studcardid=20150032 update campus_card set balance=balance+50 where studcardid=20150032 commit tran goselect balance
10、from campus_card where studcardid=20150032 【事务1】更改了数据,【事务2】读取了表中更改后的值再进行操作,【事务1】回滚。最终的表存储了错误结果。 (3)不可重复读 -事务1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran select balance from campus_card where studcardid=20150031 waitfor delay 00:00:05 select balance from campus_card where studcardid=20
11、150031commit tran -事务2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran update campus_card set balance=balance+200 where studcardid=20150031 commit tran 【事务1】读取了数据,【事务2】更改数据,【事务1】再读取数据。【事务1】两次读取都不一样!(4).幻读 插入 -事务1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran select balance from
12、 campus_card where studcardid=20150031 waitfor delay 00:00:05 select balance from campus_card where studcardid=20150031commit tran -事务2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE begin tran insert into campus_card values(20150031, 30) commit tran 删除-事务1 SET TRANSACTION ISOLATION LEVEL READ COMMITT
13、ED begin tran select balance from campus_card where studcardid=20150031 waitfor delay 00:00:05 select balance from campus_card where studcardid=20150031commit tran -事务2 set tran isolation level repeatable read begin tran delete from campus_card where studcardid=20150031commit tran 3.利用锁机制、数据库的隔离级别等,
14、设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。(30分,每种数据不一致10分,提示可以用sp_lock系统存储过程查看当前锁状况)修改隔离级别以却确定数据的正确性:丢失修改,在SQL语句前加未提交读:set tran isolation level read uncommitted读脏数据,在SQL语句前已提交读:set tran isolation level read committed不可重复读,在SQL语句前可重复读:set tran isolation level repeatable read幻读在SQL语句前加可串行读:set tran isol
15、ation level serializable4.构造一个出现死锁的情形。(10分) 首先将锁的级别改为提交可读set tran isolation level read committed-事务1begin trandeclare read intselect read=gradefrom scwhere sno=95003waitfor delay 00:00:10update sc set grade = read-1where sno=95003-事务2begin trandeclare read intselect read=gradefrom scwhere sno=95003update sc set grade = read-1where sno=950035.利用dbcc log命令查看student数据库的事务日志。(5分) DBCC log(student, TYPE=2)
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1