Monday, February 06, 2006

Query Deadlocking itself because of parallelism: OPTION (MAXDOP 1)

ASP.NET 2.0 error in ExecuteQuery:
Transaction (Process ID 71) was deadlocked on thread communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

While execute the following query:
SET NOCOUNT ON
BEGIN
DECLARE @CountOrd int
DECLARE @CustID nvarchar(50)
DECLARE @Start int
DECLARE @End int
DECLARE @Count int
Set @CustID = 'ACD'
Set @Start = 1
Set @End = 25
CREATE TABLE #op(PK int,CountOrd int, Ord int IDENTITY(1,1))
CREATE INDEX op1 ON #op(Ord)

INSERT INTO #op(PK)
SELECT PKID FROM Orders WHERE Orders.OrdStatus <> 'C' AND Orders.OrdStatus <> '0'
And Orders.CustID in (Select Customer From AllowedCustTable(@CustID))
ORDER BY OrdStatus
END

SELECT #op.Ord,od.*,dbo.OrdStrStatus(od.OrdStatus) AS OrdStrStatus
FROM #op LEFT JOIN Orders od ON #op.PK = od.PKID
WHERE #op.Ord BETWEEN @Start AND @End

Select @CountOrd= Count(#op.PK) FROM #op
Set @Count=@CountOrd

DROP TABLE #op


But we can not find any other is access the same table, so probably deadlocking itself because ofparallelism which can happen, try stop parallelism by using MAXDOP 1 onthe OPTIONS clause of the UPDATE/INSERT statement. After change to the following it works:

ORDER BY OrdStatus OPTION (MAXDOP 1)

No comments: