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 » SQL Server 2005 » SQL Server 2005 General Discussion » Nested Transactions - Best Practice
Add to briefcase
22 posts, Page 1 of 3
123»»»

Nested Transactions - Best Practice Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Adam Hardy-416657
Posted Tuesday, October 21, 2008 5:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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
Post #589516
Adam Hardy-416657
Posted Monday, November 10, 2008 5:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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?
Post #600308
Matt Miller (#4)
Posted Monday, November 10, 2008 6:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:57 PM
Points: 6,998, Visits: 13,949
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?
Post #600331
Adam Hardy-416657
Posted Monday, November 10, 2008 7:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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.



Post #600337
Jeff Moden
Posted Monday, November 10, 2008 8:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #600348
Adam Hardy-416657
Posted Monday, November 10, 2008 8:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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.

Post #600350
GilaMonster
Posted Tuesday, November 11, 2008 1:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021
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 2008, MVP
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
Post #600458
Jeff Moden
Posted Tuesday, November 11, 2008 5:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #600577
Adam Hardy-416657
Posted Tuesday, November 11, 2008 2:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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.
Post #600942
Matt Miller (#4)
Posted Tuesday, November 11, 2008 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:57 PM
Points: 6,998, Visits: 13,949
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?
Post #600967
« Prev Topic | Next Topic »

Add to briefcase
22 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

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