[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Urgent Deadlock problem

Posted on 2004-08-18
9
Medium Priority
?
1,726 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
[X]
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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 11

Accepted Solution

by:
vc01778 earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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