bugg (1/22/2013)

---

Nope I did not realize that! I thought it was specific to the row being updated

anthony.green (1/22/2013)

---

Do you have traceflag 1222 enabled?

If so can you post the deadlock graph from the error log

If not run

DBCC TRACEON (1222, -1)

and then post the graphs once a deadlock has occured.

anthony.green (1/23/2013)

---

Do you have the deadlock XML in the error log, should be many many lines that looks like this

<TextData><deadlock-list>
 <deadlock victim="process53b9288">
  <process-list>
   <process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

01/23/2013 07:25:31,spid23s,Unknown,waiter id=process27317b4c8 mode=X requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=processdb54c8 mode=S
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057605790367744 dbid=4 objectname=dbo.order indexname=IX_status id=lock3d60c8e80 mode=S associatedObjectId=72057605790367744
01/23/2013 07:25:31,spid23s,Unknown,waiter id=processdb54c8 mode=S requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=process27317b4c8 mode=X
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057602492792832 dbid=4 objectname=dbo.order indexname=PK_order id=lock3d27aec80 mode=X associatedObjectId=72057602492792832
01/23/2013 07:25:31,spid23s,Unknown,resource-list
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000880e2612bdc1c85889b28ed1a83337436c23418e
01/23/2013 07:25:31,spid23s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [order] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [order]%%=%%@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=process27317b4c8 taskpriority=0 logused=292 waitresource=KEY: 9:72057605790367744 (2e001d1b37ce) waittime=1840 ownerId=18323630766 transactionname=UPDATE lasttranstarted=2013-01-23T07:25:29.427 XDES=0xe4e2b970 lockMode=X schedulerid=8 kpid=2092 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-23T07:25:29.420 lastbatchcompleted=2013-01-23T07:25:29.420 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630766 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,SELECT count(rsn) As Count FROM order WHERE status<3 AND shipopt <99 AND dateadded <= '2013-01-23 13:00:59'
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000712b952eb475b9b249be45c1513acca8b2b31ed1
01/23/2013 07:25:31,spid23s,Unknown,(@1 tinyint<c/>@2 tinyint<c/>@3 tinyint<c/>@4 varchar(8000))SELECT COUNT([rsn]) [Count] FROM [order] WHERE [status]<@1 AND [shipopt]<@3 AND [dateadded]<=@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000007c853201340a85cf70825b9c171de622351e36b1
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=processdb54c8 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (2c00849cfb3c) waittime=1840 ownerId=18323630729 transactionname=SELECT lasttranstarted=2013-01-23T07:25:29.423 XDES=0x1c384fb80 lockMode=S schedulerid=7 kpid=8128 status=suspended spid=51 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-01-23T07:25:29.423 lastbatchcompleted=2013-01-23T07:25:29.280 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630729 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,process-list
01/23/2013 07:25:31,spid23s,Unknown,deadlock victim=processdb54c8
01/23/2013 07:25:31,spid23s,Unknown,deadlock-list
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy0x000000035B1E3770) Value:0xce952bc0 Cost0/0)
01/23/2013 07:25:31,spid5s,Unknown,Victim Resource Owner:
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000E4E2B970 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxy0x000000024AD44538) Value:0xbd6264c0 Cost0/292)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: No Event:
01/23/2013 07:25:31,spid5s,Unknown,SPID: 51 ECID: 4 Statement Type: SELECT Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x00000001BF286480 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:51 ECID:4 XactLockInfo: 0x00000001C384FBC0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 2:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057605790367744 (2e001d1b37ce) CleanCnt:2 Mode:S Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:2
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy0x000000035B1E3770) Value:0xce952bc0 Cost0/0)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: Language Event: update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x000000008164B2C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000000E4E2B9B0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 1:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057602492792832 (2c00849cfb3c) CleanCnt:2 Mode:X Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:1
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,Wait-for graph
01/23/2013 07:25:31,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

bugg (1/23/2013)

---

Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.

GilaMonster (1/23/2013)

---

That's a deadlock graph. 2 actually, since you enabled both traceflags 1204 and 1222 resulting an a messed up combination of old style of old and new deadlock graphs

anthony.green (1/23/2013)

---

All statistics and indexes rebuilt and updated on the order table?

Can you post the execution plans as a SQLPLAN file from the below query, if they exist any more.

select 
	query_plan
from 
	sys.dm_exec_query_stats 
cross apply
	sys.dm_exec_query_plan (plan_handle)
where 
	sql_handle in 
	(
		0x02000000880e2612bdc1c85889b28ed1a83337436c23418e,
		0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a,
		0x020000007c853201340a85cf70825b9c171de622351e36b1
	)

GilaMonster (1/23/2013)

---

Can you post the table definition and all index definitions for dbo.order?

CREATE TABLE [dbo].[order](
[rsn] [bigint] IDENTITY(1,1) NOT NULL,
[sessionid] [nvarchar](50) NOT NULL,
[order] [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_order] 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]

GO

SET ANSI_PADDING OFF
GO

/****** Object: Index [IX_dateadded] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_dateadded] ON [dbo].[order]
(
[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: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_gw] ON [dbo].[order]
(
[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: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_oh] ON [dbo].[order]
(
[order] 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: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_printeref] ON [dbo].[order]
(
[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: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_rd] ON [dbo].[order]
(
[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_sess] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_sess] ON [dbo].[order]
(
[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_sessionid] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[order]
(
[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: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_shipopt] ON [dbo].[order]
(
[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_status] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_status] ON [dbo].[order]
(
[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: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_suspect] ON [dbo].[order]
(
[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


ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sessionid] DEFAULT ('') FOR [sessionid]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_order] DEFAULT ((0)) FOR [order]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_perc] DEFAULT ((0)) FOR [perc]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_userid] DEFAULT ((0)) FOR [userid]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_hashtotal] DEFAULT ((0)) FOR [hashtotal]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_dateadded] DEFAULT (getdate()) FOR [dateadded]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipped] DEFAULT ((0)) FOR [shipped]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipping] DEFAULT ((0)) FOR [status]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_suspectorder] DEFAULT ((0)) FOR [suspectorder]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shippingweight] DEFAULT ((0)) FOR [shippingweight]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_discount] DEFAULT ((0)) FOR [discount]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shiptotal] DEFAULT ((0)) FOR [shiptotal]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipopt] DEFAULT ((0)) FOR [shipopt]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_rd] DEFAULT ((0)) FOR [rd]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_giftwrap] DEFAULT ((0)) FOR [giftwrap]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_bay] DEFAULT ((0)) FOR [bay]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_instock] DEFAULT ((0)) FOR [instock]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_beenupdated] DEFAULT ((0)) FOR [beenupdated]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_vatamount] DEFAULT ((0)) FOR [vatamount]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_prodname] DEFAULT ('') FOR [prodname]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_source] DEFAULT ('IC') FOR [source]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_externalsessionid] DEFAULT ('') FOR [externalsessionid]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_postalrsn] DEFAULT ((0)) FOR [postalrsn]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_printref] DEFAULT ('') FOR [printref]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sdref] DEFAULT ('') FOR [sdref]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_oos] DEFAULT ((0)) FOR [oos]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sdreflabel] DEFAULT ('') FOR [sdreflabel]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_bin] DEFAULT ('') FOR [bin]
GO