Is there a way to prevent from deadlocks (and what happens is one occurs and one is victimized)

Question

Hi,

I've tried to read an article on locks and deadlocks, and it just doesn't land, al the different kind of locks. We're running 2 different processes which try to edit records in the same table. The first process reads the new data and sends it to an external party and updates the status accordingly, the other one receives the receiveresults form the external party and updates the status accordingly.

Now we more and more get deadlocks (in which case one of the two is the victom and gets aborted). So far so good because you can anticipate on that, and try to rerun the statement, but then allways the same deadlock occurs. So that's my first question: why does the same deadlock allways reoccur?

And second, is there a way to tell the dbms not to try and get an exclusive lock for a record (we update single records via a stored procedure) when another process is already reading and updating it, but 'wait at the side' till the other process is ready? Or is that a much too simple thing to say about a deadlock?

and may be third, is there a way to ask LINQ to SQL which locks were causing the problem, so i get a little more insight of which parts of the process are causing the problem.

Answer 1

SQL Profiler is the best tool I've used to start trying to solve deadlock problems in SQL Server.

Start a trace: On the Events Selection tab: Uncheck all of the preselected events. Check "Show all events". Expand "Locks". Check "Deadlock graph", "Lock:Deadlock", and "Lock Deadlock Chain".

Run the trace and capture the deadlock events.

View the deadlock events in SQL Profiler. The deadlock display is pretty good. You may still need to look at the raw XML to get additional details.

Using what you find from this first pass, may give you ideas on what to change, or suggest other events to trace in SQL Profiler.

Answer 2

As @Darryl Peterson indicates, SQL Profiler is a good tool to capture deadlock information. If you don't know when the deadlock will occur, you can set a SQL Server trace flag to capture the data.

DBCC TRACEON (1204) 

When a deadlock occurs, information about the deadlock will be written to the SQL Server error log.

There are a number of ways to get a deadlock. Your first question "why does the same deadlock always occur" is probably a good sign. If the deadlock is repeatable, then you can catch it and fix it.

Regarding your second question, SQL Server is probably already telling one process to wait while the other completes. However, you can't avoid a deadlock by waiting. A deadlock is a situation where a process is trying to use a resource that is held by another process. But the other process is waiting for a resource that the first process is holding. Neither process will release a resource until it completes its work. Note that this is a simple explanation and there can be more complex deadlock conditions. The point is that processes in the deadlock will never be able to complete.

Once you learn more about the processes involved in the deadlock, you should be able to take steps to avoid it.