Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using BEGIN TRAN...COMMIT TRAN


Using BEGIN TRAN...COMMIT TRAN

Author
Message
Senthil T
Senthil T
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
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
Senthil T
Senthil T
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392

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, 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


Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1579 Visits: 699

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





naseemnaz2
naseemnaz2
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 38
Hi All,

Its an Excellant article to understand Begin, Commit and RollBack TransactionCool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search