SQL Deadlock.

By /

Deadlock occurs when two or more transactions are waiting for each other to release locks on resources, resulting in a situation where none of the transactions can proceed.

Real-world example where deadlock commonly occur

Online-Shopping

When multiple users attempt to purchase the same limited stock item simultaneously a deadlock can occur during inventory check and payment processing.

  • Inventory Table : Tracks the available quantity of each item.
  • Payments Table: Stores payment status for each order.

Deadlock scenario

Transaction A: User 1 places an item in the cart, locking a row in the Inventory Table to update available stock, then tries to confirm the payment by locking a row in the Payments Table.

Transaction B: User 2 initiates a payment for the same item, locking a row in the Payments Table first, and then tries to check inventory by locking a row in the Inventory Table.

Since both transactions are waiting for each other to release a lock, a deadlock occurs, causing one of the orders to fail.

Result: Deadlock

Neither transaction can proceed, creating a deadlock. The database will detect this circular waiting pattern and choose one transaction (say, Transaction A) to terminate, allowing the other (Transaction B) to complete.

The terminated transaction (Transaction A) will be rolled back, and the application will receive an error message indicating a deadlock. The error message will suggest retrying the transaction.

Deadlock resolution in SQL involves techniques and strategies that databases use to detect, prevent, or resolve deadlocks when they occur. Here are some common deadlock resolution methods in SQL:

Deadlock Detection and Resolution

When a deadlock is detected, the database will terminate one of the transactions involved in the deadlock, allowing the other transaction proceed. This terminated transaction is known as the deadlock victim.

Example:
In SQL Server, when a deadlock is detected, the system automatically:

  • Selects a “victim” transaction to be rolled back.
  • Rolls back this transaction to release its locks, allowing other transactions to proceed.

The application running the transaction receives an error message, such as:

   Msg 1205, Level 13, State 45
   Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim.

The application should be designed to handle this error and retry the operation.

Deadlock Prevention Techniques

Preventing deadlocks requires modifying transaction design.

Here are common techniques:

a) Ensure Consistent Lock Ordering

For example, if multiple transactions need to access TableA and TableB, always access TableA first and then TableB for each transaction. This reduces the risk of circular waiting and potential deadlocks.

b) Using NOLOCK or Read Uncommitted Isolation Level for Read-Only Operations

  • For transactions that do not require absolute accuracy in reads (e.g., for reporting purposes), you can use the NOLOCK hint in SQL Server or the Read Uncommitted isolation level. This prevents the transaction from placing shared locks on rows, which minimizes the chance of deadlocks.
  • Example in SQL Server: SELECT * FROM TableA WITH (NOLOCK); Note: Using NOLOCK can lead to dirty reads, where data may be read that is not yet committed, so it should be used carefully.

c) Minimize Transaction Scope and Duration

  • Keep transactions as short as possible, and avoid holding locks for long durations. This reduces the chance of deadlocks.
  • Avoid user input or long-running operations (e.g., file I/O) within transactions, as these can hold locks open longer than necessary.

d) Set Lock Timeouts

  • Some RDBMS allow you to set a lock timeout, so a transaction will wait only a specified time for a lock before failing. This can prevent indefinite waiting but will result in an error if the timeout is reached.
  • In SQL Server, you can set the lock timeout:
    sql SET LOCK_TIMEOUT 5000; -- Timeout after 5 seconds

Deadlock Handling with Application-Level Retry Logic

Since deadlocks are often inevitable in high-concurrency systems, implementing retry logic is common practice. When a deadlock error is detected, the application can catch the error, roll back the transaction, wait briefly, and attempt the transaction again.

Example in Pseudo Code:

max_retries = 3
retry_count = 0

while retry_count < max_retries:
    try:
        # Begin transaction
        # Execute SQL statements
        # Commit transaction
        break  # Exit loop if successful
    except DeadlockError:
        retry_count += 1
        if retry_count >= max_retries:
            raise  # Propagate error if max retries exceeded
        else:
            time.sleep(0.5)  # Wait briefly before retrying

Optimizing Isolation Levels

Different isolation levels can influence locking behavior. Lower isolation levels reduce lock contention but may lead to less consistent reads. Higher levels avoid dirty reads but increase the likelihood of deadlocks.

Common Isolation Levels:

  • Read Committed: Prevents dirty reads (default in many systems).
  • Repeatable Read: Ensures consistency for the duration of the transaction but can increase locking.
  • Serializable: Strongest isolation, but can cause the most locking and deadlock potential.

Example of Isolation Level Change in SQL Server:

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN TRANSACTION;
   -- SQL Statements
   COMMIT;

Using the lowest necessary isolation level for transactions can help reduce deadlocks.

Use of Optimistic Concurrency Control

In some cases, you can avoid locks by using optimistic concurrency control. This approach involves:

  • Not locking data during a read.
  • Checking data for changes just before updating it to confirm it hasn’t been modified by another transaction.

If the data has changed, the transaction can be rolled back and retried. Some systems, such as PostgreSQL, support optimistic locking natively using MVCC (Multi-Version Concurrency Control), which helps reduce deadlocks by allowing transactions to work on snapshots of data instead of holding locks.

Summary of Deadlock Resolution Techniques
  • Automatic Detection: Allow the database to detect deadlocks and handle them by rolling back a victim.
  • Prevention Strategies: Use consistent lock ordering, minimize lock scope, use lower isolation levels, and NOLOCK hints when possible.
  • Retry Logic: Implement retry logic in the application to handle deadlock errors gracefully.
  • Optimistic Concurrency: Use optimistic locking techniques or MVCC to avoid holding locks.

Using a combination of these techniques can effectively reduce and resolve deadlocks in SQL systems.

Scroll to Top