Deadlocks and Parallel Query Processing

SQL Server 2000 SP3A

Last week one of our processes starting issuing or suffering deadlock
detected errors every 15 minutes or so.

I have read several articles at MS on the subject. I set a couple of
startup parameters related to producing deadlock detection information
and ran SQL Profiler. I found the SQL statements being issued by the
deadlocked statements. In every deadlock the same UPDATE statement
appears however the data values being searched on are different. The
best I can tell from trying to query the actual data each update hits
only one or very few rows. No indexed column is updated so the indexes
should not be the source of conflict.
Looking at the query I noticed that the query does not have an
available index and Query Analyzer shows that the full table scan is
being done in parallel.

My question: Does SQL Server change or modify its locking rules when
queries are converted to be ran using parallel processing? If so, do
you have a reference?

Here is the deadlock entries posted to the error log:
SPID=167
ResType:LockOwner Stype:'OR' Mode: IX SPID:63 ECID:0 Ec:(0x65971510)
Value:0x3c577e60 Cost:(0/0)
Input Buf: Language Event: UPDATE Station_Upload set
Station_Accept_Status = 'ACC',HeadStatus =
'ACC',LastProcessedSta='110',HeadPartType='1' WHERE Part_Serial_No =
'SCH1119323' AND Station = 'H110'

SPID=63
ResType:LockOwner Stype:'OR' Mode: IX SPID:167 ECID:0 Ec:(0x65801510)
Value:0x3c27d060 Cost:(0/0)
Input Buf: Language Event: UPDATE Station_Upload set
Station_Accept_Status = 'ACC',HeadStatus =
'ACC',LastProcessedSta='70',HeadPartType='1' WHERE Part_Serial_No =
'SCH1119060' AND Station = 'H070'

I have suggested adding an index to support the query.

Any ideas?
Thanks -- Mark D Powell --

.

Relevant Pages

  • Re: SQL Server Performance Issue
    ... Deadlock were already there in the system. ... but comes the high log flush time issue and system is slow down. ... other log files, does it solve problem of high log flush time. ... You will get deadlock tracaes in the SQL Server error log by adding ...
    (microsoft.public.sqlserver.server)
  • Re: DBCC TRACEON did not capture info on deadlocking
    ... enabled tracing through DBCC TRACEON. ... And is there any other alternatives I have in capturing the deadlock ... Links for SQL Server Books Online: ...
    (comp.databases.ms-sqlserver)
  • Re: Select statement Causing Table Locks
    ... Ok, it is technically a blocking chain, but this query does not continue. ... a deadlock is when two or more processes all block each ... SQL Server will detect deadlock situations, ...
    (microsoft.public.sqlserver.programming)
  • Strange error code for deadlock
    ... I am currently writing an deadlock handler for our application (a COM ... Having searched both ADO and SQL Server references, ... "NativeError" field. ... error message to find "deadlock victim" for example); ...
    (microsoft.public.vb.database.ado)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)