April 16, 2013 at 7:56 am
I created a transaction stored procedure adding a line as
Begin Tran
But someone told me that need to modify as
SET XACT_ABORT ON
BEGIN TRAN
Is that true? What does "SET XACT_ABORT ON" mean?
April 16, 2013 at 8:03 am
it means that if you start a BEGIN TRAN,
if ANY error occurs in the transaction, instead of moving on to the next statement and continuing to process(the default behavior in SSMS without a transaction), it stops processing and immediately rolls back the transactions.
this is ideal for situations where you need to do multiple steps, and it's an "all or nothing" kind of transaction.
Lowell
April 16, 2013 at 8:13 am
So, in my understading that "SET XACT_ABORT ON" should add all time.
April 16, 2013 at 8:15 am
adonetok (4/16/2013)
So, in my understading that "SET XACT_ABORT ON" should add all time.
As with everything in sql there are NO absolutes. If you want a portion of a process to be able to complete even if there are errors in other steps then you would not want to do this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 9:09 am
I call this stored procedure from one asp.net app.
If transaction roll back, does app catch an error message?
April 16, 2013 at 9:14 am
adonetok (4/16/2013)
I call this stored procedure from one asp.net app.If transaction roll back, does app catch an error message?
That depends on what the stored proc does. Does the proc throw an exception? If so, then it would be returned to your page. If the proc handles the error then it may not be returned. You would have to post a bit more info to provide a definite answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 9:58 am
adonetok (4/16/2013)
So, in my understading that "SET XACT_ABORT ON" should add all time.
If you want SQL to automatically roll back transactions in case of an error. Personally it's a setting that I strongly prefer to be off.
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
April 16, 2013 at 10:16 am
It means that just keep "BEGIN TRAN" without "SET XACT_ABORT ON" if I want to roll back if any error occurs?
April 16, 2013 at 11:22 am
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
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 18, 2015 at 3:08 pm
GilaMonster (4/16/2013)
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
Thanks, Gail - that was a great explanatory article!
FYI I did notice that one set of code in that article seems to a couple of typos:
CREATE TABLE TestingTransactionRollbacks (
<pre> ID INT NOT NULL
PRIMARY KEY ,
SomeDate DATETIME DEFAULT GETDATE()
) ;
GO
BEGIN TRANSACTION
BEGIN TRY
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (3)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure
END CATCH
GO
EXEC InsertWithError
GO
DROP TABLE TestingTransactionRollbacks
DROP PROCEDURE InsertWithError
The first typo seems to be an artifact of the "pre" html display code. The second, perhaps, is because maybe the code originally was wrapped in a CREATE PROCEDURE InsertWithError or something like that.
Thanks again, though! I want to come up with a basic error handling framework that I can re-use when creating procedures, and I finally want to get a better grasp of transactions and rollbacks to do so.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 19, 2015 at 3:13 am
I found Erland Sommarskog's articles on SQL Error Handling very helpful. They are very detailed, and very long, but gave me a lot of reassurance that I understood all aspects of it. Erland is a big fan of SET XACT_ABORT ON 🙂
August 9, 2021 at 5:53 pm
I found Erland Sommarskog's articles on SQL Error Handling very helpful. They are very detailed, and very long, but gave me a lot of reassurance that I understood all aspects of it. Erland is a big fan of SET XACT_ABORT ON 🙂
I found an article from Erland, but that was written pre SS 2005. The TRY CATCH had yet to be introduced as well as the THROW , an option away from Raiseerror.
----------------------------------------------------
August 9, 2021 at 7:23 pm
I suggest you always explicitly set XACT_ABORT, either ON or OFF. After that, you just need to understand the implications of the XACT_ABORT setting.
If it's OFF, you may have parts of the same transaction succeed while other parts fail (*). That violates the atomicity ("all-or-none") of transactions, a core tenet of relational dbs. If you want to do that, and there are some legit reasons to do so, I'd suggest you include a comment to that effect with the XACT_ABORT setting. I'm not normally keen on commenting on the normal functioning of code, but I think a lot of people may not understand the implications of the setting, so it's worth reminding them.
Moreover, since you want your code to be able to be re-run if it failed the first time, your code must be written such that it can be re-run without extraneous errors no matter which parts of a trans did or did not commit or rollback.
(*) But NOTE that even with an OFF setting, certain errors can make the entire trans fail and rollback.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply