• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1771
  • Last Modified:

Urgent Deadlock problem

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
johan_asplund
Asked:
johan_asplund
  • 3
  • 3
1 Solution
 
illCommented:
use query hint WITH (NOLOCK) , if you can.
0
 
vc01778Commented:
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
 
johan_asplundAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
vc01778Commented:
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
 
johan_asplundAuthor Commented:
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
 
vc01778Commented:
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
 
johan_asplundAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now