March 9, 2015 at 11:35 am
Is there a scenario whereby you would create a stored procedure with the option
Set Xact_abort ON
And validly need to create transactions with commits inside that stored procedure?
I have looked around online and have not had any clear answer as most refer to xact_abort and try catch blocks.
My feeling is that Xact_abort overrides the need for begin trans with a commit, but would like to be sure.
For a picture of my example see below
create procedure MyProc (@Var int)
as
Begin
set xact_abort on
Begin Try
Begin transaction trans1
doStuff
Commit transaction Trans1
END Try
Begin Catch
Rollback transaction Trans1
logerror(@@Error)
END Catch
END
GO
March 9, 2015 at 11:42 am
March 10, 2015 at 3:02 am
XactAbort removes the need to try/catch, but if you want multiple statements to be atomic (all succeed or all fail), you need an explicit transaction.
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
March 11, 2015 at 6:12 am
By default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.
But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.
Like in case of PK violation it just terminates the duplicate row, and do not terminate the batch. Check the examples here I've shown in my blog post
1. http://sqlwithmanoj.com/2011/12/06/sql-servers-weird-behavior-in-error-handling-with-transactions/[/url]
2. http://sqlwithmanoj.com/2011/12/01/xact_abort-with-transactions/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply