[b]TRANSACTION WAS DEADLOCKED ERROR[/b]

[kissarmi]

I have some large stored procedures that run some complicated calculations. They are called from my VB app by using a batch run method and on an individual basis. There are situations where we could have several users calling the SP at the same time - batch and individual. Cursors are used extensively. Of course, this causes the 'Transaction was Deadlocked' error message. I used an 'on error' routine to trap the error number, which works. This, however, eventually causes a 'Timeout Expired' error. If I try to manipulate this error, I get locked up processes. Is there anything I can do on the SP side of things to keep this from happening.

 

[hmckillop]

Many things you can do.

The longer the procedure runs and more resources it accesses/locks the more it is likely to deadlock
Firstly, get rid of the cursors (one of the most inefficient methods of processing) and use set based processing (shown in faqs), access the resources in the same order and if possible once (if not lock the resources to stop access to them at an early stage in the proc).
Also ensure you indexes are optimal which can speed access to the data also limiting the exposure to deadlocking.

If you post the procedure we might have a better idea of understanding the problem better.


"I'm living so far beyond my income that we may almost be said to be living apart