Minimizing deadlocks

A deadlock occurs when two or more sessions permanently block each other
by each session having a lock on a resource that the other session(s) are trying to lock.

For example:

Transaction A acquires a shared lock on row 1.
Transaction B acquires a shared lock on row 2.

Transaction A now requests an exclusive lock on row 2,
and is blocked until transaction B finishes and releases the shared lock it has on row 2.

Transaction B now requests an exclusive lock on row 1,
and is blocked until transaction A finishes and releases the shared lock it has on row 1.

We can group the deadlocks into the following categories:

• Reader-Writer deadlocks
• Writer-Writer Deadlocks
• Key Lookup Deadlocks
• Parallelism-related deadlocks
• Range Scans and serializable deadlocks
• Partition escalation deadlocks

Now let`s see what we can do to minimize and/or completely prevent the deadlocks:

1) Access the shared objects/tables in the same order.

    Ensure that all the resources are accessed in the same order all the time.
    For example, if two concurrent transactions both started by requesting
    the lock for row 1 and later on requesting the lock for row 2.
    This will simply be a blocking situation rather than a deadlock because
    transaction 1 will never be deadlocking transaction 2 as resource locks
    will never be held out of order.

2) Reduce a query's lock footprint by making the query as efficient as possible.

     Make sure the involved queries use row lock and not page/table locks.
     Try to read less data, avoid index scans.

3) In case of Key Lookups deadlocks,
    there are 2 possible solutions:

    a) Include columns to the existing non-clustered index, which will eliminate the lookup.
    b) In case the clustered index is small enough,
        Use FORCESCAN hint on the SELECT to force a clustered index scan

    Key Lookup deadlocks are the most common ones, here is an example:

    The SELECT uses the NC index to find a qualifying row(s)
    While holding a Shared lock on the NC index, it needs to jump over
    to the clustered index and retrieve some columns that aren't part of the NC index.
    While it's doing this, the UPDATE is busy doing a seek on the CL index.
    It finds a row, locks it and modifies it. But because one of the columns
    being modified is a key column in the NC index, it then has to move to the NC index
    and update that index, too. This requires a second X key lock on the NC index.
    So, the SELECT ends up blocked waiting for the UPDATE to release his X lock
    on the clustered index, while the UPDATE winds up blocked and waiting for the
    SELECT to release his S lock on the NC index.

4) Force the blocking to occur at an earlier point 

   Sometimes the best way to prevent a deadlock is to force the blocking to occur
   at an earlier point in one of the two transactions.

   For example, if you force process  A to be blocked by process B at the very
   beginning of A’s transaction, it may not have a chance to acquire the lock
   resource that later ends up blocking process B.
   HOLDLOCK and UPDLOCK hints can be useful for this.

5) Deadlock caused by parallelism
  
    Happens on parallel query plan executions,
    you will see "parallelism exchange event" in the deadlock graph/xml.

    One of the possible solution here is to add MAXDOP 1 hint to force the queries go serial,
    Another solution is to see if add an index can help to reduce the query cost
    and make the query to be executed in a single thread.

6) Deadlock between two MERGE statements
    

     Try to add HOLDLOCK hint to the MERGE statement,
     If it doesn't help, try to rewrite the MERGE to INSERT/UPDATE statements 


7) Use a lower isolation level

   Check if the transaction can run at a lower isolation level and 
   use the minimum necessary transaction isolation level.

   Under read committed, SQL Server will hold shared locks for a shorter duration than
   a higher isolation level, such as repeatable read/serializable.

8) Use RCSI

    Snapshot isolation uses row versioning, which does not use shared locks during read operations.
    Snapshot isolation levels can minimize deadlocks that occur between read and write operations.

9) Implement your own manual locking (sp_getapplock)

 
    If possible, consider to lock/unlock objects manually by yourself,
    This can be done by using sp_getapplock procedure.

    Add sp_getapplock to the 2 involved stored procedures or queries
    Something like below:

 
    begin tran
             sp_getapplock @Resource = 'MyTran', @LockMode = 'Exclusive'

             ... your code ...

    commit

   Only one client will be able to run this code at a time and it will be serialized.
   Don't forget to use sp_releaseapplock

UPDATE (U) locks behavior in MERGE statement vs UPDATE statement

When analyzing deadlocks with MERGE and UPDATE statements,
I have noticed that UPDATE (U) Locks behave in a different way in

an UPDATE statement and in a MERGE statement.

Deadlock graph between MERGE and UPDATE statements on the same PK value

Let`s assume you want to update 2 rows in a table 

and the search for these rows using NC index,
the question is whether SQL Server will apply U locks one by one

and release each U lock after each row or it will apply U locks 

for all matched rows and only after the update will release them.

Let`s create a test table that will have 3 fields with CL and NC index:

CREATE TABLE dbo.Customers

(

CustomerID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

EmailAddress VARCHAR(50) NOT NULL,

FullName VARCHAR(50) NOT NULL

)

GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Customers_EmailAddress ON dbo.Customers(EmailAddress)

GO

INSERT INTO dbo.Customers (EmailAddress,FullName)
SELECT 'dan@gmail.com' AS EmailAddress,'Dan' AS FullName
UNION ALL
SELECT 'rita@gmail.com' AS EmailAddress,'Rita' AS FullName
UNION ALL
SELECT 'greg@gmail.com' AS EmailAddress,'Greg' AS FullName
UNION ALL
SELECT 'mike@gmail.com' AS EmailAddress,'Mike' AS FullName
UNION ALL
SELECT 'bob@gmail.com' AS EmailAddress,'Bob' AS FullName

GO

Customers Table

So we have the table with CL index on CustomerID and NC index on EmailAddress.

What we want to do is to update the FullName field of 2 rows that

will be filtered by EmailAddress column.

One way to do it is to use a simple UPDATE statement as below:

UPDATE c

SET c.FullName='N/A'

FROM dbo.Customers AS c

WHERE c.EmailAddress IN ('rita@gmail.com','mike@gmail.com')

GO

The second way we can use MERGE statement as below:

MERGE INTO dbo.Customers AS TGT USING ( VALUES( 'rita@gmail.com'),('mike@gmail.com')) 

AS SRC (EmailAddress)

ON SRC.EmailAddress= TGT.EmailAddress

WHEN MATCHED THEN 

UPDATE SET TGT.FullName='N/A'

WHEN NOT MATCHED THEN 

INSERT VALUES( 'rita@gmail.com','N/A'); 

Now let`s see what locks are acquired and when they released
if we run the UPDATE statement (only 'X' and 'U' locks shown in the screenshot).
SQL Server will first go to NC index to search by EmailAddress,
apply U locks for our 2 records and then go to CL index for the UPDATE.

In the screenshot above we have value 99 in Res0 column representing NC index

and value 98 representing CL index.

According to the locks sequence above,
We can see is that U lock applied to the first NC index key (Row #1, ID =4),
then U lock applied to CL index key(Row #2, ID =6),
then U lock converted to X lock on the CL key (Row #3, ID =8),
and finally, U lock released on NC index key (Row #4, ID =11).

Then the same order for the second row.

Now let`s see the locks sequence in case of the MERGE statement:

We can see that SQL Server first acquired U locks on the both NC keys
(2 keys, one after other), Rows #1,2,3,4 with Res0 =99.
Then applied U lock on CL index key(Row #5, ID =14)
Then converted the U lock to X lock of that CL index key(Row #6, ID =16)
and then applied U lock for the second key on CL index and converted it to an X lock
(Rows #7,8, IDs =20,22)

In summary, in the case of the UPDATE statement, SQL Server
acquired U locks on NC index one by one, first for 1 record
and then for the second record and released the U lock on NC index
after updating the CL index.
And in case of the MERGE statement, SQL server first acquired U locks
for both records in the NC index and did not release them until and of the transaction.

Key Range Locks (RangeS-U) in the default Isolation Level Read Committed

As we all know, SQL Server applies Key Range locks in the Serializable isolation level to prevent Phantom Reads,
But recently, in a deadlock graph, I have encountered Key Range (RangeS-U) locks in the default Isolation Level - Read Committed.

My question was, how come, why?
Well, the answer is: foreign key constraints with Cascading Deletes.

Once you have FK defined with “ON DELETE CASCADE” and you delete records from the parent table, SQL Server will use Key Range Locks at the child table to prevent insertion of new records during the Cascading Delete operation.

So now it makes sense why you can see the Key Range Locks (RangeS-U) in the default Isolation Level Read Committed:)