假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号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),lance Decimal(10,2),)示例数据如下：insert into 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)针对以上数据库按照要求完成下列实验：1.编写一个事务处理(begin tran)实现如下的操作：某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。

（15分）代码：use studentbegin transaction zhuanzhanggodeclare @x decimal(10, 2)select @x=balance from icbc_card where studcardid='20150032'set @x=@x-200if(@x>=0)beginupdate icbc_card set balance=@x where studcardid='20150032'update campus_card set balance=balance+200 where studcardid='20150032' commit tranendelsebeginprint'余额不足，不能转账'rollback tranend结果显示：2.针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读(删除和插入)。

（40分，每种数据不一致10分）1).丢失修改：执行两段代码begin transactiondeclare @a decimal(10,2)select @a=balancefrom icbc_card where studcardid='20150032'waitfor delay '00:00:05'update icbc_cardset balance=@a+1 where studcardid='20150032'commitselect * from icbc_card-------------------------------------begin transactiondeclare @b decimal(10,2)select @b=balancefrom icbc_card where studcardid='20150032' waitfor delay '00:00:05'update icbc_cardset balance=@b+2 where studcardid='20150032' commitselect * from icbc_card两段代码执行后的结果：出现了丢失修改2).读脏数据：begin transactiondeclare @b decimal(10,2)select @b=balancefrom icbc_card where studcardid='20150032' update icbc_cardset balance=@b*2 where studcardid='20150032'begin transactionselect * from icbc_cardcommitrollbackselect * from icbc_card结果显示：3).不可重复读：begin transactionselect * from icbc_cardbegin transactiondeclare @b decimal(10,2)select @b=balancefrom icbc_card where studcardid='20150032' update icbc_cardset balance=@b*2 where studcardid='20150032' commitselect * from icbc_cardcommit结果显示：4).幻读删除：begin transactionselect *from icbc_cardbegin transactiondelete from icbc_cardwhere studcardid='20150032'commitselect *from icbc_cardcommit结果显示：插入：begin transactionselect *from icbc_cardbegin transactioninsert into icbc_card values('20150034', '2015003401', 1000) commitselect *from icbc_cardcommit结果显示：3.利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。

（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）1).丢失修改：begin transactiondeclare @b decimal(10,2)select @b=balancefrom icbc_card with (xlock)where studcardid='20150032'waitfor delay '00:00:05'update icbc_cardset balance=@b+2 where studcardid='20150032'commitselect * from icbc_cardbegin transactiondeclare @a decimal(10,2)select @a=balancefrom icbc_card with (xlock) where studcardid='20150032'waitfor delay '00:00:05'update icbc_cardset balance=@a+1 where studcardid='20150032'commitselect * from icbc_card成功加3，解决了丢失修改2).读脏数据begin tranupdate campus_card with(xlock)set balance=balance-20where studcardid='20150032'waitfor delay '00:00:05'rollbackbegin transet tran isolation level read uncommittedselect * from campus_card with (holdlock) where studcardid='20150032'waitfor delay'00:00:05'select * from campus_card with (holdlock) where studcardid='20150032'结果显示：没有出现脏数据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='20150032'print('第一次数据：'+convert(varchar,@b1))waitfor delay'00:00:05'select @b2=balance from campus_card with (holdlock) where studcardid='20150032'print('第一次数据：'+convert(varchar,@b2))begin tranupdate campus_card with(xlock)set balance=balance+100where studcardid='20150032'waitfor delay '00:00:03'commit结果显示：消除了重复读错误4.构造一个出现死锁的情形。

（10分）事务一：begin transactionselect * from campus_card with (tablockx)waitfor delay'00:00:05'select * from icbc_card with (tablockx)commit事务二：begin transactionselect * from icbc_card with (tablockx)waitfor delay'00:00:05'select * from campus_card with (tablockx)commit结果：出现了死锁5.利用dbcc log命令查看student数据库的事务日志。