December 16, 2008 at 9:49 am
One of our developers came to me with a question and I don't know the answer. Although I'm doing some work to test it out, I thought I'd send it out here for some feedback.
His question was about how transactions, in a stored procedure, would work with, or without, putting the error handling in the procedure. For example, consider some code kind of like the following:
BEGIN TRANS
UPDATE tableA
SET field1 = 'ABC'
INSERT INTO tableB
VALUES('QRS', 'XYZ', 'ABC')
COMMIT TRANS
(Assume there is much more within this transaction.)
He asked what would happen if an error occurred within the transaction without error handling. Would the transaction remain out there uncommitted or would it automatically rollback. In his particular scenario, he could do some error handling within the procedure or outside it. He just wants to know if he needs to explicitly roll the transaction back if there is an error.
Thanks.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
December 16, 2008 at 10:03 am
Either the entire transaction succeeds or it fails.
IF its fails everything will be rolled back automatically.
December 16, 2008 at 10:08 am
It depends on what severity the error is.
IIRC, if the severity is 16 or greater, and there is no error-handling, then the session is terminated and any outstanding transactions are rolled back.
If the severity is 15 or less, and there is no error-handling, then it aborts (and rolls back) the current statement, prints a message and then keeps right on going at the next statement.
Hmm, I am not 100% sure here, can someone validate this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 16, 2008 at 10:24 am
hawg (12/16/2008)
He asked what would happen if an error occurred within the transaction without error handling.
Depends on the error. There are three kinds of errors in SQL. Statement terminating, batch terminating and connection terminating. I'm going to ignore the third one, because there's no handling of that.
If in the example you give, the update causes a batch-terminating error, then the entire batch will be aborted at that point and the transaction will be rolled back.
An example of a batch terminating error is a conversion error or deadlock
If, however, the update were to cause a statement terminating error (like a pk violation) only that statement would fail. The insert would run and the commit would run.
It is always good practice to handle errors, whether you're in a transaction or not.
BEGIN TRANS
BEGIN TRY
UPDATE tableA
SET field1 = 'ABC'
INSERT INTO tableB
VALUES('QRS', 'XYZ', 'ABC')
COMMIT TRANS
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- log a message or throw another error
END CATCH
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
December 16, 2008 at 10:26 am
RBarryYoung (12/16/2008)
IIRC, if the severity is 16 or greater, and there is no error-handling, then the session is terminated and any outstanding transactions are rolled back.If the severity is 15 or less, and there is no error-handling, then it aborts (and rolls back) the current statement, prints a message and then keeps right on going at the next statement.
Unfortunately, it's not that clean. A deadlock is batch aborting and it's a sev 12.
Edit: correction, it's a sev 13.
In the current versions, there's no easy way to tell what errors abort the statement and what abort the batch. Sometimes it's quite senseless which errors do what.
There's no logical reason why a conversion aborts the batch and a pk violation the statement, but that's what currently happens. It's been the subject of some intense debate recently.
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
December 16, 2008 at 10:48 am
To demonstrate:
Create table #Errors (id int primary key)
set nocount on
go
-- testing pk violation
insert into #Errors values(1)
insert into #Errors values(1)
print 'pk error thrown'
go
-- testing conversion
insert into #Errors values ('abc')
print 'conversion error thrown' -- never runs.
go
drop table #Errors
Output:
Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK__#Errors___3213E83F0EA330E9'. Cannot insert duplicate key in object 'dbo.#Errors'.
The statement has been terminated.
pk error thrown
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'abc' to data type int.
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
December 16, 2008 at 10:48 am
That's right, now I remember Adam Machanic complaining about that in his book.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply