Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using BEGIN TRAN...COMMIT TRAN Expand / Collapse
Author
Message
Posted Monday, August 21, 2006 5:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 2, 2012 1:40 PM
Points: 171, Visits: 74
Hi,

I have couple of questions on using transactions inside a stored procedure. Can any one tell me what happens if an error occurs inside t2?


CREATE PROCEDURE [dbo].[proc1]
AS

BEGIN TRAN t1

UPDATE table1 SET key = 1 WHERE id = 100
IF @@error <> 0
ROLLBACK TRAN t1

BEGIN TRAN t2

UPDATE table1 SET key = 2 WHERE id = 100
IF @@error <> 0
ROLLBACK TRAN t2

COMMIT TRAN t2

UPDATE table1 SET key = 3 WHERE id = 100
IF @@error <> 0
ROLLBACK TRAN t1

COMMIT TRAN t1

GO

Post #303086
Posted Monday, August 21, 2006 5:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 2, 2012 1:40 PM
Points: 171, Visits: 74
And what happens if the t2 block is replaced with a seperate stored procedure and an error occurs inside the stored procedure?


CREATE PROCEDURE [dbo].[proc1]
AS

BEGIN TRAN t1

UPDATE table1 SET key = 1 WHERE id = 100
IF @@error <> 0
ROLLBACK TRAN t1

EXEC proc2
IF @@error <> 0
ROLLBACK TRAN t1

UPDATE table1 SET key = 3 WHERE id = 100
IF @@error <> 0
ROLLBACK TRAN t1

COMMIT TRAN t1

GO

---------

CREATE PROCEDURE [dbo].[proc2]
AS

UPDATE table1 SET key = 2 WHERE id = 100

GO


Thanks.
Post #303087
Posted Tuesday, August 22, 2006 1:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488

Why don't you try it out? You can use the RAISERROR statement to force an error, or intentionally do an update that violates a constraint on the table.

Be very careful when naming transactions. Names are NOT used by commit and only the name of the outermost transaction can be passed to rollback. From books online:

COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] transaction_name: Is ignored by the SQL Server Database Engine

ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ]
transaction_name: Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.

A rollback will always roll back all open transactions. If there's a commit after that point, the commit will throw an error, saying no open transactions.

A commit in a nested transaction will decrement the transaction count . Only if the tran count reaches 0 will the transaction(s) be committed.

In your first example, an error inside t2 will cause both transactions to rollback and an error to be thrown when the commit of t2 is reached and another when the  commit or rollback of t1 is reached. The first and second updates will be rolled back, but the third (which won't be in a transaction at that point) will auto commit.

In your second example, an error in the stored proc will cause t1 to be rolled back. The third update will succeed, because it's no longer in a transaction, and the commit statement will throw an error.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #303136
Posted Tuesday, August 22, 2006 7:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

Fwiw,
I found an old post of mine, just basically describing how transactions work..
Have a look at it and see if you can get your head around it =;o)
It's not entirely trivial to 'get it right'.
BTW, this is in respect to unnamed 'standard' transactions, not named, nor using savepoints. 

-- begin snip --
You *cannot* nest transactions.
The only thing that happens if you nest several BEGIN TRAN is that no
warning message is given (to keep logs and such clean) and the fact
that @@TRANCOUNT increments by 1, nothing else. You have *not* started
another transaction.

You can *only* commit when @@TRANCOUNT = 1
If you say commit and @@TRANCOUNT > 1, all that is happening is that
@@TRANCOUNT decrements by 1. So, not until you reach 1 will any work
be 'truly' committed to the base.

ROLLBACK will do two things that's important to be aware of...
1) It will rollback all work back to the *first* or *outermost* BEGIN TRAN
2) It will also *reset* @@TRANCOUNT to zero (no matter the previos value)

So, 'normally', it may look something like this, question is what will
happen...?

BEGIN TRAN
-- do some stuff
INSERT myTab SELECT 1
 BEGIN TRAN
--   do some more stuff
 INSERT myTab SELECT 2
-- do a commit
 COMMIT
BEGIN TRAN
-- ..even more stuff
INSERT myTab SELECT 3
-- funky stuff happens
ROLLBACK

Now... as we say ROLLBACK here.. what will happen? =;o)

myTab now contains..
1?
1 and 2?
1 and 2 and 3?
Just 3?
Nothing?

...and do we have an open transaction hanging around.. or not?
Try figuring the answer before testing 'for real', and try to get the
understanding for why you think what happens and why. This is very
simplified, and just a pointer at the basics of transaction handling -
it's by no means worthless knowledge, though...
-- end snip --

/Kenneth




Post #303192
Posted Monday, September 7, 2009 5:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 2:42 AM
Points: 12, Visits: 38
Hi All,

Its an Excellant article to understand Begin, Commit and RollBack Transaction
Post #783722
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse