Zero SQL Deadlock by Design - Any Coding Patterns

Zero SQL deadlock by design - any coding patterns?

Writing deadlock-proof code is really hard. Even when you access the tables in the same order you may still get deadlocks [1]. I wrote a post on my blog that elaborates through some approaches that will help you avoid and resolve deadlock situations.

If you want to ensure two statements/transactions will never deadlock you may be able to achieve it by observing which locks each statement consumes using the sp_lock system stored procedure. To do this you have to either be very fast or use an open transaction with a holdlock hint.

---

Notes:

1. Any SELECT statement that needs more than one lock at once can deadlock against an intelligently designed transaction which grabs the locks in reverse order.

why deadlocks cannot be automatically handled by the lock monitor

Deadlock handling can only result in one connection being killed.

This is the only resolution to a deadlock sitution, which happens when 2 process are blocking each other. So neither can proceed and one must be aborted

How to avoid database deadlocks?

A few years wiser, I am revising the accepted answer to state that database deadlocks cannot be prevented.

If you are lucky enough to be able to break down database operations to only interact with a single table at a time (something that isn't always possible) then you are forced to choose between poor performance and the possibility of deadlocks. Pick your poison.

What are common reasons for deadlocks?

Update: This recent MSDN article, Tools And Techniques to Identify Concurrency Issues, might also be of interest

---

Stephen Toub in the MSDN article Deadlock monitor states the following four conditions necessary for deadlocks to occur:

• A limited number of a particular resource. In the case of a monitor in C# (what you use when you employ the lock keyword), this limited number is one, since a monitor is a mutual-exclusion lock (meaning only one thread can own a monitor at a time).

• The ability to hold one resource and request another. In C#, this is akin to locking on one object and then locking on another before releasing the first lock, for example:

lock(a)
{
...
    lock(b)
    {
            ...
    }
}

• No preemption capability. In C#, this means that one thread can't force another thread to release a lock.

• A circular wait condition. This means that there is a cycle of threads, each of which is waiting for the next to release a resource before it can continue.

He goes on to explain that the way to avoid deadlocks is to avoid (or thwart) condition four.

Joe Duffy discusses several techniques
for avoiding and detecting deadlocks,
including one known as lock leveling.
In lock leveling, locks are assigned
numerical values, and threads must
only acquire locks that have higher
numbers than locks they have already
acquired. This prevents the
possibility of a cycle. It's also
frequently difficult to do well in a
typical software application today,
and a failure to follow lock leveling
on every lock acquisition invites
deadlock.

Concurrency issues

Deadlocks are complicated. A deadlock means that at least two sessions have locks and are waiting for one another to release a different lock; since both are waiting, the locks never get released, neither session can continue, and a deadlock occurs.

In other words, A has lock X, B has lock Y, now A wants Y and B wants X. Neither will give up the lock they have until they are finished with their transaction. Both will wait indefinitely until they get the other lock. SQL Server sees that this is happening and kills one of the transactions in order to prevent the deadlock. Snapshot isolation won't help you - the DB still needs to preserve atomicity of transactions.

There is no simple answer anyone can give as to why a deadlock would be occurring. You'll need to profile your application to find out.

Start here: How to debug SQL deadlocks. That's a good intro.

Next, look at Detecting and Ending Deadlocks on MSDN. That will give you a lot of good background information on why deadlocks occur, and help you understand what you're looking at/for.

There are also some previous SO questions that you might want to look at:

• Diagnosing Deadlocks in SQL Server 2005
• Zero SQL deadlock by design

Or, if the deadlocks are very infrequent, just write some exception-handling code into your application to retry the transaction if a deadlock occurs. Sometimes it can be extremely hard (if not nearly impossible) to prevent certain deadlocks. As long as you write transactionally-safe code, it's not the end of the world; it's completely safe to just try the transaction again.

---

Related Topics