January 21, 2011 at 12:24 pm
Hi,
I have a problem about nested procedure rollback.
I create procedure and than begin transaction and exec another procedure which has a transaction.
When inner procedure transaction rollback ,outer transaction must be rollback.
How can I do this process?
Best Regards
January 21, 2011 at 12:40 pm
Are you familiar with "being transaction" and "commit"/"rollback"?
If you issue a rollback command, it will roll back the WHOLE transaction, including both the nested procedure and the outer procedure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2011 at 1:31 pm
Yes I am,I think I could not tell my problem
for example
create procedure aaa
as
begin tran
.....
....
create procedure bbbb
as
begin tran
exec aaa
....
....
when aaa rollback I want to rollback bbbb
January 21, 2011 at 1:39 pm
As Gus said "If you issue a rollback command, it will roll back the WHOLE transaction, including both the nested procedure and the outer procedure." That's true regardless of where in the transaction you issue the rollback. A rollback anywhere in a nested transaction rolls everything back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2011 at 1:54 pm
Yes that is true for nested transaction,but transaction is in nested procedure when rollback,outer procedure transaction does not rollback..
January 21, 2011 at 2:01 pm
I need when inner procedure transaction is rollback,outer procedure transaction must be rollback..
It is possible?
January 21, 2011 at 2:08 pm
It's very possible. It should be automatic, unless you have the transactions defined incorrectly.
I'd have to see more of the procedure code to help more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2011 at 2:11 pm
Perhaps this example might help you undestand what Gsquared and Gilamonster have stated.
--This is your inner SP
CREATE PROCEDURE Tran_02
@Error INT OUTPUT
AS
DECLARE @E INT
SELECT @E = 0;
BEGIN TRY
INSERT TranTest (Col2) VALUES (1)
END TRY
BEGIN CATCH
SELECT @E = @@Error --Should return a non zero value
PRINT '@E value is '+ CAST(@e AS VARCHAR(10)) --Only used in testing
RETURN @E
END CATCH
PRINT 'Successful' -- Only used in testing
RETURN @E
--This is your outer transaction
BEGIN TRANSACTION
DECLARE @r INT
DECLARE @RR INT
--Do something then execute the inner transaction
EXECUTE @RR = Tran_02 @r OUTPUT;
SELECT @RR -- Only used to test proper working
IF @RR <> 0
ROLLBACK
END
ELSE
COMMIT
END
January 21, 2011 at 2:27 pm
If I try to execute following,when inner procedure transaction rollback ,is outer procedure transaction rollback?
Inner SP
CREATE PROCEDURE [dbo].ProcB
(@X INT)
AS
BEGIN
BEGIN TRY
BEGIN TRAN TR11
INSERT INTO TableC SELECT 1
INSERT INTO TableD SELECT 2
COMMIT TRAN TR11
END TRY
BEGIN CATCH
SET @error = 'Error Message : '+ ERROR_MESSAGE();
IF @@TRANCOUNT > 0 ROLLBACK TRAN TR11
RAISERROR(@error,16,1)
END CATCH
END
END
Outer SP
CREATE PROCEDURE [dbo].ProcA
(@X INT)
AS
BEGIN
BEGIN TRY
BEGIN TRAN TR1
INSERT INTO Tablea SELECT 1
INSERT INTO TableB SELECT 2
EXEC PROCB @
COMMIT TRAN TR1
END TRY
BEGIN CATCH
SET @error = 'Error Message : '+ ERROR_MESSAGE();
IF @@TRANCOUNT > 0 ROLLBACK TRAN TR1
RAISERROR(@error,16,1)
END CATCH
END
END
EXEC ProcA
January 21, 2011 at 2:47 pm
If what you're wanting to do is BEGIN TRANSACTION, and then at some point have the option to ROLLBACK a unit of work while still having the option to COMMIT the remainder of the transaction as a whole, then consider using SAVE TRANSACTION to set transaction savepoints instead of creating nesting transactions.
http://msdn.microsoft.com/en-us/library/ms188378.aspx
SQL Server does not support the concept of an "autonomous transaction", which is basically a nested transaction that can COMMIT or ROLLBACK it's work independent of the higher level transaction that contains it. Other RDBMS like Oracle support it, but it can cause blocking issues and should be avoided. For what it's worth, you can mimic the functionality in SQL Server using what's called a loopback linked server connection.
However, it sounds like what you really want is to ROLLBACK your transaction to a savepoint.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 21, 2011 at 2:52 pm
Thank you very much.I will examine the links
Best Regards
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply