Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, Mefisto
Can you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, MefistoCan you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, MefistoCan you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with deadocks

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...Aswers in microsoft.public.sqlserver.server
--
--
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...|||http://www.microsoft.com/communitie...80-e67d8812bd2d
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...|||In your from statement use WITH (NOLOCKS) when you are selecting data.
Rick
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:B5CBFB12-BF8F-4BA8-B71A-8D9A893C66F6@.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...

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/colu...gdeadlocks.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:
http://groups.google.ch/group/micros...1e5ad772e285b7
--
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...

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/columnists/skumar/tracingdeadlocks.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:
http://groups.google.ch/group/microsoft.public.sqlserver.programming/browse_thread/thread/6f618594592ab8d7/381e5ad772e285b7?lnk=st&q=tibor+deadlock&rnum=1&hl=en#381e5ad772e285b7
--
--
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...

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...