实验15事务与并发控制

实验十五事务与并发控制【实验目的与要求】1. 掌握数据库事务的概念2. 熟悉数据库的四个特性3. 熟练掌握数据库事务的实现方法【实验内容与步骤】15.1. SQL Server数据库事务基础知识1事务的概念( Transaction )所谓事务是用户定义的一个数据库操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。关系数据库中，事务可以是一条SQL语句、一组SQL语句。在SQL语言中，定义事务的语句有三条： Begin Transaction 开始 Commit 结束 Rollback 回滚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_variable：是用户定义的、含有有效事务名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。WITH MARK description：指定在日志中标记事务。Description 是描述该标记的字符串。如果使用了 WITH MARK，则必须指定事务名。WITH MARK 允许将事务日志还原到命名标记。4事务提交：COMMIT TRANSACTION标志一个成功的隐性事务或用户定义事务的结束。如果 TRANCOUNT 为 1，COMMIT TRANSACTION 使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放连接占用的资源，并将 TRANCOUNT 减少到 0。如果 TRANCOUNT 大于 1，则 COMMIT TRANSACTION 使 TRANCOUNT 按 1 递减。语法结构：COMMIT TRAN SACTION transaction_name | tran_name_variable 参数说明：transaction_name：Microsoft SQL Serve 忽略该参数。transaction_name 指定由前面的 BEGIN TRANSACTION 指派的事务名称。transaction_name 必须遵循标识符的规则，但只使用事务名称的前 32 个字符。通过向程序员指明 COMMIT TRANSACTION 与哪些嵌套的 BEGIN TRANSACTION 相关联，transaction_name 可作为帮助阅读的一种方法。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。5事务回滚：ROLLBACK TRANSACTION将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法结构：ROLLBACK 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、varchar、nchar 或 nvarchar 数据类型声明该变量。savepoint_name：是来自 SAVE TRANSACTION 语句的 savepoint_name。savepoint_name 必须符合标识符规则。当条件回滚只影响事务的一部分时使用 savepoint_name。savepoint_variable：是用户定义的、含有有效保存点名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。15.2. SQL Server数据库事务创建1事务的创建（1）在查询分析器中执行以下语句，创建一个名为t_InsUpdate简单的事务，并使它正常提交。Begin transaction t_InsUpdate -t_InsUpdate为事务名Use CPXSInsert into CP(产品编号,产品名称,价格,库存量)Values(100021,宝马汽车,456780,39)Update XSSSet 负责人=张飞Where 客户编号=000003Commit transaction t_InsUpdate -事务提交结束，t_InsUpdate为事务名测试： 执行语句“select * from xss; ”，看数据是否添加到表中？请给出测试结果：（2）在查询分析器中执行以下语句，创建一个简单的事务，并使它回滚BEGIN TRANSACTION Use CPXSselect * from xss; update xss set 客户名称=厦门理工学院 where 客户编号=000002; select * from xss;Rollback;测试：select * from xss;请给出测试结果：思考：比较两条查询语句的结果差异，为什么会有这样的差异？ 执行了事务后，rollback相当于取消了事务，事务没有生效。2事务的存储点：事务的存储点可以使事务在发生回滚的情况下，存储点前的操作结果得以保存。执行以下语句，创建一个名为t_InsertCP，其中包含一个存储点。Begin transaction t_InsertCP-Use CPXSInsert into CP(产品编号,产品名称,价格,库存量)Values(100028,天山雪莲,456,57)Save transaction t_InsertCP -存储点Update CPSet 产品名称=云南白药Where 产品编号=208729 -此为一个不存在的编号，目的是使插入操作出错if error!=0 -error为系统全局变量，错误号rollback transaction t_InsertCPelsecommit transaction t_InsertCP测试：使用查询语句查询表CP中数据，观查查询结果，看存储点前的操作结果是否确实得以保存。给出相应的结果：2事务的实验练习：实验练习：写一个名为pt_CPXSB的存储过程，含一名为t_InserCPXSB的事务,用于实现向CPXSB表中插入一条数据时，检查“产品编号”字段是否包含有CP表中，“客户编号”是否包含于XSS表中，只要两者之一为否，撒销插入操作，否则，则提交数据。给出相应的代码：createprocpt_CPXSB产品编号char(6),客户编号char(6),销售日期datetime,数量int,销售额floatasbeginbegintransactiont_InserCPXSBinsertintoCPXSB(产品编号,客户编号,销售日期,数量,销售额)values(产品编号,客户编号,销售日期,数量,销售额)if(产品编号in(select产品编号fromCP)and(客户编号in(select客户编号fromXSS)begincommittransactiont_InserCPXSBprint插入一行数据成功select*fromCPXSBendif(产品编号notin(select产品编号fromCP)beginprint插入数据中产品编号与CP表中产品编号不一致 rollbacktransactiont_InserCPXSBendif(客户编号notin(select客户编号fromXSS)beginprint插入数据中客户编号与XSS表中客户编号不一致 rollbacktransactiont_InserCPXSBendend给出测试结果：15.3. 锁与并发控制15.3.0SQL Server锁简介1. 查看锁的信息 (1) 执行 EXEC SP_LOCK 报告有关锁的信息 (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） 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂； （3） 优化程序，检查并避免死锁现象出现； （4）对所有的脚本和SP都要仔细测试，在正式版本之前。 （5） 所有的SP都要有错误处理（通过error） （6） 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁 15.3.1排它锁1.新建两个连接：新建两个用户，并给相应的权限，然后各自登录到数据库中，分别打开查询窗口 2.在第一个连接中执行以下语句 begin tran update XSS set 客户名称=SM城市广场 where 客户编号=000003 waitfor delay 00:00:50 -等待50秒 commit tran3.在第二个连接中执行以下语句 begin tran select * from XSS where 客户编号=000003 commit tran先执行以上两个语句中的第一个语句，后执行第二个语句，观查执行的结果（主要是执行时间的差异）。练习：将以上两个连接的执行顺序调换，观查执行情况。思考：为什么会有这样的结果？ 其中一个用户对表XSS中000003客户编号加上排它锁，只允许该用户自己读取和修改，知道该用户释放，否则其他用户不能对其数据进行读取和修改。 注：若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待50秒 15.3.2共享锁1在第一个连接中执行以下语句 begin tran select * from XSS with(holdlock) -holdlock人为加锁 where 客户编号=000003 waitfor delay 00:00:50 -等待50秒 commit tran2.在第二个连接中执行以下语句 begin tran select 客户编号,地区 from XSS where 客户名称=SM城市广场 update XSS set 客户名称=好又多超市 where 客户编号=000003commit tran给出执行情况：练习：将以上两个连接的执行顺序调换，观查执行情况。 两个连接都要50s，结果相同。思考：为什么会有这样的结果？ 其中一个用户对表XSS中000003客户编号加上HOLDLOCK，导致其他用户只能对这一组数据读取，不能进行修改。 15.3.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 产品编号=100005waitfor delay 00:00:10 update XSS set 客户名称= 中山老虎城where客户编号=000002 commit tran 给出执行情况：练习：将以上两个连接的执行顺序调换，观查执行情况。 跟交换之前没差别。 思考：为什么会有这样的结果？15.4. 理解两段锁协议通过对比各个阶段的exec sp_lock，观察写锁和读锁的释放时间。理解二段式锁（两段锁）的工作原理。完成以下实验，思考为什么会有那样的实验结果。（1）实验场景新建两个连接：使用前面新建的两个用户，各自登录到数据库中，分别打开查询窗口，在两个查询分析窗口中分别执行以下操作。 连接1：中执行以下代码：begin transelect * from CP with (UPDLOCK) where 产品编号=100003连接2：中执行以下代码：select * from CP where 产品编号=100003update CP set 库存量 = 库存量 + 100 where 产品编号=100003select * from CP where 产品编号=100003（2）查看阻塞情况a. 通过查看第一个连接的锁定情况：exec sp_lock请给出执行结果：b. 打开文件夹:2000版本“当前活动”-“锁/进程ID”2005版本选择给出观查结果：15.5. 事务应用案例在数据库中创建两个表，账户信息表(bank)存放账户的信息，交易信息表(transInfo)存放每次的交易信息。试用事务解决银行转账问题。如下图:阅读以下程序段，领会其处理思想：BEGIN TRANSACTION /*-定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT SET errorSum=0 -初始化为0，即无错误/*-转帐：张三的帐户少1000元，李四的帐户多1000元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三SET errorSum=errorSum+errorUPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四SET errorSum=errorSum+error -累计是否有错误IF errorSum0 -如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存 COMMIT TRANSACTION ENDGOprint 查看转账事务后的余额SELECT * FROM bank GO 测试：(1) 测试转账1000时的转账情况给出测试结果：(2) 测试转账800时的转账情况给出测试结果：