Lesson Learned #513: Using SQL Copilot and Python Retry Logic to Resolve Deadlocks in Azure SQL DB

A few weeks ago, I worked with a service request where our customer has a Python application that was reporting a deadlock with the following error message:

INFO:root:Connected to the Database in dbname - Thread id 21132 - 0.0454 seconds --- 
INFO:root:Connected to the Database in dbname - Thread id 39856 - 0.4066 seconds --- 
ERROR:root:Transaction 2 failed: ('40001', '[40001] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') 
INFO:root:Transaction 1 completed successfully.

In many of our articles and public information like these ones Analyze and prevent deadlocks in Azure SQL Database or Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database or Managed Instance?, we review ways to detect and resolve deadlocks by analyzing their details. At this time, I would like to share my experience using Copilot in Azure SQL Database to pinpoint the deadlock.

After the error received, we started using with several prompts like Give me the deadlock that occurred in my database recently or Provide detailed information about the last deadlock in my database, including involved tables and queries and we got the following information: 

Based on the internal Azure SQL telemetry, the following deadlock query hashes have been identified in your database within the past 2 hours:

Deadlock Details

Query Hash 1: 0xD57AFD9584A2C053
Query Text 1: (@2 tinyint,@1 varchar(8000))UPDATE [dbo].[MS_TableA_MS] SET [ValueA] = @1 WHERE [ID]=@2;

Query Hash 2: 0xBEAB84B27CE1F534
Query Text 2: (@2 tinyint,@1 varchar(8000))UPDATE [dbo].[MS_TableB_MS] SET [ValueB] = @1 WHERE [ID]=@2;

At this point we identified the queries involved on this deadlock and we executed a second prompt that we used was: Suggest options to prevent or mitigate the recent deadlock in my database and we got very useful information about multiple options to prevent or mitigate the deadlock, like, Index Optimization, Force a Query Plan, Rewriting Transactions, Retry Logic in Application or T-SQL Code, etc..

For application retry logic, we traced the source code that triggered the issue and added a retry mechanism to handle the deadlock. 

Here’s a simple example of what we implemented. Basically, this small code, creates two tables, runs two transactions in two different threads and depending on the value of retry parameter retry or not the deadlock operation. 

 # Constants for table name used in the deadlock scenario
TABLE_A_NAME = "dbo.MS_TableA_MS"
TABLE_B_NAME = "dbo.MS_TableB_MS"
TABLE_A_COLUMN = "ValueA"
TABLE_B_COLUMN = "ValueB"
ITEM_ID = 1
TABLE_A_INITIAL_VALUE = 'Value A1'
TABLE_B_INITIAL_VALUE = 'Value B1'

def simulate_deadlock(retry=False, retry_attempts=3):
    """
    Simulates a deadlock by running two transactions in parallel that lock resources.
    If retry is enabled, the transactions will attempt to retry up to a specified number of attempts.
    """
    setup_deadlock_tables()

    thread1 = threading.Thread(target=run_transaction_with_retry, args=(deadlock_transaction1, retry_attempts,retry))
    thread2 = threading.Thread(target=run_transaction_with_retry, args=(deadlock_transaction2, retry_attempts,retry))

    thread1.start()
    thread2.start()
    thread1.join()
    thread2.join()

def run_transaction_with_retry(transaction_func, attempt_limit,retry):
   attempt = 0
   while attempt < attempt_limit:
     try:
        transaction_func(retry)
        break
     except pyodbc.Error as e:
        if 'deadlock' in str(e).lower() and retry:
           attempt += 1
           logging.warning(f"Deadlock detected. Retrying transaction... Attempt {attempt}/{attempt_limit}")
           time.sleep(1) 
        else:
           logging.error(f"Transaction failed: {e}")
           break

def setup_deadlock_tables():
    """
    Sets up the tables required for the deadlock simulation, using constants for table and column names.
    """
    conn, dbNameReturn = ConnectToTheDB()
    if conn is None:
        logging.info('Error establishing connection to the database. Exiting setup.')
        return
    cursor = conn.cursor()
    try:
        cursor.execute(f"""
        IF OBJECT_ID('{TABLE_A_NAME}', 'U') IS NULL
        CREATE TABLE {TABLE_A_NAME} (
            ID INT PRIMARY KEY,
            {TABLE_A_COLUMN} VARCHAR(100)
        );
        """)
        
        cursor.execute(f"""
        IF OBJECT_ID('{TABLE_B_NAME}', 'U') IS NULL
        CREATE TABLE {TABLE_B_NAME} (
            ID INT PRIMARY KEY,
            {TABLE_B_COLUMN} VARCHAR(100)
        );
        """)
        
        cursor.execute(f"SELECT COUNT(*) FROM {TABLE_A_NAME}")
        if cursor.fetchone()[0] == 0:
            cursor.execute(f"INSERT INTO {TABLE_A_NAME} (ID, {TABLE_A_COLUMN}) VALUES ({ITEM_ID}, '{TABLE_A_INITIAL_VALUE}');")
        
        cursor.execute(f"SELECT COUNT(*) FROM {TABLE_B_NAME}")
        if cursor.fetchone()[0] == 0:
            cursor.execute(f"INSERT INTO {TABLE_B_NAME} (ID, {TABLE_B_COLUMN}) VALUES ({ITEM_ID}, '{TABLE_B_INITIAL_VALUE}');")
        
        conn.commit()
        logging.info("Tables prepared successfully.")
    except Exception as e:
        logging.error(f"An error occurred in setup_deadlock_tables: {e}")
        conn.rollback()
    finally:
        conn.close()

def deadlock_transaction1(retry=False):
    conn, dbNameReturn = ConnectToTheDB()
    if conn is None:
        logging.info('Error establishing connection to the database. Exiting transaction 1.')
        return
    cursor = conn.cursor()
    try:
        cursor.execute("BEGIN TRANSACTION;")
        cursor.execute(f"UPDATE {TABLE_A_NAME} SET {TABLE_A_COLUMN} = 'Transaction 1' WHERE ID = {ITEM_ID};")
        time.sleep(2) 
        cursor.execute(f"UPDATE {TABLE_B_NAME} SET {TABLE_B_COLUMN} = 'Transaction 1' WHERE ID = {ITEM_ID};")
        conn.commit()
        logging.info("Transaction 1 completed successfully.")
    except pyodbc.Error as e:
        logging.error(f"Transaction 1 failed: {e}")
        conn.rollback()
        if 'deadlock' in str(e).lower() and retry:
            raise e  # Rethrow the exception to trigger retry in run_transaction_with_retry
    finally:
        conn.close()

def deadlock_transaction2(retry=False):
    conn, dbNameReturn = ConnectToTheDB()
    if conn is None:
        logging.info('Error establishing connection to the database. Exiting transaction 2.')
        return
    cursor = conn.cursor()
    try:
        cursor.execute("BEGIN TRANSACTION;")
        cursor.execute(f"UPDATE {TABLE_B_NAME} SET {TABLE_B_COLUMN} = 'Transaction 2' WHERE ID = {ITEM_ID};")
        time.sleep(2)  
        cursor.execute(f"UPDATE {TABLE_A_NAME} SET {TABLE_A_COLUMN} = 'Transaction 2' WHERE ID = {ITEM_ID};")
        conn.commit()
        logging.info("Transaction 2 completed successfully.")
    except pyodbc.Error as e:
        logging.error(f"Transaction 2 failed: {e}")
        conn.rollback()
        if 'deadlock' in str(e).lower() and retry:
            raise e  # Rethrow the exception to trigger retry in run_transaction_with_retry,
    finally:
        conn.close()