Solved

Urgent Deadlock problem

Posted on 2004-08-18
9
1,701 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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 …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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