Deadlock error trapping

[Sliderict]

Hello,

Anyone have any insight or routine to handle deadlock error from MS SQL server, i have tried few things but not working well for me.

Someone should have much better way to handle this?

Thanks for any input.

 

[chiph]

When you're a victim of a deadlock, this usually indicates that your transactions are taking too long.

I would examine every piece of code that UPDATEs or INSERTs into the tables, and make sure that there is no work being done inside the BeginTrans..Commit statements that is unrelated to the transaction. Stuff like searching an XML document (should have been done outside the transaction), doing a SELECT (should have been done outside the transaction), or reading/writing a file (should have been done outside the transaction).

By reducing the amount of time spent inside your transaction, you reduce the time that the locks are in place, and the shorter time the locks exist, the less the chances of another transaction colliding with them.

Of course, the other possible cause is that you've written some queries that really do interfere with each other. In this case, the DB Trace tool is your friend.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first