首页 / MSSQL / sql server中嵌套事务*
sql server中嵌套事务*
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了sql server中嵌套事务*，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6271字，纯文字阅读大概需要9分钟。
内容图文
转自 https://www.cnblogs.com/guanjie20/archive/2013/02/17/2914488.html
我们在写事务时经常遇到的问题如下：
消息 266，级别 16，状态 2，过程 sp1，第 0 行
EXECUTE?后的事务计数指示?BEGIN?和?COMMIT?语句的数目不匹配。上一计数 = 1，当前计数 = 0。
消息 3903，级别 16，状态 1，过程 sp2，第 15 行
ROLLBACK?TRANSACTION?请求没有对应的?BEGIN?TRANSACTION。
?
如果这只是一个单独的事务引起的，那么很好解决，我们只要检查下是否遗漏了匹配的BEGIN tran 和 COMMIT tran即可，但是如果2个存储过程都是用事务写的，那么就即使每个存储过程的事务写法都正常，也会报这个错误，
这是因为只要子事务里有回滚语句：如ROLLBACK 那么全局的@@TRANCOUNT被直接置为0了，导致父事务提交时发现 @@TRANCOUNT=0 报错 ，sql server会认为当前不存在任何事务，在父存储过程中任何的COMMIT TRAN或
ROLLBACK 语句都会找不到它对应的 BEGIN TRAN
下面我们用一个实例来看下：
假设有一张表，ID为非自增主键
USE [TestDB]
GO
/****** Object: Table [dbo].[test] Script Date: 02/17/2013 15:44:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[ID] [bigint] NOT NULL,
[UserID] [bigint] NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] 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]
GO
SET ANSI_PADDING OFF
GO
我们常规的写一个插入的子存储过程如下：
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[innertranv1] Script Date: 02/17/2013 15:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--内层事务存储过程，演示如何处理才能在嵌套的事务存储过程中正确处理事务
ALTER PROCEDURE [dbo].[innertranv1]
@ID BIGINT ,
@UserID BIGINT ,
@Name VARCHAR(50)
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))
BEGIN
ROLLBACK
RETURN 0 ;
END
--业务逻辑开始
INSERT dbo.test
( ID, UserID, Name)
VALUES ( @ID,
@UserID,
@Name
)
--业务逻辑结束
IF @@error <> 0
BEGIN
ROLLBACK
RETURN 0;
END
COMMIT
SET XACT_ABORT OFF;
RETURN 1 ;
END
调用的父存储过程如下：
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[outertranv2] Script Date: 02/17/2013 16:09:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <外层存储过程>
-- =============================================
ALTER PROCEDURE [dbo].[outertranv2]
@ID BIGINT ,
@UserID BIGINT ,
@Name VARCHAR(50)
AS
BEGIN TRAN
DECLARE @result INT
EXEC @result = innertranv1 @ID =@ID, @UserID =@UserID, @Name = @Name
IF ( @result <= 0 )
BEGIN
ROLLBACK TRAN ;
RETURN ;
END
COMMIT TRAN
我们执行父存储过程：
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[outertranv2]
@ID = 0,
@UserID = 0,
@Name = N'0'
SELECT 'Return Value' = @return_value
GO
第一次提交正常，再次执行就会出现如下错误：
消息 266，级别 16，状态 2，过程 innertranv1，第 0 行 EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1，当前计数 = 0。 消息 3903，级别 16，状态 1，过程 outertranv2，第 18 行 ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
如何解决？我们修改子存储过程如下：
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[innertran] Script Date: 02/17/2013 16:26:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--内层事务存储过程，演示如何处理才能在嵌套的事务存储过程中正确处理事务
ALTER PROCEDURE [dbo].[innertran]
@ID BIGINT ,
@UserID BIGINT ,
@Name VARCHAR(50)
AS
BEGIN
DECLARE @TRANCOUNT int=(select @@TRANCOUNT)
SET XACT_ABORT ON
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '未进入子事务前全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))
BEGIN TRAN tran1 --开始事务
SAVE TRAN tranpoint --保存事务点
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '进入子事务后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))
IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))
BEGIN
ROLLBACK TRAN tranpoint ; --回滚保存点的事务
COMMIT TRAN tran1 ; --提示当前事务
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '回滚子事务后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))
RETURN 0 ;
END
--业务逻辑开始
INSERT dbo.test
( ID, UserID, Name)
VALUES ( @ID,
@UserID,
@Name
)
--业务逻辑结束
IF @@error <> 0
BEGIN
ROLLBACK TRAN tranpoint ; --回滚保存点的事务
COMMIT TRAN tran1 ; --提示当前事务
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '回滚子事务后全局@@TRANCOUNTT'+CAST(@TRANCOUNT AS VARCHAR(50))
RETURN 0;
END
COMMIT TRAN tran1 ;
SET XACT_ABORT OFF;
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '提交子事务后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))
RETURN 1 ;
END
父过程如下：
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[outertran] Script Date: 02/17/2013 16:27:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <外层存储过程>
-- =============================================
ALTER PROCEDURE [dbo].[outertran]
@ID BIGINT,
@UserID BIGINT,
@Name VARCHAR(50)
AS
DECLARE @TRANCOUNT int=(select @@TRANCOUNT)
PRINT '未进入父事务前全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
BEGIN TRAN
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '进入父事务后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
DECLARE @result INT
EXEC @result = innertran @ID = @ID, @UserID = @UserID, @Name =@Name
IF ( @result <= 0 )
BEGIN
ROLLBACK TRAN ;
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '回滚父事务后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
RETURN ;
END
COMMIT TRAN
SET @TRANCOUNT=(select @@TRANCOUNT)
PRINT '提交父事务后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
调用父存储过程：
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[outertran]
@ID = 0,
@UserID = 0,
@Name = N'0'
SELECT 'Return Value' = @return_value
GO
结果如下：
未进入父事务前全局@@TRANCOUNT:0 进入父事务后全局@@TRANCOUNT:1 未进入子事务前全局@@TRANCOUNT:1 进入子事务后全局@@TRANCOUNT:2 回滚子事务后全局@@TRANCOUNT:1 回滚父事务后全局@@TRANCOUNT:0
不会再报"EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配"之类的错误了，实际上就是在每个嵌套的子过程中标明当前事务点，每个子事务 只提交/回滚 子事务点，而不是回滚整个事务！
实际开发中还是会出现事务错乱的情况，如在try...catch...中
内容总结
以上是互联网集市为您收集整理的sql server中嵌套事务*全部内容，希望文章能够帮你解决sql server中嵌套事务*所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】