还剩13页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
实验十五 事务与并发控制【实验目的与要求】
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[WITH__RK[description]]]参数说明transaction_name是给事务分配的名称transaction_name必须遵循标识符规则,但是不允许标识符多于32个字符仅在嵌套的BEGIN...COMMIT或BEGIN...ROLLBACK语句的最外语句对上使用事务名@tran_name_variable是用户定义的、含有有效事务名称的变量的名称必须用char、varchar、nchar或nvarchar数据类型声明该变量WITH__RK[description]指定在日志中标记事务Description是描述该标记的字符串如果使用了WITH__RK,则必须指定事务名WITH__RK允许将事务日志还原到命名标记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|s__epoint_name|@s__epoint_variable]]参数说明transaction_name是给BEGINTRANSACTION上的事务指派的名称transaction_name必须符合标识符规则,但只使用事务名称的前32个字符嵌套事务时,transaction_name必须是来自最远的BEGINTRANSACTION语句的名称@tran_name_variable是用户定义的、含有有效事务名称的变量的名称必须用char、varchar、nchar或nvarchar数据类型声明该变量s__epoint_name是来自S__ETRANSACTION语句的s__epoint_names__epoint_name必须符合标识符规则当条件回滚只影响事务的一部分时使用s__epoint_name@s__epoint_variable是用户定义的、含有有效保存点名称的变量的名称必须用char、varchar、nchar或nvarchar数据类型声明该变量
15.
2.SQLServer数据库事务创建1.事务的创建
(1)在查询分析器中执行以下语句,创建一个名为t_InsUpdate简单的事务,并使它正常提交Begintransactiont_InsUpdate--t_InsUpdate为事务名UseCPXSInsertintoCP产品编号产品名称__库存量Values100021宝马汽车45678039UpdateXSSSet负责人=张飞Where客户编号=000003Committransactiont_InsUpdate--事务提交结束,t_InsUpdate为事务名测试执行语句“select*fromxss;”,看数据是否添加到表中?请给出测试结果
(2)在查询分析器中执行以下语句,创建一个简单的事务,并使它回滚BEGINTRANSACTIONUseCPXSselect*fromxss;updatexssset客户名称=厦门理工学院where客户编号=000002;select*fromxss;Rollback;测试select*fromxss;请给出测试结果思考比较两条查询语句的结果差异,___会有这样的差异?执行了事务后,rollback相当于取消了事务,事务没有生效2.事务的存储点事务的存储点可以使事务在发生回滚的情况下,存储点前的操作结果得以保存执行以下语句,创建一个名为t_InsertCP,其中包含一个存储点Begintransactiont_InsertCP--UseCPXSInsertintoCP产品编号产品名称__库存量Values100028天山雪莲45657S__etransactiont_InsertCP--存储点UpdateCPSet产品名称=云南白药Where产品编号=208729--此为一个不存在的编号,目的是使插入操作出错if@@error!=0--@@error为系统全局变量,错误号rollbacktransactiont_InsertCPelsecommittransactiont_InsertCP测试使用查询语句查询表CP中数据,观查查询结果,看存储点前的操作结果是否确实得以保存给出相应的结果2.事务的实验练习实验练习写一个名为pt_CPX__的存储过程,含一名为t_InserCPX__的事务用于实现向CPX__表中插入一条数据时,检查“产品编号”字段是否包含有CP表中,“客户编号”是否包含于XSS表中,只要两者之一为否,撒销插入操作,否则,则提交数据给出相应的代码create proc pt_CPX__ @产品编号 char6 @客户编号 char6 @销售日期 datetime @数量 int @销售额 float as begin begin transaction t_InserCPX__ insert into CPX__ 产品编号客户编号销售日期数量销售额 values @产品编号@客户编号@销售日期@数量@销售额 if @产品编号 in select 产品编号 from CPand @客户编号 in select 客户编号 from XSS begin commit transaction t_InserCPX__ print插入一行数据成功 select * from CPX__ end if @产品编号 not in select 产品编号 from CP begin print插入数据中产品编号与CP表中产品编号不一致 rollback transaction t_InserCPX__ end if @客户编号 not in select 客户编号 from XSS begin print插入数据中客户编号与XSS表中客户编号不一致 rollback transaction t_InserCPX__ end end 给出测试结果
15.
3.锁与并发控制
15.
3.0.SQLServer锁简介
1.查看锁的信息1执行EXECSP_LOCK报告有关锁的信息2查询分析器中按Ctrl+2可以看到锁的信息
2.如何锁定数据库对象1如何锁一个表的某一行示例SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTEDSELECT*FROMtableROWLOCKWHEREid=12锁定数据库的一个表示例SELECT*FROMtableWITHHOLDLOCK
3.软件__中如何尽可能避免死锁
(1)使用事务时,尽量缩短事务的逻辑处理过程,及早提交或回滚事务;
(2)设置死锁超时参数为合理范围,如3分钟-10分种;超过时间,自动放弃本次操作,避免进程悬挂;
(3)优化程序,检查并避免死锁现象出现;
(4)对所有的脚本和SP都要仔细测试,在正式版本之前
(5)所有的SP都要有错误处理(通过@error)
(6)一般不要修改SQLSERVER事务的默认级别不推荐强行加锁
15.
3.1.排它锁
1.新建两个连接新建两个用户,并给相应的权限,然后各自登录到数据库中,分别打开查询窗口
2.在第一个连接中执行以下语句begintranupdateXSSset客户名称=__城市__where客户编号=000003waitfordelay00:00:50--等待50秒committran
3.在第二个连接中执行以下语句begintranselect*fromXSSwhere客户编号=000003committran先执行以上两个语句中的第一个语句,后执行第二个语句,观查执行的结果(主要是执行时间的差异)练习将以上两个连接的执行顺序调换,观查执行情况思考___会有这样的结果?其中一个用户对表XSS中000003客户编号加上排它锁,只允许该用户自己读取和修改,知道该用户释放,否则其他用户不能对其数据进行读取和修改注若同时执行上述两个语句,则select查询必须等待update执行完毕才能执行即要等待50秒
15.
3.2.共享锁1.在第一个连接中执行以下语句begintranselect*fromXSSwithholdlock--holdlock人为加锁where客户编号=000003waitfordelay00:00:50--等待50秒committran
2.在第二个连接中执行以下语句begintranselect客户编号地区fromXSSwhere客户名称=__城市__updateXSSset客户名称=好又多__where客户编号=000003committran给出执行情况练习将以上两个连接的执行顺序调换,观查执行情况两个连接都要50s,结果相同思考___会有这样的结果?其中一个用户对表XSS中000003客户编号加上HOLDLOCK,导致其他用户只能对这一组数据读取,不能进行修改
15.
3.3.死锁
1.在第一个连接中执行以下语句begintranupdateXSSset客户名称=中山老虎城where客户编号=000002waitfordelay00:00:30updateCPset库存量=50where产品编号=_____5committran
2.在第二个连接中执行以下语句begintranupdateCPset库存量=50where产品编号=_____5waitfordelay00:00:10updateXSSset客户名称=中山老虎城where客户编号=000002committran给出执行情况练习将以上两个连接的执行顺序调换,观查执行情况跟交换之前没差别思考___会有这样的结果?
15.
4.理解两段锁协议通过对比各个阶段的execsp_lock,观察写锁和读锁的释放时间理解二段式锁(两段锁)的工作原理完成以下实验,思考___会有那样的实验结果
(1)实验场景新建两个连接使用前面新建的两个用户,各自登录到数据库中,分别打开查询窗口,在两个查询分析窗口中分别执行以下操作连接1中执行以下代码begintranselect*fromCPwithUPDLOCKwhere产品编号=_____3连接2中执行以下代码select*fromCPwhere产品编号=_____3updateCPset库存量=库存量+100where产品编号=_____3select*fromCPwhere产品编号=_____3
(2)查看阻塞情况a.通过查看第一个连接的锁定情况execsp_lock请给出执行结果b.打开文件夹:2000版本“当前活动”-“锁/进程ID”2005版本选择给出观查结果
15.
5.事务应用案例在数据库中创建两个表,账户信息表bank存放账户的信息,交易信息表transInfo存放每次的交易信息试用事务解决银行转账问题如下图:阅读以下程序段,领会其处理思想BEGINTRANSACTION/*--定义变量,用于累计事务执行过程中的错误--*/DECLARE@errorSumINTSET@errorSum=0--初始化为0,即无错误/*--转帐张三的帐户少1000元,李四的帐户多1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三SET@errorSum=@errorSum+@@errorUPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四SET@errorSum=@errorSum+@@error--累计是否有错误IF@errorSum0--如果有错误BEGINprint交易失败,回滚事务ROLLBACKTRANSACTIONENDELSEBEGINprint交易成功,提交事务,写入硬盘,永久的保存COMMITTRANSACTIONENDGOprint查看转账事务后的余额SELECT*FROMbankGO测试1测试转账1000时的转账情况给出测试结果2测试转账800时的转账情况给出测试结果。