SQL Deadlock

Last Post 29 Aug 2008 01:09 PM by phsatech. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
phsatech
New Member
New Member

--
28 Aug 2008 04: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):

SELECT MAX(MESSAGE_SEQ) FROM PS_MESSAGE_LOG WHERE PROCESS_INSTANCE = '@P1'

This above statement gets deadlocked.

The current Clustered Unique Index has the following keys:

PROCESS_INSTANCE, MESSAGE_SEQ

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
SQLUSA
New Member
New Member

--
29 Aug 2008 12:39 AM
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/

phsatech
New Member
New Member

--
29 Aug 2008 01: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.

thnxs
SQLUSA
New Member
New Member

--
03 Sep 2008 02:39 AM
quote:

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.


You are not authorized to post a reply.

Acceptable Use Policy