Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Deadlock on update command Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 5:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 193, Visits: 684
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 TaskProxy0x00000000D7EAC538) Value:0x118db7c0 Cost0/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 TaskProxy0x0000000281984538) Value:0x42de2bc0 Cost0/456)

Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy0x00000000D7EAC538) Value:0x118db7c0 Cost0/0)

Should I use a Lock hint to force a block rather then encounter a deadlock , such as UPDLOCK? or HOLDLOCK?
Post #1432578
Posted Tuesday, March 19, 2013 6:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 193, Visits: 684
Anyone ? :s
Post #1432612
Posted Tuesday, March 19, 2013 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1432617
Posted Tuesday, March 19, 2013 9:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 193, Visits: 684
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
Post #1432723
Posted Wednesday, March 20, 2013 4:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:39 AM
Points: 229, Visits: 145
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.
Post #1433128
Posted Wednesday, March 20, 2013 5:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 193, Visits: 684
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.
Post #1433141
Posted Wednesday, March 20, 2013 5:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 193, Visits: 684
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


Post #1433151
Posted Wednesday, March 20, 2013 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1433166
Posted Wednesday, March 20, 2013 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 193, Visits: 684
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.
Post #1433194
Posted Wednesday, March 20, 2013 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1433196
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse