实验15事务与并发控制

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

2、 TRANSACTION 将 TRANCOUNT 力口 1。语法结构：BEGIN TRAN SACTION tra nsaction_ name | tran_n ame_variableWITH MARK 'descriptio n'参数说明：transaction_name：是给事务分配的名称。 transaction_name必须遵循标识符规则，但是 不允许标识符多于 32个字符。仅在嵌套的 BEGIN.COMMIT 或 BEGIN.ROLLBACK 语句的最外语句对上使用事务名。tran_n ame_variable:是用户定义的、含有有效事务名称的变量的名称。必须用c

3、har、varchar、nchar或 nvarchar数据类型声明该变量。WITH MARK 'description':指定在日志中标记事务。Description是描述该标记的字符串。如果使用了 WITH MARK，则必须指定事务名。 WITH MARK 允许将事务日志还原到 命名标记。4. 事务提交：COMMIT TRANSACTION标志一个成功的隐性事务或用户定义事务的结束。如果TRANCOUNT 为1, COMMITTRANSACTION 使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放连接占用的资源，并将TRANCOUNT 减少到 0。如果 TR

4、ANCOUNT 大于1，则COMMIT TRANSACTION 使 TRANCOUNT 按 1 递减。语法结构：COMMIT TRAN SACTION tra nsaction_ name | tran_name_variable 参数说明：transaction_name： Microsoft SQL Serve 忽略该参数。 transaction_name 指定由前面的 BEGIN TRANSACTION 指派的事务名称。transaction_name必须遵循标识符的规则，但只 使用事务名称的前32个字符。通过向程序员指明COMMIT TRANSACTION 与哪些嵌套的BEGIN T

5、RANSACTION相关联，transaction_name可作为帮助阅读的一种方法。tran_n ame_variable:是用户定义的、含有有效事务名称的变量的名称。必须用char、varchar、nchar或 nvarchar数据类型声明该变量。5 事务回滚：ROLLBACK TRANSACTION将显式事务或隐性事务回滚到事务的起点或事务的某个保存点。语法结构：ROLLBACK TRAN SACTION tra nsactio n_n ame | tran_n ame_variable| savepo int_n ame | savepo in t_variable 参数说明：tran

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

7、ame必须符合标识符规则。当条件回滚只影响事务的一部分时使用 savepo int_n ame。savepoint_variable :是用户定义的、含有有效保存点名称的变量的名称。必须用char、varchar、nchar或 nvarchar数据类型声明该变量。15.2. SQL Server数据库事务创建1 事务的创建（1）在查询分析器中执行以下语句，创建一个名为t_lnsUpdate简单的事务，并使它正常提交。Begin transaction t_lnsUpdate -t_lnsUpdate 为事务名Use CPXSInsert into CP（产品编号，产品名称，价格，库存量）Val

8、ues（'100021','宝马汽车',456780,39）Update XSSSet负责人='飞'Where 客户编号='000003'Commit transaction t_lnsUpdate -事务提交结束，t_InsUpdate 为事务名 测试：执行语句"select * from xss; ”，看数据是否添加到表中？请给出测试结果：CS-FC.CriS . . ” mnerFl,三生1* 摘要Segin匸lansacticu t_In書U匸id赴二色 -匸_工11啟1口已呂匕总为事実宅Insert into

9、fP (产品编芍产品名称价格$库存量Values ' 100021r f宝马料车 1 f &67SD, 33;Update XSS沁负责人“张飞， ere客户编号=WO0003Ccrnmit transaction L_In3Jpda.匚亡口结果U消息客户辭号客户名騎it医负贵人1j OOMOI厂电公司铜江11111111210ODOT2寡电女司李四77777777730WXM3货运泊祠福建张飞33J422114南京666666665OODQ05厂电普司iES1111111226DODM7中国建设银行NULL車世民6543627370D0WS东方航空g司上海NULLHULL&#

10、163;MWCO南总周瑜345672568(2) 在查询分析器中执行以下语句，创建一个简单的事务，并使它回滚BEGIN TRANSACTIONUse CPXS select * from xss;update xssset客户名称='理工学院'where 客户编号='000002'select * from xss;Rollback;测试：select * from xss;请给出测试结果：结果_J消息U #'U .01 IS占匕 “'J1fflWDOl广电公司馆江张三1111111122无煽季四力7阳"特3黄廷省司福建去飞334422

11、114南京赵六5DDCDO5厂电益司乘三111111122G000007中国建设NU.李世t543C2737(xxhooa东方航空上琴NULLNULL3000009南京周瑜34M725B客户慵粤客尸名称负贵.J电话1(XKK)O1广电吿司菌江张三1111111122原门理工学瞬无錫李四m?7?7773DW003貸运益司福建张飞134422114OfXHXM小家电面逐南用趨亢&666S66665广电公司张三1111111226(HXHX17中国建设帳厅NU.李世.&54362707东方航空舍司上海NULLMULL3家具公司甫京周晞W5672563思考：比较两条查询语句的结果差异，

12、为什么会有这样的差异？执行了事务后，rollback相当于取消了事务，事务没有生效。2 事务的存储点：事务的存储点可以使事务在发生回滚的情况下，存储点前的操作结果得以保存。执行以下语句，创建一个名为t_ln sertCP，其中包含一个存储点。Begi n tran sact ion t_l nsertCP-Use CPXSInsert into CP（产品编号，产品名称，价格，库存量）Values（'100028','天山雪莲',456,57）Save transaction t_I nsertCP -存储点Update CPSet产品名称='白药Whe

13、re产品编号='208729'-此为一个不存在的编号，目的是使插入操作出错if error!=0 -error为系统全局变量，错误号 rollback tran sact ion t_ln sertCPelsecommit transaction t InsertCP测试：使用查询语句查询表 CP中数据，观查查询结果，看存储点前的操作结果是否确实得以保存。给出相应的结果:C5-PC. CPT£ . . . LQneiryl. sql* 3Hn曲工匸iiiTO CF产品錮号产品名称”价格库存量Values ( *1C002S * J 天山雪莲 ” ,456.57Save

14、 tiansaction reinsert匚pUpdate CP业七产品名称云南白药，Wliexe 产品编号=72怦'1£ 低UHEOH *0iclltficX transaction t_InserrCPelsecoirnut txsaction 匸 InseitCFZ3结異_j消息产品扁号产品名称价榴库存量1D221055爱雪VCD232£120?1TO001影电3QK1DS100002洗衣机1800204KJWOS冰箱18001251000061肛牢调IBM5E1000071.5匹空调2400207iodods2肛宇调39M6&100011MP3135

15、0W91MJ015TCLH寸彩37M211Q100016东风小汽车$5.9111DD021宝马肓车45.3913天血雪莲45657131MXM2数码相机350022 事务的实验练习：实验练习：写一个名为pt_CPXSB的存储过程，含一名为t_lnserCPXSB的事务,用于实现向CPXSB表中插入一条数据时，检查产品编号”字段是否包含有 CP表中，客 户编号”是否包含于XSS表中，只要两者之一为否，撒销插入操作，否则，则提交数据。 给出相应的代码：create proc pt_CPXSB产品编号char（6）,客户编号char（6）,销售日期datetime,数量int,销售额floatas

16、beg inbegi n tran saction t_In serCPXSBinsert into CPXSB （产品编号，客户编号，销售日期，数量，销售额） values （产品编号，客户编号，销售日期，数量，销售额）if （产品编号in （select产品编号from CP）and （客户编号in （select 客户编号from XSS）begincommit tran sacti on t_ln serCPXSBprint'插入一行数据成功select * from CPXSBendif （产品编号not in （select产品编号from CP）beginprint

17、9;插入数据中产品编号与 CP表中产品编号不一致'rollback tran saction t_l nserCPXSBendif （客户编号not in （select客户编号from XSS）beginprint'插入数据中客户编号与 XSS 表中客户编号不一致'rollback tran saction t_l nserCPXSBend end给出测试结果：15.3. 锁与并发控制1530. SQL Server 锁简介1. 查看锁的信息（1）执行EXEC SP_LOCK报告有关锁的信息a.勢I为消息spiddbidObjldIn.Ty.Resource '

18、;.lodeStartus1800DBsGRANT2531n.0TAEISGRANTnUE w00DBsj RANT查询分析器中按Ctrl+2可以看到锁的信息. 为结杲'j消息spiddbidObjldIil.Ty .ModeStatus1S00DSsGRAU?1 Ml25111115151D130TABISGRAN3533C-0DEsGRAtJT454500DSsGRAM?2. 如何锁定数据库对象（1）如何锁一个表的某一行（示例）SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM table ROWLOCK WH

19、ERE id = 1（2）锁定数据库的一个表（示例）SELECT * FROM table WITH （HOLDLOCK）3软件开发中如何尽可能避免死锁（1）使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；（2） 设置死锁超时参数为合理围，如：3分钟-10分种；超过时间，自动放弃本次 操作，避免进程悬挂；（3）优化程序，检查并避免死锁现象出现；（4 ）对所有的脚本和 SP都要仔细测试，在正式版本之前。（5）所有的SP都要有错误处理（通过 error）（6）一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁1531. 排它锁1新建两个连接：新建两个用户，并给相应的权限，然

20、后各自登录到数据库中，分别打开查询窗口2在第一个连接中执行以下语句beg in tranupdate XSSset客户名称='SM城市广场where 客户编号='000003'waitfor delay '00:00:50'-等待 50 秒commit tran3在第二个连接中执行以下语句beg in transelect * from XSSwhere 客户编号='000003'commit tran先执行以上两个语句中的第一个语句，后执行第二个语句，观查执行的结果（主要 是执行时间的差异）。4行受影响1包査询己成功执行 , "

21、; _CS-FC 0 0 HTH) 7eKtA (53) fPIS 00:00:50(|练习：将以上两个连接的执行顺序调换，观查执行情况。二I錯果、消烏客尸濡号客户名秫trtix负责.电话1! :HXK阳？城市广场福連張飞33442211曲叠询已成功执行-(9.0Testk C53)CPUS 00:00:00看“行受參响)申萱谊已成助瓠行CS-PC 0 RTUJ Tewtl崩)CPXS 00:00.50* 行思考：为什么会有这样的结果？其中一个用户对表 XSS中000003客户编号加上排它锁，只允许该用户自己读取 和修改，知道该用户释放，否则其他用户不能对其数据进行读取和修改。注：若同时执行上

22、述两个语句，则select查询必须等待update执行完毕才能执行即 要等待50秒1532. 共享锁1 .在第一个连接中执行以下语句beg in transelect * from XSS with(holdlock) -holdlock 人为加锁where 客户编号='000003'waitfor delay '00:00:50'-等待 50 秒commit tran2在第二个连接中执行以下语句beg in transelect客户编号，地区from XSSwhere客户名称='SM城市广场update XSSset客户名称='好又多超市

23、9;where 客户编号='000003'commit tran给出执行情况:3结票屉消息I卷尸編号I客尸名称地区负责.电话1 i OXW03 j 城市广场上緯33333333刃查C5-PC .0 RTM) CS-PCMom(52)丈 p档 00:00:501曰结果园消區二 客戶编号地区I i aid ai aaaiM bei10DD003 i 上得wib "CS-PCg o MMCS-PCM (52)tpsts 00:00:001练习：将以上两个连接的执行顺序调换，观查执行情况。两个连接都要50s，结果相同。思考：为什么会有这样的结果？其中一个用户对表 XSS中00

24、0003客户编号加上 HOLDLOCK，导致其他用户只 能对这一组数据读取，不能进行修改。1533. 死锁1. 在第一个连接中执行以下语句begi n tranupdate XSSset客户名称='老虎城where 客户编号='000002'waitfor delay '00:00:30'update CPset 库存量=50where 产品编号='100005'commit tran2. 在第二个连接中执行以下语句begi n tranupdate CPset库存量=50where 产品编号='100005' waitf

25、or delay '00:00:10' update XSSset客户名称='老虎城where 客户编号='000002' commit tran给出执行情况：U5-rL.514 LS-rL.tegxn tranupda匸亡 XSS匸客户名称手，中山老虎城where 客户编号 h000002' waitfor delay 、0匚：二口：包。upda.t生 CPaec库存量=50wriere 产品編号=,100005' comrni匸 tran |百甬息（1行受家响）（o行受影响）C5-FC CPIS . . .Lj3u*ry6. sqI*

26、 CS-PC. CTIS . . L begin rranjpSate CPM昌匕阵存量'SO产品编号-'00005 1wai.tf or delay 1 C 1 j :匚 3 :10 1m客户名称，中山老虎城*where 客户輪号 =* 000002 rcommit 匸ran练习：将以上两个连接的执行顺序调换，观查执行情况。跟交换之前没差别。思考：为什么会有这样的结果?154理解两段锁协议通过对比各个阶段的 exec sp_lock，观察写锁和读锁的释放时间。理解二段式锁（两段 锁）的工作原理。完成以下实验，思考为什么会有那样的实验结果。（1）实验场景新建两个连接：使用前面新

27、建的两个用户，各自登录到数据库中，分别打开查询窗口， 在两个查询分析窗口中分别执行以下操作。连接1：中执行以下代码：begi n transelect * from CP with (UPDLOCK) where产品编号='100003'连接2:中执行以下代码:select * from CP where产品编号='100003'update CP set库存量=库存量 + 100 where产品编号='100003' select * from CP where 产品编号='100003'(2)查看阻塞情况a.通过查看第一个连接

28、的锁定情况：exec spock请给出执行结果： spsd dbidObjldInT>peResourceModeStatus1j52i 700D6SGRANT25273095761410P幅1:109IUGRANT3527309576141aRID1:109:1UWAIT4527309576141aTABIXGRAM554309576141aTABIXGRANT6$4309576141aPAS1:109IUGRANT7541QaMP&<1:Q:0)Sch-SGRAM8M70aDBSGRAM954309576141aRID1:109:1UGRANT1055500DBSGRA

29、NT115670QDBsGRAM125E11115151.0TABISGRAMb.打开创菩理文件夹：2000版本 当前活动”-锁/进程ID2005版本选择珑活动监观器给出观查结果：Xt；7畢T瑁供SB于现.申|3cs-rc'TA亍1 嘴 piRiCQCOHAJTDHi <rQL Si-TW'rt-Studi?命152CE-KMwCJHS2Uk <Tt 9Q L S« I"W«-Mi'l f mt S<-U.dj O-111saaw苦0MAITI血匸HDIk占M Ml洌L 卯胖甘込fd：ML SludjAacmll44V<B£1灯ahi肪mmUtcT-oiioi i 58 L Si xr-n- 皿-Q迹辭舌FT AWWMnynSTfiiRipsrSSirrlltfilllf9AAH】科氐AW屯 StrTtr0J)56cs-rcMw0A3TAIII&：- CCJRAHDIk 41応 l S L S*-rwr Uaukg4SVt S tlldhO-MQ乩|詩=ilo-rwE1<1 p：筍监dJkTAiTIfc 氏50创SG-Kl2SELECT TBWU i-ct q>£1 SQ L S«-rrrr nt