2022年实验事务与并发控制归纳 .pdf

《2022年实验事务与并发控制归纳 .pdf》由会员分享，可在线阅读，更多相关《2022年实验事务与并发控制归纳 .pdf（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第 1 页 共 15 页实验十五事务与并发控制【实验目的与要求】1.掌握数据库事务的概念2.熟悉数据库的四个特性3.熟练掌握数据库事务的实现方法【实验内容与步骤】15.1.SQL Server 数据库事务基础知识1事务的概念 ( Transaction ) 所谓事务是用户定义的一个数据库操作序列，这些操作要么都做，要么都不做， 是一个不可分割的工作单位。关系数据库中，事务可以是一条SQL 语句、一组SQL 语句。在 SQL 语言中，定义事务的语句有三条：Begin Transaction 开始Commit 结束Rollback 回滚2事务开始： BEGIN TRANSACTION 标记一个显式

2、本地事务的起始点。BEGIN TRANSACTION将 TRANCOUNT 加 1。语法结构：BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 参数说明：transaction_name：是给事务分配的名称。transaction_name 必须遵循标识符规则，但是不允许标识符多于32 个字符。仅在嵌套的BEGIN.COMMIT 或BEGIN.ROLLBACK 语句的最外语句对上使用事务名。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。必须用char、

3、varchar、nchar 或 nvarchar 数据类型声明该变量。WITH MARK description：指定在日志中标记事务。Description 是描述该标记的字符串。如果使用了WITH MARK，则必须指定事务名。WITH MARK 允许将事务日志还原到命名标记。4事务提交：COMMIT TRANSACTION 标志一个成功的隐性事务或用户定义事务的结束。如果TRANCOUNT 为1，COMMIT TRANSACTION 使得自从事务开始以来所执行的所有数据修改成为数据库的永名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - -

4、 - - 名师精心整理 - - - - - - - 第 1 页，共 15 页 - - - - - - - - - 第 2 页 共 15 页久部分， 释放连接占用的资源，并将 TRANCOUNT 减少到0。如果TRANCOUNT 大于1，则 COMMIT TRANSACTION 使 TRANCOUNT 按 1 递减。语法结构：COMMIT TRAN SACTION transaction_name | tran_name_variable 参数说明：transaction_name： Microsoft SQL Serve 忽略该参数。transaction_name 指定由前面的BEGIN T

5、RANSACTION 指派的事务名称。transaction_name 必须遵循标识符的规则，但只使用事务名称的前32 个字符。通过向程序员指明COMMIT TRANSACTION 与哪些嵌套的 BEGIN TRANSACTION 相关联， transaction_name 可作为帮助阅读的一种方法。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。必须用char、varchar、nchar 或 nvarchar 数据类型声明该变量。5事务回滚：ROLLBACK TRANSACTION 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法结构：ROLL

6、BACK TRAN SACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 参数说明：transaction_name： 是给 BEGIN TRANSACTION 上的事务指派的名称。transaction_name 必须符合标识符规则，但只使用事务名称的前32 个字符。嵌套事务时， transaction_name 必须是来自最远的BEGIN TRANSACTION 语句的名称。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。必须用char、var

7、char、nchar 或 nvarchar 数据类型声明该变量。savepoint_name ： 是 来 自SAVE TRANSACTION 语 句 的savepoint_name 。savepoint_name 必 须 符 合 标 识 符 规 则 。 当 条 件 回 滚 只 影 响 事 务 的 一 部 分 时 使 用savepoint_name。savepoint_variable：是用户定义的、 含有有效保存点名称的变量的名称。必须用char、varchar、nchar 或 nvarchar 数据类型声明该变量。15.2.SQL Server 数据库事务创建1事务的创建（1）在查询分析器中

8、执行以下语句，创建一个名为t_InsUpdate 简单的事务，并使它正常提交。Begin transaction t_InsUpdate -t_InsUpdate 为事务名Use CPXS Insert into CP( 产品编号 ,产品名称 ,价格,库存量 ) Values(100021,宝马汽车 ,456780,39) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 15 页 - - - - - - - - - 第 3 页 共 15 页Update XSS Set 负

9、责人 =张飞 Where 客户编号 =000003 Commit transaction t_InsUpdate -事务提交结束，t_InsUpdate 为事务名测试：执行语句“ select * from xss; ” ，看数据是否添加到表中？请给出测试结果：（2）在查询分析器中执行以下语句，创建一个简单的事务，并使它回滚BEGIN TRANSACTION Use CPXS select * from xss; update xss set 客户名称 =厦门理工学院 where 客户编号 =000002; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - -

10、- - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 15 页 - - - - - - - - - 第 4 页 共 15 页select * from xss; Rollback; 测试：select * from xss; 请给出测试结果：思考： 比较两条查询语句的结果差异，为什么会有这样的差异？执行了事务后，rollback 相当于取消了事务，事务没有生效。2事务的存储点：事务的存储点可以使事务在发生回滚的情况下，存储点前的操作结果得以保存。执行以下语句，创建一个名为t_InsertCP，其中包含一个存储点。Begin transaction t_Inser

11、tCP -Use CPXS Insert into CP( 产品编号 ,产品名称 ,价格 ,库存量 ) Values(100028,天山雪莲 ,456,57) Save transaction t_InsertCP -存储点Update CP Set 产品名称 =云南白药 Where 产品编号 =208729 - 此为一个不存在的编号，目的是使插入操作出错if error!=0 -error 为系统全局变量，错误号rollback transaction t_InsertCP else commit transaction t_InsertCP 名师资料总结 - - -精品资料欢迎下载 - -

12、 - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 15 页 - - - - - - - - - 第 5 页 共 15 页测试：使用查询语句查询表CP 中数据，观查查询结果，看存储点前的操作结果是否确实得以保存。给出相应的结果：2事务的实验练习：实验练习： 写一个名为pt_CPXSB 的存储过程，含一名为t_InserCPXSB 的事务 ,用于实现向 CPXSB 表中插入一条数据时，检查“ 产品编号 ” 字段是否包含有CP 表中， “ 客名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - -

13、- - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 15 页 - - - - - - - - - 第 6 页 共 15 页户编号 ” 是否包含于XSS 表中，只要两者之一为否，撒销插入操作， 否则，则提交数据。给出相应的代码：create proc pt_CPXSB 产品编号 char(6), 客户编号 char(6), 销售日期 datetime, 数量 int, 销售额 float as begin begin transaction t_InserCPXSB insert into CPXSB ( 产品编号 ,客户编号 ,销售日期 ,数量 ,销售额 )

14、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_Inse

15、rCPXSB end if ( 客户编号 not in (select 客户编号 from XSS) begin print 插 入 数 据 中 客 户 编 号 与XSS表 中 客 户 编 号 不 一 致 rollback transaction t_InserCPXSB end end 给出测试结果：15.3.锁与并发控制15.3.0SQL Server 锁简介1. 查看锁的信息(1) 执行EXEC SP_LOCK 报告有关锁的信息名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6

16、 页，共 15 页 - - - - - - - - - 第 7 页 共 15 页(2) 查询分析器中按Ctrl+2 可以看到锁的信息2. 如何锁定数据库对象(1) 如何锁一个表的某一行(示例 ) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM table ROWLOCK WHERE id = 1 (2) 锁定数据库的一个表(示例 ) SELECT * FROM table WITH (HOLDLOCK) 3.软件开发中如何尽可能避免死锁（1） 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；（2） 设置死

17、锁超时参数为合理范围，如：3 分钟 -10 分种；超过时间，自动放弃本次操作，避免进程悬挂；（3） 优化程序，检查并避免死锁现象出现；（4）对所有的脚本和SP都要仔细测试，在正式版本之前。（5） 所有的 SP都要有错误处理（通过error）（6） 一般不要修改SQL SERVER 事务的默认级别。不推荐强行加锁15.3.1排它锁1.新建两个连接：新建两个用户，并给相应的权限，然后各自登录到数据库中，分别打开查询窗口2.在第一个连接中执行以下语句begin tran update XSS set 客户名称 =SM 城市广场 名师资料总结 - - -精品资料欢迎下载 - - - - - - - -

18、 - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 15 页 - - - - - - - - - 第 8 页 共 15 页where 客户编号 =000003 waitfor delay 00:00:50 - 等待 50 秒commit tran 3.在第二个连接中执行以下语句begin tran select * from XSS where 客户编号 =000003 commit tran 先执行以上两个语句中的第一个语句，后执行第二个语句，观查执行的结果（主要是执行时间的差异） 。练习： 将以上两个连接的执行顺序调换，观查执行情况。名师资料

19、总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 15 页 - - - - - - - - - 第 9 页 共 15 页思考： 为什么会有这样的结果？其中一个用户对表XSS 中 000003 客户编号加上排它锁，只允许该用户自己读取和修改，知道该用户释放，否则其他用户不能对其数据进行读取和修改。注：若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待50秒15.3.2共享锁1在第一个连接中执行以下语句begin tran select * from

20、 XSS with(holdlock) -holdlock 人为加锁where 客户编号 =000003 waitfor delay 00:00:50 -等待 50 秒commit tran 2.在第二个连接中执行以下语句begin tran select 客户编号 ,地区名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 15 页 - - - - - - - - - 第 10 页 共 15 页from XSS where 客户名称 =SM 城市广场 update XSS s

21、et 客户名称 =好又多超市 where 客户编号 =000003 commit tran 给出执行情况：练习：将以上两个连接的执行顺序调换，观查执行情况。两个连接都要50s，结果相同。思考：为什么会有这样的结果？其中一个用户对表XSS 中 000003 客户编号加上HOLDLOCK ，导致其他用户只能对这一组数据读取，不能进行修改。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 15 页 - - - - - - - - - 第 11 页 共 15 页15.3.3死锁1

22、.在第一个连接中执行以下语句begin tran update XSS set 客户名称 = 中山老虎城 where 客户编号 =000002 waitfor delay 00:00:30 update CP set 库存量 =50 where 产品编号 =100005 commit tran 2.在第二个连接中执行以下语句begin tran update CP set 库存量 =50 where 产品编号 =100005 waitfor delay 00:00:10 update XSS set 客户名称 = 中山老虎城 where 客户编号 =000002 commit tran 给出执

23、行情况：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 15 页 - - - - - - - - - 第 12 页 共 15 页练习： 将以上两个连接的执行顺序调换，观查执行情况。跟交换之前没差别。思考：为什么会有这样的结果？15.4.理解两段锁协议通过对比各个阶段的exec sp_lock，观察写锁和读锁的释放时间。理解二段式锁（两段锁）的工作原理。完成以下实验，思考为什么会有那样的实验结果。（1）实验场景新建两个连接：使用前面新建的两个用户，各自登录到数据库中，分别

24、打开查询窗口，在两个查询分析窗口中分别执行以下操作。连接 1： 中执行以下代码：begin tran select * from CP with (UPDLOCK) where 产品编号 =100003 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 15 页 - - - - - - - - - 第 13 页 共 15 页连接 2： 中执行以下代码：select * from CP where 产品编号 =100003 update CP set 库存量= 库存量+ 1

25、00 where 产品编号 =100003 select * from CP where 产品编号 =100003 （2）查看阻塞情况a. 通过查看第一个连接的锁定情况：exec sp_lock 请给出执行结果：b. 打开文件夹 : 2000 版本 “ 当前活动 ” -“ 锁/进程 ID ”2005 版本选择给出观查结果：15.5.事务应用案例在数据库中创建两个表，账户信息表(bank) 存放账户的信息，交易信息表(transInfo)名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第

26、13 页，共 15 页 - - - - - - - - - 第 14 页 共 15 页存放每次的交易信息。试用事务解决银行转账问题。如下图: 阅读以下程序段，领会其处理思想：BEGIN TRANSACTION /*- 定义变量，用于累计事务执行过程中的错误-*/ DECLARE errorSum INT SET errorSum=0 -初始化为 0，即无错误/*- 转帐：张三的帐户少1000 元，李四的帐户多1000 元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName= 张三 SET errorSum=er

27、rorSum+error UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName= 李四 SET errorSum=errorSum+error -累计是否有错误IF errorSum0 -如果有错误BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存 COMMIT TRANSACTION END GO print 查看转账事务后的余额 SELECT * FROM bank GO 测试：(1) 测试转账 1000 时的转账情况给出测试结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 15 页 - - - - - - - - - 第 15 页 共 15 页(2)测试转账 800 时的转账情况给出测试结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 15 页 - - - - - - - - -