Prev: no answer?
Next: Best way to store image, voice, text file?
From: orgilhp on 1 Jul 2010 08:09
I have fallen into TRANSACTION problem in MSSQL2005.
I created small easy to understand sample which throws same error as
in my problem.
So sample is :

==============================================
CREATE PROCEDURE dbo.spTempProc2
AS
BEGIN TRANSACTION
CREATE TABLE #aa (col INT)
ROLLBACK
RAISERROR ('error test', 16, 1)
GO

===========================================
CREATE PROCEDURE dbo.spTempProc1
AS
BEGIN TRANSACTION

CREATE TABLE #aa (col INT)
INSERT INTO #aa SELECT 1

EXEC dbo.spTempProc2
IF @@ERROR <> 0 RETURN

COMMIT TRANSACTION
GO
======================================

After create the sample procedures above, I executes dbo.spTempProc1
procedure as below:

EXEC dbo.spTempProc1

When I execute this procedure, the procedure sends me an error
message :

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure spTempProc2, Line 7
error test
Msg 266, Level 16, State 2, Procedure spTempProc2, Line 0
Transaction count after EXECUTE indicates a mismatching number of
BEGIN and COMMIT statements. Previous count = 1, current count = 0.


Why transaction counts are not same?.
I think that inner procedure's ROLLBACK command rolls back its actions
and rolls back outer procedure's actions too.
From this, the transaction counts should be same as 0.

Please help me!

Any suggestion would be highly appreciated!

Regards!
..... Orgil
From: Erland Sommarskog on 1 Jul 2010 09:01
orgilhp (orgilhp(a)gmail.com) writes:
> Why transaction counts are not same?.
> I think that inner procedure's ROLLBACK command rolls back its actions
> and rolls back outer procedure's actions too.

Yes, this is correct. When you issue a second BEGIN TRANSACTION, all
that happens is that @@trancount is incremented. And when you issue a
COMMIT TRANSACTION, @@trancount is decremented, and if @@trancount is
still > 0, nothing more happens. Only if @@trancount reaches 0 is
the transaction committed.

ROLLBACK TRANSACTION is different. Since ROLLBACK TRANSACTION rolls
back the transaction, @@trancount goes 0 directly. @@trancount cannot
become 1, because that would not be meaningful. Recall that *all* the
second BEGIN TRANSACTION achieves is to increase @@trancount. It does
not define a point to roll back to.

There is a fourth command: SAVE TRANSACTION. This command defines a save
point, and you may be able to roll back to this savepoint. The principle
is:

SAVE TRANSACTION somename
-- Do things
-- Do bad things
ROLLBACK TRANSACTION somename

When I say "may roll back" is that if you try to rollback to a save
point in a CATCH handler, you will not be able to, if the transaction is
doomed. In this case you must roll back the entire transaction. For this
reason, I find savepoints to be of limited interest only.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: no answer?
Next: Best way to store image, voice, text file?