数据库原理与应用--实验十二

实验内容：

1. 创建一个事务，把学号为00015的选修课程号003改为001、选修课程号004改为006，以上两个操作任意一个操作失败，事务回滚，查看事务执行结果。
2. 创建一个事务，把学号为00015的的选修课程号003改为001、选修课程号004改为006，第一个操作失败，事务回滚，成功则设定为保存点，第二个操作失败，则事务回滚，查看事务执行结果。
3. 执行下列语句，看看修改的效果，并说明为什么会这样

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'

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

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

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

7.查看SM中锁的情况。

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

源代码：

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
save 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'
4.
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.
 --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
6.银行转账问题：创建一个银行账户表，里面有顾客姓名和余额，添加约束要求余额要大于等于0，然后插入几行数据，创建一个转账的存储过程，测试转账成功和转账失败例子。
create table account(
account_id char(20) primary key,
account_name char(20),
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(20) primary key,
type varchar(20),
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.丢失修改、读脏数据和不可重复读：
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