nested transaction
-
Hi: Gurus can some one explain it to me or point to me at a resource that shed some light on nested stored procedure and how can I avoid the errors caused by nested transaction e.g Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
I tired to search the BO, internet,etc; they all talk about matter very vaguely but not a single place they show you how to write a code that can avoid this problem . I know all the crap about outer/inner transaction, how the innermost transaction rollback to outermost transaction and the @@trancount. Is there any design pattern we can follow to avoid transaction..
-
Hello,
This is where I admit to being less than perfect.
I routinely encounter this situation when I explicitly open transactions with a 'BEGIN TRANSACTION' but forget to put a corresponding 'COMMIT'. The solution may be as simple as desk checking your code to ensure that each begin transaction has a corresponding commit/rollback.
I encounter this about twice a month. Mostly, I just need to pay more attention to detail.
-
I've had some awful problems due to improper transaction handling. Things got lot better couple years ago when we fixed it all as described below. You can get trickier & implement partial rollbacks with SAVE TRANSACTION & savepoints, I have not needed to so far.
Simple solution is a) only allow @@trancount to grow as high as 1; b) always assume tran may already be open before your proc is called; c) only the proc that started the tran is allowed to commit or rollback.
If you find you're already in a tran, do not begin another one and do not commit or rollback. Just proceed normally & pass back success/failure return code to caller & let caller handle commit/rollback. It is imperative that called proc communicates success/failure back to the caller, and that the caller uses this when deciding to proceed/commit or rollback.
PS - Wayne Snyder had great presentation on this at PASS a few yrs ago, not sure if he's still doing it or someone else, but this has been consistent PASS summit presentation for years now.
declare @InitialTrancount int
set @InitialTrancount = @@trancount
...
--do not begin tran if already in one
if (@InitialTrancount = 0)
begin tran
...
--handle some fatal error
if (@ErrorCode <> 0)
begin
--rollback only if we started tran here
if (@@trancount > @InitialTrancount)
rollback
return @errorcode
end
...
--done, success, commit only if we started tran here
if (@@trancount > @InitialTrancount)
commit
-
Folks I am a seasoned programmer but relatively new in sqlserver world. I am amazed and really disappointed with the error handing quirks and transaction issues in sqlserver. I am already doing all the tricks Mike mentioned in his reply earlier (thanks for that) but still they are not enough to write robust code. In some cases sqlserver abort the batch, some cases it only abort the statement, how transactions handle in these cases is only any body guess; besides that it makes your code less readable. It seems to me that writing reliable applications that sit on top of sqlserver is impossible.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply