《SQLServer嵌套事务》由会员分享，可在线阅读，更多相关《SQLServer嵌套事务（31页珍藏版）》请在金锄头文库上搜索。
1、SQLServer嵌套事务林勇桦什么是事务事务要遵循ACID四个属性Atomicity：原子性Consistency：一致性Isolation：隔离性Durability：持久性何谓嵌套事务简单来讲一个事务里面再嵌套一个或多个事务复杂来讲是一个层次结构框架，由一个顶层事务控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务。示例1PRINTTrancountbeforetransaction:+CAST(trancountasvarchar(1)BEGINTRANPRINTAfterfirstBEGINTRAN:+CAST(trancountasvarchar(1)-嵌套事务BEGINTR
2、ANPRINTAftersecondBEGINTRAN:+CAST(trancountasvarchar(1)COMMITTRANPRINTAfterfirstCOMMITTRAN:+CAST(trancountasvarchar(1)COMMITTRANPRINTAftersecondCOMMITTRAN:+CAST(trancountasvarchar(1)结果：Trancountbeforetransaction:0AfterfirstBEGINTRAN:1AftersecondBEGINTRAN:2AfterfirstCOMMITTRAN:1AftersecondCOMMITTRAN:
3、0示例1结果说明什么可以看到每一个BEGINTRAN语句都会使TRANCOUNT增加1；每一个COMMITTRAN语句都会使TRANCOUNT减少1；如前所述，一个值为0的TRANCOUNT意味着没有打开的事务;因此，在TRANCOUNT值从1降到0时结束的事务发生在最外层事务提交的时候。示例2我们使用嵌套事务一般最关注就是外事务和内事务之间的事务提交和回滚Consistency：一致性Isolation：隔离性回滚最外面事务，对内部事务有什么影响CREATEDATABASEtestUSEtest-创建表CREATETABLETestTrans(ColaINTPRIMARYKEY,Colbva
4、rchar(20)NOTNULL);-外部事务BEGINTRANSACTIONOutOfProc;-内部事务BEGINTRANSACTIONInProcINSERTINTOTestTransVALUES(1,aaaa);COMMITTRANSACTIONInProc;-回滚外部事务，也会回滚内部事务ROLLBACKTRANSACTIONOutOfProc;-无数据SELECT*FROMTestTrans;-droptableTestTrans嵌套事务的回滚上面示例说明了什么结果：没有数据。无论数据是否提交，只要最外层回滚了就会导致所有内部嵌套事务回滚。没有隔离性，最外层事务回滚内部事务也要跟着
5、回滚示例3-创建临时表CREATETABLETestTrans(ColaINTPRIMARYKEY,ColbVARCHAR(20)NOTNULL);/*外部事务*/BEGINTRANSACTIONOutOfProc;-内部事务1BEGINTRANSACTIONInProc1INSERTINTOTestTransVALUES(1,aaaa);ROLLBACKTRANSACTIONInProc1;-内部事务2BEGINTRANSACTIONInProc2INSERTINTOTestTransVALUES(2,bbbb);COMMITTRANSACTIONInProc2;/*提交外部事务*/COMM
6、ITTRANSACTIONOutOfProc;SELECT*FROMTestTrans;-droptableTestTrans上面示例说明了什么结果：消息6401，级别16，状态1，第10行无法回滚InProc1。找不到该名称的事务或保存点。我们可以看到：ROLLBACKTRANSACTIONInProc1是错误的。原因是没有保存点InProc1。代码应该改为如下：BEGINTRANSACTIONInProc1savetranInProc1;INSERTINTOTestTransVALUES(1,aaaa);ROLLBACKTRANSACTIONInProc1;操作前保存好保存点（savetr
7、anInProc1），回滚时指定当时保存的位置，SQLServer才知道回滚到哪里去。该回滚的没有回滚，没有一致性，需要借助保存点示例4-创建临时表CREATETABLETestTrans(ColaINTPRIMARYKEY,ColbVARCHAR(20)NOTNULL);/*外部事务*/BEGINTRANSACTIONOutOfProc;-内部事务1BEGINTRANSACTIONInProc1INSERTINTOTestTransVALUES(1,aaaa);ROLLBACKTRANSACTIONInProc1;-内部事务2BEGINTRANSACTIONInProc2SAVETRANIn
8、Proc2INSERTINTOTestTransVALUES(2,bbbb);ROLLBACKTRANSACTIONInProc2;-内部事务3BEGINTRANSACTIONInProc3INSERTINTOTestTransVALUES(3,cccc);COMMITTRANSACTIONInProc3;/*提交外部事务*/COMMITTRANSACTIONOutOfProc;SELECT*FROMTestTrans;-droptableTestTrans上面示例3的结果是什么SELECT*FROMTestTrans;SELECTTRANCOUNT=2关闭窗口的时候示例6CREATEDATA
9、BASENestedXactsAreNotRealGOUSENestedXactsAreNotRealGO-将数据库变为简单模式ALTERDATABASENestedXactsAreNotRealSETRECOVERYSIMPLEGO-创建表和聚集索引CREATETABLEt1(c1INTIDENTITY,c2CHAR(8000)DEFAULTa)CREATECLUSTEREDINDEXt1c1ONt1(c1)GOSELECTCOUNT(*)FROMdbo.t1示例6BEGINTRANOuterTranGOBEGINTRANInnerTranGOINSERTINTOt1DEFAULTValue
10、sGO1000DBCCSQLPERF(LOGSPACE)GO示例6COMMITTRANInnerTranGOCHECKPOINTGODBCCSQLPERF(LOGSPACE)GO示例6COMMITTRANOuterTranGOCHECKPOINTGODBCCSQLPERF(LOGSPACE)GO示例6解释我们发现日志的使用不减反赠，提交的内部事务不会导致日志被清除，这是由于外部事务回滚时也会连同内部事务一起回滚（所以这部分日志在外部事务提交之前永远不会被标记位reusable）。所以这部分日志在外部事务提交之前永远不会被截断。外部事务外部事务内部事务事务1事务2事务3第1次ckpt第2次ckp
11、t示例6查看事务日志USENestedXactsAreNotRealSELECTOperation,Context,TransactionName,AllocUnitName,CheckpointBegin,CheckpointEnd,SavepointName,PreviousSavepointFROMfn_dblog(NULL,NULL)-WHERETransactionNameISNOTNULL-14471-14476示例6示例7CREATEDATABASENestedXactsAreNotRealGOUSENestedXactsAreNotRealGO-将数据库变为简单模式ALTERD
12、ATABASENestedXactsAreNotRealSETRECOVERYSIMPLEGO-创建表和聚集索引CREATETABLEt1(c1INTIDENTITY,c2CHAR(8000)DEFAULTa)CREATECLUSTEREDINDEXt1c1ONt1(c1)GOSELECTCOUNT(*)FROMdbo.t1BEGINTRANOuterTranGOBEGINTRANInnerTranGOSAVETRANInnerTranINSERTINTOt1DEFAULTValuesGO1000ROLLBACKTRANInnerTranCOMMITTRANOuterTran示例7USENes
13、tedXactsAreNotRealSELECTOperation,Context,TransactionName,AllocUnitName,CheckpointBegin,CheckpointEnd,SavepointName,PreviousSavepointFROMfn_dblog(NULL,NULL)-WHERETransactionNameISNOTNULL-16022示例7示例8事务之间的锁USENestedXactsAreNotReal-将数据库变为简单模式ALTERDATABASENestedXactsAreNotRealSETRECOVERYSIMPLE-创建表和聚集索引C
14、REATETABLEt1(c1INTIDENTITY,c2CHAR(8000)DEFAULTa)CREATECLUSTEREDINDEXt1c1ONt1(c1)-DROPTABLEdbo.t1-SELECTCOUNT(*)FROMdbo.t1示例8事务之间的锁USENestedXactsAreNotRealGOINSERTINTOt1DEFAULTValuesGO1000SETTRANSACTIONISOLATIONLEVELREPEATABLEREADGOBEGINTRANtUPDATEdbo.t1SETc2=lockWHEREc11ANDc11ANDc110BEGINTRANInnerTr
15、anUPDATEdbo.t1SETc2=lockCOMMITTRANInnerTran-COMMITTRANOuterTranSELECTTRANCOUNTUSENestedXactsAreNotRealGOSELECTrequest_session_id,c.program_name,DB_NAME(c.dbid)ASdbname,resource_type,request_status,request_mode,resource_description,OBJECT_NAME(p.object_id)ASobjectname,p.index_idFROMsys.dm_tran_locksA
16、SaLEFTJOINsys.partitionsASpONa.resource_associated_entity_id=p.hobt_idLEFTJOINsys.sysprocessesAScONa.request_session_id=c.spidWHEREc.dbid=DB_ID(NestedXactsAreNotReal)ANDa.request_session_id=SPID-要查询申请锁的数据库ORDERBYrequest_session_id,resource_type示例8事务之间的锁示例8事务之间的锁上面示例8结果说明什么内部事务的提交并没有释放锁，要等到外部事务的提交锁才释
17、放更进一步说明并不是真正的嵌套事务，实际上是同一个事务MSDN里的介绍嵌套事务概括SQLServer不支持真正意义上的嵌套事务，当你在事务内提交一个rollbacktran，SQLServer回滚最外层begintran之后的所有操作，如果你在已存在的事务内提交一个begintran语句，并不会打开新的事务，SQLServer只是增加内部计数器，通过TRANCOUNT函数可以查询该计数器。committran语句会把计数器减一，只有最外层的committran才会真正提交事务，并把计数器减为0，SQLServer把通过begintran语句打开的事务级数限制为32个SQLServer支持保存点
18、（SAVETRAN），他允许你撤销事务中的部分操作，为此，你需要执行SAVETRAN语句用于标记一个保存点，然后执行ROLLBACKTRAN以撤销保存点以后执行的操作TRANCOUNT函数记录当前事务的嵌套级别。每个BEGINTRANSACTION语句使TRANCOUNT增加1。每个COMMITTRANSACTION或COMMITWORK语句使TRANCOUNT减去1。没有事务名称的ROLLBACKWORK或ROLLBACKTRANSACTION语句将回滚所有嵌套事务，并使TRANCOUNT减小到0。在无法确定是否已经在嵌套事务中时，可以用SELECTTRANCOUNT确定TRANCOUNT是等于1还是大于1。如果TRANCOUNT等于0，则表明不在事务中。真正的嵌套事务隔离性一致性并行顶层事务BEGIN TRANCOMMIT TRAN调用子事务调用子事务调用子事务子事务子事务子事务BEGIN TRANCOMMIT TRAN调用子事务调用子事务BEGIN TRANCOMMIT TRAN.BEGIN TRANROLLBACK TRAN.并行建议SQLServer只能使用保存点来模拟嵌套事务（并非真正的嵌套事务）作为开发人员来讲，建议尽量不要用嵌套事务1、嵌套事务增加开发难度和出错几率2、效率没有提高
