Deadlock in SQL Server 2005 sp2 running queries using temp tables or table variables

am 12.09.2007 17:56:59 von Sputnik

We are running a query in SQL Server 2005 that makes use of temporary
tables and table variables. Occassionally a call to this query locks
up and subsequent calls timeout. The only way to get out of this is to
restart SQL Server which is a real pain.

This is an extract from the error log with the relevant information:

2007-09-12 11:43:53.21 spid4s Deadlock encountered .... Printing
deadlock information
2007-09-12 11:43:53.21 spid4s Wait-for graph
2007-09-12 11:43:53.21 spid4s
2007-09-12 11:43:53.21 spid4s Node:1

2007-09-12 11:43:53.21 spid4s OBJECT: 2:12221068:0
CleanCnt:3 Mode:Sch-S Flags: 0x0
2007-09-12 11:43:53.23 spid4s Grant List 2:
2007-09-12 11:43:53.23 spid4s Owner:0x03E85540 Mode: Sch-S
Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:0 XactLockInfo: 0x066F59CC
2007-09-12 11:43:53.23 spid4s SPID: 65 ECID: 0 Statement Type:
INSERT Line #: 13
2007-09-12 11:43:53.23 spid4s Input Buf: RPC Event: Proc
[Database Id = 5 Object Id = 1362103893]
2007-09-12 11:43:53.23 spid4s Requested By:
2007-09-12 11:43:53.23 spid4s ResType:LockOwner Stype:'OR'Xdes:
0x271F3178 Mode: Sch-M SPID:65 BatchID:0 ECID:0 TaskProxy:(0x26E78364)
Value:0x4938be0 Cost:(N/A)
2007-09-12 11:43:53.23 spid4s Deadlock monitor failed to resolve
this deadlock.
Server may require restart to recover from this condition
2007-09-12 11:43:53.23 spid14s deadlock-list
2007-09-12 11:43:53.23 spid14s deadlock victim=process0
2007-09-12 11:43:53.23 spid14s process-list
2007-09-12 11:43:53.23 spid14s process id=process91eb68
waitresource=OBJECT: 2:12221068:0 waittime=12625 ownerId=30369101
transactionnameüheckAndCleanupCachedTempTable
lasttranstarted=2007-09-12T11:43:40.607 XDES=0x271f3178 lockMode=Sch-M
schedulerid=3 kpid=556 status=suspended spid=65 sbid=0 ecid=0
priority=0 transcount=1 lastbatchstarted=2007-09-12T11:43:40.403
lastbatchcompleted=2007-09-12T11:43:40.403 clientapp=.Net SqlClient
Data Provider hostname=DJR_SERVER_1 hostpid=3396 loginname=PPUser
isolationlevel=read committed (2) xactid=30368721 currentdb=5
lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2007-09-12 11:43:53.23 spid14s executionStack
2007-09-12 11:43:53.23 spid14s frame
procname=DJR_DATABASE.dbo.CurrentSchedule line=13 stmtstart=860
stmtend=1188
sqlhandle=0x03000500a7cf9334a4dff000609900000000000000000000
2007-09-12 11:43:53.23 spid14s insert into @ScheduleTable
2007-09-12 11:43:53.23 spid14s select * from
dbo.LiveSchedule(@channelid)
2007-09-12 11:43:53.23 spid14s -- Get the last item in the
Automation Xml i.e. the item with the greatest start time
2007-09-12 11:43:53.23 spid14s frame
procname=DJR_DATABASE.dbo.sp_selectcontentfieldstrails line=95
stmtstart=6514 stmtend=8304
sqlhandle=0x03000500abe7e85620ebb400a69900000100000000000000
2007-09-12 11:43:53.23 spid14s if ((@master = 0) and
2007-09-12 11:43:53.23 spid14s (exists (select * from
#TrailScheduleList as tsl
2007-09-12 11:43:53.23 spid14s join ContentProgramme as
cp
2007-09-12 11:43:53.23 spid14s on tsl.ProgrammeID =
cp.ProgrammeID
2007-09-12 11:43:53.23 spid14s join
2007-09-12 11:43:53.23 spid14s (
2007-09-12 11:43:53.23 spid14s -- This finds the next
programme id in the Schedule on the specified channel
2007-09-12 11:43:53.23 spid14s -- that is not a
continuation of the current programme i.e. that is the
2007-09-12 11:43:53.23 spid14s -- first part of the
programme
2007-09-12 11:43:53.23 spid14s select top 1 Identifier
as ProgrammeID
2007-09-12 11:43:53.23 spid14s from
dbo.CurrentSchedule(@channelid)
2007-09-12 11:43:53.23 spid14s where Start >
@sequencestart and FirstProgrammePart = 1
2007-09-12 11:43:53.23 spid14s order by Start
2007-09-12 11:43:53.23 spid14s ) as n
2007-09-12 11:43:53.23 spid14s on n.ProgrammeID =
cp.ProgrammeID
2007-09-12 11:43:53.23 spid14s where n.ProgrammeID =
cp.ProgrammeID and
2007-09-12 11:43:53.23 spid14s -- Make sure that the
programme that it is associated with is within 9 minutes of coming on
air
2007-09-12 11:43:53.23 spid14s tsl.Start <=
(DATEADD(second, 9 * 60, @sequencestart)))))
2007-09-12 11:43:53.23 spid14s frame
procname=DJR_DATABASE.dbo.sp_selectcontentfields line=143
stmtstart=9438 stmtend=9892
sqlhandle=0x03000500550e3051be910501949900000100000000000000
2007-09-12 11:43:53.23 spid14s insert into #ContentData
2007-09-12 11:43:53.23 spid14s exec
sp_selectcontentfieldstrails @contentid, @channelid,
@contentchannelid, @sequencestart, @pagestart, @roundminute,
2007-09-12 11:43:53.23 spid14s @master,
@description, @categoryid, @voiceoverfile output
2007-09-12 11:43:53.23 spid14s inputbuf
2007-09-12 11:43:53.23 spid14s Proc [Database Id = 5 Object Id =
1362103893]
2007-09-12 11:43:53.23 spid14s resource-list
2007-09-12 11:43:53.23 spid14s objectlock lockPartition=0
objid=12221068 subresource=FULL dbid=2 objectname=tempdb.dbo.#00BA7A8C
id=lock3e2cac0 mode=Sch-S associatedObjectId=12221068
2007-09-12 11:43:53.23 spid14s owner-list
2007-09-12 11:43:53.23 spid14s owner id=process91eb68
mode=Sch-S
2007-09-12 11:43:53.23 spid14s waiter-list
2007-09-12 11:43:53.23 spid14s waiter id=process91eb68
mode=Sch-M requestType=wait
2007-09-12 11:43:53.49 spid4s Using 'dbghelp.dll' version '4.0.5'
2007-09-12 11:43:53.51 spid4s **Dump thread - spid = 4, PSS =
0x03E087D8, EC = 0x03E087E0
2007-09-12 11:43:53.51 spid4s ***Stack Dump being sent to d:
\Database Logs\SQLDump0022.txt
2007-09-12 11:43:53.51 spid4s *
************************************************************ *******************
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s * BEGIN STACK DUMP:
2007-09-12 11:43:53.51 spid4s * 09/12/07 11:43:53 spid 4
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s * Unresolved deadlock
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s *
************************************************************ *******************
2007-09-12 11:43:53.51 spid4s *
------------------------------------------------------------ -------------------
2007-09-12 11:43:53.51 spid4s * Short Stack Dump
2007-09-12 11:43:53.60 spid4s Stack Signature for the dump is
0x000000F3
2007-09-12 11:43:56.89 spid4s External dump process return code
0x20000001.
External dump process returned no errors.

Has anyone got an ideas what might be going wrong?

Regards

Ian Hannah

Re: Deadlock in SQL Server 2005 sp2 running queries using temp tables or table variables

am 13.09.2007 00:08:36 von Erland Sommarskog

Sputnik (id_hannah@hotmail.com) writes:
> We are running a query in SQL Server 2005 that makes use of temporary
> tables and table variables. Occassionally a call to this query locks
> up and subsequent calls timeout. The only way to get out of this is to
> restart SQL Server which is a real pain.
>...
> 2007-09-12 11:43:53.23 spid4s Deadlock monitor failed to resolve
> this deadlock.
> Server may require restart to recover from this condition

Looks bad.

This is definitely a bug in SQL Server, and I would recommend that you
open a case with Microsoft so that they can analyse it.

I know that there are some bugs around handling of temp tables and table
variables that have been fixed post-SP2, but it could also be something
else.

Note that while MS may charge you initially when you open the case, once
they have confirmed that it is a bug, you should be refunded.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx