May 22, 2012 at 8:35 pm
Comments posted to this topic are about the item Transactions 2
May 22, 2012 at 10:12 pm
Good question !!!
May 22, 2012 at 10:53 pm
this question look very familiar......
too bad i got it wrong...again.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 22, 2012 at 11:24 pm
Very nice question, definately learned something.
Got it wrong, as I say "begin transaction" I immediately thought "rollback everything". Apparently not 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 22, 2012 at 11:55 pm
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 ON
Setting 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!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
May 23, 2012 at 12:35 am
the answer is 0 rows when xact_abort is set to on
the answer is 2 rows when xact_abort is set to off
May 23, 2012 at 12:43 am
john.straver (5/23/2012)
the answer is 0 rows when xact_abort is set to onthe answer is 2 rows when xact_abort is set to off
By default, SET XACT_ABORT is OFF.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
May 23, 2012 at 12:44 am
So that means that when xact_abort is set to off then
BEGIN TRANSACTION
...
COMMIT TRANSACTION
does not do as expected.
What's the default setting on a fresh install of SQL?
May 23, 2012 at 12:45 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?
Ah, an additional comment has been added whilst I wrote this.
That's a bit of a gotcha isn't it?
May 23, 2012 at 1:09 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 ON
Setting 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!
The question is interesting. I first thought the select would give 0 rows, but then i remind myself that XACT_ABORT is set to OFF by default.
Thank you
IgorMi
Igor Micev,My blog: www.igormicev.com
May 23, 2012 at 1:36 am
Good question. Got it wrong.
M&M
May 23, 2012 at 2:18 am
Good question, but not sure about the explanation, which talks about implied transactions even though this one is explicit . The issue surely is that statement failure doesn't cause an automatic abort/rollback unless you've coded one.
May 23, 2012 at 2:21 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?
I guess that depends on the expectation. The code in this QotD contains no error-handling and an unconditional COMMIT statement.
For handling SQL Server errors, it is important to realize that various errors have various effects on running code. Off the top of my head, the potential effects are:
* Compile-time error - the entire batch is not executed; including statements BEFORE the one that causes the error (because the batch is compiled as a whole before execution starts). Execution halts.
* Statement-aborting - running statement is rolled back; transaction is left intact; execution continues.
* Transaction-invalidating - running statement is rolled back; transaction is marked invalid (meaning you'll get an error if you try to commit); execution continues.
* Transaction-aborting - running statement and open transactions (if any) rolled back; execution continues. You'll get an error if you execute a commit or rollback statement after this.
* Batch aborting - running statement is rolled back. I don't know if transaction is rolled back or left open (sorry). Rest of batch is not executed; execution continues at next batch.
* Connection aborting - running statement and open transactions rolled back; connection dropped (so all execution halts). This only happens in the case of some very severe errors (like drive failures or so).
For a very complete discussion on this subject, read http://www.sommarskog.se/error_handling_2005.html.
May 23, 2012 at 2:32 am
Good question, caught me out! As one of the other posters said, I was railroaded by BEGIN TRANSACTION - thought the entire batch would be rolled back - apparently not. Nice one.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
May 23, 2012 at 3:49 am
Good question.
Unfortunately the explanation is all about implicit transactions, while the question is about an explicit transaction. So the explanation is not exactly helpful!
Tom
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply