SQLServer存储过程嵌套事物踩坑

问题描述

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' 中插入重复键。

• 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尽量避免产生嵌套事物

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

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