Deadlock Detection in SQL CLR
Usually, talking about the differences between running code under the CLR vs. running under SQL CLR focuses on functionality that either doesn’t work or is difficult to use in a safe and reliable manner. However, one feature that SQL Server actually adds to the CLR environment is deadlock detection.
Joe Duffy mentions this in his article, No More Hangs, about advanced CLR techniques to detect and resolve deadlocks. One of his methods is to use the CLR Hosting interfaces to write a custom CLR Host to handle all the locking primitives so he can analyze them to check if deadlock has occurred. This is essentially the same method that SQL Server uses to detect deadlocks, except rather than using a separate deadlock detection algorithm for lock requests coming from the CLR, we translate them to the standard SQL locks provided by SOS.
If you compile the following program as an executable and run it, not a whole lot happens. The program deadlocks as expected and leaves you staring at the blinking cursor, wondering what to do. However, if you create Method1 and Method2 as SQL Stored Procedures and run them at the same time from separate connections, you’ll see that SQL Server automatically detects the deadlock and kills one of them for you.
public class DeadlockSample
{
public static readonly object a = new object();
public static readonly object b = new object();
[SqlProcedure]
public static void Method1()
{
lock(a)
{
Thread.Sleep(2000);
lock (b) { SqlContext.Pipe.Send("This means Method2 was killed!"); }
}
}
[SqlProcedure]
public static void Method2()
{
lock(b)
{
Thread.Sleep(2000);
lock (a) { SqlContext.Pipe.Send("This means Method1 was killed!"); }
}
}
public static void Main()
{
Thread thread1 = new Thread(new ThreadStart(Method1));
Thread thread2 = new Thread(new ThreadStart(Method2));
thread1.Start();
thread2.Start();
thread1.Join();
thread2.Join();
}
}
Having SOS handle all locking is especially useful as it allows for deadlock detection to work even for the case of inproc data access where a CLR lock might be deadlocked against a SQL lock. In the following example, 2 methods both want to take a CLR lock and update a column in a SQL table, but requesting them in a different order leads to deadlock.
create table table1(c int)
insert into table1 values(1)
[SqlProcedure]
public static void LockAndUpdate()
{
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("update table1 set c = 2", conn);
cmd.Transaction = conn.BeginTransaction();
lock (a)
{
Thread.Sleep(2000);
cmd.ExecuteNonQuery();
SqlContext.Pipe.Send("This means UpdateAndLock was killed!");
}
cmd.Transaction.Commit();
}
}
[SqlProcedure]
public static void UpdateAndLock()
{
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("update table1 set c = 2", conn);
cmd.Transaction = conn.BeginTransaction();
cmd.ExecuteNonQuery();
Thread.Sleep(2000);
lock (a)
{ SqlContext.Pipe.Send("This means LockAndUpdate was killed!"); }
cmd.Transaction.Commit();
}
}
One important aspect to keep in mind when dealing with deadlock detection in SQL CLR is that SQL does not explicitly kill your thread with a ThreadAbortException but merely throws a regular exception so that you can catch it and deal with the problem if you are prepared to handle it. This also means, however, that poor programming practices, such as catching all exceptions, might cause you to dismiss the exception without handling it properly. If you catch the exception and retry without releasing the deadlocked resources then it is likely that you'll only deadlock again.
Here is the section from BOL, Detecting and Ending Deadlocks:
"When working with CLR, the deadlock monitor automatically detects deadlock for synchronization resources (monitors, reader/writer lock and thread join) accessed inside managed procedures. However, the deadlock is resolved by throwing an exception in the procedure that was selected to be the deadlock victim. It is important to understand that the exception does not automatically release resources currently owned by the victim; the resources must be explicitly released. Consistent with exception behavior, the exception used to identify a deadlock victim can be caught and dismissed."
-- Steven Hemingray