实验15 事务与并发控制.docx
- 文档编号:28520950
- 上传时间:2023-07-18
- 格式:DOCX
- 页数:16
- 大小:219.64KB
实验15 事务与并发控制.docx
《实验15 事务与并发控制.docx》由会员分享,可在线阅读,更多相关《实验15 事务与并发控制.docx(16页珍藏版)》请在冰豆网上搜索。
实验15事务与并发控制
实验十五 事务与并发控制
姓名:
学号:
专业:
班级:
同组人:
无
实验日期:
2012-6-2
【实验目的与要求】
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;
请给出测试结果:
思考:
比较两条查询语句的结果差异,为什么会有这样的差异?
Roback将回到第一个select执行update后的结果
2.事务的存储点:
事务的存储点可以使事务在发生回滚的情况下,存储点前的操作结果得以保存。
执行以下语句,创建一个名为t_InsertCP,其中包含一个存储点。
Begintransactiont_InsertCP
--UseCPXS
InsertintoCP(产品编号,产品名称,价格,库存量)
Values('100028','天山雪莲',456,57)
Savetransactiont_InsertCP--存储点
UpdateCP
Set产品名称='云南白药'
Where产品编号='208729'--此为一个不存在的编号,目的是使插入操作出错
if@@error!
=0--@@error为系统全局变量,错误号
rollbacktransactiont_InsertCP
else
committransactiont_InsertCP
测试:
使用查询语句查询表CP中数据,观查查询结果,看存储点前的操作结果是否确实得以保存。
给出相应的结果:
2.事务的实验练习:
实验练习:
写一个名为pt_CPXSB的存储过程,含一名为t_InserCPXSB的事务,用于实现向CPXSB表中插入一条数据时,检查“产品编号”字段是否包含有CP表中,“客户编号”是否包含于XSS表中,只要两者之一为否,撒销插入操作,否则,则提交数据。
给出相应的代码:
usecpxs
go
createprocedurept2_CPXSB
as
begintransactiont_InserCPXSB
InsertintoCPXSB(产品编号,客户编号,销售日期,数量,销售额)
Values('1000078','0000038','2004-3-180:
00:
00',14,2800)
if@@error!
=0
rollbacktransactiont_InsertCPXSB
else
committransactiont_InsertCPXSB
select*fromCPXSB
给出测试结果:
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
先执行以上两个语句中的第一个语句,后执行第二个语句,观查执行的结果(主要是执行时间的差异)。
练习:
将以上两个连接的执行顺序调换,观查执行情况。
调换执行顺序后,原第一个连接仍然需要等待50秒,原第二个连接不需要等待,执行的结果没有差别。
思考:
为什么会有这样的结果。
若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。
注:
若同时执行上述两个语句,则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
给出执行情况:
练习:
将以上两个连接的执行顺序调换,观查执行情况。
两个连接都需要等待50秒后才能执行查询。
执行结果一样。
思考:
为什么会有这样的结果?
假设事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。
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
/*--定义变量,用于累计事务执行过程中的错误--*/
DECLARE@errorSumINT
SET@errorSum=0--初始化为0,即无错误
/*--转帐:
张三的帐户少1000元,李四的帐户多1000元*/
UPDATEbankSETcurrentMoney=currentMoney-1000
WHEREcustomerName='张三'
SET@errorSum=@errorSum+@@error
UPDATEbankSETcurrentMoney=currentMoney+1000
WHEREcustomerName='李四'
SET@errorSum=@errorSum+@@error--累计是否有错误
IF@errorSum<>0--如果有错误
BEGIN
print'交易失败,回滚事务'
ROLLBACKTRANSACTION
END
ELSE
BEGIN
print'交易成功,提交事务,写入硬盘,永久的保存'
COMMITTRANSACTION
END
GO
print'查看转账事务后的余额'
SELECT*FROMbank
GO
测试:
(1)测试转账1000时的转账情况
给出测试结果:
(2)测试转账800时的转账情况
给出测试结果:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验15 事务与并发控制 实验 15 事务 并发 控制