March 10, 2015 at 12:28 am
Hi,
I am getting following deadlock message. Can any one explain what is happening in below deadlock?? Also please suggest what can I do to get rid of this deadlock?
<deadlock-list>
<deadlock victim="process46d288">
<process-list>
<process id="process46d288" taskpriority="0" logused="9968" waitresource="PAGE: 14:1:4468703" waittime="492" ownerId="292248020" transactionname="user_transaction" lasttranstarted="2015-03-03T16:25:12.250" XDES="0x38c300b50" lockMode="U" schedulerid="4" kpid="12432" status="suspended" spid="51" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2015-03-03T16:25:12.250" lastbatchcompleted="2015-03-03T16:25:12.250" clientapp=".Net SqlClient Data Provider" hostname="ER-PC" hostpid="5524" isolationlevel="read committed (2)" xactid="292248020" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname=" Live_Data.dbo.DeleteOrderByID" line="58" stmtstart="2624" stmtend="2976" sqlhandle="0x03000e009cb1ba26ad504d0133a400000100000000000000">
DELETE a
FROM
[dbo].[OrderContactAddresses] a
LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
WHERE
b.[ID] IS NULL </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process453048" taskpriority="0" logused="10000" waittime="1087" schedulerid="3" kpid="9072" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-03-03T16:25:12.250" lastbatchcompleted="2015-03-03T16:25:12.250" clientapp=".Net SqlClient Data Provider" hostname="ER-PC" hostpid="5524" loginname="CSUser" isolationlevel="read committed (2)" xactid="292248020" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname=" Live_Data.dbo.DeleteOrderByID" line="58" stmtstart="2624" stmtend="2976" sqlhandle="0x03000e009cb1ba26ad504d0133a400000100000000000000">
DELETE a
FROM
[dbo].[OrderContactAddresses] a
LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
WHERE
b.[ID] IS NULL </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 14 Object Id = 649769372] </inputbuf>
</process>
<process id="process13f288" taskpriority="0" logused="10000" waittime="489" schedulerid="1" kpid="10700" status="suspended" spid="51" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2015-03-03T16:25:12.250" lastbatchcompleted="2015-03-03T16:25:12.250" clientapp=".Net SqlClient Data Provider" hostname="ER-PC" hostpid="5524" isolationlevel="read committed (2)" xactid="292248020" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname=" Live_Data.dbo.DeleteOrderByID" line="58" stmtstart="2624" stmtend="2976" sqlhandle="0x03000e009cb1ba26ad504d0133a400000100000000000000">
DELETE a
FROM
[dbo].[OrderContactAddresses] a
LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
WHERE
b.[ID] IS NULL </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process429948" taskpriority="0" logused="21824" waitresource="KEY: 14:72057594044219392 (4e026ee4a4e6)" waittime="759" ownerId="292248016" transactionname="user_transaction" lasttranstarted="2015-03-03T16:25:09.990" XDES="0x25cf193c0" lockMode="S" schedulerid="2" kpid="14072" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-03-03T16:25:11.973" lastbatchcompleted="2015-03-03T16:25:11.970" clientapp=".Net SqlClient Data Provider" hostname="2800-PC" hostpid="2112" loginname="CSUser" isolationlevel="read committed (2)" xactid="292248016" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="94" sqlhandle="0x020000002050d705ed913668c479421685d2ab98cbf71802">
DELETE FROM [dbo].[OrderContactPhones] WHERE ([ID] = @p0) AND ([PhoneNumber] = @p1) AND ([Extension] = @p2) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000))DELETE FROM [dbo].[OrderContactPhones] WHERE ([ID] = @p0) AND ([PhoneNumber] = @p1) AND ([Extension] = @p2) </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="4468703" dbid="14" objectname=" Live_Data.dbo.OrderContactAddresses" id="lock1e373ec00" mode="IX" associatedObjectId="72057594205306880">
<owner-list>
<owner id="process429948" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process46d288" ="U" requestType="wait"/>
</waiter-list>
</pagelock>
<exchangeEvent id="Port801ab600" WaitType="e_waitPortOpen" nodeId="5">
<owner-list>
<owner id="process13f288"/>
</owner-list>
<waiter-list>
<waiter id="process453048"/>
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1151a6800" WaitType="e_waitPipeGetRow" nodeId="9">
<owner-list>
<owner id="process46d288"/>
</owner-list>
<waiter-list>
<waiter id="process13f288"/>
</waiter-list>
</exchangeEvent>
<keylock hobtid="72057594044219392" dbid="14" objectname=" Live_Data.dbo.OrderContactPhoneLink" indexname="PK_OrderContactPhoneLink" id="lock1cc613b00" mode="X" associatedObjectId="72057594044219392">
<owner-list>
<owner id="process453048" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process429948" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
In trace file I can see SPID 79 is execution following query
exec sp_executesql N'DELETE FROM [dbo].[OrderContactPhones] WHERE ([ID] = @p0) AND ([PhoneNumber] = @p1) AND ([Extension] = @p2)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=1429658,@p1=N'',@p2=N''
and SPID 51 is executing following query
exec DeleteOrderByID @OrderID=87764
DeleteOrderBYID is stored Procedure below is the body of stored procedure
GO
/****** Object: StoredProcedure [dbo].[DeleteOrderByID] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeleteOrderByID]
@OrderID int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM [dbo].[OrderInstructions]
WHERE [OrderID] = @OrderID
DELETE FROM [dbo].[OrderUserDefinedFieldLink]
WHERE [OrderID] = @OrderID
CREATE TABLE #OrderContactsIDs ([ID] int)
CREATE TABLE #LineItemsIDs ([ID] int)
INSERT INTO #OrderContactsIDs ([ID])
SELECT
[OrderContactID]
FROM
[dbo].[OrderContactLink]
WHERE
[OrderID] = @OrderID
UNION ALL
SELECT
a.[OrderContactID]
FROM
[dbo].[OrderContactLink] a
INNER JOIN [dbo].[LineItems] b ON a.[LineItemID] = b.[ID]
WHERE
b.[OrderID] = @OrderID
INSERT INTO #LineItemsIDs ([ID])
SELECT [ID] FROM [dbo].[LineItems] WHERE [OrderID] = @OrderID
DELETE a
FROM
[dbo].[OrderContactAddressLink] a
INNER JOIN #OrderContactsIDs b ON a.[OrderContactID] = b.[ID]
DELETE a
FROM
[dbo].[OrderContactEmailLink] a
INNER JOIN #OrderContactsIDs b ON a.[OrderContactID] = b.[ID]
DELETE a
FROM
[dbo].[OrderContactPhoneLink] a
INNER JOIN #OrderContactsIDs b ON a.[OrderContactID] = b.[ID]
DELETE a
FROM
[dbo].[OrderContactWebLink] a
INNER JOIN #OrderContactsIDs b ON a.[OrderContactID] = b.[ID]
DELETE a
FROM
[dbo].[OrderContactAddresses] a
LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
WHERE
b.[ID] IS NULL
DELETE a
FROM
[dbo].[OrderContactEmails] a
LEFT JOIN [dbo].[OrderContactEmailLink] b ON a.[ID] = b.[OrderContactEmailID]
WHERE
b.[ID] IS NULL
DELETE a
FROM
[dbo].[OrderContactPhones] a
LEFT JOIN [dbo].[OrderContactPhoneLink] b ON a.[ID] = b.[OrderContactPhoneID]
WHERE
b.[ID] IS NULL
DELETE a
FROM
[dbo].[OrderContactWebAddresses] a
LEFT JOIN [dbo].[OrderContactWebLink] b ON a.[ID] = b.[OrderContactWebAddressID]
WHERE
b.[ID] IS NULL
DELETE a
FROM
[dbo].[OrderContactLink] a
INNER JOIN #OrderContactsIDs b ON a.[OrderContactID] = b.[ID]
DELETE a
FROM
[dbo].[OrderContacts] a
INNER JOIN #OrderContactsIDs b ON a.[ID] = b.[ID]
DELETE a
FROM
[dbo].[LineItemCommission] a
INNER JOIN #LineItemsIDs b ON a.[LineItemID] = b.[ID]
DELETE a
FROM
[dbo].[LineItemTaxes] a
INNER JOIN #LineItemsIDs b ON a.[LineItemID] = b.[ID]
DELETE a
FROM
[dbo].[LineItemValues] a
INNER JOIN #LineItemsIDs b ON a.[LineItemID] = b.[ID]
DELETE a
FROM
[dbo].[LineItemAttributes] a
INNER JOIN #LineItemsIDs b ON a.[LineItemID] = b.[ID]
DELETE a
FROM
[dbo].[OrderInstructions] a
INNER JOIN #LineItemsIDs b ON a.[LineItemID] = b.[ID]
DELETE FROM [dbo].[LineItems]
WHERE [OrderID] = @OrderID
DELETE FROM [dbo].[OrderTaxes]
WHERE [OrderID] = @OrderID
DELETE FROM [dbo].[OrderCommission]
WHERE [OrderID] = @OrderID
DELETE FROM [dbo].[Orders]
WHERE [ID] = @OrderID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE() <> 0)
ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
March 10, 2015 at 10:25 am
Please help me
I am not deleting large number of rows. This SP deletes only 59 rows.
I am seeing this in profiler
Parallel qury worker thread was involvd in a deadlock
March 11, 2015 at 5:06 pm
Has this Sproc ever executed in live?
What isolation level is running in the sproc?
An option is to look at isolation levels. If you can change the isolation level of the DB to allow snapshot isolation, that is another option.
Some changes in isolation levels may require code changes to implement so I would first see if index optimisation gets rid of the problem.
These are tough to diagnose even with access to all objects and stats so I will throw in my 2 pence worth.
There are a few ways deadlocks can occur.
One is if a query is doing a lookup because it is using a non covering index.
Another is if a query is locking too many pages inside a transaction because of missing index, and this conflicts with another transaction. This would normally just block so might be doing something additional.
Another would be the classic deadlock scenario if update a then b in transaction X and then update B then A in transaction Y before x completes.
A scenario I had was duplicate indexes on a table and there were deletes against the table using the duplicated index to search for a row.
Dropped duplicate and it went away.
Another was indexes that was too wide and yet not covering.
Another was a missing index.
Since you know that there is parallelism, you should be able to find which table has parallelism.
I would look at the table by itself and see if that table is,
a:)clustered properly to adequately serve the most used query without hurting inserts too much.
b:) has a missing index.
What I did was make sure that there were no duplicate indexes on that table, and make sure that the indexes were the smallest I could devise that would give me lowest number of lookups, but finding the balance because too wide an index means that the index may need to be involved in more updates due to the higher likelihood of it containing a column that is updated to, and hence a higher likelihood of deadlocks.
I had a look at the sequence of events inside the transaction.
First we delete from [OrderContactPhoneLink] as an example.
Then we delete from [OrderContactAddresses] left joined to [OrderContactPhoneLink] before anything has been committed.
Maybe we should delete from [OrderContactAddresses] first joining on the temp table, and then delete from [OrderContactPhoneLink] afterwards?
Sorry for long reply but many things to consider here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply