实验内容:
- 创建一个事务,把学号为00015的选修课程号003改为001、选修课程号004改为006,以上两个操作任意一个操作失败,事务回滚,查看事务执行结果。
- 创建一个事务,把学号为00015的的选修课程号003改为001、选修课程号004改为006,第一个操作失败,事务回滚,成功则设定为保存点,第二个操作失败,则事务回滚,查看事务执行结果。
- 执行下列语句,看看修改的效果,并说明为什么会这样
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