• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

SQL sub-procedure repeats itself intermittently.

I have created an application in SSMS 2005 running on a SQL Server 2000 Engine comprised of a cursor and numerous statements inside that cursor. On a very rare occasion, the calling procedure appears to double-up on some of the sub-called SP's. It has the appearance of starting/stopping and forgetting where it's been. The data is doubled up, but it's still hard to tell how much of the procedure it ran before it hiccupped.The main procedure does, on occasion, say it's deadlocked on resources with another process. Is this a problem with locked resources? Is there something I can do to assign a higher priority/lock tables to the Calling Procedure than to the other processes which use the same tables.

Would using "Return" in my Calling procedure do any good?

What steps would you take to track down a wandering cursor's exploits?


0
ttinsley
Asked:
ttinsley
  • 3
  • 2
1 Solution
 
nmcdermaidCommented:
>> What steps would you take to track down a wandering cursor's exploits?

Create a logging table and write entries to it in every step of your stored procedure, along with an automatic datestamp.

Then you can query the table afterwards.


>> the calling procedure appears to double-up on some of the sub-called SP's

I think its more likely that the cursor has two rows where you are expecting only one.

What does the outside cursor look like? Can you post the SQL?


>> Is there something I can do to assign a higher priority/lock tables to the Calling Procedure

You can add hints such as WITH (HOLDLOCK) to the code inside your stored procedure or you can wrap the whole SP up in an OPEN TRANS then COMMIT TRANS.

This has the effect of grabbing the table and not letting go until its finished.

However, usually when you do things like that it that results in all the other processes suffering, and you end up with a great big chain of locked processes in the database, which means everything grinds to a halt.


In actual fact usually things can be achieved without cursors, and can then be achieved much more quickly, which in turn reduces blocking times.

0
 
ttinsleyAuthor Commented:
You're absolutely correct about the need for cursors, except this one was over my head when I started and there was a need to decrement the warehouse quantities on a line by line basis. The procedure basically processes parts orders and transfers stock when the stock levels are high enough. If the stocking level of the item is below what is being ordered the item is placed onto a purchase order. In this manner it uses up all of our stock first, then it automatically switches to placing items on purchase orders. So the same run of the procedure may see both processes depending on quantities ordered and quantities in stock. Normally it works great, it's only very occasionally that we see a doublling up effect.

Here is the code
Parts-Order-Processor.txt
0
 
ttinsleyAuthor Commented:
Still not a real Definitve answer
0
 
nmcdermaidCommented:
I haven't gone through your SP in detail. I'm afraid the best I can offer is for you to write everything to a logging table, and check it in detail when a double up occurs, or rewrite your SP using bulk updates or inserts instead of cursors. That way everything gets updated in one go.
You could also try using the INSENSITIVE cursor keyword to stop your cursor reflecting any change which occurs to it. That may make it worse or better depending on how the logic works.
I can't help you too much more sorry because I know will never get time to go thourgh your SP!!
0
 
ttinsleyAuthor Commented:
I don't blame you, it's a lunker. I believe you are correct about the logging, however, as it turns out, I was upgrading the error I suspected was causing the issues not too long ago, and the problem has seemed to stop occurring. If it does begin reoccurring I will follow the steps you have outlined.

Thanks nmcdermaid!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now