Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Urgent Deadlock problem

Posted on 2004-08-18
9
1,691 Views
Last Modified: 2007-11-27
Hello

Im running a .NET application against a MS SQL Server 2000 database. But sometimes i get the following error message:
Transaction (Process ID 57) was deadlocked on {thread | communication buffer} resources with another process and has been chosen as the deadlock victim.

The problem is that i dont have any transactions. I only have a lot of Select statements.
 
I tested to run the SQL Profiler and got
Lock:Deadlock chain: "parallel query worker thread was involved in a deadlock"

i also see sp_sqlagent_get_perf_counters 20 sec

Br
Johan
0
Comment
Question by:johan_asplund
  • 3
  • 3
9 Comments
 
LVL 12

Expert Comment

by:ill
ID: 11828877
use query hint WITH (NOLOCK) , if you can.
0
 
LVL 11

Expert Comment

by:vc01778
ID: 11829295
Apparently the parallel query execution plan is such that it causes a deadlock.  You can disable parallel query with this option:

OPTION(MAXDOP 1)

VC
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 11829307
I have that already in the query. But i still get the same error.

I'm starting to suspect that it has something to do with the server. The server is an P III dual processor. When i test the same on my local machine i dont get the same problem.

Br
Johan

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 11

Accepted Solution

by:
vc01778 earned 500 total points
ID: 11829419
Try this:

sp_configure 'max degree of parallelism', 1
go
reconfigure with override


and see what happens.

Also,  please read this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;266372&Product=sql

VC
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 11829508
Hello

It seems that the problem is caused by a bug in SQL server. The server that i used did not have SP3 installed

Also found this.
http://support.microsoft.com/default.aspx?scid=kb;en-us;315662

(I will be back after i have installed the SP3)

Br
Johan
0
 
LVL 11

Expert Comment

by:vc01778
ID: 11830018
I have SP3 installed but I occasionally experience the same problem (which is cured by DOP=1).  As you can see from the description,  they actually did not fix the bug but rather improved the notification about the deadlock.

Presumably,  the optimizer should not create a plan that leads to a deadlock in the first place ...

VC
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 11830919
Yes you are right

i tested it again an it did not work after installing SP3

I will test the

sp_configure 'max degree of parallelism', 1
go
reconfigure with override

and see what hapends

Br
Johan
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question