Problem with Deadlocks

Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
e5ad772e285b7" target="_blank">http://groups.google.ch/group/micro...
e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...