September 22, 2011 at 3:39 pm
Simple task -
Perform a few inserts or updates on multiple tables in the same database. If there is an error in any one of the inserts or updates, rollback all the changes.
My question is which one is apt or works better.
1-----------------------------------------------------------------------------------------------------------------
Begin Tran
Begin Try
Insert
Insert
Update
Update
Commit Tran
End Try
Begin Catch
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
End Catch
------------------------------------------------------OR--------------------------------------------------------
2-----------------------------------------------------------------------------------------------------------------
BEGIN TRY
BEGIN TRANSACTION;
Insert
Insert
Update
Update
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Roll back any active or uncommittable transactions
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
September 23, 2011 at 2:32 am
September 23, 2011 at 2:54 am
Hi Gianluca,
Have a question about this code:
IF @localTran = 1 AND XACT_STATE() = 1
ROLLBACK TRAN
Once you're in the catch block, wouldn't you want to roll back the transaction regardless of whether the transaction was commitable or not?
Apologies if I'm missing something.
September 23, 2011 at 2:57 am
krishna.vanne (9/22/2011)
My question is which one is apt or works better.
It really doesn't make a difference.
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
September 23, 2011 at 3:18 am
HowardW (9/23/2011)
Hi Gianluca,Have a question about this code:
IF @localTran = 1 AND XACT_STATE() = 1ROLLBACK TRAN
Once you're in the catch block, wouldn't you want to roll back the transaction regardless of whether the transaction was commitable or not?
Apologies if I'm missing something.
You're right!
It should be:
IF @localTran = 1 AND XACT_STATE() <> 0
ROLLBACK TRAN
Edited: I guess I didnt' have enough sleep tonight.
-- Gianluca Sartori
September 23, 2011 at 4:27 am
try this ...
Begin Try
Begin Tran
Insert Into TableName Values (99, 'ZZZ');
Update TableName
SetColName = 'XXX'
Where ColName = 99;
Commit Tran
End Try
Begin Catch
If @@Error <> 0
Begin
RollBack Tran
End
End Catch
MH-09-AM-8694
September 23, 2011 at 5:47 am
Mahesh Bote (9/23/2011)
try this ...
Begin Try
Begin Tran
Insert Into TableName Values (99, 'ZZZ');
Update TableName
SetColName = 'XXX'
Where ColName = 99;
Commit Tran
End Try
Begin Catch
If @@Error <> 0
Begin
RollBack Tran
End
End Catch
If you fall into the catch block @@Error must be other than 0.
Am I missing something?
-- Gianluca Sartori
September 23, 2011 at 7:19 am
krishna.vanne (9/22/2011)
My question is which one is apt or works better.
It's a question of style. My personal preference is to start the transaction inside the TRY (option 2) just because it feels neater to me to have all the transaction-related stuff in the same 'block'.
I also like to SET XACT_ABORT ON to minimize the chances of leaving a hanging transaction. Error handling is annoyingly quirky and imperfect in T-SQL, sad to say.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 23, 2011 at 7:33 am
SQL Kiwi (9/23/2011)
krishna.vanne (9/22/2011)
My question is which one is apt or works better.It's a question of style. My personal preference is to start the transaction inside the TRY (option 2) just because it feels neater to me to have all the transaction-related stuff in the same 'block'.
I also like to SET XACT_ABORT ON to minimize the chances of leaving a hanging transaction. Error handling is annoyingly quirky and imperfect in T-SQL, sad to say.
Agreed. My personal preference is option 1 instead.
In java and c# you generally begin the transaction outside the try block, so that you are sure that if you fall into the catch block the transaction is open and you can rollback (unless the connection was forcibly closed). I got accustomed to this style and I kept it in T-SQL.
-- Gianluca Sartori
September 23, 2011 at 8:11 am
Books Online states that
Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.
In other words, the first recording of the transaction in the log is when the first insert/update/delete is executed after the begin transaction statement for that transaction. SO as was stated before, it doesn't matter whether you put the begin transaction or the begin try first. I do have a personal preference of putting the begin transaction before the begin try, just so that -if ever- the begin transaction could fail, execution would not be passed into the catch block with an uninitialised transaction, but would fail outside the try block instead. But, as said, this is a theoretical difference only, as "begin transaction" can not fail is commonly believed never to fail.
I do have a remark on using "SET XACT_ABORT ON", though. ON is better only used when no error handling is in place. When xact_abort is OFF execution will still upon an error go straight into the catch block. The only thing that xact_abort ON does is make the engine decide to abort your code faster. This violates the intent of the try-catch construct as it bypasses the catch block in situations where xact_abort on kicks in. In fact, xact_abort ON makes the engine behave more "sensitive" to errors. For any errors that did not cause the procedure to be aborted early, the transaction will have been doomed (i.e. xact_state() = -1). To minimise the number of situations in which the engine will choose to abort my procedures and to avoid transactions becoming doomed I explicitly set xact_abort to OFF inside each procedure and test for xact_state() inside each catch block. Sadly, even with xact_abort set to OFF some errors will still return execution to the caller or doom the transaction. This will break the proper operation of any procedure that tries to "play nice" in any situation where an enclosing transaction may or may not be provided.
Here's an example of an sp that tries to properly handle errors in every possible situation. It demonstrates various errors and the difference that xact_abort makes on them. I do a lot of work on SQL server service broker handlers, which need a transaction around the entire operation. This sort of "broken" error handling makes writing these handlers very hard...
First a little scripting to set up a test table and procedure in your tempdb:
use tempdb;
go
create table dbo.Test (
id int not null,
x xml null,
primary key (id)
);
go
create procedure dbo.spTest( @error int, @xa_on int = 0)
as
begin
declare @result int = 0;
set nocount on;
if @xa_on = 1
set xact_abort on;
else
set xact_abort off;
declare @trancount int;
select @trancount = @@trancount;
if @trancount > 0
save transaction trnMyTran;
else
begin transaction trnMyTran;
begin try
-- Do something.
if @error = 1
begin
-- This creates a divide by zero error
-- which can be properly handled.
insert dbo.Test(id)
select @error/0;
end else if @error = 2
begin
-- This creates a doomed transaction (in fact: just assigning
-- the invalid xml to a local variable is sufficient to doom
-- the transaction!). This one is VERY annoying and I can not
-- find a way to avoid it.
declare @xml xml;
insert dbo.Test(id, x)
select @error, N'<some_invalid_xml>';
end else if @error = 3
begin
-- Primary key violation
insert dbo.Test(id)
select @error
union all select @error
end else
begin
-- merge the @id into the table, updating x to some
-- xml value.
with cte as (
select t.id, t.x
from dbo.Test t
where t.id = @error
)
merge cte trg
using (
select @error as id,
(
select @error as [@error],
getutcdate() as [@date]
for xml path('test'), type
) x
) src
on (trg.id = src.id)
when not matched then
insert (id, x)
values(src.id, src.x)
when matched then
update
set x = src.x;
end
print 'Just before the commit (i.e. execution has not been transferred into the catch block).'
if not @trancount > 0
commit tran trnMyTran;
end try
begin catch
select error_number(), error_message();
if xact_state() > 0
rollback tran trnMyTran;
else if not @trancount > 0 and xact_state() < 0
rollback tran;
select @result = 50000;
end catch
return @result;
end
go
Now do some tests using this procedure. Especially note the results for xact_state(): 1 = in a valid transaction, 0 = not in a transaction, -1 = in an invalid (doomed) transaction, i.e.: you're in trouble :(.
print '=============== No error, xact_abort OFF ============'
-- Show that spTest "plays nice" when no error occurs:
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 0, 0;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
print '=============== Divide by Zero error, xact_abort OFF ============'
-- Now try 'divide by zero error' with "set xact_abort off".
-- result: Transaction is ok, caller can decide how to continue.
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 1, 0;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
print '=============== Divide by Zero error, xact_abort ON ============'
-- Do the same 'divide by zero error' but with "set xact_abort on".
-- Result: Transaction is doomed, caller has not choice to abort or another error is thrown.
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 1, 1;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
print '=============== primary key violation error, xact_abort OFF ============'
-- Try 'primary key violation error' with "set xact_abort off".
-- result: Transaction is ok. Caller can decide how to continue.
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 3, 0;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
print '=============== primary key violation error, xact_abort ON ============'
-- Same 'primary key violation error' again but now with "set xact_abort on".
-- result: Transaction is doomed. Caller has no choice but to rollback.
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 3, 1;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
print '=============== XML parsing error, xact_abort OFF ============'
-- Xml parsing error is a serious problem, it can not be properly handled!
-- Try 'xml parsing error' with "set xact_abort off".
-- result: Transaction is doomed. Caller has to rollback.
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 2, 0;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
print '=============== XML parsing error, xact_abort ON ============'
-- Same 'xml parsing error' again but now with "set xact_abort on".
-- result: Transaction is still doomed. Caller has to rollback here too.
begin tran trnTest;
declare @nResult int;
exec @nResult = dbo.spTest 2, 1;
select @nResult as result, @@trancount as [trancount], xact_state() as [xact_state];
if xact_state() > 0
commit tran trnTest;
else if xact_state() < 0
rollback tran;
go
output:
=============== No error, xact_abort OFF ============
Just before the commit (i.e. execution has not been transferred into the catch block).
result trancount xact_state
----------- ----------- ----------
0 1 1
(1 row(s) affected)
=============== Divide by Zero error, xact_abort OFF ============
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8134 Divide by zero error encountered.
result trancount xact_state
----------- ----------- ----------
50000 1 1
(1 row(s) affected)
=============== Divide by Zero error, xact_abort ON ============
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8134 Divide by zero error encountered.
result trancount xact_state
----------- ----------- ----------
50000 1 -1
(1 row(s) affected)
=============== primary key violation error, xact_abort OFF ============
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2627 Violation of PRIMARY KEY constraint 'PK__Test__3213E83F108B050B'. Cannot insert duplicate key in object 'dbo.Test'.
result trancount xact_state
----------- ----------- ----------
50000 1 1
(1 row(s) affected)
=============== primary key violation error, xact_abort ON ============
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2627 Violation of PRIMARY KEY constraint 'PK__Test__3213E83F108B050B'. Cannot insert duplicate key in object 'dbo.Test'.
result trancount xact_state
----------- ----------- ----------
50000 1 -1
(1 row(s) affected)
=============== XML parsing error, xact_abort OFF ============
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9400 XML parsing: line 1, character 18, unexpected end of input
result trancount xact_state
----------- ----------- ----------
50000 1 -1
(1 row(s) affected)
=============== XML parsing error, xact_abort ON ============
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9400 XML parsing: line 1, character 18, unexpected end of input
result trancount xact_state
----------- ----------- ----------
50000 1 -1
(1 row(s) affected)
And finally, cleanup after the experiments:
drop table dbo.Test;
drop procedure dbo.spTest;
I hope this helps any of you in properly handling errors. And maybe you'll reconsider using xact_abort ON.
edit:
- added print to demonstrate execution is passed into the catch block.
- added output.
September 23, 2011 at 8:23 am
Thanks all for your wonderful post and explanations. Really helpful.
September 23, 2011 at 8:49 am
R.P.Rozema (9/23/2011)
So as was stated before, it doesn't matter whether you put the begin transaction or the begin try first. I do have a personal preference of putting the begin transaction before the begin try, just so that -if ever- the begin transaction could fail, execution would not be passed into the catch block with an uninitialised transaction, but would fail outside the try block instead. But, as said, this is a theoretical difference only, as "begin transaction"can not failis commonly believed never to fail.
Yes either way is fine. Not sure what you mean by 'uninitialised transaction' though - either a transaction gets started, or it doesn't. Any sane CATCH block has to be coded for the case when XACT_STATE() = 0.
I do have a remark on using "SET XACT_ABORT ON", though. ON is better only used when no error handling is in place.
In general, what passes for error handling in T-SQL is at least a bit more predictable with XACT_ABORT ON. There are still some edge cases that behave counter-intuitively (some for backward compatibility!) but not so many as with XACT_ABORT OFF. It is clear you particular needs and working with it OFF suits you better - and that's fine - but many/most people will find ON a better option overall.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 23, 2011 at 9:20 am
SQL Kiwi (9/23/2011)Yes either way is fine. Not sure what you mean by 'uninitialised transaction' though - either a transaction gets started, or it doesn't. Any sane CATCH block has to be coded for the case when XACT_STATE() = 0.
An 'uninitialised transaction' would be whatever the result can be of a failed call to "begin transaction". In a perfect world you are right, a transaction get started or it doesn't. But that's exactly my point here: if "begin transaction" fails, will the transaction state be consistent? Since "begin transaction" failed, we can't be sure.
In general, what passes for error handling in T-SQL is at least a bit more predictable with XACT_ABORT ON. There are still some edge cases that behave counter-intuitively (some for backward compatibility!) but not so many as with XACT_ABORT OFF. It is clear you particular needs and working with it OFF suits you better - and that's fine - but many/most people will find ON a better option overall.
I agree, if you don't implement error handling using a try-catch block, you best set xact_abort to ON to avoid having pieces of your code executed with unknown input(s). However having it with a try-catch block in place does not serve a purpose; execution is diverted into the catch block upon the first occurrence of an error, so the code following the statement that raised the error is not executed, just like with set xact_abort ON. However xact_abort ON does have this nasty side effect on your transactions which is best avoided, since many people don't even know about the existence of uncommitable transactions, let alone how to deal with them.
edit: incorrect quoting fixed
September 23, 2011 at 9:27 am
B.T.W. I have created a connect item for the fact that assigning an invalid xml invalidates the transaction, even on an unlogged action. I would appreciate any votes for it: https://connect.microsoft.com/SQLServer/feedback/details/690463/error-9400-and-other-xml-parsing-errors-should-not-make-transaction-uncommittable
September 23, 2011 at 9:42 am
R.P.Rozema (9/23/2011)
In a perfect world you are right, a transaction get started or it doesn't. But that's exactly my point here: if "begin transaction" fails, will the transaction state be consistent? Since "begin transaction" failed, we can't be sure.
Hah. Well I'm all for defensive programming, but you might as well worry about what happens if the BEGIN TRY fails and leaves you with an open transaction. Seriously, I wouldn't worry about SQL Server failing to begin a transaction leaving you in some weird netherworld.
I agree, if you don't implement error handling using a try-catch block, you best set xact_abort to ON to avoid having pieces of your code executed with unknown input(s). However having it with a try-catch block in place does not serve a purpose; execution is diverted into the catch block upon the first occurrence of an error, so the code following the statement that raised the error is not executed, just like with set xact_abort ON.
Of course there's a purpose - you might need to clean up some non-transaction resources, log some error details somewhere, and so on. I wish it were true that errors always diverted into the CATCH block, but you can't rely on this. The following snippet illustrates a very common scenario where the CATCH won't be called:
CREATE PROCEDURE #BreakMe
AS
BEGIN
SET XACT_ABORT OFF
BEGIN TRANSACTION
BEGIN TRY
-- Simulate some processing
WAITFOR DELAY '00:01:00'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION
PRINT 'Oh dear, an error'
END CATCH
END
-- Run this, and hit Cancel Executing Query in SSMS
-- while it is running
EXECUTE #BreakMe
-- Catch not executed, no message, trancount = 1
PRINT @@TRANCOUNT
-- You'll need to execute this
ROLLBACK
GO
DROP PROC #BreakMe
Notice that this leaves you with a silently-open transaction in SSMS. XACT_ABORT ON protects you from this. Any attention signal from the client causes this behaviour (including an ordinary query timeout). There are all sorts of weird and annoying quirks like this.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply