October 4, 2023 at 3:38 pm
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
October 4, 2023 at 4:40 pm
Is there a different begin tran outside of the if then begin end statement?
October 4, 2023 at 4:56 pm
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
October 4, 2023 at 6:01 pm
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
Change is inevitable... Change for the better is not.
October 5, 2023 at 1:42 am
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.
October 5, 2023 at 8:52 am
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
October 5, 2023 at 9:11 am
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
October 5, 2023 at 10:34 pm
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.
October 6, 2023 at 1:24 pm
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