Error of No Corresponding Begin Tran not getting simulated

  • Hello,

    I have come across this peculiar problem.

    This stp is called every begin of the day of the application startup. A bug got introduced in the stp.

    The BEGIN TRAN was placed within IF END but its COMMIT TRAN was placed after the END. Theoretically this should have given error on execution even the once. However, no error got simulated on the first couple of days of the deployment of the stp in the production or QA.

    This issue got simulated only when the stp was executed in the query anaylzer and then from the application.

    What to be done here?

    Regards,

    Saumik

     

  • Is there a different begin tran outside of the if then begin end statement?

  • stp = Stored Procedure?

    I suspect 'simulated' is not the right word here ... but I'm not sure what you really mean. Please clarify.

    What to be done? Fix the bug! You might also look into SET XACT_ABORT

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • saum70 wrote:

    Hello,

    I have come across this peculiar problem.

    This stp is called every begin of the day of the application startup. A bug got introduced in the stp.

    The BEGIN TRAN was placed within IF END but its COMMIT TRAN was placed after the END. Theoretically this should have given error on execution even the once. However, no error got simulated on the first couple of days of the deployment of the stp in the production or QA.

    This issue got simulated only when the stp was executed in the query anaylzer and then from the application.

    What to be done here?

    Regards,

    Saumik

    Sounds like the simple fix would be to move the COMMIT to before the END.  Another fix is to add another IF BEGIN to check to see if there's an active transaction and only fire the COMMIT if there is one.

    Then, find out what went wrong with the testing process because it missed an obvious error. 😉

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I concur with Jeff Moden put the COMMIT inside the IF for if the BEGIN TRAN is inside the IF then having the COMMIT outside the IF is totally meaningless.

    Further not only is this better code flow but it should completely eliminate the bug.

  • Hello,

    Of course the COMMIT TRAN have been put within IF and the same has been deployed already.

    The question here is why in first couple of days this error did not occur in the production environment and suddenly it gave this error. and how to trace this type of issues.

    Regards,

    Saumik

  • saum70 wrote:

    Hello,

    Of course the COMMIT TRAN have been put within IF and the same has been deployed already.

    The question here is why in first couple of days this error did not occur in the production environment and suddenly it gave this error. and how to trace this type of issues.

    Regards,

    Saumik

    It's clear now, but …

    Your initial question was 'What to be done here?' And you expected people to know exactly what you meant by that?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • saum70 wrote:

    The question here is why in first couple of days this error did not occur in the production environment and suddenly it gave this error. and how to trace this type of issues.

    If I had to make a guess, and I am sure you could (or could have) test this but the BEGIN TRAN was getting executed because the IF was evaluated to True and you finally got a False evaluation and then got the error. BUT that is just a guess.

  • Hello,

    Thank you all for your efforts in replying. I could trace back the issue. The flow of the stp was not proper and hence the issue.

    Regards,

    Saumik Vora

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply