Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Transactions 2 Expand / Collapse
Author
Message
Posted Tuesday, May 22, 2012 8:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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

Before posting a performance problem please read
Post #1304651
Posted Tuesday, May 22, 2012 10:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 11:26 PM
Points: 116, Visits: 397
Good question !!!
Post #1304670
Posted Tuesday, May 22, 2012 10:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
this question look very familiar......
too bad i got it wrong...again.


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1304677
Posted Tuesday, May 22, 2012 11:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
Very nice question, definately learned something.
Got it wrong, as I say "begin transaction" I immediately thought "rollback everything". Apparently not




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1304679
Posted Tuesday, May 22, 2012 11:55 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 4:37 AM
Points: 1,456, Visits: 1,857
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1304697
Posted Wednesday, May 23, 2012 12:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 18, 2013 5:40 AM
Points: 61, Visits: 42
the answer is 0 rows when xact_abort is set to on
the answer is 2 rows when xact_abort is set to off
Post #1304713
Posted Wednesday, May 23, 2012 12:43 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 4:37 AM
Points: 1,456, Visits: 1,857
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1304721
Posted Wednesday, May 23, 2012 12:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:46 AM
Points: 356, Visits: 173
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?
Post #1304722
Posted Wednesday, May 23, 2012 12:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:46 AM
Points: 356, Visits: 173
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?
Post #1304723
Posted Wednesday, May 23, 2012 1:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,115, Visits: 3,239
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,
SQL Server developer at Seavus
www.seavus.com
Post #1304732
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse