Mikel
来源： Sqlserver——异常总结——详解嵌套事务中EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配发生的原因及处理方法_qq_40205468的博客-CSDN博客
下面是测试表和存储过程的代码
—创建测试表
IF EXISTS ( SELECT * FROM sys.tables WHERE name = ‘tt’ )
DROP TABLE dbo.tt ;
CREATE TABLE dbo.tt ( ID INT IDENTITY , Name NVARCHAR (100), TransCount INT ) ;
GO
—创建主存储过程
IF EXISTS ( SELECT * FROM sys.procedures WHERE name = ‘P_proc’ )
DROP PROC P_proc ;
GO
CREATE PROC P_proc
AS
BEGIN
BEGIN TRAN ;
INSERT INTO dbo.tt ( Name, TransCount ) SELECT ‘查询1’, @@TRANCOUNT ;
EXEC dbo.S_proc ;
RETURN ;
END ;
GO
—创建子存储过程
IF EXISTS ( SELECT * FROM sys.procedures WHERE name = ‘S_proc’ )
DROP PROC S_proc ;
GO
CREATE PROC S_proc
AS
BEGIN
BEGIN TRAN ;
INSERT INTO dbo.tt ( Name, TransCount ) SELECT ‘查询2’, @@TRANCOUNT ;
ROLLBACK ;
RETURN ;
END ;
执行主存储过程P_proc
分析这个错误提示，可以发现，这个错误其实是由子存储过程报错报错抛出的，
调用S_proc之前的 事务层数是1 ，而执行之后的事务层数是0 所以系统抛出执行异常。
其中 提示 上一计数 = 1，当前计数 = 0。
当单独执行时子存储过程时，可以看到。
是没有任务问题的。
我们再做个测试，独立执行子存储过程之前 手动开启两个事务，执行结果
可以看到上一计数的值正好和 事务的开启层数相同。
可以得出结论：调用存储过程，会对执行前后事务的层数作判断(查询 @@TRANCOUNT 可以看到当前会话的事务层数)，如果不相等时会错误 ‘EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 =X，当前计数 =X’
——————————————–下面介绍一种解决方法
看示例代码
——Sp避免嵌套调用异常,编写格式
CREATE PROC TestSp ( @ReturnValue INT OUTPUT )
AS
BEGIN
SET XACT_ABORT ON ;
SET NOCOUNT ON ;
–开启事务–
BEGIN TRANSACTION ;
–创建事务节点(节点名格式: TRANSACTION +节点顺序(1,2,3,4)+ ‘_’+ Sp名称 )
SAVE TRANSACTION TRANSACTION1_TestSp ;
—-业务逻辑代码
BEGIN
INSERT tt SELECT 1 ;
—–异常处理1(回滚指定到事务节点)
IF 1 = 0
BEGIN
SET @ReturnValue = -1 ;
ROLLBACK TRANSACTION TRANSACTION1_TestSp ;
COMMIT ;
RETURN ;
END ;
END ;
–无异常,提交事务
SET @ReturnValue = 1 ;
COMMIT ;
RETURN ;
END ;
上面用到了一个SQL server的特性 ：事务节点。
并以上述为例,讲述其用法和含义：存储过程中开启事务(@@TranCount=1)，同时创建一个事务节点 TRANSACTION1_TestSp，当执行业务代码时:发生异常,并进行异常处理时,将回滚事务节点(而非回滚事务),将节点范围内的所有事务日志全部回滚(回滚之后的事务层数没有变化,@@TranCount未变化,仍然未1,只是回滚了事务日志),之后进行正常的提交,@@TranCount的层数-1；未发生异常时,正常提交@@TranCount的层数-1。
可以看到，不管最终的执行结果是成功返回还是异常返回，结果都只会将@@TranCount-1,而不会回滚整个事务层，避免了嵌套事务中的‘EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配’问题。
————原创，纯手打，觉得对您有帮助的话，帮忙点个赞哦！
————————————————
版权声明：本文为CSDN博主「志向数据库架构师的初级DBA」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_40205468/article/details/100561823
