Deadlock on a single resource ?

Question

Deadlock on a single resource ?

Jim McBrim

Mr or Mrs. 500

Points: 509
More actions
March 23, 2011 at 11:21 am

#389118

Hi,
I am struggling to interpret the following deadlock graph. From the diagram in profiler it looks unlike the scenario that I am familiar with - in this case it seems that the deadlock is on a single resource. We are running in read committed mode. Any help would be appreciated....
2011-03-08 12:30:22.25 spid6s Deadlock encountered .... Printing deadlock information
2011-03-08 12:30:22.25 spid6s Wait-for graph
2011-03-08 12:30:22.25 spid6s
2011-03-08 12:30:22.25 spid6s Node:1
2011-03-08 12:30:22.25 spid6s KEY: 10:72057594043039744 (3e00cedb1991) CleanCnt:3 Mode:X Flags: 0x1
2011-03-08 12:30:22.25 spid6s Wait List:
2011-03-08 12:30:22.25 spid6s Owner:0x000000009FA97A40 Mode: S Flg:0x42 Ref:1 Life:00000001 SPID:67 ECID:0 XactLockInfo: 0x00000000AC30FB90
2011-03-08 12:30:22.25 spid6s SPID: 67 ECID: 0 Statement Type: SELECT Line #: 1
2011-03-08 12:30:22.25 spid6s Input Buf: Language Event: (@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)
[Project1].[name] AS [name],
[Project1].[employee_name] AS [employee_name],
[Project1].[name1] AS [name1],
2011-03-08 12:30:22.25 spid6s Requested by:
2011-03-08 12:30:22.25 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0000000080E27CC0 Mode: S SPID:60 BatchID:2 ECID:0 TaskProxy:(0x000000008AB44538) Value:0x86e09940 Cost:(0/0)
2011-03-08 12:30:22.25 spid6s
2011-03-08 12:30:22.25 spid6s Node:2
2011-03-08 12:30:22.25 spid6s KEY: 10:72057594043039744 (3e00cedb1991) CleanCnt:3 Mode:X Flags: 0x1
2011-03-08 12:30:22.25 spid6s Grant List 0:
2011-03-08 12:30:22.25 spid6s Owner:0x000000009C4C9700 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:84 ECID:0 XactLockInfo: 0x0000000093E879B0
2011-03-08 12:30:22.25 spid6s SPID: 84 ECID: 0 Statement Type: INSERT Line #: 1
2011-03-08 12:30:22.25 spid6s Input Buf: Language Event: (@0 nvarchar(255),@1 bigint,@2 int,@3 nvarchar(255),@4 nvarchar(255),@5 nvarchar(255))insert [dbo].[employee]([employee_request_id], [job_history_id], [employee_name], [state])
values (@0, @1, @2, @3, @4, @5)
select 09:55 09/03/2011
2011-03-08 12:30:22.25 spid6s Requested by:
2011-03-08 12:30:22.25 spid6s ResType:LockOwner Stype:'OR'Xdes:0x00000000AC30FB50 Mode: S SPID:67 BatchID:2 ECID:0 TaskProxy:(0x00000000AE7F6538) Value:0x9fa97a40 Cost:(0/0)
2011-03-08 12:30:22.25 spid6s
2011-03-08 12:30:22.25 spid6s Node:3
2011-03-08 12:30:22.25 spid6s OBJECT: 10:21575115:0 CleanCnt:2 Mode:S Flags: 0x1
2011-03-08 12:30:22.25 spid6s Grant List 0:
2011-03-08 12:30:22.25 spid6s Owner:0x00000000A0101FC0 Mode: S Flg:0x40 Ref:2 Life:00000001 SPID:67 ECID:0 XactLockInfo: 0x00000000AC30FB90
2011-03-08 12:30:22.25 spid6s Requested by:
2011-03-08 12:30:22.25 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0000000093E87970 Mode: IX SPID:84 BatchID:2 ECID:0 TaskProxy:(0x0000000093C5A538) Value:0x9b54a280 Cost:(0/5820)
2011-03-08 12:30:22.25 spid6s
2011-03-08 12:30:22.25 spid6s Victim Resource Owner:
2011-03-08 12:30:22.25 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0000000080E27CC0 Mode: S SPID:60 BatchID:2 ECID:0 TaskProxy:(0x000000008AB44538) Value:0x86e09940 Cost:(0/0)
2011-03-08 12:30:22.25 spid6s
2011-03-08 12:30:22.25 spid6s Victim Resource Owner:
2011-03-08 12:30:22.25 spid6s ResType:LockOwner Stype:'OR'Xdes:0x00000000AC30FB50 Mode: S SPID:67 BatchID:2 ECID:0 TaskProxy:(0x00000000AE7F6538) Value:0x9fa97a40 Cost:(0/0)

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the table definitions and index definitions?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Jimble,

Is this everything? The final component of the graph looks like it's a 3 way deadlocking but only spids 67 and 84 have descriptions above, and 60 seems like its details got left out.

Either way, you're deadlocking a select and an insert against dbo.Employee, which is why Gail asked for the DDL on the table. I'd need to do some research to confirm I remember how to read the OBJECT encoding in this case properly.

How many rows are you trying to insert at once, it looks like only one row, but I wanted to confirm. It's trying to open an Intent Exclusive lock while the select(s?) are hanging onto some shared locks, so they're running into each other in this case. More then that I'd have to do some research into reading these again.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Craig Farrell (3/23/2011)
Jimble,
Is this everything? The final component of the graph looks like it's a 3 way deadlocking but only spids 67 and 84 have descriptions above, and 60 seems like its details got left out.

I though so too, wasn't sure if I was mis-remembering, it looks to me as though there's stuff missing at the end

I'd need to do some research to confirm I remember how to read the OBJECT encoding in this case properly.

May I suggest chapter 42 of SQL MVP Deep Dives 1. (I wrote that chapter)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim McBrim Mr or Mrs. 500 Points: 509 More actions · Answer 4

Hi,

Some more information below, including the schema. Yes, that is the whole of the output, spid 60 looks like it is missing details - but that is all the server gives me. I have added the 1222 output as well below which gives a little more.

The insert query is part of a transaction like so (spid 84):

begin tran

insert 1 job_history row

insert 1 child employee row

commit

The select querys are reporting queries that join the three tables together (spids 60 & 67).

The resource in which we X lock is job_history_if2, presumably the insert has added a new entry to this index. I would expect the select to wait until the commit if it needs to read it, as opposed to going into deadlock.

The server is under a lot of pressure, I wonder if lock escalation might be involved?

Schema:

CREATE TABLE dbo.task

(

task_id integer IDENTITY ( 1,1 ) ,

risk_strategy_type_id int NOT NULL ,

name nvarchar(255) NOT NULL ,

CONSTRAINT task_pk PRIMARY KEY CLUSTERED (task_id ASC)

)

CREATE TABLE dbo.job_history

(

job_history_id bigint IDENTITY ( 1,1 ) ,

task_id integer NOT NULL ,

job_date datetime NOT NULL ,

CONSTRAINT job_history_pk PRIMARY KEY CLUSTERED (job_history_id ASC)

)

CREATE NONCLUSTERED INDEX job_history_if2 ON dbo.job_history

(task_id ASC)

CREATE TABLE dbo.employee

(

employee_id bigint IDENTITY ( 1,1 ) ,

employee_request_id nvarchar(255) NOT NULL,

job_history_id bigint NOT NULL ,

employee_name nvarchar(255) NOT NULL ,

state nvarchar(255) NOT NULL ,

CONSTRAINT employee_pk PRIMARY KEY CLUSTERED (employee_id ASC)

)

CREATE NONCLUSTERED INDEX employee_if2 ON dbo.employee

(job_history_id ASC )

-- RI:

ALTER TABLE dbo.job_history ADD CONSTRAINT job_history_fk2 FOREIGN KEY (task_id) REFERENCES dbo.task(task_id)

ALTER TABLE dbo.employee ADD CONSTRAINT employee_fk2 FOREIGN KEY (job_history_id) REFERENCES dbo.job_history(job_history_id)

1222 output:

2011-03-08 12:30:22.26 spid20s deadlock-list

2011-03-08 12:30:22.26 spid20s deadlock victim=processa38f8088

2011-03-08 12:30:22.26 spid20s process-list

2011-03-08 12:30:22.26 spid20s process id=processa38f8088 taskpriority=0 logused=0 waitresource=KEY: 10:72057594043039744 (3e00cedb1991) waittime=1921 ownerId=37689594 transactionname=SELECT lasttranstarted=2011-03-08T12:30:20.090 XDES=0x80e27cc0 lockMode=S schedulerid=1 kpid=3716 status=suspended spid=60 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-03-08T12:30:19.973 lastbatchcompleted=2011-03-08T12:30:19.973 clientapp=.Net SqlClient Data Provider hostname=XXXXXXX hostpid=4296 loginname=xxxxxxx isolationlevel=read committed (2) xactid=37689594 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2011-03-08 12:30:22.26 spid20s executionStack

2011-03-08 12:30:22.26 spid20s frame procname=adhoc line=1 stmtstart=156 sqlhandle=0x0200000071e251329982dc3194664ec0ff8b249211e29c9b

2011-03-08 12:30:22.26 spid20s SELECT TOP (30)

2011-03-08 12:30:22.26 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.26 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.26 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.26 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.26 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.26 spid20s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2011-03-08 12:30:22.26 spid20s unknown

2011-03-08 12:30:22.26 spid20s inputbuf

2011-03-08 12:30:22.26 spid20s (@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)

2011-03-08 12:30:22.26 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.26 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.26 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.26 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.26 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s process id=process803102c8 taskpriority=0 logused=0 waitresource=KEY: 10:72057594043039744 (3e00cedb1991) waittime=2180 ownerId=37689551 transactionname=SELECT lasttranstarted=2011-03-08T12:30:19.750 XDES=0xac30fb50 lockMode=S schedulerid=1 kpid=2752 status=suspended spid=67 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-03-08T12:30:19.750 lastbatchcompleted=2011-03-08T12:30:19.750 clientapp=.Net SqlClient Data Provider hostname=XXXXXXX hostpid=4296 loginname=xxxxxxx isolationlevel=read committed (2) xactid=37689551 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2011-03-08 12:30:22.27 spid20s executionStack

2011-03-08 12:30:22.27 spid20s frame procname=adhoc line=1 stmtstart=156 sqlhandle=0x020000005f0a5b1b3841c9f6461965464bd3c4aa29922675

2011-03-08 12:30:22.27 spid20s SELECT TOP (30)

2011-03-08 12:30:22.27 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.27 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.27 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.27 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.27 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2011-03-08 12:30:22.27 spid20s unknown

2011-03-08 12:30:22.27 spid20s inputbuf

2011-03-08 12:30:22.27 spid20s (@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)

2011-03-08 12:30:22.27 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.27 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.27 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.27 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.27 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s process id=processa9ddce08 taskpriority=0 logused=5820 waitresource=OBJECT: 10:21575115:0 waittime=2256 ownerId=37689566 transactionname=user_transaction lasttranstarted=2011-03-08T12:30:19.773 XDES=0x93e87970 lockMode=IX schedulerid=1 kpid=460 status=suspended spid=84 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-03-08T12:30:20 lastbatchcompleted=2011-03-08T12:30:19.990 clientapp=.Net SqlClient Data Provider hostname=XXXXXXX hostpid=4540 loginname=yyyyyyyyy isolationlevel=read committed (2) xactid=37689566 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2011-03-08 12:30:22.27 spid20s executionStack

2011-03-08 12:30:22.27 spid20s frame procname=adhoc line=1 stmtstart=172 stmtend=494 sqlhandle=0x02000000483a14237ae8e29062d96edf6757c28ebc87eaab

2011-03-08 12:30:22.27 spid20s insert [dbo].[employee]([employee_request_id], [job_history_id], [employee_name], [state])

2011-03-08 12:30:22.27 spid20s values (@0, @1, @2, @3, @4, @5)

2011-03-08 12:30:22.27 spid20s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2011-03-08 12:30:22.27 spid20s unknown

2011-03-08 12:30:22.27 spid20s inputbuf

2011-03-08 12:30:22.27 spid20s (@0 nvarchar(255),@1 bigint,@2 int,@3 nvarchar(255),@4 nvarchar(255),@5 nvarchar(255))insert [dbo].[employee]([employee_request_id], [job_history_id], [employee_name], [state])

2011-03-08 12:30:22.27 spid20s values (@0, @1, @2, @3, @4, @5)

2011-03-08 12:30:22.27 spid20s select [employee_id]

2011-03-08 12:30:22.27 spid20s from [dbo].[employee]

2011-03-08 12:30:22.27 spid20s where @@ROWCOUNT > 0 and [employee_id] = scope_identity()

2011-03-08 12:30:22.27 spid20s resource-list

2011-03-08 12:30:22.27 spid20s keylock hobtid=72057594043039744 dbid=10 objectname=myDB.dbo.job_history indexname=job_history_if2 id=locka57e1a80 mode=X associatedObjectId=72057594043039744

2011-03-08 12:30:22.27 spid20s owner-list

2011-03-08 12:30:22.27 spid20s waiter-list

2011-03-08 12:30:22.27 spid20s waiter id=processa38f8088 mode=S requestType=wait

2011-03-08 12:30:22.27 spid20s keylock hobtid=72057594043039744 dbid=10 objectname=myDB.dbo.job_history indexname=job_history_if2 id=locka57e1a80 mode=X associatedObjectId=72057594043039744

2011-03-08 12:30:22.27 spid20s owner-list

2011-03-08 12:30:22.27 spid20s owner id=processa9ddce08 mode=X

2011-03-08 12:30:22.27 spid20s waiter-list

2011-03-08 12:30:22.27 spid20s waiter id=process803102c8 mode=S requestType=wait

2011-03-08 12:30:22.27 spid20s objectlock lockPartition=0 objid=21575115 subresource=FULL dbid=10 objectname=myDB.dbo.employee id=locka7d5d580 mode=S associatedObjectId=21575115

2011-03-08 12:30:22.27 spid20s owner-list

2011-03-08 12:30:22.27 spid20s owner id=processa38f8088 mode=S

2011-03-08 12:30:22.27 spid20s owner id=process803102c8 mode=S

2011-03-08 12:30:22.27 spid20s waiter-list

2011-03-08 12:30:22.27 spid20s waiter id=processa9ddce08 mode=IX requestType=wait

2011-03-08 12:30:22.27 spid20s deadlock-list

2011-03-08 12:30:22.27 spid20s deadlock victim=process803102c8

2011-03-08 12:30:22.27 spid20s process-list

2011-03-08 12:30:22.27 spid20s process id=processa38f8088 taskpriority=0 logused=0 waitresource=KEY: 10:72057594043039744 (3e00cedb1991) waittime=1921 ownerId=37689594 transactionname=SELECT lasttranstarted=2011-03-08T12:30:20.090 XDES=0x80e27cc0 lockMode=S schedulerid=1 kpid=3716 status=suspended spid=60 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-03-08T12:30:19.973 lastbatchcompleted=2011-03-08T12:30:19.973 clientapp=.Net SqlClient Data Provider hostname=XXXXXXX hostpid=4296 loginname=xxxxxxx isolationlevel=read committed (2) xactid=37689594 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2011-03-08 12:30:22.27 spid20s executionStack

2011-03-08 12:30:22.27 spid20s frame procname=adhoc line=1 stmtstart=156 sqlhandle=0x0200000071e251329982dc3194664ec0ff8b249211e29c9b

2011-03-08 12:30:22.27 spid20s SELECT TOP (30)

2011-03-08 12:30:22.27 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.27 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.27 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.27 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.27 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2011-03-08 12:30:22.27 spid20s unknown

2011-03-08 12:30:22.27 spid20s inputbuf

2011-03-08 12:30:22.27 spid20s (@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)

2011-03-08 12:30:22.27 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.27 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.27 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.27 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.27 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s process id=process803102c8 taskpriority=0 logused=0 waitresource=KEY: 10:72057594043039744 (3e00cedb1991) waittime=2181 ownerId=37689551 transactionname=SELECT lasttranstarted=2011-03-08T12:30:19.750 XDES=0xac30fb50 lockMode=S schedulerid=1 kpid=2752 status=suspended spid=67 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-03-08T12:30:19.750 lastbatchcompleted=2011-03-08T12:30:19.750 clientapp=.Net SqlClient Data Provider hostname=XXXXXXX hostpid=4296 loginname=xxxxxxx isolationlevel=read committed (2) xactid=37689551 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2011-03-08 12:30:22.27 spid20s executionStack

2011-03-08 12:30:22.27 spid20s frame procname=adhoc line=1 stmtstart=156 sqlhandle=0x020000005f0a5b1b3841c9f6461965464bd3c4aa29922675

2011-03-08 12:30:22.27 spid20s SELECT TOP (30)

2011-03-08 12:30:22.27 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.27 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.27 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.27 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.27 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2011-03-08 12:30:22.27 spid20s unknown

2011-03-08 12:30:22.27 spid20s inputbuf

2011-03-08 12:30:22.27 spid20s (@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)

2011-03-08 12:30:22.27 spid20s [Project1].[name] AS [name],

2011-03-08 12:30:22.27 spid20s [Project1].[employee_name] AS [employee_name],

2011-03-08 12:30:22.27 spid20s [Project1].[name1] AS [name1],

2011-03-08 12:30:22.27 spid20s [Project1].[state] AS [state],

2011-03-08 12:30:22.27 spid20s [Project1].[job_date] AS [job_date]

2011-03-08 12:30:22.27 spid20s process id=processa9ddce08 taskpriority=0 logused=5820 waitresource=OBJECT: 10:21575115:0 waittime=2257 ownerId=37689566 transactionname=user_transaction lasttranstarted=2011-03-08T12:30:19.773 XDES=0x93e87970 lockMode=IX schedulerid=1 kpid=460 status=suspended spid=84 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-03-08T12:30:20 lastbatchcompleted=2011-03-08T12:30:19.990 clientapp=.Net SqlClient Data Provider hostname=XXXXXXX hostpid=4540 loginname=yyyyyyyyy isolationlevel=read committed (2) xactid=37689566 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2011-03-08 12:30:22.27 spid20s executionStack

2011-03-08 12:30:22.27 spid20s frame procname=adhoc line=1 stmtstart=172 stmtend=494 sqlhandle=0x02000000483a14237ae8e29062d96edf6757c28ebc87eaab

2011-03-08 12:30:22.27 spid20s insert [dbo].[employee]([employee_request_id], [job_history_id], [employee_name], [state])

2011-03-08 12:30:22.27 spid20s values (@0, @1, @2, @3, @4, @5)

2011-03-08 12:30:22.27 spid20s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2011-03-08 12:30:22.27 spid20s unknown

2011-03-08 12:30:22.27 spid20s inputbuf

2011-03-08 12:30:22.27 spid20s (@0 nvarchar(255),@1 bigint,@2 int,@3 nvarchar(255),@4 nvarchar(255),@5 nvarchar(255))insert [dbo].[employee]([employee_request_id], [job_history_id], [employee_name], [state])

2011-03-08 12:30:22.27 spid20s values (@0, @1, @2, @3, @4, @5)

2011-03-08 12:30:22.27 spid20s select [employee_id]

2011-03-08 12:30:22.27 spid20s from [dbo].[employee]

2011-03-08 12:30:22.27 spid20s where @@ROWCOUNT > 0 and [employee_id] = scope_identity()

2011-03-08 12:30:22.27 spid20s resource-list

2011-03-08 12:30:22.27 spid20s keylock hobtid=72057594043039744 dbid=10 objectname=myDB.dbo.job_history indexname=job_history_if2 id=locka57e1a80 mode=X associatedObjectId=72057594043039744

2011-03-08 12:30:22.27 spid20s owner-list

2011-03-08 12:30:22.27 spid20s waiter-list

2011-03-08 12:30:22.27 spid20s waiter id=processa38f8088 mode=S requestType=wait

2011-03-08 12:30:22.27 spid20s keylock hobtid=72057594043039744 dbid=10 objectname=myDB.dbo.job_history indexname=job_history_if2 id=locka57e1a80 mode=X associatedObjectId=72057594043039744

2011-03-08 12:30:22.27 spid20s owner-list

2011-03-08 12:30:22.27 spid20s owner id=processa9ddce08 mode=X

2011-03-08 12:30:22.27 spid20s waiter-list

2011-03-08 12:30:22.27 spid20s waiter id=process803102c8 mode=S requestType=wait

2011-03-08 12:30:22.27 spid20s objectlock lockPartition=0 objid=21575115 subresource=FULL dbid=10 objectname=myDB.dbo.employee id=locka7d5d580 mode=S associatedObjectId=21575115

2011-03-08 12:30:22.27 spid20s owner-list

2011-03-08 12:30:22.27 spid20s owner id=processa38f8088 mode=S

2011-03-08 12:30:22.27 spid20s owner id=process803102c8 mode=S

2011-03-08 12:30:22.27 spid20s waiter-list

2011-03-08 12:30:22.27 spid20s waiter id=processa9ddce08 mode=IX requestType=wait

Leo.Miller SSChampion Points: 13051 More actions · Answer 5

It's relativly unusual to see a single resource deadlock, but it does happen. I've coined the phrase "intra object deadlock". I don't know if the phrase has been used by any one else before.

I've seen in most often when one process is trying to do a delete and another process is trying to do an insert on the same table, or simmilar sets of processors that require both data and index changes in opposit orders.

Logically an insert must add the row to the table, then modify the index(s) to point to the relevant row, where the delete uses the index to determine which row will be deleted. It looks like it locks the index, then deletes the row, then comes back to update the index(s).

We now have one process locking the index and waiting for the data page, and another process locking the data page and waiting for the index.

Although row level locking is on by default it may be that one of the processes is updating enough data to have escalated the locks to page level.

I've seen this primarily on very busy systems, particularly where the clustering results in inserts, deletes and index changing updates on the same data page, like cluster and index by datetime.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jimble McBrim (3/25/2011)
Hi,
Some more information below, including the schema. Yes, that is the whole of the output, spid 60 looks like it is missing details - but that is all the server gives me. I have added the 1222 output as well below which gives a little more.

That's why it looked odd, I haven't used the output of 1204 in years.

Will take a look at this when I have a few minutes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

imSQrLy Ten Centuries Points: 1335 More actions · Answer 7

As a quick fix, try using the with (nolock) query hint on your select statements. Many apps are not affected by dirty reads so this may be an option.

Jimmy

"I'm still learning the things i thought i knew!"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

just make sure you know what nolock does first. It's not just about dirty reads.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Leo.Miller (3/27/2011)
It's relativly unusual to see a single resource deadlock, but it does happen. I've coined the phrase "intra object deadlock". I don't know if the phrase has been used by any one else before.
I've seen in most often when one process is trying to do a delete and another process is trying to do an insert on the same table, or simmilar sets of processors that require both data and index changes in opposit orders.
Logically an insert must add the row to the table, then modify the index(s) to point to the relevant row, where the delete uses the index to determine which row will be deleted. It looks like it locks the index, then deletes the row, then comes back to update the index(s).
We now have one process locking the index and waiting for the data page, and another process locking the data page and waiting for the index.
Although row level locking is on by default it may be that one of the processes is updating enough data to have escalated the locks to page level.
I've seen this primarily on very busy systems, particularly where the clustering results in inserts, deletes and index changing updates on the same data page, like cluster and index by datetime.
Cheers
Leo

The official term is "Intra-Query Parallel Thread Deadlocks", coined in 2008 by Bart Duncan. http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

TheSQLGuru (3/28/2011)

The official term is "Intra-Query Parallel Thread Deadlocks", coined in 2008 by Bart Duncan. http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx%5B/quote%5D

Isn't that a single session deadlocking itself?

A deadlock between multiple sessions on a single object is not uncommon. Often to do with key lookups and index inserts/updates.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim McBrim Mr or Mrs. 500 Points: 509 More actions · Answer 11

There is only one cpu on the server, so parallelism isn't involved.

We do have a number of options when it comes to avoiding the deadlock - nolock hints, changing the indexing, changing the select queries or even beefing up the server to prevent the pressure that is leading to the deadlock. Also we have a retry built in, so the code is still functioning correctly (however these retries could well cause performance targets to be missed).

The proble is that I would like to understand what is causing the deadlock, otherwise it feels like I am just brushing it under the carpet.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

GilaMonster (3/28/2011)

TheSQLGuru (3/28/2011)
The official term is "Intra-Query Parallel Thread Deadlocks", coined in 2008 by Bart Duncan. http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx%5B/quote%5D
Isn't that a single session deadlocking itself?
A deadlock between multiple sessions on a single object is not uncommon. Often to do with key lookups and index inserts/updates.

Oopsie! Should have read more carefully! Gail is also correct on her explanation of why single-object deadlocks happen between multiple sessions. See this OTHER blog post (actually 3-part series) from Bart for some help on deadlock troubleshooting: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

WayneS SSC Guru Points: 95460 More actions · Answer 13

Can you post the deadlock xml information?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jim McBrim Mr or Mrs. 500 Points: 509 More actions · Answer 14

Hi, This is the deadlock xml from a different, deadlock - but one that is exactly the same scenario as the previous postings. Again, two selects, one insert.

<deadlock-list>

<process-list>

SELECT TOP (30)

</frame>

unknown </frame>

</executionStack>

(@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)

</inputbuf>

</process>

SELECT TOP (30)

</frame>

unknown </frame>

</executionStack>

(@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30)

</inputbuf>

</process>

insert [dbo].[employee](...)

values (@0, @1, @2, @3, @4, @5) </frame>

unknown </frame>

</executionStack>

(@0 nvarchar(255),@1 bigint,@2 int,@3 nvarchar(255),@4 nvarchar(255),@5 nvarchar(255))insert [dbo].[employee](...)

values (@0, @1, @2, @3, @4, @5)

select [employee_id]

from [dbo].[employee]

where @@ROWCOUNT > 0 and [employee_id] = scope_identity() </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list/>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</objectlock>

</resource-list>

</deadlock>

</deadlock-list>