Another deadlock problem

Last Post 06 May 2008 04:23 PM by SQLUSA. 12 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
trans53
New Member
New Member

--
04 May 2008 08:26 PM
Hi all,

Every time stored procedures below execute at the same time the deadlock happened and it seems that
xx_GetOrdersFromDeliveryRequest is always a victim.
What can i do in this case?


Node:1 0
PAG: 9:1:607834 CleanCnt:2 Mode: U Flags: 0x2 0
Grant List 2:: 0
Owner:0x844a35c0 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0 0
SPID: 122 ECID: 0 Statement Type: UPDATE Line #: 52 0
Input Buf: RPC Event: xx_GetOrdersFromDeliveryRequest;1 0
Requested By: 0
ResType:LockOwner Stype:'OR' Mode: IX SPID:113 ECID:0 Ec0x70E735B0) Value:0x9adf0ea0 Cost0/18A8) 0

Node:2 0
PAG: 9:1:564231 CleanCnt:2 Mode: IX Flags: 0x2 0
Grant List 3:: 0
Owner:0x800a3240 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:113 ECID:0 0
SPID: 113 ECID: 0 Statement Type: UPDATE Line #: 21 0
Input Buf: RPC Event: code from xx_deliveryRequestIntOrderId;1 0
Requested By: 0
ResType:LockOwner Stype:'OR' Mode: U SPID:122 ECID:0 Ec0xA0C995B0) Value:0x72107820 Cost0/0) 0

Victim Resource Owner: 0
ResType:LockOwner Stype:'OR' Mode: U SPID:122 ECID:0 Ec0xA0C995B0) Value:0x72107820 Cost0/0) 0


Code from xx_GetOrdersFromDeliveryRequest sp:

UPDATE dbo.deliveryrequest
SET releasetime = DATEADD(ss, durationinseconds, GETDATE()),
batchid = @batchid
FROM dbo.deliveryrequest
INNER JOIN
(
SELECT TOP 100 deliveryrequestid FROM dbo.deliveryrequest WITH (UPDLOCK)
WHERE deliverypDate >= @startdate
AND deliveryDate <= @EndDate
AND NOT EXISTS (SELECT deliveryrequestid FROM dbo.ignoredelivery WITH (NOLOCK)
WHERE deliveryrequest.requestid = ignoredelivery.requestid)
ORDER BY deliverypDate
) request_top ON dbo.deliveryrequest.requestid = request_top.requestid



Code from xx_deliveryRequestIntOrderId sp:


UPDATE dbo.deliveryrequest
SET
ReleaseTime = DATEADD(ss, @ReleaseDelay, GETDATE())
WHERE OrderID = @OrderID


The dbo.deliveryrequest table has a clustered index on requestid column and non-clustered on Orderid column.

Thank you
SQLUSA
New Member
New Member

--
04 May 2008 11:28 PM
To avoid deadlocks:

1. Dynamic tables should be dbreindexed with FILLFACTOR = 80 nightly
2. Dynamic tables should be thin & fixed (no varchars)
3. Transactions should be thin

This is too FAT for a transaction:
===========================================
UPDATE dbo.deliveryrequest
SET releasetime = DATEADD(ss, durationinseconds, GETDATE()),
batchid = @batchid
FROM dbo.deliveryrequest
INNER JOIN
(
SELECT TOP 100 deliveryrequestid FROM dbo.deliveryrequest WITH (UPDLOCK)
WHERE deliverypDate >= @startdate
AND deliveryDate <= @EndDate
AND NOT EXISTS (SELECT deliveryrequestid FROM dbo.ignoredelivery WITH (NOLOCK)
WHERE deliveryrequest.requestid = ignoredelivery.requestid)
ORDER BY deliverypDate
) request_top ON dbo.deliveryrequest.requestid = request_top.requestid
================================================

Come up with a different solution.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/


trans53
New Member
New Member

--
05 May 2008 01:13 PM
Thank you for an answer,

I just checked and the primary key is on requestid(clustered). Also, all parameters are matching the column datatypes and orderID will only return one row. I also checked the estimated execution plan and it returned index seeks for both stored procedures.

The isolation level of both sessions is sql default(read comitted).
SQLUSA
New Member
New Member

--
05 May 2008 10:08 PM
Thanks Andy for your advice.

Now I give you an advice.

Take a job as production DBA for 5 years.

You have good ideas, but you are not practical, too theoritical.

Example: you, high-paid consultants, go to a shop and say, auto-shrink is not good because it causes fragmentation. Yet production DBA-s pick auto-shrink in 70% of the shops, because THEY VALUE automation over fragmentation. Because they have to run the shop long after the high-paid consultant gone.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
05 May 2008 10:19 PM
This is not a good structure in a transaction:

SELECT TOP 100 deliveryrequestid FROM dbo.deliveryrequest WITH (UPDLOCK)
WHERE deliverypDate >= @startdate
AND deliveryDate <= @EndDate
AND NOT EXISTS (SELECT deliveryrequestid FROM dbo.ignoredelivery WITH (NOLOCK)
WHERE deliveryrequest.requestid = ignoredelivery.requestid)
ORDER BY deliverypDate

This makes the transaction too cumbersome, slow, prone to deadlock.

Change table design to streamline the UPDATE.
Or restucture it by taking out everything what can be done outside (prior) the transaction.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
trans53
New Member
New Member

--
06 May 2008 06:37 AM
i run display execution plan on the proc since i can't run the actual in production and it seems that
SELECT deliveryrequestid FROM dbo.ignoredelivery WITH (NOLOCK) is doing index scan on dbo.ignoredelivery.

i need to get permissions to modify this in prod., but can you explain please how modfying column deliveryrequestid with a * or a 1 can help?

Another question: do i really need UPDLOCK hint if this update wrapped inside begin transaction.
The begin transaction is not inside the stored procedure but sometimes developers use that in application code.I am not sure in this case.

Thanks
SQLUSA
New Member
New Member

--
06 May 2008 07:01 AM
quote:

Originally posted by: trans53
i run display execution plan on the proc since i can't run the actual in production and it seems that
SELECT deliveryrequestid FROM dbo.ignoredelivery WITH (NOLOCK) is doing index scan on dbo.ignoredelivery.






What a big surprise Andy!

Isn't that the statement I picked as PROBLEMATIC without having any access to the production server?

Basically what a DBA can do is indexing.

Normally the developers would have to redo the logic. Database designer to redesign (if any around).

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
trans53
New Member
New Member

--
06 May 2008 07:08 AM
Kalman, the deliveryrequestid is also a primary key(clustered) on dbo.ignoredelivery so the index is already in place.
SQLUSA
New Member
New Member

--
06 May 2008 08:44 AM
But if you look at the entire structure it is BAD NEWS correlated subquery. Those rarely perform well.

Since you can't do anything with indexes, ask development to rewrite it for high performance.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005highperformance

SQLUSA
New Member
New Member

--
06 May 2008 04:23 PM
SwePeso
New Member
New Member

--
06 May 2008 10:23 PM
The real problem is that the derived table "request_top" does not expose the binding column "requestid" for the JOIN.




UPDATE dr WITH (ROWLOCK)
SET dr.ReleaseTime = DATEADD(SECOND, @ReleaseDelay, GETDATE())
FROM dbo.DeliveryRequest AS dr
WHERE dr.OrderID = @OrderID

UPDATE dr WITH (UPDLOCK)
SET dr.ReleaseTime = DATEADD(SECOND, DurationInSeconds, GETDATE()),
dr.BatchID = @BatchID
FROM dbo.DeliveryRequest AS dr
INNER JOIN (
SELECT TOP 100 dr.DeliveryRequestID
FROM dbo.DeliveryRequest AS dr WITH (UPDLOCK)
WHERE dr.DeliveryDate >= @StartDate
AND dr.DeliveryDate <= @EndDate
LEFT JOIN dbo.IgnoreDelivery AS id WITH (NOLOCK) ON id.RequestID = dr.RequestID
WHERE id.RequestID IS NULL
ORDER BY dr.DeliveryDate
) x ON x.DeliveryRequestID = dr.DeliveryRequestID
SQLUSA
New Member
New Member

--
06 May 2008 11:10 PM
Brilliant Peter as usual. So how this joined worked with request_top.requestid? Non-working code?

INNER JOIN
(
SELECT TOP 100 deliveryrequestid FROM dbo.deliveryrequest WITH (UPDLOCK)
WHERE
NOT EXISTS (SELECT deliveryrequestid FROM dbo.ignoredelivery WITH (NOLOCK)
WHERE deliveryrequest.requestid = ignoredelivery.requestid)
ORDER BY deliverypDate
) request_top ON dbo.deliveryrequest.requestid = request_top.requestid

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
trans53
New Member
New Member

--
07 May 2008 07:33 AM
Yes, it is a typo.Sorry for confusion.
Thank you all for your help. I will try to test this asap and will keep you posted.


Acceptable Use Policy
---