Sql query deadlock on same table with update query

Question

I am getting a deadlock error with below stored procedure - UpdateTestEvents.

Below is the xml deadlock report:

<deadlock>
 <victim-list>
  <victimProcess id="process1128b529468" />
 </victim-list>
 <process-list>
  <process id="process1128b529468" taskpriority="0" logused="0" waitresource="KEY: 7:72057594042777600 (fec90e3a2350)" waittime="2364" ownerId="158290173" transactionname="user_transaction" lasttranstarted="2017-12-17T01:20:45.553" XDES="0x1064ff98408" lockMode="U" schedulerid="9" kpid="6664" status="suspended" spid="57" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-12-17T01:20:45.547" lastbatchcompleted="2017-12-17T01:20:45.543" lastattention="1900-01-01T00:00:00.543" clientapp="EntityFramework" hostname="STAAP8895" hostpid="3616" loginname="XLAPSDBScoring" isolationlevel="read committed (2)" xactid="158290173" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="analytics.dbo.UpdateTestEvents" line="25" stmtstart="1836" stmtend="2132" sqlhandle="0x030007005e4b4b2a97304c0155a5000001000000000000000000000000000000000000000000000000000000">
UPDATE dbo.History
SET Ignore = 0
WHERE Number = @Number
    AND dbo.StringsMatch(@candidate, ACType, DEFAULT) =    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 7 Object Id = 709577566]   </inputbuf>
  </process>
  <process id="process1127e522ca8" taskpriority="0" logused="301092" waitresource="KEY: 7:72057594043039744 (c41e1b4226b6)" waittime="2364" ownerId="158290165" transactionname="user_transaction" lasttranstarted="2017-12-17T01:20:45.447" XDES="0xf8dc5ff8a8" lockMode="U" schedulerid="2" kpid="4888" status="suspended" spid="60" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-12-17T01:20:45.440" lastbatchcompleted="2017-12-17T01:20:45.437" lastattention="1900-01-01T00:00:00.437" clientapp="EntityFramework" hostname="STAAP1493" hostpid="3304" loginname="XLAPSDBScoring" isolationlevel="read committed (2)" xactid="158290165" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="analytics.dbo.UpdateTestEvents" line="32" stmtstart="2370" stmtend="3926" sqlhandle="0x030007005e4b4b2a97304c0155a5000001000000000000000000000000000000000000000000000000000000">
WITH ValidOriginsAndDestinations AS
(
      SELECT Origin FROM dbo.History
     WHERE Ignore = 0
        AND Number = @Number
     UNION ALL
         SELECT Destination FROM dbo.History
     WHERE Ignore = 0
        AND Number = @Number
)
UPDATE fh
SET Ignore = 0
FROM dbo.History AS fh
WHERE Number = @Number
    AND Ignore = 1
    AND 
    (       
        Origin IN (SELECT * FROM ValidOriginsAndDestinations)
        OR Destination IN (SELECT * FROM ValidOriginsAndDestinations)    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 7 Object Id = 709577566]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594042777600" dbid="7" objectname="analytics.dbo.History" indexname="PK_History" id="lockf50c41ac80" mode="X" associatedObjectId="72057594042777600">
   <owner-list>
    <owner id="process1127e522ca8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1128b529468" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594043039744" dbid="7" objectname="xl_analytics_aviation.dbo.History" indexname="IX_History_Number" id="lock1128b5be680" mode="U" associatedObjectId="72057594043039744">
   <owner-list>
    <owner id="process1128b529468" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process1127e522ca8" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

And the stored procedure looks like below:

CREATE PROCEDURE [dbo].[UpdateTestEvents]
    @Number varchar(50)
AS
DECLARE @tolerance decimal(10,10) = 0.15
DECLARE @totalEvents decimal(10,0) = (SELECT COUNT(*) FROM dbo.History fh WHERE fh.Number = @Number)

IF(@totalEvents = 0) RETURN

DECLARE @candidate VARCHAR(50) = 
    (SELECT TOP 1 ACType
    FROM dbo.History AS fh
    WHERE fh.Number = @Number
    GROUP BY ACType
    HAVING (COUNT(*) / @totalEvents) > @tolerance
    ORDER BY MAX(ActualDepartureTime) DESC)
SELECT @candidate

UPDATE dbo.History
SET Ignore = 0
WHERE Number = @Number
    AND dbo.StringsMatch(@candidate, ACType, DEFAULT) = 1;


WITH ValidOriginsAndDestinations AS
(

    SELECT Origin FROM dbo.History
     WHERE Ignore = 0
        AND Number = @Number
     UNION ALL
     SELECT Destination FROM dbo.History
     WHERE Ignore = 0
        AND Number = @Number
)

UPDATE fh
SET Ignore = 0
FROM dbo.History AS fh
WHERE Number = @Number
    AND Ignore = 1
    AND 
    (
        Origin IN (SELECT * FROM ValidOriginsAndDestinations)
        OR Destination IN (SELECT * FROM ValidOriginsAndDestinations)
    );

WITH Comfirmeddt AS
(
    SELECT a.lat, a.long FROM dbo.places AS a
    JOIN dbo.History AS fh
    ON     a.tidentifier = fh.Origin
        OR a.tidentifier = fh.Destination    
    WHERE fh.Number = @Number
    GROUP BY a.tidentifier, a.lat, a.long
)
UPDATE fh
SET Ignore = 0
FROM dbo.History AS fh
JOIN dbo.places AS a
ON a.tidentifier = fh.Origin
    OR a.tidentifier = fh.Destination
WHERE fh.Ignore = 1
AND fh.Number = @Number
AND EXISTS
(
    SELECT * FROM Comfirmeddt AS confirmed   
    WHERE
    (
        a.lat  < confirmed.lat  + 0.5 AND a.lat  > confirmed.lat  - 0.5 AND
        a.long < confirmed.long + 0.5 AND a.long > confirmed.long - 0.5
    )
)

GO

I am getting the below error: An error occurred while executing the command. See the inner exception for details. Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

The index definition for [PK_History] is as below:

ALTER TABLE [dbo].[History] ADD  CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
    [HashCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The Primary Key is HashCode Can someone suggest what I can do on this query to avoid such deadlocks in future.

Please find the table structure below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[History](
    [HashCode] [varchar](50) NOT NULL,
    [FaID] [varchar](50) NOT NULL,
    [Number] [varchar](255) NOT NULL,
    [ActualArrivalTime] [datetime] NULL,
    [ActualDepartureTime] [datetime] NULL,
    [ACType] [varchar](10) NULL,
    [Destination] [varchar](40) NULL,
    [DestinationCity] [varchar](100) NULL,  
    [Origin] [varchar](40) NULL,
    [Ignore] [bit] NOT NULL DEFAULT ((1)),
    [FlNumber] [varchar](255) NULL,
    [DateAdded] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [History] PRIMARY KEY CLUSTERED 
(
    [HashCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[History]  WITH CHECK ADD  CONSTRAINT [FK_Number] FOREIGN KEY([Number])
REFERENCES [dbo].[Number] ([Number])
GO

ALTER TABLE [dbo].[History] CHECK CONSTRAINT [FK_Number]
GO

The call to the stored procedure happends from .NET code and is achieved by entity framework. Below is the skeleton of the call to this stored procedure from application

using (var db = new NumberDbContext())
            {
foreach (var tn in Numbers)
            {
 db.UpdateTestEvents(tailNumber);
}
}

Please find the execution plan of the query below: https://www.brentozar.com/pastetheplan/?id=B1dGzUMQf

Also definition if IX_History_Number index:

CREATE NONCLUSTERED INDEX [IX_History_Number] ON [dbo].[History]
(
    [Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Answer 1

The deadlock is due to queries using different indexes to access the same row(s). One way to avoid this deadlock condition is to use an application lock to serialize access to the same @Number value until the transaction is committed. Note that the proc must be invoked from an explicit transaction with a Transaction lock owner.

CREATE PROCEDURE [dbo].[UpdateTestEvents]
    @Number varchar(50)
AS
DECLARE @return_code int;
EXEC @return_code = sp_getapplock @Resource = @Number, @LockMode = 'Exclusive', @LockOwner = 'Transaction';
IF @return_code NOT IN(0,1)
BEGIN
    RAISERROR('Unexpected sp_getapplock return code is %d',16,1,@return_code);
    RETURN @return_code;
END;
--remainder of proc code here
GO

Answer 2

I would firstly check the isolation level in the database,if snapshot isolation is on, there is less likely to get shared locks in the database.

select name
        , s.snapshot_isolation_state
        , snapshot_isolation_state_desc
        , is_read_committed_snapshot_on
        , recovery_model
        , recovery_model_desc
        , collation_name
    from sys.databases s

To avoid deadlocks while executing query, it is good to issue nolock in this scenario, although some argue that it causes dirty reads, use begin and end for each update statement or use try block.

CREATE PROCEDURE [dbo].[UpdateTestEvents] @Number VARCHAR(50)
AS
BEGIN
    DECLARE @tolerance DECIMAL(10, 10) = 0.15
    DECLARE @totalEvents DECIMAL(10, 0) = (
            SELECT COUNT(*)
            FROM dbo.History fh(NOLOCK)
            WHERE fh.Number = @Number
            )

    IF (@totalEvents = 0)
    BEGIN
        DECLARE @candidate VARCHAR(50) = (
                SELECT TOP 1 ACType
                FROM dbo.History(NOLOCK) AS fh
                WHERE fh.Number = @Number
                GROUP BY ACType
                HAVING (COUNT(*) / @totalEvents) > @tolerance
                ORDER BY MAX(ActualDepartureTime) DESC
                )

        --SELECT @candidate
        UPDATE dbo.History
        SET Ignore = 0
        WHERE Number = @Number
            AND dbo.StringsMatch(@candidate, ACType, DEFAULT) = 1;

        BEGIN
            WITH ValidOriginsAndDestinations
            AS (
                SELECT Origin
                FROM dbo.History(NOLOCK)
                WHERE Ignore = 0
                    AND Number = @Number
                
                UNION ALL
                
                SELECT Destination
                FROM dbo.History(NOLOCK)
                WHERE Ignore = 0
                    AND Number = @Number
                )
            UPDATE fh
            SET Ignore = 0
            FROM dbo.History AS fh
            WHERE Number = @Number
                AND Ignore = 1
                AND (
                    Origin IN (
                        SELECT *
                        FROM ValidOriginsAndDestinations
                        )
                    OR Destination IN (
                        SELECT *
                        FROM ValidOriginsAndDestinations
                        )
                    )
        END

        BEGIN
            WITH AirportsWithConfirmedFlights
            AS (
                SELECT a.lat
                    ,a.long
                FROM dbo.places AS a
                INNER JOIN dbo.History AS fh ON a.tidentifier = fh.Origin
                    OR a.tidentifier = fh.Destination
                WHERE fh.Number = @Number
                GROUP BY a.tidentifier
                    ,a.lat
                    ,a.long
                )
            UPDATE fh
            SET Ignore = 0
            FROM dbo.History AS fh
            INNER JOIN dbo.places AS a ON a.tidentifier = fh.Origin
                OR a.tidentifier = fh.Destination
        END
    END
END

`

• Nolock` and Bad practices

  various documents, best practices suggested to stop using nolock hints as this can cause dirty reads and other affects. But if we test by using nolock hints and the performance and affects and well documented they are much useful. The best work around for this is to alter isolation level in the database. There are several considerations for this aswel.