Thursday, November 27, 2008

SQL Server deadlock!!!!!

You should encounter deadlocks in SQL Server infrequently. That is because an enterprise class database system like SQL server does lot of work behind the scene to make sure this does not happen. But when you encounter one, this is what you should be doing….
1. Remember deadlock happens in following cases…..
Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.
Locks. Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.
Worker threads. A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result. For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. This results in a deadlock.
Memory. When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.
Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.
2. Reproduce the deadlock condition so that you can debug…..
This is easier said than done. If you can reproduce it every time that’s great, if not, and if your application has logs that may help. Read point 1 carefully again and you will get some idea as to what parameter to modify so that you can increase chance of reproducing it.
3. Amidst all your trials and turbulences ALWAYS run profiler and trace on
Start profiler and run Deadlock Graph trace. Also you can enable Trace Flag 1204 so that SQL server error log files shows all the information related to deadlock.
4. Analyze deadlock graph
This is the easiest thing. You would see both the process and resources due to which deadlock has happened.
To resolve deadlock you have to change code so that you can avoid one of the process statement, use mutex so that two processes runs sequentially or modify statement so that same resources are not used.
One of the strangest deadlock I had seen (and I have seen few) when apparently two innocent statements created deadlock. Upon investigation (as outlined above) it was found out that the deadlock was happening at system table level (not my fault), we modified one of the statements so that internally it does not use system table in question.
5. To fix (technicality) or not to fix (Project Management).
As always a call needs to be taken whether to fix deadlock based on frequency of occurrence, actual impact of deadlock on functionality, impact of fix on coding/testing etc. So don’t get disappointed if you have worked hard to resolve deadlock and your manager decides not to fix. You can always take heart from the fact that you have just solved one of the complex problems in computer application.

Until me write about something else…. Happy programming…….

Credits: I am not a geek, so the credit goes to Microsoft documentation :-)