DEADLOCK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DEADLOCK

Author

Topic

agiotti
Starting Member

37 Posts

Posted - 2010-08-19 : 11:43:43

SQL Server 2005 Enterprise.

Process deadlocking on metadata subtype indexstats within the tempdb database. Can this be related to auto update stats kicking in?

Procedure involved creates one temporary table, inserts data, then performs a series of updates.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-19 : 15:40:17

please explain a bit more detailed. and deadlock error would also been helpful.

agiotti
Starting Member

37 Posts

Posted - 2010-08-20 : 09:19:11

2010-08-19 10:49:03.240 spid4s Deadlock encountered .... Printing deadlock information
2010-08-19 10:49:03.240 spid4s Wait-for graph
2010-08-19 10:49:03.240 spid4s NULL
2010-08-19 10:49:03.240 spid4s Node:1
2010-08-19 10:49:03.240 spid4s METADATA: database_id = 2 INDEXSTATS(object_id = 1397119984, index_id or stats_id = 2) CleanCnt:3 Mode:Sch-M Flags: 0x0
2010-08-19 10:49:03.240 spid4s Grant List 2:
2010-08-19 10:49:03.240 spid4s Owner:0x00000001EFE27140 Mode: Sch-M Flg:0x0 Ref:3 Life:02000000 SPID:129 ECID:0 XactLockInfo: 0x000000029A929008
2010-08-19 10:49:03.240 spid4s SPID: 129 ECID: 0 Statement Type: READTEXT Line #: 75
2010-08-19 10:49:03.240 spid4s Input Buf: Language Event: /*/////////////////////////////////////////////////////////////////////
//
// Procedure:
//
// Parent: n/a
//
// Purpose: Update the security_id_xfer column in the BenchMark_Security table with the
// security_id from the security table
2010-08-19 10:49:03.240 spid4s Requested By:
2010-08-19 10:49:03.240 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000351EAEB60 Mode: Sch-S SPID:129 BatchID:0 ECID:0 TaskProxy:(0x00000003384912E0) Value:0xe6782fc0 Cost:(0/676)
2010-08-19 10:49:03.240 spid4s NULL
2010-08-19 10:49:03.240 spid4s Victim Resource Owner:
2010-08-19 10:49:03.240 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000351EAEB60 Mode: Sch-S SPID:129 BatchID:0 ECID:0 TaskProxy:(0x00000003384912E0) Value:0xe6782fc0 Cost:(0/676)
2010-08-19 10:49:03.240 spid24s deadlock-list
2010-08-19 10:49:03.240 spid24s deadlock victim=process3c31588
2010-08-19 10:49:03.240 spid24s process-list
2010-08-19 10:49:03.240 spid24s process id=process3c31588 taskpriority=0 logused=676 waitresource=METADATA: database_id = 2 INDEXSTATS(object_id = 1397119984, index_id or stats_id = 2) waittime=3328 ownerId=566965646 transactionname=CREATE STATISTICS lasttranstarted=2010-08-19T10:48:59.910 XDES=0x351eaeb60 lockMode=Sch-S schedulerid=5 kpid=7048 status=suspended spid=129 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-08-19T10:48:59.370 lastbatchcompleted=2010-08-19T10:44:30.840 clientapp=Microsoft SQL Server Management Studio - Query hostname=JBHASKARA-07 hostpid=3192 loginname=MyID isolationlevel=read committed (2) xactid=566965646 currentdb=2 lockTimeout=4294967295 clientoption1=673253472 clientoption2=390200
2010-08-19 10:49:03.240 spid24s executionStack
2010-08-19 10:49:03.240 spid24s frame procname=adhoc line=75 stmtstart=5122 stmtend=6862 sqlhandle=0x02000000277ed42563141f9b3e5587b8c1e9da26ea71ad87
2010-08-19 10:49:03.240 spid24s frame procname=unknown line=75 stmtstart=5122 stmtend=6862 sqlhandle=0x0100060001000000b0d9d6cb010000000000000000000000
2010-08-19 10:49:03.240 spid24s unknown
2010-08-19 10:49:03.240 spid24s frame procname=adhoc line=75 stmtstart=5122 stmtend=6862 sqlhandle=0x02000000e016f61248e35db73e06af2578e529e9a9a13b03
2010-08-19 10:49:03.240 spid24s update #brenchMark_xref
2010-08-19 10:49:03.240 spid24s inputbuf
2010-08-19 10:49:03.240 spid24s /*/////////////////////////////////////////////////////////////////////
2010-08-19 10:49:03.240 spid24s //
2010-08-19 10:49:03.240 spid24s // Procedure:
2010-08-19 10:49:03.240 spid24s //

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources