sql server deadlock issue on a single table..................

Question

I have one table let's say "xxx "in sql server 2000 . One .exe is inserting data into that table "xxx" through sql job. But once data is inserted , one stored procedure is reading the data from that "xxx table "and inserting/updating into other two tables and updating back the status into the same "xxx" table. Now, client says that multiple deadlocks are occuring on that "xxx" table . please kindly anyone sdvice me the resolution steps to be taken to resolve this deadlock issue and how to identify it step by step.............

Thanks in advance..... XXX

Answer 1

Even if I had access to your system and all of your code, this is a very difficult issue to resolve. That said, you don't provide many details in your question.

So apply the general rules of Reducing SQL Server Deadlocks:

• Ensure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don't allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
• Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
• Consider using bound connections.

Answer 2

You might try to apply hint to the query that reads the rows: with(updlock, holdlock). Try to create a clustered index that would be used during the select phase if you don't have one. Try to narrow the amount of rows processed in transactions, if possible.

Is it possible to first update rows in the table and then populate other tables? If there is an error you can roll back the transaction.

I don't recommend using NOLOCK hint especially when you are updating the same data.

Answer 3

From my experience with Sql Server 2000 best way to avid deadlocks is to limit parallelism inside the query. This can be done by adding on both queries (insert and SP) or even just the insert the hint Option (Maxdop 1) at the end of the query

This will limit the performance but you have a safe execution. If only one thread is running there is no other thread that can deadlock.