Sqlserver——异常总结——详解嵌套事务中EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配发生的原因及处理方法_qq_40205468的博客-CSDN博客

来源: 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

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏