ZK数据库实验十二(事务创建)

1. 创建一个事务，把学号为00015的选修课程号003改为001、选修课程号004改为006，以上两个操作任意一个操作失败，事务回滚，查看事务执行结果。

   begin transaction Q1
   update sc
   set CNo = '001'
   where SNo = '00015' and CNo ='003'
   update sc
   set CNo = '006'
   where SNo = '00015' and CNo ='004'
   if @@ERROR !=0
   rollback transaction Q1
   else
   commit transaction Q1
   

2. 创建一个事务，把学号为00015的的选修课程号003改为001、选修课程号004改为006，第一个操作失败，事务回滚，成功则设定为保存点，第二个操作失败，则事务回滚，查看事务执行结果。

   begin transaction Q2
   update sc
   set CNo = '001'
   where SNo = '00015' and CNo ='003'
   if @@ERROR !=0
   rollback transaction Q2
   else 
   commit transaction Q2
   update sc
   set CNo = '006'
   where SNo = '00015' and CNo ='004'
   if @@ERROR !=0
   rollback transaction Q2
   else 
   commit transaction Q2
   

3. 执行下列语句，看看修改的效果，并说明为什么会这样

   UPDATE SC

   SET Score=score+1, CNO='008'

   WHERE SNO='00003' AND CNO='002'

   update sc
   SET Score=score+1, CNO='008'
   WHERE SNO='00003' AND CNO='002'
   

4. 启动隐式事务，然后以下两个更新语句一起执行，看看效果，并说明为什么会这样，实验完成后把隐式事务关闭。

   UPDATE SC

   SET Score=score+1

   WHERE SNO='00001' AND CNO='001'

   UPDATE SC

   SET CNO='008'

   WHERE SNO='00001' AND CNO='001'

   set implicit_transactions on use SM
   UPDATE SC
   SET Score=score+1
   WHERE SNO='00001' AND CNO='001'
   UPDATE SC
   SET CNO='008'
   WHERE SNO='00001' AND CNO='001'
   commit transaction
   set implicit_transactions off
   

5. 开两个查询窗口，均连接到SM，分别执行下面两段sql（两次启动执行的时间要在1分钟之内） :

   --Query 1 

   Begin Tran    

   Update Student Set Sage = Sage + 1;   

   WaitFor Delay '00:01:00';   

   SELECT * FROM Course 

   Rollback Tran;   

    --Query 2 

   Begin Tran    

   Update Course Set CCredits = CCredits + 0.5;   

   WaitFor Delay '00:01:00';   

   SELECT * FROM Student

   Commit Tran

   观察出现的现象，思考一下为什么？如何通过加锁解决该问题？

   begin transaction Q501
   update Student set SAge = SAge+1
   waitfor delay '00:00:05'
   select * from course
   rollback transaction Q501
   begin transaction Q502
   update course
   set CCredits = CCredits+0.5
   waitfor delay '00:00:05'
   select * from Student
   commit transaction Q502
   

6. 银行转账问题：创建一个银行账户表，里面有顾客姓名和余额，添加约束要求余额要大于等于0，然后插入几行数据，创建一个转账的存储过程，测试转账成功和转账失败例子。

   %%创建账户信息表
   create table account(
   account_id varchar(50) primary key,
   account_name varchar(50),
   balance numeric(10,2) not null)
   
   alter table account
   add constraint FK_account_balance check(balance>=0)
   insert into account values('622300000001','张三','100.0')
   insert into account values('622300000002','李四','0.0') 
   
   %%创建转账记录表
   create table sp_account(
   id varchar(50) primary key,
   type varchar(50),
   amount numeric(10,2),
   time datetime)
   
   %%创建转账存储过程
   if OBJECT_ID('p_transfer','p') is not null
   drop proc p_transfer
   go
   create proc p_transfer
   @out_id varchar(50),
   @in_id varchar(50),
   @balance numeric(10,2),
   @balance1 numeric(10,2),
   @time datetime
   as 
   begin try
   select @balance = balance
   from account
   where account_id = @out_id
   if @balance >= @balance1
   begin 
   update account
   set balance = balance-@balance1
   where account_id = @out_id
   update account
   set balance = balance+@balance1
   where account_id = @in_id
   print '转账成功'
   end
   else
   begin 
   print '余额不足'
   end
   end try
   begin catch
   if(@@TRANCOUNT>0)
   declare @message varchar(50)
   set @message =(select error_message()as message)
   print @message
   rollback transaction
   end catch
   if (@@TRANCOUNT>0)
   commit transaction
   
   %%执行情况
   declare @a int
   exec p_transfer '622300000001','622300000002',100,200,@a
   select *
   from account
   
   declare @b int
   exec p_transfer '622300000001','622300000002',100,20,@b
   select *
   from account
   
   

7. 查看SM中锁的情况。

   exec sp_lock

8. 在SC表中，分别设计具体例子来展示数据不一致问题：丢失修改、读脏数据和不可重复读。

   %%丢失修改
   begin transaction Q801
   declare @age1 int
   select @age1 = Sage
   from Student
   where SNo = '00001'
   waitfor delay '00:00:05'
   update Student
   set SAge = @age1+1
   where SNo = '00001'
   commit transaction Q801
   
   
   %%读脏数据
   begin transaction Q802
   update Student
   set SAge = 10
   where SNo = '00001'
   save transaction Q802
   waitfor delay '00:00:10'
   rollback transaction Q802
   begin transaction Q8021
   select *
   from Student
   with(nolock)
   where SNo = '00001'
   commit transaction Q8021
   
   %%不可重复度
   begin transaction Q8031
   select SAge
   from Student
   where SNo = '00001'
   waitfor delay '00:00:05'
   select SAge
   from Student
   where SNo = '00001'
   commit transaction Q8031
   begin transaction Q8032
   update Student
   set SAge = SAge+1
   where SNo = '00001'
   commit transaction Q8032