实验15事务与并发控制.docx

实验15事务与并发控制.docx

《实验15事务与并发控制.docx》由会员分享，可在线阅读，更多相关《实验15事务与并发控制.docx（19页珍藏版）》请在冰豆网上搜索。

实验15事务与并发控制

实验十五　事务与并发控制

【实验目的与要求】

1.掌握数据库事务的概念

2.熟悉数据库的四个特性

3.熟练掌握数据库事务的实现方法

【实验容与步骤】

15.1.SQLServer数据库事务基础知识

1．事务的概念（Transaction）

所谓事务是用户定义的一个数据库操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。

关系数据库中，事务可以是一条SQL语句、一组SQL语句。

在SQL语言中，定义事务的语句有三条：

BeginTransaction开始

Commit结束

Rollback回滚

2．事务开始：

BEGINTRANSACTION

标记一个显式本地事务的起始点。

BEGINTRANSACTION将TRANCOUNT加1。

语法结构：

BEGINTRAN[SACTION][transaction_name|tran_name_variable

[WITHMARK['description']]]

参数说明：

transaction_name：

是给事务分配的名称。

transaction_name必须遵循标识符规则，但是不允许标识符多于32个字符。

仅在嵌套的BEGIN...COMMIT或BEGIN...ROLLBACK语句的最外语句对上使用事务名。

tran_name_variable：

是用户定义的、含有有效事务名称的变量的名称。

必须用char、varchar、nchar或nvarchar数据类型声明该变量。

WITHMARK['description']：

指定在日志中标记事务。

Description是描述该标记的字符串。

如果使用了WITHMARK，则必须指定事务名。

WITHMARK允许将事务日志还原到命名标记。

4．事务提交：

COMMITTRANSACTION

标志一个成功的隐性事务或用户定义事务的结束。

如果TRANCOUNT为1，COMMITTRANSACTION使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放连接占用的资源，并将TRANCOUNT减少到0。

如果TRANCOUNT大于1，则COMMITTRANSACTION使TRANCOUNT按1递减。

语法结构：

COMMIT[TRAN[SACTION][transaction_name|tran_name_variable]]

参数说明：

transaction_name：

MicrosoftSQLServe忽略该参数。

transaction_name指定由前面的BEGINTRANSACTION指派的事务名称。

transaction_name必须遵循标识符的规则，但只使用事务名称的前32个字符。

通过向程序员指明COMMITTRANSACTION与哪些嵌套的BEGINTRANSACTION相关联，transaction_name可作为帮助阅读的一种方法。

tran_name_variable：

是用户定义的、含有有效事务名称的变量的名称。

必须用char、varchar、nchar或nvarchar数据类型声明该变量。

5．事务回滚：

ROLLBACKTRANSACTION

将显式事务或隐性事务回滚到事务的起点或事务的某个保存点。

语法结构：

ROLLBACK[TRAN[SACTION]

[transaction_name|tran_name_variable

|savepoint_name|savepoint_variable]]

参数说明：

transaction_name：

是给BEGINTRANSACTION上的事务指派的名称。

transaction_name必须符合标识符规则，但只使用事务名称的前32个字符。

嵌套事务时，transaction_name必须是来自最远的BEGINTRANSACTION语句的名称。

tran_name_variable：

是用户定义的、含有有效事务名称的变量的名称。

必须用char、varchar、nchar或nvarchar数据类型声明该变量。

savepoint_name：

是来自SAVETRANSACTION语句的savepoint_name。

savepoint_name必须符合标识符规则。

当条件回滚只影响事务的一部分时使用savepoint_name。

savepoint_variable：

是用户定义的、含有有效保存点名称的变量的名称。

必须用char、varchar、nchar或nvarchar数据类型声明该变量。

15.2.SQLServer数据库事务创建

1．事务的创建

（1）在查询分析器中执行以下语句，创建一个名为t_InsUpdate简单的事务，并使它正常提交。

Begintransactiont_InsUpdate--t_InsUpdate为事务名

UseCPXS

InsertintoCP（产品编号,产品名称,价格,库存量）

Values（'100021','宝马汽车',456780,39）

UpdateXSS

Set负责人='飞'

Where客户编号='000003'

Committransactiont_InsUpdate--事务提交结束，t_InsUpdate为事务名

测试：

执行语句“select*fromxss;”，看数据是否添加到表中？

请给出测试结果：

（2）在查询分析器中执行以下语句，创建一个简单的事务，并使它回滚

BEGINTRANSACTION

UseCPXS

select*fromxss;

updatexss

set客户名称='理工学院'

where客户编号='000002';

select*fromxss;

Rollback;

测试：

select*fromxss;

请给出测试结果：

思考：

比较两条查询语句的结果差异，为什么会有这样的差异？

执行了事务后，rollback相当于取消了事务，事务没有生效。

2．事务的存储点：

事务的存储点可以使事务在发生回滚的情况下，存储点前的操作结果得以保存。

执行以下语句，创建一个名为t_InsertCP，其中包含一个存储点。

Begintransactiont_InsertCP

--UseCPXS

InsertintoCP（产品编号,产品名称,价格,库存量）

Values（'100028','天山雪莲',456,57）

Savetransactiont_InsertCP--存储点

UpdateCP

Set产品名称='白药'

Where产品编号='208729'--此为一个不存在的编号，目的是使插入操作出错

iferror!

=0--error为系统全局变量，错误号

rollbacktransactiont_InsertCP

else

committransactiont_InsertCP

测试：

使用查询语句查询表CP中数据，观查查询结果，看存储点前的操作结果是否确实得以保存。

给出相应的结果：

2．事务的实验练习：

实验练习：

写一个名为pt_CPXSB的存储过程，含一名为t_InserCPXSB的事务,用于实现向CPXSB表中插入一条数据时，检查“产品编号”字段是否包含有CP表中，“客户编号”是否包含于XSS表中，只要两者之一为否，撒销插入操作，否则，则提交数据。

给出相应的代码：

create proc pt_CPXSB 

产品编号 char（6）,                    

客户编号 char（6）,                 

销售日期 datetime,              

数量 int,                

销售额 float 

as begin 

begin transaction t_InserCPXSB  

insert into CPXSB （产品编号,客户编号,销售日期,数量,销售额）         

values （产品编号,客户编号,销售日期,数量,销售额） 

if （（产品编号  in （select 产品编号 from  CP））and （客户编号  in （select 

客户编号 from  XSS）））  

 begin   

commit transaction t_InserCPXSB           

print'插入一行数据成功'           

select * from CPXSB        

 end  

if （产品编号 not in （select 产品编号 from  CP））         

begin 

print'插入数据中产品编号与CP表中产品编号不一致'           rollback transaction  t_InserCPXSB        

end 

if （客户编号 not in （select 客户编号 from  XSS））          

 begin 

print'插入数据中客户编号与XSS表中客户编号不一致'           rollback transaction  t_InserCPXSB          

end end 

给出测试结果：

15.3.锁与并发控制

15.3.0．SQLServer锁简介

1.查看锁的信息

（1）执行EXECSP_LOCK报告有关锁的信息

（2）查询分析器中按Ctrl+2可以看到锁的信息

2.如何锁定数据库对象

（1）如何锁一个表的某一行（示例）

SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED

SELECT*FROMtableROWLOCKWHEREid=1

（2）锁定数据库的一个表（示例）

SELECT*FROMtableWITH（HOLDLOCK）

3.软件开发中如何尽可能避免死锁

（1）使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；

（2）设置死锁超时参数为合理围，如：

3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；

（3）优化程序，检查并避免死锁现象出现；

（4）对所有的脚本和SP都要仔细测试，在正式版本之前。

（5）所有的SP都要有错误处理（通过error）

（6）一般不要修改SQLSERVER事务的默认级别。

不推荐强行加锁

15.3.1．排它锁

1.新建两个连接：

新建两个用户，并给相应的权限，然后各自登录到数据库中，分别打开查询窗口

2.在第一个连接中执行以下语句

begintran

updateXSS

set客户名称='SM城市广场'

where客户编号='000003'

waitfordelay'00:

00:

50'--等待50秒

committran

3.在第二个连接中执行以下语句

begintran

select*fromXSS

where客户编号='000003'

committran

先执行以上两个语句中的第一个语句，后执行第二个语句，观查执行的结果（主要是执行时间的差异）。

练习：

将以上两个连接的执行顺序调换，观查执行情况。

思考：

为什么会有这样的结果？

其中一个用户对表XSS中000003客户编号加上排它锁，只允许该用户自己读取和修改，知道该用户释放，否则其他用户不能对其数据进行读取和修改。

注：

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待50秒

15.3.2．共享锁

1．在第一个连接中执行以下语句

begintran

select*fromXSSwith（holdlock）--holdlock人为加锁

where客户编号='000003'

waitfordelay'00:

00:

50'--等待50秒

committran

2.在第二个连接中执行以下语句

begintran

select客户编号,地区

fromXSS

where客户名称='SM城市广场'

updateXSS

set客户名称='好又多超市'

where客户编号='000003'

committran

给出执行情况：

练习：

将以上两个连接的执行顺序调换，观查执行情况。

两个连接都要50s，结果相同。

思考：

为什么会有这样的结果？

其中一个用户对表XSS中000003客户编号加上HOLDLOCK，导致其他用户只能对这一组数据读取，不能进行修改。

15.3.3．死锁

1.在第一个连接中执行以下语句

begintran

updateXSS

set客户名称='老虎城'

where客户编号='000002'

waitfordelay'00:

00:

30'

updateCP

set库存量=50

where产品编号='100005'

committran

2.在第二个连接中执行以下语句

begintran

updateCP

set库存量=50

where产品编号='100005'

waitfordelay'00:

00:

10'

updateXSS

set客户名称='老虎城'

where客户编号='000002'

committran

给出执行情况：

练习：

将以上两个连接的执行顺序调换，观查执行情况。

跟交换之前没差别。

思考：

为什么会有这样的结果？

15.4.理解两段锁协议

通过对比各个阶段的execsp_lock，观察写锁和读锁的释放时间。

理解二段式锁（两段锁）的工作原理。

完成以下实验，思考为什么会有那样的实验结果。

（1）实验场景

新建两个连接：

使用前面新建的两个用户，各自登录到数据库中，分别打开查询窗口，在两个查询分析窗口中分别执行以下操作。

连接1：

中执行以下代码：

begintran

select*fromCPwith（UPDLOCK）where产品编号='100003'

连接2：

中执行以下代码：

select*fromCPwhere产品编号='100003'

updateCPset库存量=库存量+100where产品编号='100003'

select*fromCPwhere产品编号='100003'

（2）查看阻塞情况

a.通过查看第一个连接的锁定情况：

execsp_lock

请给出执行结果：

b.打开

文件夹:

2000版本“当前活动”-“锁/进程ID”

2005版本选择

给出观查结果：

15.5.事务应用案例

在数据库中创建两个表，账户信息表（bank）存放账户的信息，交易信息表（transInfo）存放每次的交易信息。

试用事务解决银行转账问题。

如下图:

阅读以下程序段，领会其处理思想：

BEGINTRANSACTION

/*--定义变量，用于累计事务执行过程中的错误--*/

DECLAREerrorSumINT

SETerrorSum=0--初始化为0，即无错误

/*--转帐：

三的少1000元，四的多1000元*/

UPDATEbankSETcurrentMoney=currentMoney-1000

WHEREcustomerName='三'

SETerrorSum=errorSum+error

UPDATEbankSETcurrentMoney=currentMoney+1000

WHEREcustomerName='四'

SETerrorSum=errorSum+error--累计是否有错误

IFerrorSum<>0--如果有错误

BEGIN

print'交易失败，回滚事务'

ROLLBACKTRANSACTION

END

ELSE

BEGIN

print'交易成功，提交事务，写入硬盘，永久的保存'

COMMITTRANSACTION

END

GO

print'查看转账事务后的余额'

SELECT*FROMbank

GO

测试：

（1）测试转账1000时的转账情况

给出测试结果：

（2）测试转账800时的转账情况

给出测试结果：