SQL Deadlock

Last Post 29 Aug 2008 12:09 PM by phsatech. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

28 Aug 2008 03:14 PM
We run a OLAP Peoplesoft Database (SQL Server 2000) and occasionally have deadlocks on specific tables. One particular process that deadlocks I have captured with Profiler. (Note SQL Statements cannot be changed/modified with this type of system for specific processes)

Here are the details:

SQL Deadlock Chain Statement (3 SPIDS are involved run the same statement shown below):


This above statement gets deadlocked.

The current Clustered Unique Index has the following keys:


When I run the execution plan it runs a Index Seek. Is it possible even in a index seek condition to have a deadlock? Would it be an idea to create a Non-Clustered Index on one of the above keys?

Appreciate insight
New Member
New Member

28 Aug 2008 11:39 PM
You can't possibly deadlock on a singular SELECT statement.

There must be a transaction involved with INSERT/UPDATE/DELETE.

You can turn on a trace flag to troubleshoot deadlocks: http://msdn.microsoft.com/en-us/lib...L.80).aspx

The deadlock messages will come into the server error log.

Independently, rebuild your indexes with FILLFACTOR:

80 - highly dynamic tables
90 - dynamic tables
95 - less dynamic tables
100 - static tables

Reindexing should be done every weekend. Very busy online systems may require nightly reindexing.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/

New Member
New Member

29 Aug 2008 12:09 PM
Thnxs for the input. So from you notes it sounds imposible for a deadlock on 3 selects at the same time to the same table. I will be doing a re-index on the tables this wknd with the needed fill factor. As for the deadlock, it may occur again inspite of a reindex. I will set the 1204 Trace flag next week. The only caveat is their will probably be alot of sp_prepexec or sp_cursorexec in the Input Buffer so it may be a bit harder to find the exact SQL's. I believe from the Profiler trace that there is an insert just around the deadlock time on the same table. So with the trace flag enabled I should at least see the Statement type which is a start.
Open for other comments or suggestions.

New Member
New Member

03 Sep 2008 01:39 AM

Originally posted by: phsatech
I believe from the Profiler trace that there is an insert just around the deadlock time on the same table. thnxs

Yes, there must be INSERT, UPDATE or DELETE involved beside SELECTs.

Acceptable Use Policy