October 21, 2008 at 5:18 pm
Hey all,
I've recently just learned the full extent of how SQL Server deals with nested transactions (badly or otherwise) and am currently in the process of writing a best practice document for the developers in my team.
Is there any official or semi-official best practice documents around with what the best practices are when dealing with nested transactions within nested store procedures and or triggers? There are several ways in which they can be dealt with in terms of when the @@Trancount is checked, when rollbacks occur and how this is incorporated into try\catch blocks etc, and I am currently a bit torn as to which method may or may not be the most beneficial.
Kind regards,
Adam Hardy
November 10, 2008 at 5:12 pm
Well don't all talk at once now.
Come on, this is not cutting edge stuff, someone out there must at the very least have an opinion?
November 10, 2008 at 6:53 pm
Well - I personally try to avoid nesting my transactions (since ultimately only the outer one "counts"), but that being said - if I'm worried about scoping and knowing what's being committed, then I start "going manual". Meaning - start naming my transactions and carry a variable in my stored procs telling me how many transactions I started with.
If it gets too messy - then I tend to revert to relying on other methods to allow me "transactional integrity. Such as using a customized "roll your own" scenario for the simpler inner transactions.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 10, 2008 at 7:21 pm
Thanks for your reply Matt.
Yes I agree with you in that nested transactions should be avoided, but I'm utilising triggers for a variety of data integrity tasks on top of a (in some places) shallow stored proc tree (mostly whereby business related stored procs call CRUD (create, update, delete) SPs) I need to implement a consistent approx to dealing with the unavoidable nested transactions caused by implicit trigger transactions or otherwise.
I think I'll got with storing the trancount on entry and checking it in the catch block before performing the rollback.
November 10, 2008 at 8:31 pm
Business rules in triggers? I think that more than 95% of all business rules can either be resolved by the proper use of constraints and foreign keys on the tables without the heavy overhead of using triggers. Could be wrong, though... what kind of business rules are you trying to enforce with your triggers?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2008 at 8:38 pm
Hi Jeff,
Thanks for the input, but I think you may have miss-read my post, I said I was utilising triggers for data integrity tasks, not business rules.
November 11, 2008 at 1:25 am
Adam Hardy (11/10/2008)
Thanks for the input, but I think you may have miss-read my post, I said I was utilising triggers for data integrity tasks, not business rules.
Same (even more) goes for data integrity. Most can be done with constraints and the rest should (imho) be done in stored procedures before doing the data modification. Rollbacks are expensive and triggers fire after the modification has been done.
As for nested transactions, I tend to avoid them because of the risk of a rollback rolling back more than what the developer expected.
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
November 11, 2008 at 5:20 am
Adam Hardy (11/10/2008)
Hi Jeff,Thanks for the input, but I think you may have miss-read my post, I said I was utilising triggers for data integrity tasks, not business rules.
Sorry Adam... I used a confusing term...
Basically, "Business rules" and "Data Integrity" are the same thing. In most cases, a set of well formed constraints on the columns of the table and the presence of Primary and Foreign Keys solves most data integrity tasks.
What data integrity tasks are you trying to do in the triggers?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2008 at 2:32 pm
Guys, your making me suck eggs here,
Yes data integrity tasks on the most part can be modelled with PKs and FKs. Those that can't, can be modelled with triggers. I've used triggers on tables containing critical data where I wanted to protect the data even when a modification has come from a source I hadn't expected (e.g. Some idiot running a query they shouldn't).
Now I suppose you'll tell me to use stored procedures and user permissions to achieve the same goal. I could, but wont as at this stage the triggers would be easier to implement and I honestly don't care about the performance hit in this particular area of the system (it will be negligible at best). There are no nested triggers.
Yes "data integrity" is the lowest form of "business rule". I didn't actually use that term either, I said "business related stored procs" (a business related function implemented in the data tier for performance). Hopefully now we can dispense with the semantics as they are not really relevant to the question I asked.
Adam.
November 11, 2008 at 3:09 pm
Adam Hardy (11/11/2008)
Guys, your making me suck eggs here,Yes data integrity tasks on the most part can be modelled with PKs and FKs. Those that can't, can be modelled with triggers. I've used triggers on tables containing critical data where I wanted to protect the data even when a modification has come from a source I hadn't expected (e.g. Some idiot running a query they shouldn't).
Now I suppose you'll tell me to use stored procedures and user permissions to achieve the same goal. I could, but wont as at this stage the triggers would be easier to implement and I honestly don't care about the performance hit in this particular area of the system (it will be negligible at best). There are no nested triggers.
Yes "data integrity" is the lowest form of "business rule". I didn't actually use that term either, I said "business related stored procs" (a business related function implemented in the data tier for performance). Hopefully now we can dispense with the semantics as they are not really relevant to the question I asked.
Adam.
Actually - the cost isn't so bad if you use a BEFORE trigger (the update hasn't happened yet, so there wouldn't be two separate updates.). Of course - you only get just one of these per table.
Of course - now the scenario flips and you have to be sure to issue the ones you want to commit to the table, or your INSTEAD OF trigger acts as a barrier to entry....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 11, 2008 at 3:18 pm
hmm, Ok Matt thanks.
BEFORE triggers! I wish! Bloody SQL Server doesn't support them.
My triggers used to be "INSTEAD OF", but yeah I wasn't too impressed with the whole maintenance issue of keeping the insert within the trigger up to date with all the new columns added. Now if I could just write a DDL trigger to keep the DML trigger up to date when the table structure changes . . . 😉 shudder!
November 11, 2008 at 3:32 pm
Sorry - I did in fact mean Instead of (since they can be made to implement like a BEFORE sorta like).
I don't let tables get updated ad-hoc, so I can't say I've thought of that as a big maintenance task. But I suppose it would be ugly if the table changed a lot....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 11, 2008 at 3:38 pm
Well we don't really either, it's just that this DB is not in production yet and we're still heavily in development (with DB changes coming in every day) so I have to remember to keep updating the trigger if the table changes. . . .
. . . and my memory aint that good. 🙂
I may look at changing the AFTER triggers to INSTEAD OF later down the track before ALPHA (unit) testing occurs.
November 11, 2008 at 5:57 pm
Hey I've been playing around this morning, with some example SP and example triggers, trying to write an error handler and testing how to structure the try\catch blocks etc.
So I've got one stored proc which opens a transaction then does an update (which fires a trigger). The weird thing is the @@TRANCOUNT when printed from within the Trigger is 1? Not 2? I don't understand, shouldn't it be 2, 1 for the opened transaction within the SP and 1 for the implicit trigger transaction?.
Also when doing the same update outside of the stored procedure (with no transaction), the same the printed out Trancount is still 1!!
November 11, 2008 at 6:15 pm
Adam Hardy (11/11/2008)
I've got one stored proc which opens a transaction then does an update (which fires a trigger). The weird thing is the @@TRANCOUNT when printed from within the Trigger is 1? Not 2? I don't understand, shouldn't it be 2, 1 for the opened transaction within the SP and 1 for the implicit trigger transaction?.
No, Triggers will only start a transaction if @@TRANCOUNT is 0 when the trigger is activated.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply