![]() ![]() You can see how it is being set within the CATCH block (identical to prior steps).Ĥ. Upon entry to the above code segment, must be = 0 because it would be 0 if errors had occurred in either of the 2 prior steps. The error occurs on the second try of the UPDATE (apparently).ģ. The SP produces an email (this information is provided below) that indicates line 632 is where the error occurred and that is the BEGIN TRY statement above. = 3 and there were no errors reported (e.g., deadlock retries) in any of the 2 prior transaction steps.Ģ. WHILE = 0 and > 0 and = + 'Retry count exceeded on this step'+ 'ġ. When this ran in UAT (the users were trying hard to break the logic by executing multiple instances of the SP concurrently) it failed with this 3930 error. We performed a concurrency test and we were able to get a real deadlock condition and the re-try worked, but it was on two different steps than where this error is occurring now. I've wrapped a retry-LOOP structure around each of the 8 queries, so that if a deadlock is detected it will retry 3 times and then give up. The database I'm working with is prone to frequent deadlocks that are partly due to high volume but also due to inefficient SQL, which is an issue I'm trying to address in this rewrite, so I've gone to some pains to ensure that each of the 8 SQL statements is performing the best it possibly can. I know you should keep transactions short but in this case I have no alternative. I have a rather complex SP (1300+ lines) that includes a single transaction consisting of 8 SQL steps (INSERTs, UPDATEs, etc.). In a recent flurry of posts, I've attempted to help users of SSC with some of their query questions and now it is I that must seek the advice of those more experienced than myself.
0 Comments
Leave a Reply. |