Rollback the committed transaction !!!

Question

Post reply

Rollback the committed transaction !!!

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
January 29, 2010 at 2:51 am

#215136

In the below code, I am calling the stored procedure usp_getNextSequence to get the next requirementID.
inside this procedure the code is
CREATE PROCEDURE [dbo].[usp_getNextSequence] @SeqName char(16), @retNextSeq int output
as
declare @next_sequence int, @last_sequence int, @count int
BEGIN TRAN
select @last_sequence = last_seq
from SEQUENCE_TABLE(UPDLOCK HOLDLOCK) where seq_name = @SeqName
-- Could not get a lock
if (@@error <> 0)
begin
ROLLBACK TRAN
select @retNextSeq = NULL
return (-1)
end
select @next_sequence = @last_sequence + 1
update SEQUENCE_TABLE set last_seq = @next_sequence where seq_name = @SeqName --i.e., REQ
select @retNextSeq= @next_sequence --OUTput variable
COMMIT TRAN --committing inside procedure
return (0)
In the above code we have a BEGIN TRAN and a COMMIT TRAN
In the below code, inside BEGIN TRAN insertReq, procedure usp_getNextSequence is being called...
and COMMIT or ROLLBACK is happening.
if usp_getNextSequence fails and returns other than 0, then rollback is executed.
I found that the committed transaction inside the usp_getNextSequence procedure is also rolled back.
How it is happening?DECLARE @ret_val INT, @nextRequirementID INT
BEGIN TRAN insertReq
EXEC @ret_val = usp_getNextSequence 'REQ', @nextRequirementID OUTPUT
IF (@ret_val = 0) -- no error
BEGIN
INSERT INTO REQ(requirement_id,requirement_code,pol_no,seq_no)
VALUES( @nextRequirementID, 'R1', @polNo, @availableSeq_no)
COMMIT TRAN insertReq
END
ELSE --error
BEGIN
ROLLBACK TRAN insertReq
RAISERROR(111004,-1,-1)
RETURN
END
-
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

You'll find the answer here.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Ken McKelvey SSCoach Points: 18843 More actions · Answer 2

You may also find this thread useful:

http://www.sqlservercentral.com/Forums/Topic754864-169-1.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You've got nested transactions. With nested transactions, all a commit does is decrement @@trancount until it reaches 0, then the transaction is actually committed. A rollback however will always roll the entire thing back, right back to the first Begin Tran.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

thanks, I Got it... and i got how the nested transactions work..

thank you,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!