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 Wednesday, May 23, 2012 1:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:11 PM
Points: 2,270, Visits: 3,785
Good question. Got it wrong.

Mohammed Moinudheen
Post #1304750
Posted Wednesday, May 23, 2012 2:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 1,737, Visits: 6,338
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.
Post #1304770
Posted Wednesday, May 23, 2012 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 5,969, Visits: 8,224
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1304773
Posted Wednesday, May 23, 2012 2:32 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1304777
Posted Wednesday, May 23, 2012 3:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 8,690, Visits: 9,226
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
Post #1304808
Posted Wednesday, May 23, 2012 4:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:53 AM
Points: 1,657, Visits: 2,084
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.
Post #1304817
Posted Wednesday, May 23, 2012 5:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:26 AM
Points: 1,803, Visits: 2,168
Thanks for the question. Mission accomplished for me = learned something.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1304854
Posted Wednesday, May 23, 2012 6:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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
Post #1304898
Posted Wednesday, May 23, 2012 6:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 2,818, Visits: 2,558
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.
Post #1304904
Posted Wednesday, May 23, 2012 6:54 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 @ 7:04 AM
Points: 3,670, Visits: 72,433
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?


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






--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1304908
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse