What is a Deadlock ?
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.
The following graph presents a high level view of a deadlock state where:
• Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
• Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).
• Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.
The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.
Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process
Deadlocks are sometimes called a deadly embrace.
What can cause deadlocks?
• Locks
• Worker threads
• Memory
• Parallel query execution-related resources
• Multiple Active Result Sets (MARS) resources.
SQL Server contains a deadlock detector which is capable of noticing the majority of cases of deadlocks that occur. When it notices a deadlock, it will pick one of the processes and automatically kill it. The client of that process will receive an error message saying that their process was involved in a deadlock and picked as the deadlock victim. The client of the other process will not receive any messages.
It is not necessary, not will it likely be possible, to manually kill a connection involved in a deadlock. The SQL deadlock detector takes care of that.
How to identify a deadlock
The following message is given when
the server resolves a deadlock
situation:
Server: Msg 1205, Level 13, State 50,
Line 1
Transaction (Process ID 55) was
deadlocked on {lock} resources with
another process and has been chosen as
the deadlock victim. Rerun the
transaction.
Deadlocks trace output can be examined in SQL Server log.
Deadlocks can found using System_health extended event
Deadlocks can found Using Server side trace (DBCC TRACEON (1204) )
Predefined notification functionalities to log the deadlock info using service broker, extended events etc.
In SQL Server 2005 and SQL Server 2008, the Deadlock graph trace feature of SQL Server Profiler provides visualization of deadlocks.
Identify deadlock - Please Click1 ::
Identify deadlock - Please Click2::
Typical methods you can use to resolve deadlocks include:
• Adding and dropping indexes.
• Adding index hints.
• Modifying the application to access resources in a similar pattern.
• Removing activity from the transaction like triggers. By default, triggers are transactional.
• Use of try catch
• Keeping transactions as short as possible.
/*
Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:
*/
-- 1) Create Objects for Deadlock Example
USE TEMPDB
CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1
CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1
-- 2) Run in first connection
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1
-- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1
-- 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1
/*
Connection two will be chosen as the deadlock victim
ie.
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
*/
How to Minimize Deadlocks
Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead
Because fewer transactions are:
• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.
To help minimize deadlocks:
• Access objects in the same order.
If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. Using stored procedures for all data modifications can standardize the order of accessing objects.
• Avoid user interaction in transactions.
Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application.
• Keep transactions short and in one batch.
A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.
Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.
• Use a lower isolation level.
Determine whether a transaction can run at a lower isolation level. Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.
• Use a row versioning-based isolation level.
When the READCOMMITTEDSNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.
Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
• Use snapshot isolation.
Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Before a transaction can run under snapshot isolation, the ALLOWSNAPSHOTISOLATION database option must be set ON.
• Use bound connections.
Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.
Here are some tips on how to avoid deadlocking on your SQL Server:
• 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.
Few links wich are studied ::
Deadlocking click
How to resolve a deadlock click