数据库第五次实验报告
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
南昌航空大学实验报告
二0一年月日
课程名称:数据库原理实验名称:数据库的并发控制与恢复备份班级:姓名:同组人:
指导教师评定:签名:
一、实验环境
1、Windows2000或以上版本;
2、SQLServer2000或2005。
二、实验目的
1.掌握数据库并发控制与恢复备份基础知识;
2.掌握创建、修改、使用、数据库并发控制与恢复的不同方法。
三、实验步骤及参考源代码
实验一:数据库并发控制
1、丢失数据
create table sales
(客户代号char(5)primary key,
数量int null)
insert into sales values('A0001',0)
create procedure modi_a as
declare @i int
declare @s1 int
set transaction isolation level read committed select @i=1
while(@i<=2000)begin
begin tran
select @s1=数量from sales where客户代号
='A0001'
waitfor delay '00:00:00.002'
update sales set数量=@s1+1 where客户代号
='A0001'
commit tran
select @i=@i+1
end
create procedure modi_m as
declare @i int
declare @s1 int
set transaction isolation level read committed select @i=1
while(@i<=2000)begin
begin tran
select @s1=数量from sales where客户代号
='A0001'
waitfor delay '00:00:00.002'
update sales set数量=@s1-1 where客户代号
='A0001'
commit tran
select @i=@i+1
end
同时运行存储过程modi_a和modi_m可以看到如下结果:
本应该数量一栏应该是0的,说明数据发生了丢失数据。
将两个存储过程中事务中select语句改为:
select @s1=数量from sales with(tablockx) where 客户代号=’A0001’
然后再次同时运行两个存储过程,结果如下:
可以看到如果在存储过程中对数据加上独立锁后数量结果始终为0。
2、脏读数据
create procedure dirt_wroll as
declare @i int
declare @s1 int
set transaction isolation level read uncommitted
select @i=1
while(@i<=16000)begin
select @i=@i+1
begin tran
select @s1=数量from sales where客户代号
='A0001'
update sales set数量=@s1+1 where客户代号
='A0001'
rollback tran
waitfor delay '00:00:00.000'
end
create procedure dirt_r as
declare @i int
declare @s1 int
set transaction isolation level read uncommitted
select @i=1
while(@i<=60000)begin
select @i=@i+1
begin tran
select @s1=数量from sales where客户代号
='A0001'
if(@s1<>1000)raiserror('发生了脏读!',16,1)
commit tran
end
并行运行上面两个存储过程dirt_wroll和dirt_r看到如下结果:
可知如果是这样的话,就会发生脏读的现象。
如果把上述的存储过程中的设置隔离级别的语句
“set transaction isolation level read uncommitted”
改为:
“set transaction isolation level read committed”
则就可以把问题解决了。
3、不可重复读
create procedure rep_r as
declare @i int
declare @s1 int
declare @s2 int
set transaction isolation level read committed
select @i=1
while(@i<=3000)begin
select @i=@i+1
begin tran
select @s1=数量from sales where客户代号
='A0001'
waitfor delay '00:00:00.001'
select @s2=数量from sales where客户代号
='A0001'
if(@s1<>@s2)raiserror('发生不可重复
读!',16,1)
commit tran
end
create procedure rep_w as
declare @i int
declare @s1 int
declare @s2 int
set transaction isolation level read committed select @i=1
while(@i<=1000)begin
begin tran
select @s1=数量from sales where客户代号
='A0001'
waitfor delay '00:00:00.002'
update sales set数量=@s1+1 where客户代号
='A0001'
commit tran
select @i=@i+1
end
以上两个存储过程同时运行的时候,会出现不可重复的现象,结果如下:
预防这种结果的方法就是制定更高的事务隔离级别,如:repeatable read、snapshot、serializable.
4、幻影问题
create procedure huany_I as
declare @i int
set transaction isolation level repeatable read delete from sales where(客户代号='A1111')
select @i=1
while(@i<=1000)begin
begin tran
insert into sales(客户代号,数
量)values('A1111',1000)
commit tran
waitfor delay '00:00:00.001'
select @i=@i+1
end
create procedure huany_u as
declare @i int
declare @j int
set transaction isolation level repeatable read select @i=1
while(@i<=300)begin
begin tran
update sales set数量=数量+3 where客户代号
='A1111'
select @j=0
select @j=count(*)from sales where客户代号
='A1111'and数量=1000
if(@j>0)raiserror('发生了幻影现象!',16,1)
commit tran
waitfor delay '00:00:00.001'
select @i=@i+1
end
同时运行上面两个存储过程huany_I和huany_u会出现幻影现象,结果如下图:
解决的方法为:指定事务级别为serializable。
5、抢答问题
create procedure qiangxian1 as
declare @i int
set transaction isolation level read committed select @i=1
while(@i<=5000)begin
begin tran
update sale set数量=数量+10000 where客户代号=@i
if(@@error<>0)rollback tran
else commit tran select @i=@i+1
end
当同时执行像上面多个存储过程的时候就会出现抢答现象,记录的修改只能由先加锁的进程完成。
6、编号产生问题
create procedure bhsc as
declare @i int
declare@s1 int
set transaction isolation level read committed select @i=1
while(@i<=1500)begin
begin tran
select @s1=max(客户代号)+1 from sale
insert into sale(客户代号,数量)values(@s1,@i)
commit tran
select @i=@i+1
end
多个以上的存储过程并行运行会出现编号重复现象,而且发现重复程度与并发程度成正比。
解决的办法法有:(1)设计编号产生事务一开始就加独立锁;(2)设计编号产生事务,其中采用插入后即查询重复编号情况,若发现重复,能进行反复尝试再插入;(3)利用一般数据库具有的identity字段来保障编号的唯一性。
下面的存储过程体现了前
两种方法,当多个此存储过程同时运行时不会在发生编号重复现象。
create procedure bhsc2 as
declare @i int
declare @s1 int
declare @kk int
select transaction level read committed
while(@i<=1500)begin
begin tran
select @s1=max(客户代号)+1 from sale
with(tablock)
insert into sale(客户代号,数量)values(@s1,@i)
select @kk=count(*)from sale where客户代号=@1
if @kk>=2 begin rollback tran continue end
else commit tran
select @i=@i+1
end
7、手工加锁下并发事务读写冲突
create procedure pmin as
set transaction isolation level read committed select @i=1
while(@i<=500)begin
begin tran
select@s1=数量from sales with(updlock)where 客户代号='A0001'
waitfor delay '00:00:00.003'
update sales set数量=@s1-1 where客户代号
='A0001'
if(@@error<>0)rollback tran
else commit tran
select @i=@i+1;
end
create procedure padd as
set transaction isolation level read committed select @i=1
while(@i<=500)begin
begin tran
select@s1=数量from sales with(updlock)where 客户代号='A0001'
waitfor delay '00:00:00.003'
update sales set数量=@s1+1 where客户代号
='A0001'
if(@@error<>0)rollback tran
else commit tran
select @i=@i+1;
end
实验二:数据库备份与恢复
(1)备份数据库
创建用于存放jxgl数据库完整备份的逻辑备份设备,然后备份整个jxgl数据库
use master
exec sp_addumpdevice'disk','jxgl_1',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\jxgl_1.dat'
backup database JXGL to jxgl_1
创建一个数据库和日志的完整备份。
将数据库备份到称为jxgl_1的逻辑备份设备上,然后将日志备份到称为jxglLog1的逻辑备份设备上:
use master
exec sp_addumpdevice'disk','jxglLog1',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\jxglLog1.dat' backup log jxgl to jxglLog1;
创建一个文件备份:
backup database [JXGL] file='JXGl'to disk=
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\jxglBackUp.bak'with init,
nounload,name='jxglBackUp',noskip,stats=10,nofo rmat
backup log JXGL to jxgl_1 with norecovery;
(2)还原数据库
从还原设备jxgl_1还原完整数据库:
restore database JXGL from jxgl_1
还原完整数据库备份后还原差异备份,差异备份追加到包含完整数据库备份的备份设备上。
restore database jxgl from jxgl_1 with norecovery restore database jxgl from jxgl_1 with file=2
使用restart选项重新启动因服务器电源故障而中断的restore 操作:
restore database jxgl from jxgl_1
restore database jxgl from jxgl_1 with restart
还原完整数据库和事务日志,并将已还原的数据库移动到C:\Program Files\Microsoft SQL Server\MSSQL\Data 目录下:
restore database jxgl from jxgl_1 with
norecovery ,move'jxgl'to
'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Newjxgl.mdf',move'jxgl_Log'
to
'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Newjxgl.1df'
restore log jxgl from jxglLog1 with recovery 从一个文件备份中还原:
restore database[jxgl]file=N'jxgl'from DISK=N'f:\Program Files\Microsoft SQL
Server\MSSQL\backup\jxgl 备份.bak'
(3)对数据库jxgl的备份与还原操作
<1>将jxgl数据库的故障还原模型设置为“完整”
<2>建立一个备份设备jxgl_dev,对应的物理文件名为:c:\jxgl_dev.bak;
use master
exec sp_addumpdevice'disk','jxgl_dev',
'C:\jxgl_dev.bak';
<3>为jxgl数据库做完全备份至备份设备jxgl_dev;
backup database jxgl to jxgl_dev;
<4>向s表中插入一行数据;
insert into s values('98015','王二','男',20,'IT')
<5>为jxgl数据库做差异备份至备份设备jxgl_dev;
backup database jxgl to jxgl_dev
<6>再向s表中插入一行数据;
insert into s values('98016','张三','男',20,'SW')
<7>为jxgl数据库做日志备份至备份设备jxgl_dev;
backup log jxgl to jxgl_dev
<8>删除数据库,并创建新的数据库jxgl,为新数据库jxgl进行完全备份的恢复,查看s表的内容;
drop database jxgl
创建新的jxgl数据库
恢复:
restore database jxgl with recovery
<9>为jxgl数据库进行差异备份的恢复,查看s表中的内容;restore database jxgl from jxgl_dev with norecovery
<10>为jxgl数据库进行事务日志备份的恢复,查看s表的内容;
restore log jxgl from jxgl_dev
(4)将数据库导出到Access中
创建一个Access数据库jxgl(jxgl.mdb文件),把在SQL SERVER 中创建的jxgl数据库导出到Access数据库jxgl中。
四、实验总结
通过本次试验,基本掌握数据库并发控制与恢复备份基础知识;基本掌握创建、修改、使用、数据库并发控制与恢复的不同方法。