Nested Transactions
-
As a general rule, any stored procedure that does multiple updates should do this in a transaction, so that ALL or NONE of the updates are done after calling the stored procedure (some people may disagree, but this isn’t the point of this post).
If this stored procedure is then called by another stored procedure that also does multiple updates, then this should also be done in a transaction.
I’ve seen 2 different templates for this (inner and outer stored procedures using the same template):-
BEGIN TRAN
Update, delete etc
IF OK COMMIT TRAN ELSE ROLLBACK TRAN
Or
IF @@trancount > 0 SAVE TRAN xxx ELSE BEGIN TRAN xxx
Update, delete etc
IF OK COMMIT TRAN xxx ELSE ROLLBACK TRAN xxx
I can see advantages and disadvantages to both templates, e.g.
Template 1 is very simple. Only the outermost commit will actually commit the data and any failure will rollback everything, whether it’s the inner or outer stored procedure issuing the rollback. The disadvantage is that the outer procedure may not want its work rolled back when the inner procedure fails.
Template 2 is more complex, but does leave the outer procedure to make the decision to commit or rollback its work. The disadvantage is that it relies on knowing that the call to the inner procedure has failed and the minefield that is SQL Server error handling.
Comments/Suggestions?
How do other people manage nested transaction?
-
I'd be interested in comments this this
I only use single transactions whether or not I call multiple procedures.
And if BOL (SQL2K) is to be believed (see Nesting Transactions) then I think nested transactions are worthless other than for possible locking as the outermost tran commit/rollback controls all updates, you cannot rollback a named nested tran and any rollback will rollback all trans.
Far away is close at hand in the images of elsewhere.
Anon. -
If I ever had to design a (sub)system like this, I'd maybe try to do something like "public" and "private" procedures:
- The public ones are the interface to the outside world, and control the (single!) transaction
- The private ones are not exposed to the outside, and do not interact with or control the transaction
- An "outer" procedure can call any inner procedure (after starting the transaction), but may not call any other outer procedure
- An "inner" procedure can call any other inner procedure, but may not call any outer procedure
This way, only one transaction would ever get started. The "cost" is that you have to carefully manage your code base to ensure the pattern is maintained. If you (that is, your organization) doesn't have that level of conotrol over your code base, this probably wouldn't work.
Plan B (made up as I wrote the above): Code defensively. If you need to start a transaction in a nested procedure environment, first check @@trancount. If it's > 0, don't start a new transaction (and assume that whatever did start the transaction is managing it properly, e.i. error detection...) Alternatively, use parameters to indicate to your procedures (a) is a transaction currently active or (b) if this procedure should start a transaction, and (c) what the calling procedure should do when control is returned [commit, rollback, proceed?] Similar checks would required for commits and rollbacks (and that'd be the hard part--transactions are like IF statements with two possible alternate ENDIFs.
Nasty problem. Not at all trivial. Perhaps best to avoid nested procedures involving serious database modifications?
Last comment: beware DTC transactions. How would they factor in to any of this?
Philip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply