March 19, 2013 at 5:33 am
I have the following dead lock happening quite frequently at 3-4 times a day.
The deadlock seems to happen on 2 things one being an index on the status column
keylock hobtid=72057605790367744 dbid=9 objectname=dbname.dbo.orderha indexname=IX_status id=lock2189e7200 mode=S associatedObjectId=72057605790367744
the other on the primary clustered key
keylock hobtid=72057602492792832 dbid=9 objectname=dbname.dbo.orderha indexname=PK_orderha id=lock1399f2200 mode=X associatedObjectId=72057602492792832
DEADLOCK INFO:
Node:1
KEY: 9:72057602492792832 (7900b77d3449) CleanCnt:2 Mode:X Flags: 0x1
Grant List 1:
Owner:0x00000001A34042C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:67 ECID:0 XactLockInfo: 0x00000002643C19B0
SPID: 67 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7560129' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000D7EAC538) Value:0x118db7c0 Cost:(0/0)
Node:2
KEY: 9:72057605790367744 (7a00de2866cc) CleanCnt:2 Mode:S Flags: 0x1
Grant List 0:
Owner:0x00000002E14CBCC0 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x00000001A183B7C0
SPID: 64 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: select rsn from orderha where sessionid='7558101' and status < 3
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000002643C1970 Mode: X SPID:67 BatchID:0 ECID:0 TaskProxy:(0x0000000281984538) Value:0x42de2bc0 Cost:(0/456)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000D7EAC538) Value:0x118db7c0 Cost:(0/0)
Should I use a Lock hint to force a block rather then encounter a deadlock , such as UPDLOCK? or HOLDLOCK?
March 19, 2013 at 6:47 am
Anyone ? :s
March 19, 2013 at 6:55 am
DDL for the table and all indexes, and the actual plans for both queries, would help diagnosis.
An index on sessionid including status and rsn may solve the problem.
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
March 19, 2013 at 9:13 am
ChrisM@Work (3/19/2013)
DDL for the table and all indexes, and the actual plans for both queries, would help diagnosis.An index on sessionid including status and rsn may solve the problem.
Will grab this info
March 20, 2013 at 4:50 am
Here is my guess as to what is happening. It's only a guess since we don't have full information.
The classic reason for a deadlock is that 2 processes try to lock the same 2 resources but they request the locks in the opposite order.
The 'SELECT' process will take out shared read locks on the Status index, and as it looks up each 'bookmark' on the clustered index it will briefly take a shared read lock on each clustered index row which satisfies the condition 'Status < 3'.
The 'UPDATE' process will take out exclusive locks on the clustered index and the Status index - in that order.
Shared and exclusive locks cannot coexist so if you are unlucky and the Select attempts to read a row in the clustered index which is locked by the Update - deadlock. Of course the time window is small but if both queries are run frequently you're likely to get this problem.
Note that the fact that the sessionid is different for the two queries is irrelevant since the bookmark lookup in the Select happens for all rows satisfying 'Status < 3'
IMHO this is a flaw in SQL Server's lock design - the queries are perfectly reasonable and the database server should be able to handle them without throwing the onus back on the developer to find a workround.
However, be that as it may, if this explanation is correct, you have various options for fixing it; the first few that come to mind are:
1. Start using Snapshot isolation (http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.80%29.aspx). This entirely prevents deadlocks involving Select statements.
2. Evaluate whether the index on Status is actually beneficial - if there is a only small number of possible Status values the index will not be very selective. If you simply drop the index - no more deadlocks.
3. Add a new index on sessionid. Assuming there is a large number of sessionid values, this will be a highly selective index which will likely improve the performance of the Select query and, since the Update doesn't change the sessionid, the Update won't take an exclusive lock on the sessionid index.
4. Create a 'covering index' on sessionid, Status and rsn - then the Select query would simply use the new index and perform no bookmark lookups at all. The Update would take exclusive locks on this index (to change the Status) but that won't lead to deadlocks.
Any of these changes would solve your deadlock problem but might actually damage your performance or introduce other issues elsewhere. But that's just part of the fun.
March 20, 2013 at 5:31 am
David Griffiths-273839 (3/20/2013)
Here is my guess as to what is happening. It's only a guess since we don't have full information.2. Evaluate whether the index on Status is actually beneficial - if there is a only small number of possible Status values the index will not be very selective. If you simply drop the index - no more deadlocks.
Any of these changes would solve your deadlock problem but might actually damage your performance or introduce other issues elsewhere. But that's just part of the fun.
Thanks for all the info much appreciated , dropping the status index was going to be my first move.
March 20, 2013 at 5:56 am
ChrisM@Work (3/19/2013)
DDL for the table and all indexes, and the actual plans for both queries, would help diagnosis.An index on sessionid including status and rsn may solve the problem.
Apologies for the delay,
CREATE TABLE [dbo].[orderha](
[rsn] [bigint] IDENTITY(1,1) NOT NULL,
[sessionid] [nvarchar](50) NOT NULL,
[orderha] [int] NOT NULL,
[perc] [decimal](18, 8) NOT NULL,
[userid] [bigint] NOT NULL,
[hashtotal] [decimal](18, 2) NOT NULL,
[dateadded] [datetime] NOT NULL,
[shipped] [tinyint] NOT NULL,
[status] [int] NOT NULL,
[suspectorder] [tinyint] NOT NULL,
[shippingweight] [decimal](18, 2) NOT NULL,
[discount] [decimal](18, 2) NOT NULL,
[shiptotal] [decimal](18, 2) NOT NULL,
[shipopt] [int] NOT NULL,
[rd] [tinyint] NOT NULL,
[giftwrap] [tinyint] NOT NULL,
[bay] [int] NOT NULL,
[instock] [tinyint] NOT NULL,
[beenupdated] [tinyint] NOT NULL,
[shipdate] [datetime] NULL,
[vatamount] [decimal](18, 2) NOT NULL,
[prodname] [nvarchar](250) NOT NULL,
[source] [char](2) NOT NULL,
[externalsessionid] [nvarchar](70) NOT NULL,
[postalrsn] [bigint] NOT NULL,
[printref] [nvarchar](50) NOT NULL,
[sdref] [nvarchar](20) NOT NULL,
[oos] [tinyint] NOT NULL,
[sdreflabel] [nvarchar](20) NOT NULL,
[bin] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_orderha] PRIMARY KEY CLUSTERED
(
[rsn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
/****** Object: Index [IX_dateadded] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_dateadded] ON [dbo].[orderha]
(
[dateadded] ASC
)
INCLUDE ( [shiptotal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_gw] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_gw] ON [dbo].[orderha]
(
[giftwrap] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_oh] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_oh] ON [dbo].[orderha]
(
[orderha] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_printeref] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_printeref] ON [dbo].[orderha]
(
[printref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_rd] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_rd] ON [dbo].[orderha]
(
[rd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_sessionid] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha]
(
[sessionid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_shipopt] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_shipopt] ON [dbo].[orderha]
(
[shipopt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_shipopt_suspect_status_rsn_dateadded] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_shipopt_suspect_status_rsn_dateadded] ON [dbo].[orderha]
(
[shipopt] ASC,
[suspectorder] ASC,
[status] ASC,
[rsn] ASC,
[dateadded] ASC
)
INCLUDE ( [sessionid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_shipopt_suspect_status_rsn_dateadded_oos] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_shipopt_suspect_status_rsn_dateadded_oos] ON [dbo].[orderha]
(
[suspectorder] ASC,
[status] ASC,
[shipopt] ASC,
[oos] ASC,
[dateadded] ASC,
[rsn] ASC
)
INCLUDE ( [sessionid],
[shippingweight],
[rd]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_status] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_status] ON [dbo].[orderha]
(
[status] ASC
)
INCLUDE ( [printref]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_suspect] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_suspect] ON [dbo].[orderha]
(
[suspectorder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_suspect_status_rsn_shipopt] Script Date: 03/20/2013 11:38:13 ******/
CREATE NONCLUSTERED INDEX [IX_suspect_status_rsn_shipopt] ON [dbo].[orderha]
(
[suspectorder] ASC,
[status] ASC,
[rsn] ASC,
[shipopt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
March 20, 2013 at 6:22 am
Add column [status] to the include list for index IX_sessionid.
As an aside, you appear to have a fair amount of duplication in your indexes.
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
March 20, 2013 at 7:22 am
ChrisM@Work (3/20/2013)
Add column [status] to the include list for index IX_sessionid.As an aside, you appear to have a fair amount of duplication in your indexes.
Thanks for that suggestion.
The indexes are a mess, don't like passing the blame but that's how they were before I start looking after the DB they do need to be rectified.
March 20, 2013 at 7:29 am
bugg (3/20/2013)
ChrisM@Work (3/20/2013)
Add column [status] to the include list for index IX_sessionid.As an aside, you appear to have a fair amount of duplication in your indexes.
Thanks for that suggestion.
The indexes are a mess, don't like passing the blame but that's how they were before I start looking after the DB they do need to be rectified.
Only rarely is a finger pointed around here - you'd be surprised how often folk need help with inherited code and systems. If you haven't already, I'd suggest you read through Glenn Berry's maintenance scripts. A few of them assist with index optimisation. Pretty much everything you need to know is in the comments embedded in the code.
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
March 25, 2013 at 3:04 am
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy:(0x00000002E58F1A60) Value:0xff19db40 Cost:(0/0)
Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy:(0x00000001C09FA538) Value:0x94aaf3c0 Cost:(0/220)
Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000B69AC510) Value:0xd9bdc8 Cost:(0/10000)
March 25, 2013 at 3:42 am
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy:(0x00000002E58F1A60) Value:0xff19db40 Cost:(0/0)
Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy:(0x00000001C09FA538) Value:0x94aaf3c0 Cost:(0/220)
Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000B69AC510) Value:0xd9bdc8 Cost:(0/10000)
Can you post the missing bits please?
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
March 25, 2013 at 4:03 am
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy:(0x00000002E58F1A60) Value:0xff19db40 Cost:(0/0)
Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy:(0x00000001C09FA538) Value:0x94aaf3c0 Cost:(0/220)
Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000B69AC510) Value:0xd9bdc8 Cost:(0/10000)
Can you post the missing bits please?
Hi ChrisM, do you need the table def with indexes?
March 25, 2013 at 4:27 am
bugg (3/25/2013)
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy:(0x00000002E58F1A60) Value:0xff19db40 Cost:(0/0)
Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy:(0x00000001C09FA538) Value:0x94aaf3c0 Cost:(0/220)
Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000B69AC510) Value:0xd9bdc8 Cost:(0/10000)
Can you post the missing bits please?
Hi ChrisM, do you need the table def with indexes?
Please see execution plans:
March 25, 2013 at 4:30 am
bugg (3/25/2013)
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy:(0x00000002E58F1A60) Value:0xff19db40 Cost:(0/0)
Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy:(0x00000001C09FA538) Value:0x94aaf3c0 Cost:(0/220)
Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000B69AC510) Value:0xd9bdc8 Cost:(0/10000)
Can you post the missing bits please?
Hi ChrisM, do you need the table def with indexes?
You've already posted them. No, it looks like a part of the deadlock info is missing. Can you post the actual plans for both of the queries please?
You may well get some mileage by changing the update query as follows:
UPDATE orderha
SET [status] = 2
WHERE sessionid = '7560129'
AND orderha = 1
AND [status] <> 2
The actual plan for this query would be useful too.
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
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply