Help needed in resolving dead lock

Question

Post reply

Help needed in resolving dead lock

shamantha4

Old Hand

Points: 352
More actions
June 24, 2017 at 10:16 am

#326954

Hello All,
We are getting frequent deadlocks on one of sql server instance.
Any help\suggestions will be greatly appreciated.
Below is the deadlock info.
2017-06-23 12:05:26.760 spid28s deadlock-list
2017-06-23 12:05:26.760 spid28s deadlock victim=process5dfe2c8
2017-06-23 12:05:26.760 spid28s process-list
2017-06-23 12:05:26.760 spid28s process id=process5dfe2c8 taskpriority=0 logused=0 waitresource=KEY: 20:72057594113163264 (20108c853da6) waittime=4502 ownerId=3403758819 transactionname=UPDATE lasttranstarted=2017-06-23T12:05:21.487 XDES=0x16783b740 lockMode=U schedulerid=3 kpid=91392 status=suspended spid=149 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-06-23T12:05:21.487 lastbatchcompleted=2017-06-23T12:05:21.487 clientapp=.Net SqlClient Data Provider hostname=xyz hostpid=22496 loginname=*** isolationlevel=read committed (2) xactid=3403758819 currentdb=20 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
2017-06-23 12:05:26.760 spid28s executionStack
2017-06-23 12:05:26.760 spid28s frame procname=KSE.dbo.DequeueOrders line=11 stmtstart=204 stmtend=1212 sqlhandle=0x030014003ae5955d944a77018fa300000100000000000000
2017-06-23 12:05:26.760 spid28s UPDATE Orders WITH(UPDLOCK, READPAST)
2017-06-23 12:05:26.760 spid28s SET OrderStatusID = 1
2017-06-23 12:05:26.760 spid28s OUTPUT inserted.OrderId, inserted.ServiceProviderID, inserted.OrderStatusID, inserted.ReprocessCount, inserted.EffectiveDate, inserted.CreateDate, inserted.LastModifiedDate, inserted.SourceName, inserted.OrderXML
2017-06-23 12:05:26.760 spid28s WHERE OrderId in (SELECT TOP(@MaxRows) OrderId
2017-06-23 12:05:26.760 spid28s FROM Orders WITH (UPDLOCK)
2017-06-23 12:05:26.760 spid28s WHERE OrderStatusID = 0 AND
2017-06-23 12:05:26.760 spid28s EffectiveDate <= GETDATE()
2017-06-23 12:05:26.760 spid28s ORDER BY CreateDate)
2017-06-23 12:05:26.760 spid28s inputbuf
2017-06-23 12:05:26.760 spid28s Proc [Database Id = 20 Object Id = 1570104634]
2017-06-23 12:05:26.760 spid28s process id=processe0da7288 taskpriority=0 logused=0 waitresource=KEY: 20:72057594113163264 (e305804383b4) waittime=4738 ownerId=3403759704 transactionname=UPDATE lasttranstarted=2017-06-23T12:05:22.013 XDES=0x1e9810e90 lockMode=U schedulerid=1 kpid=92780 status=suspended spid=128 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-06-23T12:05:22.013 lastbatchcompleted=2017-06-23T12:05:22.013 clientapp=.Net SqlClient Data Provider hostname=XYZ hostpid=8644 loginname=*** isolationlevel=read committed (2) xactid=3403759704 currentdb=20 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
2017-06-23 12:05:26.760 spid28s executionStack
2017-06-23 12:05:26.760 spid28s frame procname=KSE.dbo.DequeueOrders line=11 stmtstart=204 stmtend=1212 sqlhandle=0x030014003ae5955d944a77018fa300000100000000000000
2017-06-23 12:05:26.760 spid28s UPDATE Orders WITH(UPDLOCK, READPAST)
2017-06-23 12:05:26.760 spid28s SET OrderStatusID = 1
2017-06-23 12:05:26.760 spid28s OUTPUT inserted.OrderId, inserted.ServiceProviderID, inserted.OrderStatusID, inserted.ReprocessCount, inserted.EffectiveDate, inserted.CreateDate, inserted.LastModifiedDate, inserted.SourceName, inserted.OrderXML
2017-06-23 12:05:26.760 spid28s WHERE OrderId in (SELECT TOP(@MaxRows) OrderId
2017-06-23 12:05:26.760 spid28s FROM Orders WITH (UPDLOCK)
2017-06-23 12:05:26.760 spid28s WHERE OrderStatusID = 0 AND
2017-06-23 12:05:26.760 spid28s EffectiveDate <= GETDATE()
2017-06-23 12:05:26.760 spid28s ORDER BY CreateDate)
2017-06-23 12:05:26.760 spid28s inputbuf
2017-06-23 12:05:26.760 spid28s Proc [Database Id = 20 Object Id = 1570104634]
2017-06-23 12:05:26.760 spid28s resource-list
2017-06-23 12:05:26.760 spid28s keylock hobtid=72057594113163264 dbid=20 objectname=KSE.dbo.Orders indexname=IX_Orders_OrderStatusID_EffectiveDate id=lock29379ed80 mode=U associatedObjectId=72057594113163264
2017-06-23 12:05:26.760 spid28s owner-list
2017-06-23 12:05:26.760 spid28s owner id=processe0da7288 mode=U
2017-06-23 12:05:26.760 spid28s waiter-list
2017-06-23 12:05:26.760 spid28s waiter id=process5dfe2c8 mode=U requestType=wait
2017-06-23 12:05:26.760 spid28s keylock hobtid=72057594113163264 dbid=20 objectname=KSE.dbo.Orders indexname=IX_Orders_OrderStatusID_EffectiveDate id=lock24f28d600 mode=U associatedObjectId=72057594113163264
2017-06-23 12:05:26.760 spid28s owner-list
2017-06-23 12:05:26.760 spid28s owner id=process5dfe2c8 mode=U
2017-06-23 12:05:26.760 spid28s waiter-list
2017-06-23 12:05:26.760 spid28s waiter id=processe0da7288 mode=U requestType=wait

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Can you post the deadlock graph?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

shamantha4 Old Hand Points: 352 More actions · Answer 2

Ed Wagner - Saturday, June 24, 2017 1:33 PM
Can you post the deadlock graph?

Hello Ed Wagner,

We didn't capture deadlock graph,We captured only above deadlock chain.
By looking at the above Deadlock chain,Could you please suggest me a resolution for this issue.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 3

shamantha4 - Sunday, June 25, 2017 12:39 AM
Ed Wagner - Saturday, June 24, 2017 1:33 PM
Can you post the deadlock graph?
Hello Ed Wagner,
We didn't capture deadlock graph,We captured only above deadlock chain.
By looking at the above Deadlock chain,Could you please suggest me a resolution for this issue.

We know nothing of the table involved. Could you at least post the CREATE TABLE statement being sure to include any and all constraints, indexes, and triggers. I have an idea but it's a total waste of time to bring it up without knowing those things. I also need to know what the average CPU, Duration, and Logical Reads for that bit of code is and whether or not that bit of code is embedded in an explicit BEGIN TRANSACTION. It would also be good to know how many rows are currently in the table.

--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)

crow1969 Hall of Fame Points: 3001 More actions · Answer 4

What I am getting out of the listing above, is that you have two instances of the procedure KSE.dbo.DequeueOrders running at the same time. These two procedures are colliding on the index IX_Orders_OrderStatusID_EffectiveDate.

The update statement causing the problem is
UPDATE Orders WITH(UPDLOCK, READPAST) SET OrderStatusID = 1 OUTPUT inserted.OrderId, inserted.ServiceProviderID, inserted.OrderStatusID, inserted.ReprocessCount, inserted.EffectiveDate, inserted.CreateDate, inserted.LastModifiedDate, inserted.SourceName, inserted.OrderXML WHERE OrderId in (SELECT TOP(@MaxRows) OrderId FROM Orders WITH (UPDLOCK) WHERE OrderStatusID = 0 AND EffectiveDate <= GETDATE() ORDER BY CreateDate)

The questions I would look into are:

Should two of these procedures be running at the same time?
Are the statistics on the index out of date?
Does the procedure run longer than it has in the past?
Should this code be re-written as a service broker application? Looks like most of the details would lie in the OrderXML field, already.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 5

Jeff Moden - Monday, June 26, 2017 11:22 AM
shamantha4 - Sunday, June 25, 2017 12:39 AM
Ed Wagner - Saturday, June 24, 2017 1:33 PM
Can you post the deadlock graph?
Hello Ed Wagner,
We didn't capture deadlock graph,We captured only above deadlock chain.
By looking at the above Deadlock chain,Could you please suggest me a resolution for this issue.
We know nothing of the table involved. Could you at least post the CREATE TABLE statement being sure to include any and all constraints, indexes, and triggers. I have an idea but it's a total waste of time to bring it up without knowing those things. I also need to know what the average CPU, Duration, and Logical Reads for that bit of code is and whether or not that bit of code is embedded in an explicit BEGIN TRANSACTION. It would also be good to know how many rows are currently in the table.

Still waiting on a reply... I'm pretty sure we can help but need the information I asked for.

--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)

shamantha4 Old Hand Points: 352 More actions · Answer 6

Jeff Moden - Tuesday, June 27, 2017 7:25 PM
Jeff Moden - Monday, June 26, 2017 11:22 AM
shamantha4 - Sunday, June 25, 2017 12:39 AM
Ed Wagner - Saturday, June 24, 2017 1:33 PM
Can you post the deadlock graph?
Hello Ed Wagner,
We didn't capture deadlock graph,We captured only above deadlock chain.
By looking at the above Deadlock chain,Could you please suggest me a resolution for this issue.
We know nothing of the table involved. Could you at least post the CREATE TABLE statement being sure to include any and all constraints, indexes, and triggers. I have an idea but it's a total waste of time to bring it up without knowing those things. I also need to know what the average CPU, Duration, and Logical Reads for that bit of code is and whether or not that bit of code is embedded in an explicit BEGIN TRANSACTION. It would also be good to know how many rows are currently in the table.
Still waiting on a reply... I'm pretty sure we can help but need the information I asked for.

Hello All,
Thanks a ton for your reply.
below is the create table script and the index definition.

CREATE TABLE [dbo].[Orders](
    [OrderId] [uniqueidentifier] NOT NULL,
    [ServiceProviderID] [int] NOT NULL,
    [OrderStatusID] [int] NOT NULL,
    [ReprocessCount] [tinyint] NOT NULL CONSTRAINT [DF_Orders_ReprocessCount] DEFAULT ((0)),
    [EffectiveDate] [datetime] NOT NULL CONSTRAINT [DF_Orders_EffectiveDate] DEFAULT (getdate()),
    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Orders_CreateDate] DEFAULT (getdate()),
    [LastModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Orders_LastModifiedDate] DEFAULT (getdate()),
    [SourceName] [varchar](200) NULL,
    [OrderXML] [varbinary](max) NULL,
    [Action] [varchar](50) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_OrderStatus] FOREIGN KEY([OrderStatusID])
REFERENCES [dbo].[OrderStatus] ([OrderStatusID])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_OrderStatus]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [CK_Orders_OrderStatusID] CHECK (([OrderStatusID]=(9) OR [OrderStatusID]=(1) OR [OrderStatusID]=(0)))
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [CK_Orders_OrderStatusID]
GO

--index definition
CREATE NONCLUSTERED INDEX [IX_Orders_OrderStatusID_EffectiveDate] ON [dbo].[Orders]
(
[OrderStatusID] ASC,
[EffectiveDate] 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)
GO

Will capture the deadlock graph and will post it soon

RohitK25 SSC Enthusiast Points: 198 More actions · Answer 7

Put the subquery into temporary table and then join the temporary table with update statement. Do the same for both update statements.
SELECT TOP (@MaxRows) OrderId
INTO #TempOrders
FROM Orders WITH (UPDLOCK)
WHERE OrderStatusID = 0
AND EffectiveDate <= GETDATE()
ORDER BY CreateDate

UPDATE Orders WITH(UPDLOCK, READPAST)
SET OrderStatusID = 1
OUTPUT inserted.OrderId,
   inserted.ServiceProviderID,
   inserted.OrderStatusID,
   inserted.ReprocessCount,
   inserted.EffectiveDate,
   inserted.CreateDate,
   inserted.LastModifiedDate,
   inserted.SourceName,
   inserted.OrderXML
WHERE OrderId IN
(
select orderid from #TempOrders
);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

RohitK25 - Wednesday, June 28, 2017 12:52 AM
Put the subquery into temporary table and then join the temporary table with update statement. Do the same for both update statements.
SELECT TOP (@MaxRows) OrderId
INTO #TempOrders
FROM Orders WITH (UPDLOCK)
WHERE OrderStatusID = 0
    AND EffectiveDate <= GETDATE()
        ORDER BY CreateDate
UPDATE Orders WITH(UPDLOCK, READPAST)
SET OrderStatusID = 1
OUTPUT inserted.OrderId,
   inserted.ServiceProviderID,
   inserted.OrderStatusID,
   inserted.ReprocessCount,
   inserted.EffectiveDate,
   inserted.CreateDate,
   inserted.LastModifiedDate,
   inserted.SourceName,
   inserted.OrderXML
WHERE OrderId IN
(
select orderid from #TempOrders
);

It's close, but won't work. Here's why:

-- This technique will only work within a transaction. The locks 
-- obtained in the acquisition query are only retained because of it.
BEGIN TRAN

-- The first statement (acquisition) must use UPDLOCK hint to lock qualifying rows,
-- and must use READPAST hint to ignore rows which might qualify but which are
-- locked by another process.
SELECT TOP(@MaxRows) OrderId
INTO #TempOrders
FROM Orders WITH (UPDLOCK, READPAST)
WHERE OrderStatusID = 0
AND EffectiveDate <= GETDATE()
ORDER BY CreateDate

-- No locking hints are required in the UPDATE statement
UPDATE o
SET OrderStatusID = 1
OUTPUT inserted.OrderId,
   inserted.ServiceProviderID,
   inserted.OrderStatusID,
   inserted.ReprocessCount,
   inserted.EffectiveDate,
   inserted.CreateDate,
   inserted.LastModifiedDate,
   inserted.SourceName,
   inserted.OrderXML
FROM Orders o
INNER JOIN #TempOrders t ON t.OrderId = o.OrderId;

COMMIT TRAN

-- You may find this necessary depending upon
-- the proportion of rows being locked, and their distribution within the table
ALTER TABLE Orders SET (LOCK_ESCALATION = DISABLE);

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sergiy SSC Guru Points: 110206 More actions · Answer 9

[OrderId] [uniqueidentifier] NOT NULL, ....... CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderId] ASC )
Nothing much to save here.
PK on an uniqueidentifier column, and it's clustered.
You've been asking specifically for deadlocks, so you've got them.

You must have a clustered index on a datetime column.
Which one - I can't tell from the data available.
You're using different dates to align the orders:
WHERE .... EffectiveDate <= GETDATE() ORDER BY CreateDate)
Which one of them is actually defining the sequence of orders in the system?
That one must be chosen as a clustered index on the table, and only that one must be mentioned in the query.

And with a GUID column used for WHERE IN you better use TABLOCKX hint for that UPDATE.
READPAST would not allow to pick the rows which are being updated by another transaction, but it would allow to pick the rows which are being selected for an UPDATE by another transaction. And the strict ORDER BY rule will make sure both simultaneously executed WHERE IN subqueries will pick up exactly the same set of OrderID's and pass them to the UPDATE's. Which would guarantee a deadlock almost every single time when there are 2 or more concurrent executions.

But I would suggest to exclude the GUID column from the selection altogether.
With a correct clustered index this query should be quite fast:

UPDATE TOP(@MaxRows) Orders SET OrderStatusID = 1 OUTPUT inserted.OrderId, inserted.ServiceProviderID, inserted.OrderStatusID, inserted.ReprocessCount, inserted.EffectiveDate, inserted.CreateDate, inserted.LastModifiedDate, inserted.SourceName, inserted.OrderXML WHERE OrderStatusID = 0 AND EffectiveDate <= GETDATE() -- Assuming EffectiveDate is chosen for a clustered index

This won't guarantee earliest orders will be updated first, but I understand the point is to update all of them which match the criteria as soon as possible.
This query will do just that.
Again - assuming you've got a correct clustered index on the table.

_____________
Code for TallyGenerator