Transactions 2

Question

Post reply

Transactions 2

Ron McCullough

SSC Guru

Points: 63877
More actions
May 22, 2012 at 8:35 pm

#274333

Comments posted to this topic are about the item Transactions 2
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 56 total)

You must be logged in to reply to this topic. Login to reply

sanjeewan99 Say Hey Kid Points: 700 More actions · Answer 1

sanjeewan99

Say Hey Kid

Points: 700

May 22, 2012 at 10:12 pm

#1491526

Good question !!!

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

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

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 4

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

john.straver SSC Enthusiast Points: 103 More actions · Answer 5

the answer is 0 rows when xact_abort is set to on

the answer is 2 rows when xact_abort is set to off

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 6

john.straver (5/23/2012)
the answer is 0 rows when xact_abort is set to on
the 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

Neil Thomas Mr or Mrs. 500 Points: 544 More actions · Answer 7

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?

Neil Thomas Mr or Mrs. 500 Points: 544 More actions · Answer 8

Neil Thomas (5/23/2012)
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?

Ah, an additional comment has been added whilst I wrote this.

That's a bit of a gotcha isn't it?

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 9

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

M&M SSC-Insane Points: 21700 More actions · Answer 10

M&M

SSC-Insane

Points: 21700

May 23, 2012 at 1:36 am

#1491593

Good question. Got it wrong.

M&M

Toreador SSChampion Points: 11358 More actions · Answer 11

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.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

Neil Thomas (5/23/2012)
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?

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 13

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:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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