Rollback transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rollback transaction

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2010-01-08 : 14:21:23

I have situation as this

CREATE PROCEDURE Run_All AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
EXEC PROCEDURE1
EXEC PROCEDURE2
EXEC PROCEDURE3
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR
ROLLBACK TRANSACTION
END CATCH
END

In PROCEDURE1, PROCEDURE2 AND PROCEDURE3 there is BEGIN TRY and ROLLBACK in CATCH block. These 3 procedures have looked almost identical with constcrutcion as this Run_All procedure. Here is PROCEDURE1 ...

CREATE PROCEDURE PROCEDURE1 AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION

SQL statements

COMMIT TRANSACTION
SET IDENTITY_INSERT TableInsertTo OFF
END TRY
BEGIN CATCH
RAISERROR
ROLLBACK TRANSACTION
END CATCH
END

So, they call 3 procedures from one procedure where they set ROLLBACK in CATCH block.

My question is, if error happend in PROCEDURE3, is it going to ROLLBACK results from PROCEDURE1 and PROCEDURE2 even they run good (these 2 procedures have COMMIT TRANSACTION line)

Thanks,

Ilimax

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-08 : 14:27:09

No a child stored procedure does not rollback anything from the parents.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-08 : 15:00:24

Gonna have to disagree there.
The way you have it written, where Run_All has a transaction and all three inner procs have a (nested) transaction, with no save points, a rollback anywhere will roll back everything. Notice here that the @@trancounts from proc3 before and after the rollback go from 2 to 0. And that the final output of Select * from Junk shows the original value for [i] of "0". So the inner proc3 rollback rolled back the changes from run_all, proc1, and proc2.


use tempdb

go
create table junk (i int)
go
insert junk (i) values (0)
go
create proc PROCEDURE1
as
begin tran
begin try
       update junk set i = 1
       commit tran
end try
begin catch
       rollback tran
end catch
go

create proc PROCEDURE2
as
begin tran
begin try
       update junk set i = 2
       commit tran
end try
begin catch
       rollback tran
end catch
go

create proc PROCEDURE3
as
begin tran
begin try
       update junk set i = 3
       --force an error
       raiserror('forced error in proc3', 17, 1)
end try
begin catch
       select @@trancount  [Proc3: trancount before rollback]
       rollback tran
       select @@trancount  [Proc3: trancount after rollback]
end catch

go

CREATE PROCEDURE Run_All AS
BEGIN
       BEGIN TRANSACTION
       BEGIN TRY
              --parent update
              update junk set i = -1
              EXEC PROCEDURE1
              EXEC PROCEDURE2
              EXEC PROCEDURE3
              COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
              select @@trancount [trancount from run_all]
              if @@trancount > 0 ROLLBACK TRANSACTION
              RAISERROR('error from Run_All', 17, 1)
       END CATCH
END

go

exec Run_All
go
select * from junk
go

drop proc Run_All
drop proc PROCEDURE1
drop proc PROCEDURE2
drop proc PROCEDURE3
drop table junk

OUTPUT:
(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Proc3: trancount before rollback
--------------------------------
2

(1 row(s) affected)

Proc3: trancount after rollback
-------------------------------
0

(1 row(s) affected)

trancount from run_all
----------------------
0

(1 row(s) affected)

Msg 50000, Level 17, State 1, Procedure Run_All, Line 16
error from Run_All
i
-----------
0

(1 row(s) affected)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-08 : 15:04:57

I prefer to use a single transaction in the calling procedure. Either have your inner catch blocks return a RETURN code to the caller to OR raise an error in the inner catch block to force the caller to go to it's catch block. But in either case a single controlling transaction is much less confusing.

Be One with the Optimizer
TG

ilimax
Posting Yak Master

164 Posts

Posted - 2010-01-08 : 15:15:43

Thanks TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-08 : 15:27:06

Yeah I didn't review the code sample, just the explanation part. So I was going off how we have ours coded.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-02 : 12:10:05

I am still stuck on this. We have to be 2000-compatible so we can't use the try-catch. Without the try-catch, is there anyway to avoid the 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements' error?

We need a begin tran/rollback/commit for the Parent Stored Proc. Plus we need a begin tran/rollback/commit for the Child Stored Proc since it could be called either on its own or from the Parent. It seems no matter what, we will get the mismatch error if there is a rollback in the Child since before calling the Child, Trancount will be 1 and after returning from the child, Trancount will be 0. Any ideas?

CREATE PROCEDURE dbo.p1
AS
	BEGIN TRAN
	EXEC dbo.p2;
	if @@Trancount = 1
		COMMIT TRAN;
GO

CREATE PROCEDURE dbo.p2
AS
begin tran
	select 1/0

	if @@Error <> 0 
	begin
		ROLLBACK TRAN
		--begin tran
end
GO

EXEC dbo.p1;

drop procedure p1
drop procedure p2

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-03-04 : 16:38:42

The best way is to find a way not to nest transactions. Either combine the SPs or re-evaluate if you really need a transaction on the inner proc. But if you absolutely have to do it this way:
In 2000 you can use CONTEXT_INFO to set a "message" for any other code that is in the context of the calling SPID. So you set the value in the p1 and check it in p2. Conditionally start a transaction only if you're not already in one.


CREATE PROCEDURE dbo.p1
AS
	BEGIN TRAN

	declare @InTran varbinary(128)
       select @inTran = convert(varbinary(128), 'In Tran from p1')
       set context_info @InTran

	EXEC dbo.p2;

	if @@Trancount > 0
		COMMIT TRAN;
GO

CREATE PROCEDURE dbo.p2
AS

select @@trancount [trancount beginngin of p2]

--check the transaction state.  
declare @TranState varchar(128)
select @TranState = convert(varchar(128), context_info)
from sys.sysprocesses 
where spid = @@spid

select @TranState [TranState]
if isNull(@TranState,'') not like 'In Tran from p1%'
       begin tran
else 
       print 'no nested transaction'

select @@trancount [trancount middle of p2]

--generate an error
select 1/0

if @@Error <> 0 
begin
       print 'error'
       if isNull(@TranState,'') not like 'In Tran from p1%'
              rollback tran
end             

if isNull(@TranState,'') not like 'In Tran from p1%'
       commit
       
select @@trancount [trancount end of p2]
go


EXEC dbo.p1
exec dbo.p2

go
drop procedure p1
drop procedure p2

Be One with the Optimizer
TG

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-09 : 08:46:51

Thankyou TG.

I'm trying a way that is similar. I hope it will work?

declare @InitialTranCount int	

select @InitialTranCount = @@TranCount

if @InitialTranCount = 0 BEGIN TRAN
...

if @InitialTranCount = 0 COMMIT TRAN
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources