Solved

Urgent Deadlock problem

Posted on 2004-08-18
9
1,677 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now