sql 嵌套事务学习笔记

以下内容根据此官方文档修改：(v=sql.105).aspx 嵌套事务的使用场景或者说目的主要是为了调用包含了事务的存储过程。不然没必要使用嵌套事务。 下列示例显示了嵌套事务的用途。在 TransProc 存储过程中包含事务，在另外的代码中分别启动事务调用TransProc和

以下内容根据此官方文档修改：(v=sql.105).aspx

嵌套事务的使用场景或者说目的主要是为了调用包含了事务的存储过程。不然没必要使用嵌套事务。

下列示例显示了嵌套事务的用途。在TransProc 存储过程中包含事务，在另外的代码中分别启动事务调用TransProc和不启动事务调用TransProc。

 

SET QUOTED_IDENTIFIER OFF; GO SET NOCOUNT OFF; GO USE AdventureWorks2008R2; TestTrans(Cola , Colb CHAR(3) NOT NULL); TransProc ( InProc INSERT INTO TestTrans VALUES (@PriKey, @CharCol) , @CharCol) COMMIT TRANSACTION InProc; OutOfProc; ; GO /* Roll back the outer transaction, this will roll back TransProc's nested transaction. OutOfProc; ; GO /* The following SELECT statement shows only rows 3 and 4 are still in the table. This indicates that the commit of the inner transaction from the first EXECUTE statement of TransProc was overridden by the subsequent rollback. TestTrans; GO

嵌套事务有以下特点：

1、SQL Server 数据库引擎将忽略内部事务的提交，除了将@@TRANCOUNT 减 1。内部事务的真正提交或者回滚是依靠最外部事务结束时进行的提交或者回滚。如果提交外部事务，也将提交内部嵌套事务。如果回滚外部事务，也将回滚所有内部事务，不管是否单独提交过内部事务。

2、但针对第1条，假如内部事务进行了提交动作（COMMIT TRANSACTION 或 COMMIT WORK），COMMIT只对当前所处的TRANSACTION起作用，也就是说即使COMMIT TRANSACTION transaction_name中的transaction_name是外部事务的名称，也不会提交该外部事务。这一条的重点在于内部事务无法提交外部事务。

3、@@TRANCOUNT 函数记录当前事务的嵌套级别，@@TRANCOUNT=0表示不在事务中，等于1表示在一个事务中，大于1表示处在嵌套事务中。每次 BEGIN TRANSACTION 语句使 @@TRANCOUNT 增加 1，每次 COMMIT TRANSACTION 或 COMMIT WORK 语句使 @@TRANCOUNT 减去 1，而只要有一个ROLLBACK就会使@@TRANCOUNT等于0.

4、ROLLBACK TRANSACTION 语句的 transaction_name只能是最外部事务的事务名称，使用内部事务名称是非法的。带最外部事务名称的ROLLBACK或者不带任何名称的ROLLBACK语句都将回滚所有嵌套事务，，包括最外部事务。此时@@TRANCOUNT等于0。

5、针对第4条，这里特别需要说明的是：虽然这里说“ROLLBACK语句都将回滚所有嵌套事务，包括最外部事务”，但这里的前提是在最外层进行ROLLBACK，经过本人亲自实验，如果在内部事务中执行带最外部事务名称的ROLLBACK或者不带任何名称的ROLLBACK，则只回滚当前内部事务和已执行过的外部事务语句，此内部事务后续的外层事务将继续执行，并能成功修改数据，但后续外部事务中的所有rollback和commit都将不起作用，并提示错误信息，因为@@TRANCOUNT早已经等于0，数据库引擎找不到对应的BEGIN TRANSACTION。可以自己通过下面代码示例中的bad code示例进行验证。

 

以下最佳代码示范说明参考以下博文：

NORTHWIND; (N,N) TESTTRAN; CREATE TABLE TESTTRAN ( COLA , COLB CHAR(3)); ; ; OUTERTRAN; BEGIN TRANSACTION INNER1; BEGIN TRANSACTION INNER2; );(, 16, 1) INNER2; ; ); INNER1; ; ); OuterTran; ; TESTTRAN (NOLOCK); SELECT @@Trancount;

下面看一下怎么处理内层事务的错误(何时Rollback, Commit及错误的传递)

NORTHWIND; (N,N) TESTTRAN; CREATE TABLE TESTTRAN ( COLA , COLB CHAR(3)); ; ; OUTERTRAN; BEGIN TRANSACTION INNER1; BEGIN TRANSACTION INNER2; ); INNER2; INNER2; INNER2; ); INNER1; INNER1; INNER1; );(,16,1) OUTERTRAN; ; OUTERTRAN; TESTTRAN (NOLOCK)

考虑到SP的调用，我们开发SP时应该在最后把@@ERROR返回供调用者检查。另外测试注意检查一下@@Trancount，有时结果看似正确，但是如果@@Trancount不等于0，说明我们的代码出了问题。