How to create a .Net SqlTransaction deadlock with SQL Server
Today I’ll show you an example on how to create DeadLocks with .Net and SQL Server.
Normally this is something you would like to avoid, but I thought that if there is an example that shows this is in a simple way then there is a simple way to understand what is going on.
And also to potentially test out different solutions and debug stuff since DeadLocks usually are of the intermittent type of errors and thus are not easy to catch.
First, what is a deadlock? I’ll take the liberty to quote the following document:
“SQL Server 2008 Books Online (May 2009) – Deadlocking”
https://msdn.microsoft.com/en-us/library/ms177433.aspx
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.
In a ‘normal’ application you may be using several threads and several transactions; this may work fine for most of the time since execution is so fast.
I.e. the transactions complete so fast that the locks are released before the next transaction kicks in.
But when hitting a certain load (on the application or the server) then there is a possibility that the transactions end up in a deadlock situation.
So, first create the tables and stored procedures needed for this:
-- Create two tables that will be lockin each other.
create table DLTOne (userid int primary key, username nvarchar(10))
create table DLTTwo (userid int primary key, username nvarchar(10))
-- Add some data that will be edited
insert into DLTOne values (1, 'Mike')
insert into DLTOne values (2, 'Fred')
insert into DLTTwo values (1, 'Paul')
insert into DLTTwo values (2, 'John')
-- Create two stored procedures used for updating the respective table.
create procedure UpdateDLTOne @uid int, @uname nvarchar(10)
as begin
set nocount on;
update DLTOne set username = @uname where userid = @uid;
end
create procedure UpdateDLTTwo @uid int, @uname nvarchar(10)
as begin
set nocount on;
update DLTTwo set username = @uname where userid = @uid;
end
--drop procedure UpdateDLTOne
--drop procedure UpdateDLTTwo
--drop table DLTOne
--drop table DLTTwo
Then create a new C# console .Net application. What will happen here is the following:
. Two transactions will run in their own threads, this is because if they were to run on the same thread, then they would be execute sequentially and no deadlock would occur.
. I’ve also paused the threads so that we are guaranteed to get a deadlock.
So, execution wise.
. First thread starts, then there is a 500 ms pause to let this transaction start before the second thread starts.
. In the first thread we start a transaction and execute the first stored procedure (UpdateDLTOne). We now have a share lock on the first row in DLTOne.
. We pause the execution in the first thread allowing the second thread to start.
. In the second thread we start a transaction and execute the second stored procedure (UpdateDLTTwo). We now have a share lock on the first row in DLTTwo.
. We pause the execution in the second thread allowing the first thread to continue.
. The first thread now continues and executes the second stored procedure.
This will request an exclusive lock on the first row in DLTTwo, however this is blocked until the transaction in the second thread finishes and releases its lock on this row.
. At this stage the second thread will continue and execute first stored procedure (UpdateDLTOne).
This will request an exclusive lock on the first row in DLTOne, however this is blocked until the transaction in the first thread finishes and releases its lock on this row.
And there you go, you have a deadlock and the following SqlException will show.
In thread: ThreadA
Executing procedure UpdateDLTOne in thread ThreadA
In thread: ThreadB
Executing procedure UpdateDLTTwo in thread ThreadB
Executing procedure UpdateDLTTwo in thread ThreadA
Executing procedure UpdateDLTOne in thread ThreadB
System.Data.SqlClient.SqlException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
namespace DeadLock
{
class Program
{
String connectionString = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
String procOne = "UpdateDLTOne";
String procTwo = "UpdateDLTTwo";
static void Main(string[] args)
{
Program p = new Program();
Thread threadA = new Thread(new ThreadStart(p.TransactionAThread));
threadA.Name = "ThreadA";
threadA.Start();
Thread.Sleep(500);
Thread threadB = new Thread(new ThreadStart(p.TransactionBThread));
threadB.Name = "ThreadB";
threadB.Start();
}
public void TransactionAThread()
{
String threadName = Thread.CurrentThread.Name;
Console.WriteLine("In thread: {0}", threadName);
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand cmd = GetStoredProcedureCommandWithParameters();
cmd.Connection = con;
SqlTransaction tranA = con.BeginTransaction("TransactionA");
cmd.Transaction = tranA;
try
{
cmd.CommandText = procOne;
cmd.Parameters[0].Value = 1;
cmd.Parameters[1].Value = "aaaa";
Console.WriteLine("Executing procedure {0} in thread {1}", cmd.CommandText, threadName);
cmd.ExecuteNonQuery();
Thread.Sleep(1000);
cmd.CommandText = procTwo;
cmd.Parameters[0].Value = 2;
cmd.Parameters[1].Value = "bbbb";
Console.WriteLine("Executing procedure {0} in thread {1}", cmd.CommandText, threadName);
cmd.ExecuteNonQuery();
tranA.Commit();
con.Close();
}
catch (Exception ex)
{
tranA.Rollback();
Console.WriteLine(ex);
}
}
}
public void TransactionBThread()
{
String threadName = Thread.CurrentThread.Name;
Console.WriteLine("In thread: {0}", threadName);
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand cmd = GetStoredProcedureCommandWithParameters();
cmd.Connection = con;
SqlTransaction tranB = con.BeginTransaction("TransactionB");
cmd.Transaction = tranB;
try
{
cmd.CommandText = procTwo;
cmd.Parameters[0].Value = 2;
cmd.Parameters[1].Value = "bbbb";
Console.WriteLine("Executing procedure {0} in thread {1}", cmd.CommandText, threadName);
cmd.ExecuteNonQuery();
Thread.Sleep(1000);
cmd.CommandText = procOne;
cmd.Parameters[0].Value = 1;
cmd.Parameters[1].Value = "aaaa";
Console.WriteLine("Executing procedure {0} in thread {1}", cmd.CommandText, threadName);
cmd.ExecuteNonQuery();
tranB.Commit();
con.Close();
}
catch (Exception ex)
{
tranB.Rollback();
Console.WriteLine(ex);
}
}
}
public SqlCommand GetStoredProcedureCommandWithParameters()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pUid = new SqlParameter("@uid", SqlDbType.Int);
SqlParameter pUname = new SqlParameter("@uname", SqlDbType.NVarChar, 10);
cmd.Parameters.Add(pUid);
cmd.Parameters.Add(pUname);
return cmd;
}
}
}
As mentioned earlier, this post is not intended to solve deadlock issues. More a way to show by example how these could happen.
Some more information here:
"SQL Server 2008 Books Online (May 2009) - Detecting and Ending Deadlocks"
https://msdn.microsoft.com/en-us/library/ms178104.aspx
.NET Framework Class Library - SqlTransaction Class
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx