Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Nested Transactions - Best Practice Expand / Collapse
Author
Message
Posted Tuesday, October 21, 2008 5:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:04 PM
Points: 15, Visits: 79

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
Posted Monday, November 10, 2008 5:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:04 PM
Points: 15, Visits: 79

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
Posted Monday, November 10, 2008 6:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 7,135, Visits: 15,152
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
Posted Monday, November 10, 2008 7:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:04 PM
Points: 15, Visits: 79

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
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: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #600348
Posted Monday, November 10, 2008 8:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:04 PM
Points: 15, Visits: 79

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
Posted Tuesday, November 11, 2008 1:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
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
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: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #600577
Posted Tuesday, November 11, 2008 2:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:04 PM
Points: 15, Visits: 79

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
Posted Tuesday, November 11, 2008 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 7,135, Visits: 15,152
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 123»»»

Permissions Expand / Collapse