西工大数据库实验报告

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

2、/校园卡ID，工行卡ID，银行卡余额 campus_card(studcardid,balance) /校园卡ID，校园卡余额创建数据库的代码如下：create 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 into campus_card values(, 30)insert i

3、nto campus_card values(, 50)insert into campus_card values(, 70)insert into icbc_card values(, , 1000)insert into icbc_card values(, , 1000)insert into icbc_card values(, , 1000)可以看出，执行插入数据的语句后，数据库相关的表里成功出现插入的数据:针对以上数据库按照要求完成下列实验：1. 编写一个事务处理(begin tran)实现如下的操作：某学号为的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行roll

4、back。（15分）创建该事务的代码如下，执行结果如图：begin transaction declare x decimal(10,2)select x=balance from icbc_card where studcardid=set x=x-200if(x0)beginupdate icbc_cardset balance=x where studcardid=update campus_cardset balance=balance+200 where studcardid=commit transactionendelsebeginprint 余额不足，交易失败rollback

5、transactionend 然后执行查询语句，查看是否成功转账，数据是否发生更改：执行如下语句，查询结果如图： select *from icbc_cardwhere studcardid=select *from campus_cardwhere studcardid=可以看出，转账成功执行，学号为的学生的校园卡余额从5元增加了200元变为250元，而银行卡余额也成功减少200元，从1000元变为800元。2. 针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读(删除和插入)。（40分，每种数据不一致10分）第一种情况：丢失修改 事务A代码如

6、下，执行结果如图： begin transaction a declare balance1 decimal(10,2) select balance1=balance from icbc_card where studcardid= set balance1=balance1+50 update icbc_card set balance=balance1 where studcardid= commit transaction a go select * from icbc_card where studcardid=学号为的学生的银行卡里原先的余额是1000元，在执行完事务A后，可以看到

7、，表里的数据更改为事务A所更新的余额增加50元，成为1050元。 事务B代码如下，执行结果如图： begin transaction b declare balance decimal(10,2) select balance=balance from icbc_card where studcardid= waitfor delay00:00:05 set balance=balance+100 update icbc_card set balance=balance where studcardid= commit transaction b go select * from icbc_c

8、ard where studcardid= 可以看到，在执行完事务B后，由于设置了waitfor delay 所以事务B对学号为的记录的修改增加余额100元的结果为1100元，覆盖了事务A的修改，即产生了丢失修改，该过程也是一个并行执行的过程，属于并发控制。第二种情况：读脏数据代码如下，执行结果如图：begin tran adeclare x decimal (10,2)select x=balancefrom icbc_cardwhere studcardid=set x=x+100update icbc_cardset balance=xwhere studcardid=begin tra

9、n bselect *from icbc_cardwhere studcardid=commit tran bwaitfor delay 00:00:08rollbackselect *from icbc_cardwhere studcardid=可以看出，在执行完事务A后，由于事务A更新了学号为的学生的银行卡的余额，给其余额增加100元，所以执行事务B查询学号为的学生的银行卡的余额结果变为1100元，可是事务A在等待了8秒后执行rollback回滚操作，所以数据库中学号为的学生的银行卡的余额变为1000元，可是事务B读取到的学号为的学生的银行卡的余额结果仍然是1100元，这就叫读脏数据。第三

10、种情况：不可重复读不可重复读的代码如下：begin transactionselect * from icbc_card begin transaction declare b decimal(10,2) select b=balance from icbc_card where studcardid= update icbc_card set balance=b*2 where studcardid= commitselect * from icbc_cardcommit执行结果如下图：可以看出，在第一个事务读取表icbc_card的全部数据后，显示的结果为上面的结果，然后第二个事务执行了更

11、新操作，更新了学号为的学生的余额字段，把其余额乘以2，提交第二个事务。然后第一个事务执行查询表icbc_card的全部数据，结果显示学号为的学生的余额字段的数据不再是原先的350元，而是700元，原因是第二个事务更新了这个数据，这就是不可重复读。第四种情况：幻读（删除和插入） 删除的幻读：begin transactionselect * from icbc_card begin transaction delete from icbc_card where studcardid= commitselect * from icbc_cardcommit执行结果如下图：可以看出，在第一个事务读取

12、表icbc_card的全部数据后，显示的结果为上面的结果，然后第二个事务执行了更新操作，删除了学号为的学生的记录，提交第二个事务。然后第一个事务执行查询表icbc_card的全部数据，结果显示没有学号为的记录，原因是第二个事务删除了这个数据，这就是删除幻读。插入的幻读：begin transactionselect * from icbc_card begin transaction insert into icbc_card values(, , 1000) commitselect * from icbc_cardcommit 可以看出，在第一个事务读取表icbc_card的全部数据后，显

13、示的结果为上面的结果，然后第二个事务执行了更新操作，插入了学号为的学生的记录，提交第二个事务。然后第一个事务执行查询表icbc_card的全部数据，结果显示增加了学号为的记录，原因是第二个事务插入了这个数据，这就是插入幻读。3. 利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）1） 解决丢失修改通过设置X锁事务A代码如下：begin transaction adeclare b decimal(10,2)select b=balance from

14、icbc_card with (xlock) where studcardid=waitfor delay 00:00:05update icbc_cardset balance=b+200 where studcardid=commitselect * from icbc_card执行结果如图：可以看出，事务A更新了icbc_card表里的学号为的学生的余额字段，给其增加200，查询结果为1200，然后我们执行事务B:begin transaction bdeclare a decimal(10,2)select a=balance from icbc_card with (xlock) w

15、here studcardid=update icbc_cardset balance=a+100 where studcardid=commitselect * from icbc_card执行结果如下：可以看出，事务B更新了icbc_card表里的学号为的学生的余额字段，给其增加100，查询结果为1300，解决了丢失修改的问题，通过设置X锁。2） 解决读脏数据通过设置数据库隔离级别和设置holdlock保持锁来解决这个这个问题代码如下：begin transaction update icbc_card with(xlock) set balance=balance-200 where s

16、tudcardid= waitfor delay 00:00:05 rollbackbegin transactionset tran isolation level read uncommittedselect * from icbc_card with (holdlock)where studcardid=waitfor delay 00:00:05select * from icbc_card with (holdlock)where studcardid=执行结果如下图:可以看到，第一个事务更新了学号为的学生的银行卡余额，而事务二查询学号为的学生的银行卡余额时查询结果显示的数据是事务一

17、执行前的数据，没有发生变化。然后在事务一rollback后，再次查询相关数据，结果如下面的图，发现查询结果未发生变化，说明解决了读脏数据。3） 解决不可重复读begin transet tran isolation level read uncommitteddeclare b1 Decimal(10,2)declare b2 Decimal(10,2)select b1=balance from campus_card with (holdlock)where studcardid=print(第一次数据：+convert(varchar,b1)waitfor delay00:00:05se

18、lect b2=balance from campus_card with (holdlock)where studcardid=print(第一次数据：+convert(varchar,b2)begin tran update campus_card with(xlock) set balance=balance+100 where studcardid= waitfor delay 00:00:03commit执行结果如图：可以看出，不可重复读得到了解决。4. 构造一个出现死锁的情形。（10分）构造死锁的代码如下：事务一：begin transactionselect * from campus_card with (tablockx)waitfor delay00:00:05select * from icbc_card with (tablockx)commit事务二：begin transactionselect * from icbc_card with (tabloc