How to minimize Deadlock

Deadlocks can be prevented by one or more of the following methods:

• Adding missing indexes to support faster queries
• Dropping unnecessary indexes which may slow down INSERTs for example
• Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)
• Adding index hints to queries
• Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int
• Modifying the stored procedures to access tables in a similar pattern
• Keeping transactions as short and quick as possible: "mean & lean"
• Removing unnecessary extra activity from the transactions like triggers
• Removing JOINs to Linked Server (remote) tables
• Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables
• Setting MAXDOP=1 solves deadlocking in some cases

Difference between blocking and deadlocks in sql server 2008

BLOCKING occurs when two connections need access to same piece of data concurrently and one connection is blocked because at a particular time, only one connection can have access.
 (e.g., process X is updating a row and has an exlusive lock on it and process Y wants to read that row... Y cannot put a shared lock on the row due to X's exclusive lock, so Y is "blocked" and must wait for X to finish).


DEADLOCK occurs when one connection is blocked and waiting for a second to complete his work, but on other side, second connection is also waiting for first connection to release the lock. So that they're waiting on each other (that is, they're "blocking" each other). Since neither can proceed until the other releases its lock on some resource, the SQL Engine will choose one of the processes to kill (known as a 'deadlock victim'). 


That is why, one should be clear that locking is integral part of SQL Server to handle concurrency, blocking is bad when one connection/transaction is waiting unnecessary for a long time, and deadlocking is a phenomenon which should never occur.

SQL Server blocking and deadlocks

SQL Server blocking

SQL Server blocking occurs when one connection (user process or application process) places a lock on a table (or a number of rows) and a second connection attempts to read or modify the data under the lock. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.

SQL Server deadlocks

The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.

Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.

Blocks escalating to deadlocks

The following diagram shows the sequence of events leading to a deadlock. Consider two applications (A1, A2) accessing two different table (T1, T2):

Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements

Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements

Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the transaction) but has to wait for A2 to release its lock

At this point, a block is created since A2 is blocking A1

Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can finish its own transaction)

A deadlock is created since two connections have blocked one another. SQL Server automatically resolves the deadlock by choosing one of the connections as a deadlock victim and killing it.

The more blocking happens on SQL Server the less concurrency the system. A certain amount of short blocks are unavoidable but too many blocks for longer periods of time can degrade performance and increase the chances of SQL Server deadlocks.

Deadlock

Problem

I am getting this error very frequently.

Runtime error '-2147467259(80004005)'

Transaction (Process Id 81) was deadlock on lock resources with another process and has been chosen as the deadlock victim.Rerun the transaction.

Solution

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

• Transaction A acquires a share lock on row 1.
  
  
• Transaction B acquires a share lock on row 2.
  
  
• Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
  
  
• Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.
  
  

Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. This allows the other task to complete its transaction. The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

You need to find out what these processes are to minimize/eliminate the chance they will dead lock. To do this you can run a profile trace (see:http://msdn.microsoft.com/en-us/library/ms188246.aspx) or you can use the sp_blocker_pss08 stored procedure to gather the information you need - you can go here for that:http://support.microsoft.com/kb/271509. You might also want to run a profile trace at the same time the sp_blocker_pss08 is running to gather what stored procedures (and the parameters they are passed) and sql batches are running during that time as well.

check out these tips:

http://www.mssqltips.com/tip.asp?tip=1036

http://www.mssqltips.com/tip.asp?tip=1210

http://www.mssqltips.com/tip.asp?tip=1222

http://www.mssqltips.com/tip.asp?tip=2130

http://www.mssqltips.com/tip.asp?tip=1234

A Deadlock Occurrence and Resolution

http://www.sqlservercentral.com/articles/deadlock/64315/

We are using MSSQL for our site which a large number of users visit at the same time. Our problem is that we encounter the "deadlock" problem so often when the number of these visitors increase. Is there anything you could recommend us to get rid of this problem?

There are many solutions to this problem. But you have to see the business requirement and I can suggest you some standard solutions.

1. Access objects in the same order.
2. Avoid user interaction in transactions.
3. Keep transactions short and in one batch.
4. Use a low isolation level.
5. Use locking hints like NOLOCK (Check Business Requirement)
6. Use FAST_Forward / Read Only cursors
7. Try to get as minimum rows as possible.
8. Try to minimize the use of triggers
9. Make sure that all heavy-processing logic is in SP only.
10. Check normalization

What is a deadlock?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks.
A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Description of handling deadlock conditions in SQL Server

A deadlock is a condition where 2 (or more) processes attempt to access a resource that the other holds a lock on. Since each process has a request for the other's resource, neither process can be completed. When a deadlock is detected, SQL Server rolls back the transaction that has the least processing time and returns error message 1205 to the application. Error message 1205 terminates the current batch and rolls back the current transaction.


In some instances, a deadlock condition will cause a DB-Library (DB-Lib) command (such as dbsqlexec, dbsqlok, or dbresults) to return FAIL. It is always the responsibility of the program to check the return codes from each DB-Lib command. If FAIL is returned by one of these DB-Lib commands, the program should cancel the batch and not attempt to continue. In some cases, it is possible to continue execution of subsequent commands in the batch. However, because a deadlock situation occurred and the command that caused it was rolled back, later commands in the batch will probably fail with a more serious error, such as an object not found. In other instances, a deadlock condition will not cause a DB-Lib command to return FAIL. To handle this condition, the program must check for message 1205 in the message handler and use the dbsetuserdata function to communicate this to your application (an example can be found in Chapter 4 "DB-Library Functions" in the SQL Server "Programmer's Reference for C" under dbsetuserdata). The program must then check for the deadlock indicator after every DB-Library call and should cancel the batch if a deadlock is detected. While it may seem unnecessary to cancel a batch after receiving a 1205 deadlock message, it is necessary because the server does not always abort the batch in a deadlock situation. If the batch is not canceled, any attempt to submit a new batch may result in a DB-Library error 10038 "Results Pending." You can find a description of deadlocking and an example of how to detect a deadlock condition in "Appendix E Maximizing Consistency and Concurrency" in the SQL Server for Windows NT "Programmer's Reference for C".


Ref: http://support.microsoft.com/kb/118552