Error of No Corresponding Begin Tran not getting simulated

Question

Post reply

Error of No Corresponding Begin Tran not getting simulated

saum70

SSC Eights!

Points: 993
More actions
October 4, 2023 at 3:38 pm

#4301346

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

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

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

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

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)

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 4

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.

This reply was modified 1 year, 1 month ago by Dennis Jensen.

saum70 SSC Eights! Points: 993 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

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

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 7

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.

This reply was modified 1 year, 1 month ago by Dennis Jensen.

saum70 SSC Eights! Points: 993 More actions · Answer 8

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