Deadlock on update command

  • 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?

  • Anyone ? :s

  • 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.

    “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

  • 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

  • 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.

  • 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.

  • 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

  • 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.

    “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

  • 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.

  • 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.

    “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

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

  • 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?

    “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

  • 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?

  • 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:

    Select - http://s8.postimg.org/owmsmdlcl/exeplan1.png

    Update - http://s17.postimg.org/jl20ih17z/exeplan2.png

  • 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.

    “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

Viewing 15 posts - 1 through 15 (of 29 total)

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