Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions 2


Transactions 2

Author
Message
M&M
M&M
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2595 Visits: 3898
Good question. Got it wrong.

M&M
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 8064
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
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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
derek.colley
derek.colley
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 603
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 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.

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
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

Ian_McCann
Ian_McCann
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2282
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.

Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2339 Visits: 2254
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
roger.plowman
roger.plowman
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 1125
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
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
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.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
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 Smile


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search