Stored procedure often causes a deadlock

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

Transact-SQL (2005)

Stored procedure often causes a deadlock

Author

Topic

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-06-23 : 05:36:15

We have a stored procedure that runs daily (6am) through a job. The table is always truncated and newly generated. There is also a fulltext index on the table.
The following steps happen in the procedure:

1.) Fulltext index is disabled
2.) Table is truncated
3.) insert into Table
4.) Several Updates on the table
5.) Fulltext index is enabled
6.) Rebuild All indexes on this table

I show you a piece of the deadlock graph:
<EVENT_INSTANCE>
<EventType>DEADLOCK_GRAPH</EventType>
<PostTime>2009-06-23T05:34:04.250</PostTime>
<SPID>5</SPID>
<TextData>
<deadlock-list>
<deadlock victim="processbd51f8">
<process-list>
<process id="process9285c8" schedulerid="2" kpid="0" />
<process id="process9287a8" taskpriority="0" logused="20090" waittime="3500" schedulerid="2" kpid="6016" status="suspended" spid="102" sbid="0" ecid="8" priority="0" transcount="0" lastbatchstarted="2009-06-23T05:33:21.200" lastbatchcompleted="2009-06-23T05:33:21.200" clientapp="SQLAgent - TSQL JobStep (Job 0x82693E80200838458084D75A243D8DCE : Step 12)" hostname="HAMBRUDS015" hostpid="2264" isolationlevel="read committed (2)" xactid="61897170" currentdb="16" lockTimeout="4294967295" clientoption1="671222112" clientoption2="128056">
<executionStack>
<frame procname="unknown" line="33" stmtstart="2268" stmtend="11738" sqlhandle="0x03001000bbf707272ab2b600019c00000100000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x01001000f413c825a879dd5e000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf />
</process>
<process id="process929018" taskpriority="0" logused="20115" waittime="2640" schedulerid="2" kpid="2276" status="suspended" spid="102" sbid="0" ecid="14" priority="0" transcount="0" lastbatchstarted="2009-06-23T05:33:21.200" lastbatchcompleted="2009-06-23T05:33:21.200" clientapp="SQLAgent - TSQL JobStep (Job 0x82693E80200838458084D75A243D8DCE : Step 12)" hostname="HAMBRUDS015" hostpid="2264" isolationlevel="read committed (2)" xactid="61897170" currentdb="16" lockTimeout="4294967295" clientoption1="671222112" clientoption2="128056">
<executionStack>
<frame procname="unknown" line="33" stmtstart="2268" stmtend="11738" sqlhandle="0x03001000bbf707272ab2b600019c00000100000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x01001000f413c825a879dd5e000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf />

Can someone tell me why I often get deadlocks on this procedure?

Thanks.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-23 : 06:53:40

What you pasted doesn't really tell us anything...it's what's behind each sql handle (<frame procname="unknown" line="1" sqlhandle="0x01001000f413c825a879dd5e000000000000000000000000">unknown </frame>) that really matters.

Do you know when the deadlock occurs? During which of the steps (1-5)? If so you need to provide details about that..

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources