May 23, 2012 at 4:05 am
Thank you great question.
Plus extra thanks to Hugo for the link
Hugo Kornelis (5/23/2012)
For a very complete discussion on this subject, read http://www.sommarskog.se/error_handling_2005.html.
May 23, 2012 at 6:43 am
Having the default setting to OFF basically break transactions and is a *bug* in SQL Server.
Explicit transactions should be exactly that, explicit. This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?
However, kudos for pointing this out, at least now it won't catch me...
Member of SPCP -- Society for the Prevention of Cruelty to Programmers
May 23, 2012 at 6:51 am
I agree with some of the other posts that express frustration that this is the default setting that seems rather contrary to an explicit transaction.
So the take away here is that whenever you need to declare an explicit transaction you should include SET XACT_ABORT ON before proceeding.
I have to remember that one, thanks.
May 23, 2012 at 6:54 am
Neil Thomas (5/23/2012)
So that means that when xact_abort is set to off thenBEGIN TRANSACTION
...
COMMIT TRANSACTION
does not do as expected.
What's the default setting on a fresh install of SQL?
But this would also work as though xact_abort is on 🙂
BEGIN try
begin TRANSACTION
INSERT qotd2(col1,col2,col3) VALUES (1,'x','some')
INSERT qotd2(col1,col2,col3) VALUES (1,'Y','thing')
INSERT qotd2(col1,col2,col3) VALUES (2,'Z','or other')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END catch
SELECT * FROM qotd2
May 23, 2012 at 7:03 am
Daniel Bowlin (5/23/2012)
I agree with some of the other posts that express frustration that this is the default setting that seems rather contrary to an explicit transaction.So the take away here is that whenever you need to declare an explicit transaction you should include SET XACT_ABORT ON before proceeding.
I have to remember that one, thanks.
So just remember the Begin transaction statement is spelled
SET XACT_ABORT ON
BEGIN TRANSACTION
:hehe:
May 23, 2012 at 7:09 am
roger.plowman (5/23/2012)
Having the default setting to OFF basically break transactions and is a *bug* in SQL Server.Explicit transactions should be exactly that, explicit. This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?
They were probably thinking that explicit transactions usually come with explicit error handling. For instance using the TRY CATCH pattern, as shown by mtassin.
Daniel Bowlin (5/23/2012)
So the take away here is that whenever you need to declare an explicit transaction you should include SET XACT_ABORT ON before proceeding.
Hmm, no. Only when you declare an explicit transaction AND you don't want to add explicit error handling AND you are fine with the default error handling with XACT_ABORT set to ON. There are lots of situations where you would not want that.
May 23, 2012 at 7:11 am
mtassin (5/23/2012)
But this would also work as though xact_abort is on 🙂
BEGIN try
begin TRANSACTION
INSERT qotd2(col1,col2,col3) VALUES (1,'x','some')
INSERT qotd2(col1,col2,col3) VALUES (1,'Y','thing')
INSERT qotd2(col1,col2,col3) VALUES (2,'Z','or other')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END catch
SELECT * FROM qotd2
No, it would not. With XACT_ABORT set to ON, the final SELECT statement would never be executed. XACT_ABORT causes the batch to be terminated on an error.
May 23, 2012 at 7:13 am
Hugo Kornelis (5/23/2012)
Only when you declare an explicit transaction AND you don't want to add explicit error handling AND you are fine with the default error handling with XACT_ABORT set to ON. There are lots of situations where you would not want that.
Most situations I'd have thought. Wouldn't youy normally want unexpected errors of this sort to be logged somewhere? Using XACT_ABORT won't do this, but explicit error handling will if you remember to code it for course).
May 23, 2012 at 7:29 am
Hugo Kornelis (5/23/2012)
I guess that depends on the expectation. The code in this QotD contains no error-handling and an unconditional COMMIT statement.
My expectation is that those two conditions never exist in production code.
Either begin a transaction and handle errors correctly (which might be to rollback) or don't incur the overhead of the transaction in the first place.
Our typical ad-hoc queries have the commit before the begin transaction and commented out so it can't be run with a batch of code. Instead it is run after successful execution, with the understanding that any error in a batch means something was wrong so rollback.
All new hires are taught that "begin transaction" is part of a delete statement per the CYA rule. Also any time 'begin transaction' is used, you are not allowed to leave for lunch until after commit or rollback. (that was a hard-won lesson)
Is there ever a case where row-level failures are acceptable and a batch should be committed regardless? I understand ETL processes might have some reason to not insert already-existing rows, but shouldn't that be handled explicitly rather than ignoring key/constraint failures? How would you explain to management or a coworker why this is a Bad Thing?
May 23, 2012 at 7:34 am
Hugo Kornelis (5/23/2012)
mtassin (5/23/2012)
But this would also work as though xact_abort is on 🙂
BEGIN try
begin TRANSACTION
INSERT qotd2(col1,col2,col3) VALUES (1,'x','some')
INSERT qotd2(col1,col2,col3) VALUES (1,'Y','thing')
INSERT qotd2(col1,col2,col3) VALUES (2,'Z','or other')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END catch
SELECT * FROM qotd2
No, it would not. With XACT_ABORT set to ON, the final SELECT statement would never be executed. XACT_ABORT causes the batch to be terminated on an error.
Sorry, I worry more about what goes into or out of a table... the select statement at the end was an after thought. If the intention was that none of the data goes in if one of the inserts fails, then the try/catch blocks above also give that result.
May 23, 2012 at 7:48 am
mtassin (5/23/2012)
Sorry, I worry more about what goes into or out of a table... the select statement at the end was an after thought. If the intention was that none of the data goes in if one of the inserts fails, then the try/catch blocks above also give that result.
Yes, I understand.
My point is that using a TRY ... CATCH block is better than using XACT_ABORT, because it gives you much more control over how errors are handled and what information is returned to the client.
May 23, 2012 at 7:52 am
Thanks for the question!
May 23, 2012 at 7:55 am
Hugo Kornelis (5/23/2012)
mtassin (5/23/2012)
Sorry, I worry more about what goes into or out of a table... the select statement at the end was an after thought. If the intention was that none of the data goes in if one of the inserts fails, then the try/catch blocks above also give that result.Yes, I understand.
My point is that using a TRY ... CATCH block is better than using XACT_ABORT, because it gives you much more control over how errors are handled and what information is returned to the client.
Oh I agree... We got TRY/CATCH 4 versions of SQL ago (2005, 2008, 2008R2, 2012 by my count)
It wasn't until recently that we've really started to use it more to its fullest... Too many years with SQL 2000 makes using new features tough.... 🙂
May 23, 2012 at 8:00 am
Nakul Vachhrajani (5/22/2012)
Good question with a couple of learning points.1. By default SQL does not abort the entire transaction if it encounters a statment-level failure. In this case, the primary key violation is a statement level failure and hence, only the statement terminated, not the entire transaction
If the requirement is to cause a failure of the entire transaction (i.e. abort), then the following SET option needs to be set to ON:
SET XACT_ABORT ONSetting XACT_ABORT to ON will cause SQL Server to abort the transaction even if it encounteres the statement level failure.
2. Referring one of my blog posts, row constructors (introduced in SQL Server 2008) process the entire batch at once. Hence, had this example used row constructors, the correct answer would have been 0 rows affected (Reference post: http://beyondrelational.com/modules/2/blogs/77/Posts/14434/0159-sql-server-row-constructors-triggers-process-the-entire-batch-at-once.aspx)
Thank-you!
THIS should have been the explanation. The explanation given for the question explains nothing.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply