Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bitbucket » Transactions 2
Add to briefcase
57 posts, Page 2 of 6
««12345»»»

Transactions 2 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mohammed moinudheen
Posted Wednesday, May 23, 2012 1:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 2,172, Visits: 3,584
Good question. Got it wrong.

Mohammed Moinudheen
Post #1304750
Toreador
Posted Wednesday, May 23, 2012 2:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:52 AM
Points: 1,356, Visits: 4,761
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
Hugo Kornelis
Posted Wednesday, May 23, 2012 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244, Visits: 7,061
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
derek.colley
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: 2 days ago @ 8:19 AM
Points: 496, Visits: 584
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
L' Eomot Inversé
Posted Wednesday, May 23, 2012 3:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 7,110, Visits: 7,184
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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
Post #1304808
440692 I am just a number
Posted Wednesday, May 23, 2012 4:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:46 AM
Points: 1,631, Visits: 2,031
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
Thomas Abraham
Posted Wednesday, May 23, 2012 5:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 1,152, Visits: 1,457
Thanks for the question. Mission accomplished for me = learned something.

Please don't go. The drones need you. They look up to you.
Post #1304854
roger.plowman
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: Yesterday @ 7:11 AM
Points: 317, Visits: 619
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
Daniel Bowlin
Posted Wednesday, May 23, 2012 6:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 2,673, Visits: 2,418
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
mtassin
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: Today @ 3:15 PM
Points: 3,231, Visits: 64,322
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
57 posts, Page 2 of 6
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.