Unlocking SQL Server Deadlocks: Detect, Handle, and Prevent

Deadlocks are a common case in SQL Server in particular; these are liable to upset the smooth operation of SQL transactions. In typical large-scale systems, deadlocks can affect 0.1% – 1% of transactions resulting in transaction rollbacks and performance slowdowns. In fact, utilizing outdated indexes or poorly optimized queries, may see deadlock incidents increase by as much as 20%.

Proper understanding of the causative agents and their handling is very important to maintain reasonable performance and reliability in SQL Server.

What is SQL Server Deadlock?

A deadlock is an extremely rare condition where two or more transactions acquire mutually incompatible locks on each other’s resources and become unable to proceed without SQL Server intervention. This usually happens in a situation where each transaction requires a resource—basically, some data that is locked by the other transaction.

Simulation of a SQL Server Deadlock

Suppose you have two sessions (SPID 58 and SPID 61) that are trying to update two different rows in a single table. Each session has an exclusive lock on one row. But after that, both sessions want to update the row that the other session has locked, resulting in a deadlock.

SQL Server can clear this deadlock by killing one of the transactions, which we can call the deadlock victim.

Now let’s try a simulation to create a SQL server deadlock through a minimal Java JDBC program:

First, create the required tables in your SQL Server:

Unset

CREATE TABLE Account (AccountID INT PRIMARY KEY, Balance INT NOT NULL);
INSERT INTO Account VALUES (1, 1000);
INSERT INTO Account VALUES (2, 1000);

Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DeadlockSimulator {

    private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDatabase";
    private static final String USER = "username";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        Thread t1 = new Thread(() -> updateBalance(1, 2));
        Thread t2 = new Thread(() -> updateBalance(2, 1));

        t1.start();
        t2.start();
    }

    private static void updateBalance(int firstAccountId, int secondAccountId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            conn.setAutoCommit(false);
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("UPDATE Account SET Balance = Balance - 100 WHERE AccountID = " + firstAccountId);
                // Simulate processing time
                Thread.sleep(500);
                stmt.executeUpdate("UPDATE Account SET Balance = Balance + 100 WHERE AccountID = " + secondAccountId);
                conn.commit();
            } catch (Exception e) {
                conn.rollback();
                System.out.println("Rollback transaction. Error: " + e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
}

After executing the above simulator program, you will eventually encounter an SQL Exception with a description of the SQL Server deadlock error. Here is an example of the error message:

SQL Server Deadlock Exception:

Unset

Rollback transaction. Error: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Common Types of SQL Server Deadlocks

1. Reader-Writer Deadlocks

Definition: These deadlocks arise between a read operation and a data modification operation. For instance, when a SELECT statement reads data, and simultaneously, an UPDATE or DELETE statement tries to modify the same data.

Cause: This type of deadlock is typically caused when one transaction holds a shared lock (due to a read operation) on a resource, while another transaction requests an exclusive lock on the same resource for modification. Neither transaction can proceed, leading to a deadlock situation.

Impact: Reader-writer deadlocks can lead to delays or transaction rollbacks in high-concurrency environments, reducing the overall performance of the SQL Server. One way to mitigate this is by using a row-version-based isolation level like READ COMMITTED SNAPSHOT, which can reduce the need for shared locks and thus avoid deadlocks.

2. Writer-Writer Deadlocks

Definition: Writer-writer deadlocks occur between two or more data modification statements (INSERT, UPDATE, DELETE) that are competing for exclusive locks on the same resource(s).

Cause: These deadlocks are typically caused by two transactions trying to modify the same resource at the same time. Because exclusive locks are required for data modification, the locks cannot be shared, and the transactions wait on each other, resulting in a deadlock.

Impact: Writer-writer deadlocks are more complex and severe since exclusive locks are involved, which prevent any other transaction from accessing the locked resource until the lock is released. This can lead to longer transaction times, performance bottlenecks, and potential rollback of critical data-modifying operations.

3. Key Lookup Deadlocks

Here is the illustration of a SQL Server Key Lookup Deadlock. It shows the circular dependency where one transaction is executing a SELECT query requiring a key lookup, and another transaction is holding an exclusive lock on the clustered index, causing a deadlock. 

Definition: Key lookup deadlocks are a specific subtype of reader-writer deadlocks, occurring when a non-covering index is used, and a key lookup to the clustered index is necessary to retrieve additional column data.

Cause: These deadlocks happen when a SELECT operation using a non-covering index causes a lookup to the clustered index, while another transaction is trying to modify the data in the clustered index. The SELECT query holds a shared lock, and the modification query requires an exclusive lock, leading to a deadlock.

Impact: Key lookup deadlocks can slow down query performance and cause contention between read and write operations. Optimizing queries by creating covering indexes or redesigning queries to avoid clustered index lookups can help mitigate these deadlocks.

4. Parallelism-related Deadlocks

Definition: Parallelism-related deadlocks occur when parallel threads executing a query encounter a deadlock situation among themselves, or when a single parallel query deadlocks with itself due to resource contention.

Cause: This deadlock type can arise due to SQL Server’s parallel query execution, where multiple threads simultaneously access shared resources, leading to circular blocking. A parallel query might also deadlock with itself if its threads are contending for the same resources, especially in complex queries or on large datasets.

Impact: Parallelism-related deadlocks can severely degrade system performance as multiple threads are involved. They can also complicate query execution, making it harder to troubleshoot and resolve. In some cases, reducing the degree of parallelism (MAXDOP) or rewriting queries for better execution plans can help prevent this type of deadlock.

Detecting and Handling Deadlocks in SQL Server

SQL Server uses a system process `LOCK_MONITOR` to the actual deadlocks, under normal circumstances, to occur every 5 seconds.  It would subsequently shut off the transaction that has either done less work or one that’s cheaper to roll back. We can set the deadlock priority to govern by killing which transaction.

SQL Deadlock vs Blocking

Deadlocks often are misunderstood as blocking. Blocking happens due to one session holding a lock needed by another; in turn, the second waits. Whereas blocking can result in big performance issues, deadlocks are resolved automatically by SQL Server so there are no such long waits.

Given the highly transactional nature of some environments, there may be unavoidable deadlocks. However, this may also protect against worse concerns, such as blocking chains. Powerful retry logic in applications can significantly soften the impact of a deadlock.

Troubleshooting Deadlocks in SSMS

Configuring SQL Server Management Studio:

There are a few different methods to detect deadlocks in SQL Server;

• Trace Flags: The trace flags 1204 and 1222 must be enabled to record to the SQL Server error log the information concerning a deadlock.
• SQL Server Profiler: Obsolete tool, use can still trace deadlock events.
• Extended Events: This is the most efficient way. Setting up specific extended event session that traces deadlocks on a very granular level.

Let’s set up the SQL Profiler to detect the root causes of deadlocks.

First, open the SQL Server Management Studio. Then, on the SSMS, under the tools section, select Profiler. Connect it using your SQL Server credentials. A dialog box will then appear and set up the initial configuration.

The Profiler can save the logs to a file or directly into a table. For profiling deadlocks, use the ‘Events Selection’ tab and then select all events. 

In this case, under the ‘Locks’ section, only ‘Deadlock graph’ would be selected to capture data about deadlock incidents in detail and through graphs.

Now, start up the profiler with the ‘Run’ button.

Capturing an SQL Server Deadlock Graph

Capturing a deadlock graph is one of the important steps in the diagnosis and resolution of deadlocks. Starting with SQL Server 2008, users can now directly take deadlock graphs from the extended events system_health session, providing an easier postmortem analysis of deadlocked data.

As we can see from the above graph, both SPIDs 58 and 61 owned an exclusive key lock; a request for an update lock was sent for a row that was already being locked by the other SPID. So here neither session could proceed, and SQL Server has decided to kill SPID 58, rolling back the work so far.

Preventing Deadlocks

Sure, it’s nice for SQL Server to automatically take care of detecting and resolving deadlocks, but of course, that doesn’t mean that a DBA can fail to take them seriously. After all, the deadlock victim may well be a crucial business operation, such that its failure to execute will cause significant disruption.

Several strategies detailing the avoidance of deadlocks:

Consistent Locking Order: Whenever a list of transactions is trying to access resources all of that should be in order.

Optimize Queries: Improve query performance to minimize the duration locks are held.

Unset

CREATE INDEX IX_Account_ID ON Account(AccountID);

Isolation Levels: Using appropriate Isolation Levels use appropriate row-versioning isolation levels of isolation, then this helps minimize lock contention.

Unset

-- Syntax for SQL Server Database
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Short Transactions: Make the transaction duration as short as possible so that locks are not held for long duration

Retry Logic: Robust application-level retry logic in place ensures proper handling of deadlock errors.

Java

int maxRetries = 3;
int attempt = 0;
while (attempt < maxRetries) {
  try {
    // Simulated operation
    performSQLServerOperation();
    return true; // Operation succeeded
  } catch (Exception e) {
    attempt++;
    if (attempt >= maxRetries) {
      System.out.println("Max retries reached ");
      return false; // Operation failed after max retries
    }
    System.out.println("Retrying attempt " + attempt);
  }
}

Use of “WITH (NOLOCK)” to prevent Deadlocks at query level

The hint `WITH (NOLOCK)` on the table allows reading of that table without holding locks on the data, allowing the query to run without causing deadlock. Of course, it presents a risk of reading uncommitted or dirty data. Quite a lot of concurrency might be possible, then, when some level of data inaccuracy can be accepted.

Unset

-- Update query with NOLOCK hint
UPDATE Employees
SET Salary = 70000
FROM Employees WITH (NOLOCK)
WHERE EmployeeID = 1;

-- Select query with NOLOCK hint
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees WITH (NOLOCK);

Pros and Cons of “WITH (NOLOCK)”

Pros:

• Reduces usage of memory.
• Deadlock prevention.
• Ability to make better query performance.

Cons:

• Risk of dirty reads.
• Possibility of missing or repetitive rows.
• May introduce random bugs and index corruption.

Conclusion

Deadlocks occur when two different transactions have incompatible locks on the same resource and become stuck. SQL Server deals with a deadlock by rolling back one of the transactions. Deadlocks can be maddeningly hard to resolve. 

Deadlocks can have a significant impact on user experience and performance. Understanding and resolving SQL Server deadlocks requires effective solutions and strategies. By using the right techniques and tools, you can detect, monitor, and resolve deadlocks in your SQL Server instance. 

For example, you may avoid deadlocks by `WITH (NOLOCK)`, but you must be cautious so that data integrity is not compromised.

Talk to our experts to see how DBSync Data Replication handles SQL Server deadlocks with retry logics and query optimizations.

FAQs