实验17事务与并发控制.pdf

《实验17事务与并发控制.pdf》由会员分享，可在线阅读，更多相关《实验17事务与并发控制.pdf（22页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、百度文库-好好学习，天天向上-1 实验十七 事务与并发控制 姓名：学号：专业：班级：同组人：无 实验日期：2009-12-31 【实验目的与要求】1.掌握数据库事务的概念 2.熟悉数据库的四个特性 3.熟练掌握数据库事务的实现方法【实验内容与步骤】SQL Server 数据库事务基础知识 1事务的概念(Transaction)所谓事务是用户定义的一个数据库操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。关系数据库中，事务可以是一条 SQL 语句、一组 SQL 语句。在 SQL 语言中，定义事务的语句有三条：Begin Transaction 开始 Commit 结束 Roll

2、back 回滚 2事务开始：BEGIN TRANSACTION 标记一个显式本地事务的起始点。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

3、_variable：是用户定义的、含有有效事务名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。百度文库-好好学习，天天向上-2 WITH MARK description：指定在日志中标记事务。Description 是描述该标记的字符串。如果使用了 WITH MARK，则必须指定事务名。WITH MARK 允许将事务日志还原到命名标记。4事务提交：COMMIT TRANSACTION 标志一个成功的隐性事务或用户定义事务的结束。如果 TRANCOUNT 为 1，COMMIT TRANSACTION 使得自从事务开始以来所执行的所有数据

4、修改成为数据库的永久部分，释放连接占用的资源，并将 TRANCOUNT 减少到 0。如果 TRANCOUNT 大于 1，则 COMMIT TRANSACTION 使 TRANCOUNT 按 1 递减。语法结构：COMMIT TRAN SACTION transaction_name|tran_name_variable 参数说明：transaction_name：Microsoft SQL Server 忽略该参数。transaction_name 指定由前面的 BEGIN TRANSACTION 指派的事务名称。transaction_name 必须遵循标识符的规则，但只使用事务名称的前 3

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

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

7、SAVE TRANSACTION 语 句 的 savepoint_name。savepoint_name 必须符合标识符规则。当条件回滚只影响事务的一部分时使用 savepoint_name。savepoint_variable：是用户定义的、含有有效保存点名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。SQL Server 数据库事务基础知识 1事务的创建（1）在查询分析器中执行以下语句，创建一个名为 t_InsUpdate 简单的事务，并使它正常提交。Begin transaction t_InsUpdate -t_InsUpdat

8、e 为事务名 Use CPXS 百度文库-好好学习，天天向上-3 Insert into CP(产品编号,产品名称,价格,库存量)Values(100021,宝马汽车,456780,39)Update XSS Set 负责人=张飞 Where 客户编号=000003 Commit transaction t_InsUpdate （2）在查询分析器中执行以下语句，创建一个简单的事务，并使它回滚 给出运行结果：百度文库-好好学习，天天向上-4 思考：比较两条查询语句的结果差异，为什么会有这样的差异？答：使用 commit 语句查询时，会将数据的更改保存在磁盘上的物理数据库中，而用 rollback

9、语句查询时，并没有将改动的数据保存。因为 commit 表示提交，即提交事物的所以操作，就是将事物中所有对数据库的更新写回到磁盘上的物理数据库中去，事物正常结束。而 rollback 表示回滚，即在事物运行的过程中发生了某种故障，事物不能继续执行，系统将对事物中对数据库的所以已完成的操作全部撤销，回滚到事物开始的状态。2事务的存储点：事务的存储点可以使事务在发生回滚的情况下，存储点前的操作结果得以保存。执行以下语句，创建一个名为 t_InsertCP，其中包括一个存储点。Begin transaction t_InsertCP -Use CPXS Insert into CP(产品编号,产品名

10、称,价格,库存量)Values(100028,天山雪莲,456,57)百度文库-好好学习，天天向上-5 Save transaction t_InsertCP Update CP Set 产品名称=云南白药 Where 产品编号=208729-此为一个不存在的编号，目的是使插入操作出错 if error!=0 rollback transaction t_InsertCP else commit transaction t_InsertCP 测试：使用查询语句查询表 CP 中数据，观查查询结果，看存储点前的操作结果是否确实得以保存。给出相应的结果：2事务的实验练习：实验练习：写一个名为 pt_

11、CPXSB 的存储过程，含一名为 t_InserCPXSB 的事务,用于实现向 CPXSB 表中插入数据时，检查“产品编号”字段是否包含有 CP 表中，“客户编百度文库-好好学习，天天向上-6 号”是否包含于 XSS 表中，只要两者之一为否，撒销插入操作，否则，则提交数据。给出相应的代码：create proc pt_CPXSB 产品编号 char(6),客户编号 char(6),销售日期 datetime,数量 int,销售额 float as begin begin transaction t_InserCPXSB insert into CPXSB(产品编号,客户编号,销售日期,数量,销

12、售额)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

13、客户编号 from XSS)begin print插入数据中客户编号与 XSS 表中客户编号不一致 rollback transaction t_InserCPXSB end end 原有表中数据：百度文库-好好学习，天天向上-7 给出测试结果：百度文库-好好学习，天天向上-8 锁与并发控制 17.2.0SQL Server 锁简介 1.查看锁的信息 (1)执行 EXEC SP_LOCK 报告有关锁的信息 (2)查询分析器中按 Ctrl+2 可以看到锁的信息 2.如何锁定数据库对象 (1)如何锁一个表的某一行(示例)百度文库-好好学习，天天向上-9 SET TRANSACTION ISOLAT

14、ION LEVEL READ UNCOMMITTED SELECT*FROM table ROWLOCK WHERE id=1 (2)锁定数据库的一个表(示例)SELECT*FROM table WITH(HOLDLOCK)3.软件开发中如何尽可能避免死锁 （1）使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；（2）设置死锁超时参数为合理范围，如：3 分钟-10 分种；超过时间，自动放弃本次操作，避免进程悬挂；（3）优化程序，检查并避免死锁现象出现；（4）对所有的脚本和 SP 都要仔细测试，在正是版本之前。（5）所有的 SP 都要有错误处理（通过error）（6）一般不要修改 SQ

15、L SERVER 事务的默认级别。不推荐强行加锁 17.2.1排它锁 1.新建两个连接：新建两个用户，并给相应的权限，然后各自登录到数据库中，分别打开查询窗口 2.在第一个连接中执行以下语句 begin tran update XSS set 客户名称=SM 城市广场 where 客户编号=000003 waitfor delay 00:00:50-等待 50 秒 commit tran 3.在第二个连接中执行以下语句 begin tran select*from XSS where 客户编号=000003 commit tran 先执行以上两个语句中的第一个语句，后执行第二个语句，观查执行的

16、结果（主要是执行时间的差异）。百度文库-好好学习，天天向上-10 练习：将以上两个连接的执行顺序调换，观查执行情况。思考：为什么会有这样的结果。答：其中一个用户对表 XS 中客户编号为000003的数据所在元组加上了排它锁，只允许该用户自己读取和修改，直到该用户释放，否则其它用户均不能对其数据进行读取和修改。注：若同时执行上述两个语句，则 select 查询必须等待 update 执行完毕才能执行即要等待 30 秒 17.2.2共享锁 1在第一个连接中执行以下语句 begin tran 百度文库-好好学习，天天向上-11 select*from XSS with(holdlock)-holdl

17、ock 人为加锁 where 客户编号=000003 waitfor delay 00:00:50-等待 50 秒 commit tran 2.在第二个连接中执行以下语句 begin tran select 客户编号,地区 from XSS where 客户名称=SM 城市广场 update XSS set 客户名称=好又多超市 where 客户编号=000003 commit tran 给出执行情况：练习：将以上两个连接的执行顺序调换，观查执行情况。百度文库-好好学习，天天向上-12 思考：为什么会有这样的结果？答：其中一个用户对 XSS 中 客户编号为000003数据所在元组加上 HOLD

18、LOCK，即加上了共享锁，导致其它用户只能对这一组数据读取，不能进行修改。17.2.3死锁 1.在第一个连接中执行以下语句 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 客户名称=

19、中山老虎城 where 客户编号=000002 commit tran 百度文库-好好学习，天天向上-13 给出执行情况：练习：将以上两个连接的执行顺序调换，观查执行情况。百度文库-好好学习，天天向上-14 思考：为什么会有这样的结果？答：以上事务执行过程中产生了死锁，DBMS 解除了死锁。理解两段锁协议 通过对比各个阶段的 exec sp_lock，观察写锁和读锁的释放时间。理解二段式锁（两段锁）的工作原理。完成以下实验，思考为什么会有那样的实验结果。（1）实验场景 新建两个连接 连接 1 执行以下代码：百度文库-好好学习，天天向上-15 begin tran select*from CP

20、with(UPDLOCK)where 产品编号=100003 连接 2 中执行以下代码：select*from CP where 产品编号=100003 update CP set 库存量=库存量+100 where 产品编号=100003 select*from CP where 产品编号=100003 （2）查看阻塞情况 a.通过查看第一个连接的 exec sp_lock b.打开文件夹:2000 版本“当前活动”-“锁/进程 ID”2005 版本选择 给出观查结果：百度文库-好好学习，天天向上-16 实验练习：新建两个连接（利用现成的亦可）（1）连接 1 执行以下代码：begin tra

21、n select*from CP with(UPDLOCK)where 产品编号=100003（2）连接 2 中执行以下代码：select*from CP where 产品编号=100003 update CP set 库存量=库存量+100 where 产品编号=100003 select*from CP where 产品编号=100003 通过查看第一个连接的 exec sp_lock，给出观查结果：百度文库-好好学习，天天向上-17 事务应用案例 在数据库中创建两个表，账户信息表(bank)存放账户的信息，交易信息表(transInfo)存放每次的交易信息。试用事务解决银行转账问题 百度

22、文库-好好学习，天天向上-18 阅读以下程序段，领会其处理思想：BEGIN TRANSACTION /*-定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT SET errorSum=0 -初始化为 0，即无错误/*-转帐：张三的帐户少 1000 元，李四的帐户多 1000 元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 SET errorSum=errorSum+error UPDATE bank SET currentMoney=currentMoney+100

23、0 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 试演示：分别转账 1000 和 800 时的转账情况 建表：USE CPXS GO SET NOCOUNT ON-创建帐户信息表 bank 和

24、交易信息表 transInfo IF EXISTS(SELECT*FROM sysobjects WHERE name=bank)DROP TABLE bank 百度文库-好好学习，天天向上-19 IF EXISTS(SELECT*FROM sysobjects WHERE name=transInfo)DROP TABLE transInfo GO CREATE TABLE bank-帐户信息表(customerName CHAR(8)NOT NULL,-顾客姓名 cardID CHAR(10)NOT NULL,-卡号 currentMoney MONEY NOT NULL -当前余额)GO

25、 CREATE TABLE transInfo-交易信息表(cardID CHAR(10)NOT NULL,-卡号 transType CHAR(4)NOT NULL,-交易类型（存入/支取）transMoney MONEY NOT NULL,-交易金额 transDate DATETIME NOT NULL,-交易日期)GO/*-添加约束：帐户余额不能少于 1 元，交易日期默认为当天日期-*/ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=0)ALTER TABLE transInfo ADD CONSTR

26、AINT DF_transDate DEFAULT(getDate()FOR transDate,CONSTRAINT CK_transType CHECK(transType IN(存入,支取)GO/*-插入测试数据：张三开户，开户金额为 1000；李四开户，开户金额 1-*/INSERT INTO bank(customerName,cardID,currentMoney)VALUES(张三,1001 0001,1000)-张三的卡号假定为 1001 0001 INSERT INTO bank(customerName,cardID,currentMoney)VALUES(李四,1001

27、0002,1)-李四的卡号假定为 1001 0002 SELECT*FROM bank GO 转账 800：BEGIN TRANSACTION /*-定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT,myMoney Money SET myMoney=800 -转帐金额假定为 800 元 百度文库-好好学习，天天向上-20 SET errorSum=0-初始化为 0，即无错误 /*-转帐：张三转帐 800 元到李四的帐号上。现实中的转帐依靠卡号，即张三的卡号支出 800 元，李四的卡号存入 800 元*/-张三的卡号支取 800，并保存交易信息 INSERT

28、 INTO transInfo(cardID,transType,transMoney)-保存交易信息 VALUES(1001 0001,支取,myMoney)SET errorSum=errorSum+ERROR-累计是否有错误 UPDATE bank SET currentMoney=currentMoney-myMoney -更新帐户余额 WHERE cardID=1001 0001 SET errorSum=errorSum+ERROR-累计是否有错误 -李四的卡号存入 1000 元，并保存交易信息 INSERT INTO transInfo(cardID,transType,tran

29、sMoney)-保存交易信息 VALUES(1001 0002,存入,myMoney)SET errorSum=errorSum+ERROR-累计是否有错误 UPDATE bank SET currentMoney=currentMoney+myMoney -更新帐户余额 WHERE cardID=1001 0002 SET errorSum=errorSum+ERROR-累计是否有错误 /*-根据是否有错误，确定事务是提交还是撤销-*/IF errorSum0-如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN pr

30、int 交易成功，提交事务，写入硬盘永久的保存 COMMIT TRANSACTION END GO print-转帐事务结束后的余额和交易信息-SELECT*FROM bank SELECT*FROM transInfo GO 百度文库-好好学习，天天向上-21 当前状态下继续转账：转账 1000：BEGIN TRANSACTION /*-定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT,myMoney Money SET myMoney=1000 -转帐金额假定为 1000 元 SET errorSum=0-初始化为 0，即无错误-张三的卡号支取 1000 元，并保存交易信息 INSERT INTO transInfo(cardID,transType,transMoney)-保存交易信息 VALUES(1001 0001,支取,myMoney)SET errorSum=errorSum+ERROR-累计是否有错误 UPDATE bank SET currentMoney=currentMoney-myMoney -更新帐户余额 百度文库-好好学习，天天向上-23