System.Data.SqlClient.SqlException – Transaction was deadlocked on lock

The System.Data.SqlClient.SqlException – Transaction was deadlocked on lock is a runtime exception in C# that occurs when two or more transactions in SQL Server are waiting for each other to release locks on resources, resulting in a deadlock. This typically happens when:

Multiple transactions attempt to access the same resources in conflicting orders.
Transactions hold locks on resources for too long.
The database is under heavy concurrent load.

Here’s how you can troubleshoot and fix this issue:

1. Retry the Transaction

Implement a retry mechanism to handle deadlocks gracefully. Example:

   int retries = 3;
   while (retries > 0)
   {
       try
       {
           using (var transaction = connection.BeginTransaction())
           {
               // Execute SQL commands
               transaction.Commit(); // Commit the transaction
               break; // Exit the loop if successful
           }
       }
       catch (SqlException ex)
       {
           if (ex.Number == 1205) // Error number for "Transaction was deadlocked"
           {
               retries--;
               if (retries == 0) throw; // Re-throw the exception if all retries fail
               System.Threading.Thread.Sleep(1000); // Wait before retrying
           }
           else
           {
               throw; // Re-throw other exceptions
           }
       }
   }

2. Optimize Transaction Scope

Minimize the scope and duration of transactions to reduce the likelihood of deadlocks. Example:

   using (var transaction = connection.BeginTransaction())
   {
       // Execute only necessary SQL commands
       transaction.Commit(); // Commit the transaction as soon as possible
   }

3. Access Resources in a Consistent Order

Ensure that all transactions access resources in the same order to avoid circular dependencies. Example:
Always access TableA before TableB in all transactions.

4. Use Locking Hints

Use SQL Server locking hints (e.g., WITH (NOLOCK)) to reduce contention, but be cautious as this can lead to dirty reads. Example:

   SELECT * FROM MyTable WITH (NOLOCK); -- Use NOLOCK hint

5. Analyze Deadlock Graphs

Use SQL Server’s deadlock graph to analyze and identify the root cause of deadlocks. Example:
Enable trace flag 1222 or 1204 to capture deadlock information in the SQL Server log.

Example of Correct Code

using System;
using System.Data.SqlClient;

public class Program
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=MyDatabase;User Id=myUsername;Password=myPassword;";

        int retries = 3;
        while (retries > 0)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        var command1 = new SqlCommand("UPDATE TableA SET Column1 = 'Value1' WHERE Id = 1", connection, transaction);
                        command1.ExecuteNonQuery();

                        var command2 = new SqlCommand("UPDATE TableB SET Column2 = 'Value2' WHERE Id = 1", connection, transaction);
                        command2.ExecuteNonQuery();

                        transaction.Commit(); // Commit the transaction
                        Console.WriteLine("Transaction completed successfully");
                        break; // Exit the loop if successful
                    }
                }
            }
            catch (SqlException ex)
            {
                if (ex.Number == 1205) // Error number for "Transaction was deadlocked"
                {
                    retries--;
                    if (retries == 0) throw; // Re-throw the exception if all retries fail
                    System.Threading.Thread.Sleep(1000); // Wait before retrying
                }
                else
                {
                    throw; // Re-throw other exceptions
                }
            }
        }
    }
}