Reduce Deadlocks

This business case is closely related to another post: Retry Execution (Even on Deadlocks!)

Business Case: A multi-client application frequently updates data. Occasionally, transactions fail with resulting loss of information and application instability. The application team has requested a solution in the database layer.

Environment: It has been determined that deadlock on lock resources is the primary cause of the failed transactions.

Approach: While there are a handful of ways to help mitigate locked resource deadlocks, in this instance we will modify the database transaction isolation level to reduce the likelihood of deadlocks. This mitigation strategy is appropriate when there is reasonable certainty that locked resources are not logically locked on the same record.

Demonstration Preparation: For demonstration purposes, first we will create two tables with a handful of records in each. We will then create update queries that, when run back to back in separate processes, will produce a deadlock situation. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- If the first table already exists, drop it
IF OBJECT_ID('DB.dbo.TableOne') IS NOT NULL
    DROP TABLE DB.dbo.TableOne
;

-- Create the first table
CREATE TABLE DB.dbo.TableOne (
    ID INT)
;

-- Populate the first table with data
INSERT INTO DB.dbo.TableOne
VALUES
    (1)
    , (2)
    , (3)
;

-- If the second table already exists, drop it
IF OBJECT_ID('DB.dbo.TableTwo') IS NOT NULL
    DROP TABLE DB.dbo.TableTwo
;

-- Create the second table
CREATE TABLE DB.dbo.TableTwo (
    ID INT)
;

-- Populate the second table with data
INSERT INTO DB.dbo.TableTwo
VALUES
    (10)
    , (11)
    , (12)
;

Now we will create two queries that, when run within a few seconds of each other in separate processes, will produce a locked resource deadlock.

-- This transaction should be run in one query window (process);
-- note the update statements are innocuous and do not overlap
-- with the other transaction; nevertheless, a deadlock will be
-- created
BEGIN TRANSACTION
    UPDATE DB.dbo.TableOne
    SET ID = 1
    WHERE ID = 1
    ;
    -- Delay execution for three seconds, to give
    -- the second transaction time to start
    WAITFOR DELAY '00:00:03'
    ;
    UPDATE DB.dbo.TableTwo
    SET ID = 11
    WHERE ID = 11
    ;
COMMIT TRANSACTION


-- This transaction should be run in a second query window (process);

-- note the update statements are innocuous and do not overlap
-- with the other transaction; nevertheless, a deadlock will be
-- created
BEGIN TRANSACTION
    UPDATE DB.dbo.TableTwo
    SET ID = 12
    WHERE ID = 12
    ;
    -- Delay execution for three seconds, to give
    -- the first transaction time to create the
    -- deadlock
    WAITFOR DELAY '00:00:03'
    ;
    UPDATE DB.dbo.TableOne
    SET ID = 2
    WHERE ID = 2
    ;
COMMIT TRANSACTION

When the two transactions above are run within three seconds of each other in separate windows (processes), it will create a deadlock and SQL Server will decide the second transaction (based on execution order) is the victim and cancel its execution.

Solution: To allow both transactions to run, we can change the transaction isolation level. In this case, we will choose SNAPSHOT isolation, which means that any data handled by a transaction will be the data that was committed at the time the transaction begins. This level of isolation can be dangerous if transactions and/or data integrity issues are not managed through the application or by other means. Again, this approach is most suitable when there is reasonable certainty that locking is not due to simultaneous interaction with individual records (rather, that the lock is due to simultaneous interaction at the table level, but not at the record level; or, more specifically, at the column level depending on transaction purpose).

First, lets confirm what the current transaction isolation level is. This can be done by running the DBCC user options command.

DBCC USEROPTIONS;

In the result set, note the isolation level. If the database was configured with default settings, the isolation level is “read committed”. In order to change the isolation level to SNAPSHOT, we first must ensure the database is set to allow SNAPSHOT isolation.

ALTER DATABASE DB
SET ALLOW_SNAPSHOT_ISOLATION ON
;

Now we can set the transaction isolation level to SNAPSHOT.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Running the two transactions above, which formerly resulted in the second being killed as the deadlock victim, will now result in both completing successfully.

For more information in transaction isolation levels, refer to MSDN.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: