Deadlock on two delete statements

Question

Post reply

Deadlock on two delete statements

Mr. Holio

Hall of Fame

Points: 3314

July 24, 2019 at 8:03 pm

#3665858

Hi everyone,

Can anyone advise why would the below statements cause a deadlock and what the permanent fix would be for this? Find attached the table, 2 stored procedures involved and the deadlock graph in xml. Also, why was a "U" lock requested if there are no update statements in either stored procedures? I imagine the select (before the delete) could cause this deadlock but a NOLOCK hint is applied to it so I'm out of ideas. Thanks!!

<?xml version="1.0"?>
<deadlock victim="process26crtd10e0">
  <process-list>

<process id="process26crtd10e0" taskpriority="10" logused="16800" waitresource="KEY: 6:66440041235984644 (99sss20f9898)" waittime="2850" ownerId="65946590090" transactionname="TXN" lasttranstarted="2019-04-12T19:11:33.388" XDES="0x4ff4ere660" lockMode="X" schedulerid="26" kpid="19840" status="suspended" spid="46" sbid="0" ecid="0" priority="-2" trancount="2" lastbatchstarted="2019-04-12T19:11:33.388" lastbatchcompleted="2019-04-12T19:11:33.388" lastattention="1900-01-01T00:00:00.100" clientapp=".Net SqlClient Data Provider" hostname="HST-1" hostpid="9898" loginname="LNTA\lna" isolationlevel="read committed (2)" xactid="56666555400" currentdb="14" currentdbname="AW9" lockTimeout="5000" clientoption1="544400484" clientoption2="111000" databaseName="AW9"><executionStack><frame procname="AW9.dbo.DeleteRecord" line="00" stmtstart="2756" stmtend="2976" sqlhandle="0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
DELETE FROM 
Customers
WHERE 
CustomerID IN (SELECT CustId FROM @CustIds     </frame></executionStack><inputbuf>
Proc [Database Id = 14 Object Id = 98984000054]    </inputbuf></process>

<process id="process8f7f4f5f666" taskpriority="0" logused="0" waitresource="KEY: 6:66440041235984644 (99sss20f9898)" waittime="2850" ownerId="59284663401" transactionname="DELETE" lasttranstarted="2019-04-12T19:11:33.388" XDES="0x909c77c777" lockMode="U" schedulerid="36" kpid="19880" status="suspended" spid="100" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-12T19:11:33.388" lastbatchcompleted="2019-04-12T19:11:33.388" lastattention="1900-01-01T00:00:00.100" clientapp=".Net SqlClient Data Provider" hostname="HST-02" hostpid="2600" loginname="LNTA\lna" isolationlevel="read committed (2)" xactid="16666555400" currentdb="14" currentdbname="AW9" lockTimeout="4294967295" clientoption1="998754100" clientoption2="111000" databaseName="AW9"><executionStack><frame procname="AW9.dbo.DeleteRecord_v2" line="00" stmtstart="1406" stmtend="1716" sqlhandle="0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
DELETE FROM Customers 
WHERE CustomerGUID = @CustomerGUID     </frame></executionStack><inputbuf>
Proc [Database Id = 14 Object Id = 98000225466]    </inputbuf></process>

<process id="process9f564sf940" taskpriority="0" logused="0" waitresource="KEY: 6:72057594538098688 (e963a71503c8)" waittime="2850" ownerId="59284662841" transactionname="DELETE" lasttranstarted="2019-04-12T19:11:33.388" XDES="0x3r544b9898" lockMode="U" schedulerid="2" kpid="19999" status="suspended" spid="113" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-12T19:11:33.388" lastbatchcompleted="2019-04-12T19:11:33.388" lastattention="1900-01-01T00:00:00.100" clientapp=".Net SqlClient Data Provider" hostname="HST-03" hostpid="4194" loginname="LNTA\lna" isolationlevel="read committed (2)" xactid="66666555400" currentdb="14" currentdbname="AW9" lockTimeout="4294967295" clientoption1="998754100" clientoption2="111000" databaseName="AW9"><executionStack><frame procname="AW9.dbo.DeleteRecord_v2" line="00" stmtstart="1406" stmtend="1716" sqlhandle="0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
DELETE FROM Customers 
WHERE CustomerGUID = @CustomerGUID     </frame></executionStack><inputbuf>
Proc [Database Id = 14 Object Id = 98000225466]    </inputbuf></process>


</process-list>
  <resource-list>
    <keylock hobtid="66440041235984644" dbid="14" objectname="AW9.dbo.Customers" indexname="IX_Cust" id="lock988dvvc402" mode="U" associatedObjectId="66440041235984644">
      <owner-list>
        <owner id="process8f7f4f5f666" mode="U" requestType="wait"/>
      </owner-list>
      <waiter-list>
        <waiter id="process26crtd10e0" mode="X" requestType="wait"/>
      </waiter-list>
    </keylock>
    <keylock hobtid="66440041235984644" dbid="14" objectname="AW9.dbo.Customers" indexname="IX_Cust" id="lock988dvvc402" mode="U" associatedObjectId="66440041235984644">
      <owner-list>
        <owner id="process9f564sf940" mode="U"/>
      </owner-list>
      <waiter-list>
        <waiter id="process8f7f4f5f666" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594538098688" dbid="14" objectname="AW9.dbo.Customers" indexname="PK_Customers" id="lock550ff41c682" mode="X" associatedObjectId="72057594538098688">
      <owner-list>
        <owner id="process26crtd10e0" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process9f564sf940" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

CREATE TABLE Customers
(
CustomerGUID UNIQUEIDENTIFIER
, CustomerID INT IDENTITY(1,1) PRIMARY KEY
, TerritoryId INT
, AccountNumber VARCHAR(10)
, CustomerType VARCHAR(1)
, ModifiedDate DATETIME
)
GO

CREATE NONCLUSTERED INDEX [IX_Cust] ON [dbo].[Customers]
(CustomerGUID, TerritoryId, AccountNumber, CustomerType, ModifiedDate)
GO

CREATE PROCEDURE [dbo].[DeleteRecord]
@CustomerIds VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON; 
SET DEADLOCK_PRIORITY -2;
SET LOCK_TIMEOUT 5000;

DECLARE @CustIds TABLE (CustId INT)
INSERT INTO @CustIds (CustId)
SELECT [Value] FROM STRING_SPLIT(@CustomerIds, ',')

BEGIN TRAN TXN;
INSERT INTO 
CustomersRemoved (
CustomerGUID,
CustomerID,
TerritoryID,
AccountNumber,
CustomerType,
ModifiedDate)
SELECT
CustomerGUID,
CustomerID,
TerritoryID,
AccountNumber,
CustomerType,
ModifiedDate
FROM 
Customers WITH(NOLOCK)
WHERE 
CustomerID IN (SELECT CustId FROM @CustIds);

DELETE FROM 
Customers
WHERE 
CustomerID IN (SELECT CustId FROM @CustIds);
COMMIT TRAN TXN;

END
GO

CREATE PROCEDURE [dbo].[DeleteRecord_v2] @CustomerGUID UNIQUEIDENTIFIER
AS
BEGIN

DELETE FROM Customers 
WHERE CustomerGUID = @CustomerGUID

END

__________________________
Allzu viel ist ungesund...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

NOLOCK isn't to prevent locks... it's to prevent from waiting for locks to be released when you're doing reads. You've kind of overridden that because the SELECT is in a transaction and its output is being used as the source of an INSERT.

I'll also state that instead of doing it this way, you could use the OUTPUT clause to save what's being deleted. A properly written DELETE trigger would work, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 2

Thank you, that makes sense. I got confused because the deadlock graph suggested that each process was a "delete". If I leave the query as is and remove the NOLOCK hint, what difference would that make? If I changed the NOLOCK to UPDLOCK would that prevent deadlocks in the future? And why is there a "U" lock on the graph? Is that related to the INSERT query?

I'll take your advice and use the output clause for the delete and use it for the insert. Just looking to get a better understanding of the situation hence asking so many questions. Thanks for your help!!

__________________________
Allzu viel ist ungesund...