SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested Transactions - Best Practice


Nested Transactions - Best Practice

Author
Message
Adam Hardy-416657
Adam Hardy-416657
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 122
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
Adam Hardy-416657
Adam Hardy-416657
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 122
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?
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29385 Visits: 19002
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?
Adam Hardy-416657
Adam Hardy-416657
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 122
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214370 Visits: 41979
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Adam Hardy-416657
Adam Hardy-416657
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 122
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223620 Visits: 46297
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214370 Visits: 41979
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Adam Hardy-416657
Adam Hardy-416657
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 122
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.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29385 Visits: 19002
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?
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