DeadLock In SQL Server, Monitoring and Prevention

Deadlock is a nightmare for SQL DBA, since it stuck our database!!

So here i will explain what is Deadlock and how can we avoid it in realtime SQL situations.

A common issue with SQL Server is deadlocks. A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward. When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process or processes can move forward. 

In rare scenarios, I have heard of deadlocks caused by SQL Statements executing in parallel however I cannot reproduce it. Neither side is willing to step aside for the other processes. 

A database server should be able to service requests from a large number of concurrent users. When a database server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block.
A blocked process usually resolves itself when the first process releases the resource.

A more serious condition, called a deadlock or fatal embrace, occurs when the first process is also waiting on a resource held by the second process (see below). Each process is stuck waiting for the other to release a resource. Unless one process times out, the lock won’t clear itself.

When a deadlock occurs

These are the following conditions when a deadlock can occur:

1. Mutual Exclusion - A resource is assigned to one process. Requests are delayed until the resources are released.
2. Hold and wait - One process holds one resource and also waits for another.
3. No preemption - Only a process can hold a resources when it's released from another process.
4. Circular wait - The first transaction has a lock on some database object that the other transaction wants to access and vice versa, causing a deadlock by building a circle of dependencies.

 
Deadlock information can be captured in the SQL Server Error Log or by using Profiler / Server Side Trace.

Trace Flags

If you want to capture this information in the SQL Server Error Log you need to enable one or both of these trace flags.

• 1204 - this provides information about the nodes involved in the deadlock
• 1222 - returns deadlock information in an XML format

You can turn on each of these separately or turn them on together.

Extended Events:

SQL Server extended events are the light weight event driven (fired) data collection for SQL Servers. It is much beneficial in case of tracing in the busy servers. It’s fundamentally an advanced version of Profiler Trace. 

Profiler / Server Side Trace

Profiler works without the trace flags being turned on and there are three events that can be captured for deadlocks. Each of these events is in the Locks event class.

• Deadlock graph - Occurs simultaneously with the Lock:Deadlock event class. The Deadlock Graph event class provides an XML description of the deadlock.
• Lock: Deadlock - Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns.
• Lock: Deadlock Chain - Is produced for each of the events leading up to the deadlock.

Although we cannot completely avoid deadlocks but they can be minimised by following the tips below:

• Ensure the database is normalized properly because bad database design can increase the number of deadlocks to occur inside database.
• Deadlocks can also occur if the resources are not acquired in some well-defined order because 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 Department table and then on the Sales table, one transaction is blocked on the Department table until the other transaction is completed. After the first transaction commits or rolls back, the second continues, and a deadlock does not occur.
• Do not allow users to input the data during transactions. Update all the data before the transaction begins.
• Avoid cursors if possible because same transaction locking rules will apply to SELECT statement within a cursor definition that applies to any other SELECT statement. You can control the transaction locks for cursors definition SELECT statement by choosing the correct isolation and/or using the locking hints specified in the FROM clause. The locks are held until the current transaction for both cursors and independent SELECT statements ends. When SQL Server is running in auto commit mode, each individual SQL statement is a transaction and the locks are freed when the statement finishes. If SQL Server is running in explicit or implicit transaction mode, then the locks are held until the transaction is either committed or rolled back.
• Reduce the time a transaction takes to complete by making 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, control locks escalation by using the ROWLOCK or PAGLOCK because transaction locks in SQL Server consumes memory resources and when number of locks increases, memory decreases. If the percentage of memory used for transaction lock exceeds a certain threshold then SQL Server convert the fine-grained locks page or row) into a coarse-grained locks (table locks) which is also known as lock escalation. Lock escalation reduces the overall number of locks being held on the SQL Server instance, reducing the lock memory usage. While finer grained locks do consume more memory, they also can improve concurrency, as multiple queries can access unlocked rows.
• Consider using the NOLOCK hint where possible because when you run the query against table in SQL Server default isolation level it will put a lock on table and any other query that will try to access the table will have to wait for the lock to be released. This is fine if your table is small but the things get slow if your table is big. A way to get around that is to add a NOLOCK hint to the query, which will override locking of the whole table and allow access to it to other queries. You have to be careful with using NOLOCK hint because it will allow for dirty reads for example if you execute the query with NOLOCK and the query runs for 30 seconds. Because the table is not locked, during these 30 seconds other queries may have added new rows that the query will not return or modified or deleted rows that were already read. It may also have read data from other queries that were uncommitted and could have been rolled back. So keep that in mind when using the NOLOCK hint.

You can see some more details on below link -
http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

If you have any problems related to Deadlock just reach me by comments!!!
Your suggesions and comments are most welcome!!!

Thankyou :)