《（本科）03_项目三_实现事务和锁ppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）03_项目三_实现事务和锁ppt课件.pptx（77页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、课程主讲人：03_项目三_实现事务和锁项目三 实现事务和锁大数据时代下SQL Server项目实战中等职业教育“十三五”规划课程改革创新教材中高职贯通计算机网络技术专业系列教材中国铁道出版社 编著：刘编著：刘 丹丹 PPT制作：顾洪：顾洪 任务一任务一 实现实现基本的事务管理基本的事务管理任务二任务二 实现实现TSQLTSQL中的事务中的事务任务三任务三 实现实现死锁的控制死锁的控制内容导航一、任务描述二、任务分析三、任务实施四、任务小结五、相关知识与技能六、任务拓展任务四任务四 实现实现事务进阶管理事务进阶管理任务五任务五 实现实现事务高级管理事务高级管理项目综合实项目综合实训训 实现实现家
2、庭管理系统中家庭管理系统中的的 事务事务管理管理4 一、任务描述上海御恒信息科技公司接到客户的一份订单，要求实现基本的事务管理。公司刚招聘了一名程序员小张，软件开发部经理要求他尽快熟悉事务的基本管理，小张按照经理的要求开始做以下的任务分析。5 二、任务分析1、设计如何提高网络性能。2、创建一张表进行测试。3、开启隐式事务。4、在执行DML时触发隐式事务。5、提交事务。6 三、任务实施第一步：SQL Server 是否遵从 SQL-92 规则及是否返回计数，从而提高网络性能。SET QUOTED_IDENTIFIER OFFGOSET NOCOUNT OFFGO7 第二步：创建一张测试表。CRE
3、ATE TABLE ImplicitTran(Cola int PRIMARY KEY,Colb char(3) NOT NULL)GO5/13/20228 第三步：设置隐式事务开启。SET IMPLICIT_TRANSACTIONS ONGO5/13/20229 第四步：自动启动一个隐式事务。-BEGIN TRANSACTION/*第一次执行Insert语句的时候将自动启动一个隐式事务*/INSERT INTO ImplicitTran VALUES(1,aaa)GOINSERT INTO ImplicitTran VALUES(2,bbb)GO5/13/202210 第五步：提交第一个事务
4、。/*提交第一个事务*/COMMIT TRANSACTIONGO5/13/202211 第六步：自动启动第二个隐式事务。/*执行SELECT语句将启动第二个隐式事务*/SELECT COUNT(*) FROM ImplicitTranGOINSERT INTO ImplicitTran VALUES(3,ccc)GOSELECT * FROM ImplicitTranGO5/13/202212 第七步：提交第二个隐式事务。/*提交第二个隐式事务*/COMMIT TRANSACTIONGO5/13/202213 第八步：设置隐式事务关闭。SET IMPLICIT_TRANSACTIONS OFF
5、GO5/13/202214 四、任务小结1、使用SET QUOTED_IDENTIFIER与 SET NOCOUNT来提高网络性能。 2、用CREATE TABLE来创建测试表。3、用SET IMPLICIT_TRANSACTIONS来开启和关闭隐式事务。4、用INSERT INTO来自动启动隐式事务。5、用COMMIT TRANSACTION来提交事务。15 五、相关知识与技能 1、事务2、调整事务隔离级别3、SET TRANSACTION ISOLATION LEVEL 16 六、任务拓展1、请问如何启动隐式事务？2、请问如何触发隐式事务？3、请问如何开始和提交事务？任务一任务一 实现实现
6、基本的事务管理基本的事务管理任务二任务二 实现实现TSQLTSQL中的事务中的事务任务三任务三 实现实现死锁的控制死锁的控制内容导航一、任务描述二、任务分析三、任务实施四、任务小结五、相关知识与技能六、任务拓展任务四任务四 实现实现事务进阶管理事务进阶管理任务五任务五 实现实现事务高级管理事务高级管理项目综合实项目综合实训训 实现实现家庭管理系统中家庭管理系统中的的 事务事务管理管理18 一、任务描述上海御恒信息科技公司接到客户的一份订单，要求用实现TSQL中的事务。公司刚招聘了一名程序员小张，软件开发部经理要求他尽快熟悉TSQL中的事务，小张按照经理的要求开始做以下的任务分析。19 二、任务
7、分析1、设计显示事务来更新表中数据。2、设计隐式事务在表中插入记录并提交。3、设计在隐式事务中进行回滚测试。4、设计在隐式事务中保存事务点并回滚。5、设计用显示事务来查询和更新信息。6、设计在显示事务中用全局变量来控制事务回滚。20 三、任务实施第一步：通过显示事务来更新titles表中的信息。BEGIN TRANSACTIONUPDATE titlesSET price=20.00WHERE title_id=TC7777BEGIN TRANSACTIONUPDATE titlesSET type=potboilerWHERE title_id=TC7777COMMIT TRANSACTIO
8、NCOMMIT TRANSACTION21 三、任务实施第二步：用隐式事务在表中插入记录。create table A (col1 int,col2 char(1)set implicit_Transactions oninsert into A values(1,A)insert into A values(2,B)select count(*) from ACOMMIT TRANSACTIONINSERT INTO A VALUES (3,C)SELECT COUNT(*) FROM A-ROLLBACK TRANSACTIONCOMMIT TRANSACTIONSET IMPLICIT_
9、TRANSACTIONS OFF22 三、任务实施第三步：在隐式事务中用回滚来测试记录是否插入。/*set implicit_Transactions oncreate table A (col1 int,col2 char(1)set implicit_Transactions oninsert into A values(1,A)insert into A values(2,B)select count(*) from ACOMMIT TRANSACTIONINSERT INTO A VALUES (3,C)SELECT COUNT(*) FROM AROLLBACK TRANSACTIO
10、NCOMMIT TRANSACTIONSET IMPLICIT_TRANSACTIONS OFF*/23 三、任务实施第四步：在隐式事务中保存事务点并回滚到事务点。DROP TABLE Aset implicit_Transactions oncreate table A (col1 int,col2 char(1)24 三、任务实施insert into A values(1,A)select * from ASAVE TRANSACTION s1insert into A values(2,B)select * from AROLLBACK TRANSACTION s1select * f
11、rom ACOMMIT TRANSACTIONselect * from AINSERT INTO A VALUES (3,C)select * from ACOMMIT TRANSACTIONselect * from ASET IMPLICIT_TRANSACTIONS OFF25 三、任务实施第五步：用显示事务来查询和更新信息。USE NORTHWINDGOBEGIN TRAN Tran2SELECT productid,productnameFROM ProductsUPDATE ProductsSET UnitPrice=UnitPrice+15WHERE CategoryID=1C
12、OMMIT TRAN26 三、任务实施第六步：在显示事务中使用全局变量来控制事务回滚。USE NorthwindGOBEGIN TRAN TRAN3select productid,productnamefrom products where unitprice=16update productsset unitprice=unitprice+14where unitprice=1627 三、任务实施if rowcount0beginselect unitprice from products where unitprice=30print 事务回滚rollback tranendselect
13、 unitprice from products where unitprice=30COMMIT TRAN28 四、任务小结1、在BEGIN TRANSACTION和COMMIT TRANSACTION之间使用显示事务。2、使用set implicit_Transactions on来打开隐式事务。3、使用ROLLBACK TRANSACTION可以回滚事务。4、保存事务的格式为：SAVE TRANSACTION 事务点名称。5、用全局变量rowcount来控制回滚。29 五、相关知识与技能1、事务恢复 2、回滚个别事务 3、ROLLBACK TRANSACTION 4、ROLLBACK 对
14、游标的影响由三个规则定义 5、COMMIT TRANSACTION 6、事务示例30 六、任务拓展1、请问如何在显示和隐式事务之间切换？2、请问如何保存事务点并回滚到事务点？3、全局变量rowcount的作用是什么？任务一任务一 实现实现基本的事务管理基本的事务管理任务二任务二 实现实现TSQLTSQL中的事务中的事务任务三任务三 实现实现死锁的控制死锁的控制内容导航一、任务描述二、任务分析三、任务实施四、任务小结五、相关知识与技能六、任务拓展任务四任务四 实现实现事务进阶管理事务进阶管理任务五任务五 实现实现事务高级管理事务高级管理项目综合实项目综合实训训 实现实现家庭管理系统中家庭管理系统
15、中的的 事务事务管理管理32 一、任务描述上海御恒信息科技公司接到客户的一份订单，要求用能控制死锁。公司刚招聘了一名程序员小张，软件开发部经理要求他尽快熟悉死锁的控制，小张按照经理的要求开始做以下的任务分析。33 二、任务分析1、设计死锁的优先级。2、设计死锁的延迟。3、设计使用LOCK_TIMEOUT 函数来自动重新提交阻塞的语句。34 三、任务实施第一步：控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。语法/*SET DEADLOCK_PRIORITY LOW | NORMAL | deadlock_va
16、r 参数LOW35 三、任务实施指定当前会话为首选死锁牺牲品。Microsoft? SQL Server? 自动回滚死锁牺牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。NORMAL指定会话返回到默认的死锁处理方法。deadlock_var是指定死锁处理方法的字符变量。如果指定 LOW，则 deadlock_var 为 3；如果指定 NORMAL，则 deadlock_var 为 6。注释SET DEADLOCK_PRIORITY 的设置是在执行或运行时设置，而不是在分析时设置。*/36 三、任务实施第二步：SET LOCK_TIMEOUT 允许应用程序设置语句等待阻塞资源的最长
17、时间。当一条语句已等待超过 LOCK_TIMEOUT所设置的时间，则被锁住的语句将自动取消，并给应用程序返回一条错误信息。在一个连接的开始，LOCK_TIMEOUT 返回一个 1值。*/SET LOCK_TIMEOUT 1800GO37 三、任务实施第三步：可以自动重新提交阻塞的语句或者回滚整个事务。若要确定当前 LOCK_TIMEOUT 设置，请执行 LOCK_TIMEOUT 函数。DECLARE Timeout intSELECT Timeout = lock_timeoutSELECT TimeoutGO38 四、任务小结1、SET DEADLOCK_PRIORITY可以设置死锁的优先级
18、。2、设置死锁的延迟格式为：SET LOCK_TIMEOUT 毫秒。3、LOCK_TIMEOUT 函数可以确定当前 LOCK_TIMEOUT 设置。39 五、相关知识与技能1、死锁 2、处理死锁 3、检测和结束死锁 4、识别死锁 5、SET DEADLOCK_PRIORITY 6、将死锁减至最少 7、避免事务中的用户交互40 六、任务拓展1、请问如何设置死锁的优先级？2、请问死锁的延迟有何作用？3、请问LOCK_TIMEOUT 函数的作用是什么？任务一任务一 实现实现基本的事务管理基本的事务管理任务二任务二 实现实现TSQLTSQL中的事务中的事务任务三任务三 实现实现死锁的控制死锁的控制内容
19、导航一、任务描述二、任务分析三、任务实施四、任务小结五、相关知识与技能六、任务拓展任务四任务四 实现实现事务进阶管理事务进阶管理任务五任务五 实现实现事务高级管理事务高级管理项目综合实项目综合实训训 实现实现家庭管理系统中家庭管理系统中的的 事务事务管理管理42 一、任务描述上海御恒信息科技公司接到客户的一份订单，要求用实现事务的进阶管理。公司刚招聘了一名程序员小张，软件开发部经理要求他尽快熟悉如何提升事务的管理级别，小张按照经理的要求开始做以下的任务分析。43 二、任务分析1、使用显示事务从表中删除当天的记录。2、在显示事务中使用子查询。3、在多语句事务中设计回滚。44 三、任务实施第一步：
20、编写一个显示事务，从Enquiry表中删除今天的记录。create database CenterManagementgouse CenterManagementgoif exists (select * from sysobjects where id=object_id(Ndbo.Batch) and OBJECTPROPERTY(id,NIsUserTable)=1)drop table dbo.Batchgoif exists (select * from sysobjects where id=object_id(Ndbo.Enquiry) and OBJECTPROPERTY(id
21、,NIsUserTable)=1)drop table dbo.Enquirygo45 create table dbo.Batch(BatchNo int null,DateStarted datetime null,NoOfStudentsEnrolled int null,MinimumNumberOfStudents int null,MaximumNumberOFStudents int null,CourseCode int null,BatchTimings varchar(15) null,BatchOver bit)on primarygoinsert into Batch
22、values (1,2001-10-08 00:00:00,8,5,20,1,11 am to 1 pm,0)insert into Batch values (2,2001-12-12 00:00:00,19,8,30,2,7 am to 9 am,0)insert into Batch values (3,2001-12-12 00:00:00,20,5,20,1,7 pm to 9 pm,0)insert into Batch values (4,2001-12-13 00:00:00,9,5,30,3,7 pm to 9 pm,1)insert into Batch values (5
23、,2001-10-15 00:00:00,8,7,30,5,3 pm to 5 pm,0)insert into Batch values (6,2001-10-13 00:00:00,7,4,30,4,5 pm to 7 pm,1)insert into Batch values (7,2001-10-14 00:00:00,4,5,30,3,9 am to 11 am,0)insert into Batch values (8,2002-01-15 00:00:00,10,5,20,1,7 am to 9 am,0)46 三、任务实施print 表 Batch 已创建create tabl
24、e Enquiry(EnquiryNo int null,EName varchar(20) null,EDate datetime null,UserId varchar(9) null,CourseCode int null,Qualification varchar(20) null,Address varchar(100) null,EmailId varchar(20) null,ContactNo bigint null,Opinion varchar(100) null,PreferredDateOfFollowUp datetime null,Status varchar(20
25、) null)on primary47 insert into Enquiry values (3,Amie,2001-11-10 00:00:00,5,2,Degree,Dallas,8787887,OK,2002-01-04 00:00:00,Thinking)insert into Enquiry values (2,ajaj,2002-02-01 00:00:00,4,1,Degree,djdjdjdj,83833,2002-12-01 00:00:00,)insert into Enquiry values (5,richard,2001-12-12 00:00:00,6,3,BSc
26、,jgjgg,aa,222222,2002-01-04 00:00:00,)insert into Enquiry values (6,pamela,2001-11-10 00:00:00,3,2,10+2,xdfsf,aws,1211,OK,2002-01-04 00:00:00,)print 表 Enquiry 已创建begin transactiondelete enquiry where edate=getdate()commit transaction48 三、任务实施第二步：编写一个显示事务，完成下列任务： a: 从Batch表中删除课程代码没有包含在Course表中的批次所对应的
27、记录。 b: 显示Batch表的所有记录。 c: 显示Enquiry表的所有记录。Begin Transactiondelete from batch where batchNo not in (select coursecode from enquiry)select * from batchselect * from enquiryCommit Transaction49 三、任务实施第三步：写一个多语句事务的示例，创建两张表，分别为两张表的某一列更新，更新时无影响行数时自动回滚并显示提示“发生错误，未更新任何行”。CREATE TABLE table_a(X smallint null,
28、y smallint null)GOCREATE TABLE table_b(X smallint null)GOBEGIN TRANUPDATE table_aSET X=X+1WHERE Y=100UPDATE table_bSET X=X+1If rowcount=0 or error!=0BEGINROLLBACK TRANPRINT 发生错误，未更新任何行RETURNENDCOMMIT TRAN50 四、任务小结1、在显示事务中使用delete 表名 where 列名=getdate()，需要Begin Trans与Commit Trans。2、在显示事务中可以用NOT IN来连接子
29、查询。3、rowcount和error可以用来设计回滚事务。51 五、相关知识与技能1、控制事务 2、指定事务边界 3、事务处理过程中的错误 4、显式事务 5、隐性事务 6、事务恢复 7、回滚个别事务 8、启动时恢复所有未完成的事务52 六、任务拓展1、如何在显示事务中使用系统函数？2、如何在显示事务中使用子查询？3、如何使用rowcount和error来设计回滚事务?任务一任务一 实现实现基本的事务管理基本的事务管理任务二任务二 实现实现TSQLTSQL中的事务中的事务任务三任务三 实现实现死锁的控制死锁的控制内容导航一、任务描述二、任务分析三、任务实施四、任务小结五、相关知识与技能六、任务
30、拓展任务四任务四 实现实现事务进阶管理事务进阶管理任务五任务五 实现实现事务高级管理事务高级管理项目综合实项目综合实训训 实现实现家庭管理系统中家庭管理系统中的的 事务事务管理管理54 一、任务描述上海御恒信息科技公司接到客户的一份订单，要求实现事务的高级管理。公司刚招聘了一名程序员小张，软件开发部经理要求他尽快熟悉事务的高级管理操作，小张按照经理的要求开始做以下的任务分析。55 二、任务分析1、在显示事务中设计使用局部变量。2、复制一张新表并在其中插入一条记录。3、保存事务点并设计回滚。4、设计使用rowcount来进行事务回滚。56 三、任务实施第一步：通过显示事务实现对登录表中名为adm
31、in登录名的删除（要求用局部变量为该事务命名）。DECLARE TranName VARCHAR(20)SELECT TranName = MyRobertTransBEGIN TRANSACTION TranNameUSE RobotMgrSelect * from usersDELETE FROM users WHERE u_name=adminSelect * from usersCOMMIT TRANSACTION TranName57 第二步：为robert复制一张新表为robertbak,并在新表中写入一条记录。use FamilyMgrgoselect * from robert
32、gosp_help robertgoselect *into robertbakfrom robertgoselect * from robertbakgosp_help robertbakgoinsert into robertbak(r_id,r_name,r_type,r_birth,r_speed)values(r00005,T4000,science,2009-09-10,460)go58 三、任务实施第三步：在robert中插入一条语句，如发生错误则回滚,否则显示插入语句正常。use robertmgrgoselect * from robertgoBEGIN TRAN mytra
33、nselect * from robertsave transaction mypointinsert into robert(r_id,r_name,r_type,r_birth,r_speed,r_price)values(r00006,T3000,home,2009-13-04,280,6000)59 三、任务实施IF rowcount=0 or error!=0BEGINROLLBACK TRAN mypointPRINT 插入语句发生错误，未插入任何行RETURNENDElseBEGINPRINT 插入语句正常select * from robertENDCOMMIT TRAN my
34、transelect * from robertgo60 三、任务实施第四步：为item表设置事务，修改价格并进行事务回滚。USE mydbGOBEGIN TRAN TRAN1select i_id,i_name,i_pricefrom item where i_price=10.4update itemset i_price=i_price+4.6where i_price=10.4if rowcount0beginselect i_id,i_name,i_price from item where i_price=15print 事务回滚rollback tranendselect i_i
35、d,i_name,i_pricefrom item where i_price=10.4COMMIT TRAN61 四、任务小结1、rowcount和error可以用来设计回滚事务。2、select.into可以复制一张新表。3、save transaction 事务点要与rollback transaction 事务点相结合。4、rowcount的结果可以作为判断回滚的依据。62 五、相关知识与技能1、并发构架 2、指定并发控制类型 3、并发问题 4、丢失更新 5、未确认的相关性（脏读） 6、不一致的分析（非重复读） 7、幻像读 8、游标并发 9、READ_ONLY 10、OPTIMISTI
36、C WITH VALUES 11、OPTIMISTIC WITH ROW VERSIONING 12、SCROLL LOCKS 13、滚动锁63 六、任务拓展1、请问如何设计显示事务？2、请问保存的事务点有什么用处？3、请问如何将全局变量和局部变量结合起来设计事务的回滚？任务一任务一 实现实现基本的事务管理基本的事务管理任务二任务二 实现实现TSQLTSQL中的事务中的事务任务三任务三 实现实现死锁的控制死锁的控制内容导航一、项目描述二、项目分析三、项目实施四、项目小结五、项目实训评价表任务四任务四 实现实现事务进阶管理事务进阶管理任务五任务五 实现实现事务高级管理事务高级管理项目综合实项目综
37、合实训训 实现实现家庭管理系统中家庭管理系统中的的 事务事务管理管理65 一、项目描述上海御恒信息科技公司接到一个订单，需要用事务来管理家庭管理系统中相应表格的DML操作。并使用显示与隐式事务，程序员小张根据以上要求进行相关的事务设计后，按照项目经理的要求开始做以下的任务分析。66 二、项目分析1、用隐式事务实现对表familyout的查询。2、在familyout中实现错误。3、通过局部变量设计FamilyUser表的显示事务。4、用隐式事务实现对表familyout的查询。5、在familyout中设置事务保存点和回滚点。67 三、项目实施第一步：用隐式事务实现对表familyout的查询
38、,如果支出金额为零则回滚事务，否则按支出类别分类小计总支出。use FamilyMgrgoset implicit_transactions on-BEGIN TRAN peter_transelect o_money from familyout where o_money=0if rowcount0beginprint 事务回滚rollback tran peter_tranendelseselect * from familyoutorder by o_kindcompute sum(o_money) by o_kindCOMMIT TRAN peter_transet implicit
39、_transactions off68 三、项目实施第二步：在familyout中插入一条语句，如发生错误则回滚,否则显示插入语句正常。use familymgrgoselect * from familyoutgoBEGIN TRAN mymoneyselect * from familyoutsave transaction mypointinsert into familyout(o_date,o_name,o_money,o_kind)values(2008-8-30,buy apple,25.5,0,extend)69 三、项目实施IF rowcount=0 or error!=0B
40、EGINROLLBACK TRAN mypointPRINT 插入语句发生错误，未插入任何行RETURNENDElseBEGINPRINT 插入语句正常ENDCOMMIT TRAN mymoneyselect * from familyoutgo70 三、项目实施第三步：通过显示事务实现对FamilyUser表中名为admin登录名的删除（要求用局部变量为该事务命名）。DECLARE TranName VARCHAR(20)SELECT TranName = MyFamilyTransBEGIN TRANSACTION TranNameUSE FamilyMgrSelect * from Fa
41、milyUserDELETE FROM FamilyUser WHERE u_name=adminSelect * from FamilyUserCOMMIT TRANSACTION TranName71 三、项目实施第四步：用隐式事务实现对表familyout的查询,如果支出金额为零则回滚事务，否则按支出类别分类小计总支出。use FamilyMgrgoset implicit_transactions on-BEGIN TRAN peter_transelect o_money from familyout where o_money=0if rowcount0beginprint 事务回
42、滚rollback tran peter_tranendelseselect * from familyoutorder by o_kindcompute sum(o_money) by o_kindCOMMIT TRAN peter_transet implicit_transactions off72 三、项目实施第五步：在familyout中设置事务保存点和回滚点。use familymgrgoselect * from familyoutgodelete from familyout where o_id=o00007goBEGIN TRAN mymoneyselect * from
43、familyoutsave transaction mypointinsert into familyout(o_id,o_date,o_name,o_money,o_kind)values(o00007,2008-8-30,buy apple,25.5,extend)73 三、项目实施IF rowcount=0 or error!=0BEGINROLLBACK TRAN mypointPRINT 插入语句发生错误，未插入任何行RETURNENDElseBEGINPRINT 插入语句正常select * from familyoutENDCOMMIT TRAN mymoneyselect *
44、from familyoutGo74 四、项目小结1、首先设计好使用显示还是隐式事务。2、其次考虑好可能出现的错误，并保存回滚点。3、第三要设计好回滚语句。4、最后要提交整个事务。75 项目实训评价表项目三 实现事务和锁 内容评价 学习目标评价项目321 职业能力 实现事务和锁任务一、实现基本的事务管理 任务二、实现TSQL中的事务 任务三、实现死锁的控制 任务四、实现事务进阶管理 任务五、实现事务高级管理 通用能力动手能力 解决问题能力 综合评价 76 以上表格根据国家职业技能标准相关内容设定评价等级说明表等级说明3能高质、高效地完成此学习目标的全部内容，并能解决遇到的特殊问题2能高质、高效地完成此学习目标的全部内容1能圆满完成此学习目标的全部内容，不需任何帮助和指导77 77Thank you!