March 9, 2015 at 11:48 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 11, 2015 at 5:55 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]
Thanks,
Manoj
March 31, 2022 at 9:18 pm
My one thought here is that a call to a stored procedure may not roll back within a transaction, whereas if you do ,say, just inserts to multiple tables within the body of your code , then those would Rollback. So Xact_abort is called to address a scenario such as this (with the stored procedure).
Still, I have to admit this is a hypothesis as I have not tested this out. Maybe I will try to do that and give a definitive answer.
----------------------------------------------------
March 31, 2022 at 9:43 pm
Old thread on a 7 year delay! Minor correction to the earlier response tho. For generic SQL statements the default is OFF, however, for TRIGGERS it's ON. The documentation only mentions this in the Remarks afaik. From the MS Docs:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is 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. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 31, 2022 at 9:45 pm
From Mircrosoft Docs : "
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. "
Implied here is the word ''Transaction." So to me you need a transaction to apply Xact_abort to.
There is a good example here on the very page i got the quote from. Inserts into multiple tables within one transaction.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply