SQLServer存储过程嵌套事物踩坑

数据库 专栏收录该内容
5 篇文章 0 订阅

问题描述

SQLServer中存储过程嵌套,且两个存储过程均有事物的情况下,内层事物的回滚会导致外层事物管控失效,示例如下:

建表脚本

CREATE TABLE [dbo].[t1](
	[id] [int] NOT NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[t2](
	[id] [int] NOT NULL,
 CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

存储过程脚本

内层存储过程

CREATE PROCEDURE InnerProduce
AS
BEGIN
	SET NOCOUNT ON
	BEGIN TRAN
	
	BEGIN TRY
		INSERT INTO t1 (id) VALUES (1),(1);
		
		COMMIT TRAN
		RETURN 0
	END TRY
	BEGIN CATCH
		IF(@@TRANCOUNT > 0)
			BEGIN
				ROLLBACK TRAN
				RETURN 1
			END
	END CATCH	
END

外层存储过程

CREATE PROCEDURE OutterProduce
AS
BEGIN
	SET NOCOUNT ON 
	
	-- 设置异常全部回滚
	SET XACT_ABORT ON
	BEGIN TRAN
	
	DECLARE @result int
	EXEC @result = InnerProduce
	
	INSERT INTO t2 (id) VALUES (@result);
	
	INSERT INTO t2 (id) VALUES (1); 
	
	COMMIT TRAN
END

问:如上外层存储过程的调用结果?

外层调用结果

> 错误信息1:
消息 266,级别 16,状态 2,过程 InnerProduce,第 0 行
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1,当前计数 = 0> 错误信息2:
消息 2627,级别 14,状态 1,过程 OutterProduce,第 14 行
违反了 PRIMARY KEY 约束 'PK_t2'。不能在对象 'dbo.t2' 中插入重复键。

1563288915157.png

  • t1表中无记录
  • t2表中一条记录

问:为什么会产生这样的结果?

问题原因分析

内层处理分析:

CREATE PROCEDURE InnerProduce
AS
BEGIN
	SET NOCOUNT ON
	BEGIN TRAN
	
	BEGIN TRY
		-- 此处出现PK重复异常,进入Catch块
		INSERT INTO t1 (id) VALUES (1),(1);
		
		COMMIT TRAN
		RETURN 0
	END TRY
	BEGIN CATCH
		-- 当前事物计数1,回滚事物返回1,此时事物计数0
		IF(@@TRANCOUNT > 0)
			BEGIN
				ROLLBACK TRAN
				RETURN 1
			END
	END CATCH	
END

外层处理分析:

CREATE PROCEDURE OutterProduce
AS
BEGIN
	SET NOCOUNT ON 
	
	-- 设置异常全部回滚(此处异常回滚依旧生效)
	SET XACT_ABORT ON
	BEGIN TRAN
	
	-- 返回的处理结果为@result,此处不会产生异常(内层已捕获并处理)
	DECLARE @result int
	EXEC @result = InnerProduce
	-- 调用结束后抛出 > 错误信息1
	-- 内层存储过程调用结束后事物回滚,此时事物计数为0,下方脚本的事物控制失效
	
	-- 正常写入数据(隐式事物,自动提交)
	INSERT INTO t2 (id) VALUES (@result);
	
	-- 写入数据失败(PK重复 - 隐式事物,自动回滚),抛出 > 错误信息2
	INSERT INTO t2 (id) VALUES (1); 
	
	-- 此处提交事物未做任何处理
	COMMIT TRAN
END

结论

  • SQLServer尽量避免产生嵌套事物

  • 如果是由程序直接调用存储过程,不要在存储过程中设置事物,将事物交由外部程序控制

  • 如果不可避免的出现嵌套事物,一定要对内层嵌套存储过程(含事物)执行完成后的结果进行校验,根据处理结果及时回滚事物或继续处理

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值