August 21, 2006 at 5:22 pm
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
August 21, 2006 at 5:25 pm
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.
August 22, 2006 at 1:40 am
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
August 22, 2006 at 7:29 am
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
September 7, 2009 at 5:39 am
Hi All,
Its an Excellant article to understand Begin, Commit and RollBack Transaction:cool:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply