转自 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…中