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_Statu
s = 'ACC',HeadStatus =
 'ACC',LastProcessedS
 ta='110',HeadPartTyp
e='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_Statu
s = 'ACC',HeadStatus =
 'ACC',LastProcessedS
 ta='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 --

Mark D Powell (Mark.Powell@eds.com)  writes:
> 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?

No. I would guess that what happens that both grab a table lock to
scan the table. When they found the row they are looking for, they
try to get an exclusive lock on that row, which they can't because the
other has a shared lock on the table.

> I have suggested adding an index to support the query.

That sounds like an excellent idea.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Erland, thank you for the reply.  I say it a few hours of your post but
got sidetracked on the followup.

About 1/3 of the data in the table was obsolete so we deleted it.  The
deadlocks were still occurring.  I ran maintenance on the database
during a break period.  The update is once again showing as being
single threaded by the query plan and the deadlocks errors have
disappeared.

We have not applied the index because the customer wants to hold off on
changing the customized by the providing vendor application until their
consultant(s) have had time to review the code and come up with a
comprehensive list of changes.

Thanks again.  -- Mark D Powell --

Mark D Powell (Mark.Powell@eds.com)  writes:
> About 1/3 of the data in the table was obsolete so we deleted it.  The
> deadlocks were still occurring.  I ran maintenance on the database
> during a break period.  The update is once again showing as being
> single threaded by the query plan and the deadlocks errors have
> disappeared.

Hm, so maybe locks are acquired in a different order when the plan is
single-threaded.

> We have not applied the index because the customer wants to hold off on
> changing the customized by the providing vendor application until their
> consultant(s) have had time to review the code and come up with a
> comprehensive list of changes.

I still think the index is a good idea. If nothing else, it will take
off load from the machine.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

A correctly designed index does things like cut execution time by a
millionth, speed things up, with NO downsides.

A bad index can indeed hurt things somewhat.