数据库第五次实验报告

南昌航空大学实验报告

二0一年月日

课程名称：数据库原理实验名称：数据库的并发控制与恢复备份

班级：姓名：同组人：

指导教师评定：签名：

1、实验环境

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,noformat 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中。

四、实验总结

通过本次试验，基本掌握数据库并发控制与恢复备份基础知识；基本掌握创建、修改、使用、数据库并发控制与恢复的不同方法。