April 17, 2017 at 10:06 am
I have an entire procedure wrapped in an try/catch statement. commit tran is placed at the end of the begin/try (just prior to end try)
Inside the Catch statement is rollback transaction [named transaction].
I don't need to set xact_abort on do I? Isn't that the whole purpose of the explicit transaction and the commit occurring only if the control does not first jump to the Catch statement, which rolls back?
(I have no nested transactions here).
April 17, 2017 at 12:28 pm
I'm not sure why you're asking the question... XACT_ABORT is designed around handling OLE_DB connections. What have you experienced that makes you ask this question?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2017 at 2:39 pm
As far as it being designed only for that, that's not the impression I got after reading Microsoft's description of it. Rather, it "Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error", and applies to (including others), sql server 2008 and onward.
The reason it came up in my mind is because I had someone suggest at work that I should set xact_abort ON. I didn't have experience in xact_abort, so I began reading about it. As I read about it, sure enough, Microsoft's documentation raised doubt in my mind as to whether what I had been doing was enough.
In a particular stored procedure, I essentially wanted to wrap the entire procedure in a single transaction, and rollback EVERYTHING if there was any error raised.
To that end, what I had so far was what I described in my first post:
Begin Try
Begin Transaction
>>>many different statements
Commit Transaction
End Try
Begin Catch
Rollback transaction
End Catch
....So my question is, in order to accomplish what I wanted to accomplish, would I need to add to all of this set xact_abort on ? I don't think so?
(Conversely, I would like to better understand whether I could have accomplished ALL of what I wanted to accomplish but ONLY using set xact_abort on, and skipping my entire transaction, try/catch, etc, but mainly the first question).
April 17, 2017 at 4:32 pm
pisorsisaac - Monday, April 17, 2017 10:06 AMI have an entire procedure wrapped in an try/catch statement. commit tran is placed at the end of the begin/try (just prior to end try)
Inside the Catch statement is rollback transaction [named transaction].I don't need to set xact_abort on do I? Isn't that the whole purpose of the explicit transaction and the commit occurring only if the control does not first jump to the Catch statement, which rolls back?
(I have no nested transactions here).
If you have properly implemented error handling with TRY ... CATCH, then no.
That said, you might want to remove the name from that ROLLBACK...
http://sqlinthewild.co.za/index.php/2015/12/01/why-would-you-want-to-name-a-transaction/
http://sqlinthewild.co.za/index.php/2015/12/15/when-naming-transactions-causes-an-error/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2017 at 9:39 am
ok that's what i was thinking but had started to become confused & doubt. 🙂
thanks for clarification - and also for the links, i've started reading them - (and yes the only reason i have been naming transactions is for readability, including my own self to help me remember which is which), but thank you, i will be more fully studying that issue now as well.
April 18, 2017 at 9:49 am
Name on the BEGIN TRANSACTION - fine. Name on the COMMIT TRANSACTION - fine. Name on the ROLLBACK TRANSACTION - potential error that can leave orphaned transactions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply