Deadlock that does not make sence.

I am getting a deadlock error message that is perplexing me.
Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The
database and stored procedures are what we have in common. We are doing some testing now to see how well these different
applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records, we
have received a deadlock error in the ADO.NET application.
We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the ADO.NET
side is for the entire select.
I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I would
think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't there
be a timeout instead of a deadlock?
The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
deadlock.
Any insight into this perplexing problem would be very welcomed.
MikeHard to say without having access to your db, but the scenario you describe
doesn't sound like deadlock proof.
Sometimes deadlock might occur because of lack of appropriate indexes, and
sometimes because of the way your applications/transactions are written.
Best way to figure this out is to use Profiler to trace statement starting,
deadlock and deadlock chain events. Once you identify the conflicting
processes, reopen the trace file and filter by process id's. Move your way
upwards from the deadlock event and write down a time-based chain of events
under columns representing the different processes. Examine the tables'
indexes and try to figure out the cause of the deadlock.
--
BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com
"Mike Malter" <mikemalter@.nospam.com> wrote in message
news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET
with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing
some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO
edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO
side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked
by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the
case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a
deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike
>|||First of all, do you have triggers on your tables? If you do, that's the
place you need to check.
Second, make sure both your applications use Optimistic concurrency control.
Especially the one that only reads should be optimistic.
Finally,
open your books online and check for the "ROWLOCK" documentation. It gives
you a list of query hints like "Read Past" which skips locked rows, or
HoldLock which will wait for the records.
"Mike Malter" <mikemalter@.nospam.com> wrote in message
news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET
with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing
some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO
edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO
side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked
by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the
case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a
deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike
>|||Just some general information here. Maybe it is of some help to you.
In SQL-Server, there are basically two situations that may lead to
deadlocks:
1. Locks are acquired in different order in different transactions. This
is the 'classic' deadlock. The chance is increased when the transactions
can not use an index to lock at row level. The chance is also increased
when there are many lock requests, or when memory is low.
2. Lock escalation (from row/page locks to table locks) can lead to
deadlocks. If lock escalation is caused by low memory, then lowering
your locking granularity from row locks to page locks may help.
Gert-Jan
Mike Malter wrote:
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike|||May be it will be more helpful if you can enable the Trace Flags (1204,
3605) and run the scenario you are describing post the Portions of the
deadlock images that (if any) get logged in the SQL Error logs.
DBCC TRACEON(1204, 3605)
--
HTH
Satish Balusa
Corillian Corp.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uKiIeAjdDHA.1632@.TK2MSFTNGP12.phx.gbl...
> Hard to say without having access to your db, but the scenario you
describe
> doesn't sound like deadlock proof.
> Sometimes deadlock might occur because of lack of appropriate indexes, and
> sometimes because of the way your applications/transactions are written.
> Best way to figure this out is to use Profiler to trace statement
starting,
> deadlock and deadlock chain events. Once you identify the conflicting
> processes, reopen the trace file and filter by process id's. Move your way
> upwards from the deadlock event and write down a time-based chain of
events
> under columns representing the different processes. Examine the tables'
> indexes and try to figure out the cause of the deadlock.
> --
> BG, SQL Server MVP
> Solid Quality Learning
> www.solidqualitylearning.com
>
> "Mike Malter" <mikemalter@.nospam.com> wrote in message
> news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET
> with EnterpriseServices are coexisting in this project. The
> > database and stored procedures are what we have in common. We are doing
> some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO
> edits random records and ADO.NET selects all records, we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the
ADO
> side is per record, and the transaction on the ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is
locked
> by the edit, however the edit is so quick that I would
> > think that the select would not be blocked very long. If that were the
> case, where the select was blocked too long, wouldn't there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a
> deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike
> >
> >
>|||Gert,
Thanks for your reply.
The deal in this case is that there is only one table called parent.
What I am doing is a select on this table for two columns while the other system is doing random updates.
I am wondering if you could elaborate a little more about too many lock requests.
Thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> Just some general information here. Maybe it is of some help to you.
> In SQL-Server, there are basically two situations that may lead to
> deadlocks:
> 1. Locks are acquired in different order in different transactions. This
> is the 'classic' deadlock. The chance is increased when the transactions
> can not use an index to lock at row level. The chance is also increased
> when there are many lock requests, or when memory is low.
> 2. Lock escalation (from row/page locks to table locks) can lead to
> deadlocks. If lock escalation is caused by low memory, then lowering
> your locking granularity from row locks to page locks may help.
> Gert-Jan
>
> Mike Malter wrote:
> >
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project.
The
> > database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records,
we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the
ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I
would
> > think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't
there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike|||Vassilis,
Thanks for your reply.
We do not have any triggers on the table in this test.
The one that reads is using ADO.NET. Do you know how to set concurrency control to Optimistic in ADO.NET?
I will look into query hints, thanks.
Mike
"Vassilis Devletoglou" <vdev@.acn.gr> wrote in message news:OjlLowjdDHA.1876@.TK2MSFTNGP12.phx.gbl...
> First of all, do you have triggers on your tables? If you do, that's the
> place you need to check.
> Second, make sure both your applications use Optimistic concurrency control.
> Especially the one that only reads should be optimistic.
> Finally,
> open your books online and check for the "ROWLOCK" documentation. It gives
> you a list of query hints like "Read Past" which skips locked rows, or
> HoldLock which will wait for the records.
>
>
> "Mike Malter" <mikemalter@.nospam.com> wrote in message
> news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET
> with EnterpriseServices are coexisting in this project. The
> > database and stored procedures are what we have in common. We are doing
> some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO
> edits random records and ADO.NET selects all records, we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the ADO
> side is per record, and the transaction on the ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is locked
> by the edit, however the edit is so quick that I would
> > think that the select would not be blocked very long. If that were the
> case, where the select was blocked too long, wouldn't there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a
> deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike
> >
> >
>|||The maximum number of outstanding locks is determined by some internal
formula. A major factor in this formula is the total amount of memory
that is available to SQL-Server. If the number of actual locks comes
close to this maximum number, lock escalation will occur/increase. In
that situation, SQL-Server is more likely to 'trade in' several row
locks or page locks for one table lock. If this happens for two
processes that have locks on the same table, this results in a deadlock.
BOL has a special section for "number of locks the system can allocate"
Gert-Jan
Mike Malter wrote:
> Gert,
> Thanks for your reply.
> The deal in this case is that there is only one table called parent.
> What I am doing is a select on this table for two columns while the other system is doing random updates.
> I am wondering if you could elaborate a little more about too many lock requests.
> Thanks.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> > Just some general information here. Maybe it is of some help to you.
> >
> > In SQL-Server, there are basically two situations that may lead to
> > deadlocks:
> > 1. Locks are acquired in different order in different transactions. This
> > is the 'classic' deadlock. The chance is increased when the transactions
> > can not use an index to lock at row level. The chance is also increased
> > when there are many lock requests, or when memory is low.
> >
> > 2. Lock escalation (from row/page locks to table locks) can lead to
> > deadlocks. If lock escalation is caused by low memory, then lowering
> > your locking granularity from row locks to page locks may help.
> >
> > Gert-Jan
> >
> >
> > Mike Malter wrote:
> > >
> > > I am getting a deadlock error message that is perplexing me.
> > >
> > > Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project.
> The
> > > database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> > > applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records,
> we
> > > have received a deadlock error in the ADO.NET application.
> > >
> > > We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the
> ADO.NET
> > > side is for the entire select.
> > >
> > > I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I
> would
> > > think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't
> there
> > > be a timeout instead of a deadlock?
> > >
> > > The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> > > deadlock.
> > >
> > > Any insight into this perplexing problem would be very welcomed.
> > >
> > > Mike|||Quick clarification: Simultaneous attempts to escalate locks on the same
table by two processes will never directly lead to deadlock. If a table
level lock (S or X) can not be acquired when escalation is attempted, the
escalation attempt is cancelled and locks will continue to be acquired at
the row/page levels.
Mike: You should verify that the update transactions always commit after a
single update. If not, the randomness of the updates could easily lead to
deadlocks. What is the isolation mode for the select -- read committed, or
stronger?
It would also be helpful if you posted the queries involved and the table
schema -- deadlocks are possible if the update modifies non-clustered index
keys even if each update transasction only modifies one row.
--
Santeri Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F5E2280.E7455FE3@.toomuchspamalready.nl...
> The maximum number of outstanding locks is determined by some internal
> formula. A major factor in this formula is the total amount of memory
> that is available to SQL-Server. If the number of actual locks comes
> close to this maximum number, lock escalation will occur/increase. In
> that situation, SQL-Server is more likely to 'trade in' several row
> locks or page locks for one table lock. If this happens for two
> processes that have locks on the same table, this results in a deadlock.
> BOL has a special section for "number of locks the system can allocate"
> Gert-Jan
>
> Mike Malter wrote:
> >
> > Gert,
> >
> > Thanks for your reply.
> >
> > The deal in this case is that there is only one table called parent.
> >
> > What I am doing is a select on this table for two columns while the
other system is doing random updates.
> >
> > I am wondering if you could elaborate a little more about too many lock
requests.
> >
> > Thanks.
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> > > Just some general information here. Maybe it is of some help to you.
> > >
> > > In SQL-Server, there are basically two situations that may lead to
> > > deadlocks:
> > > 1. Locks are acquired in different order in different transactions.
This
> > > is the 'classic' deadlock. The chance is increased when the
transactions
> > > can not use an index to lock at row level. The chance is also
increased
> > > when there are many lock requests, or when memory is low.
> > >
> > > 2. Lock escalation (from row/page locks to table locks) can lead to
> > > deadlocks. If lock escalation is caused by low memory, then lowering
> > > your locking granularity from row locks to page locks may help.
> > >
> > > Gert-Jan
> > >
> > >
> > > Mike Malter wrote:
> > > >
> > > > I am getting a deadlock error message that is perplexing me.
> > > >
> > > > Our scenario is like this; separate applications using ADO and
ADO.NET with EnterpriseServices are coexisting in this project.
> > The
> > > > database and stored procedures are what we have in common. We are
doing some testing now to see how well these different
> > > > applications can get along with each other, and in one of our tests,
ADO edits random records and ADO.NET selects all records,
> > we
> > > > have received a deadlock error in the ADO.NET application.
> > > >
> > > > We are both in a transaction when this occurs. The transaction on
the ADO side is per record, and the transaction on the
> > ADO.NET
> > > > side is for the entire select.
> > > >
> > > > I can understand that the select will want to read a record that is
locked by the edit, however the edit is so quick that I
> > would
> > > > think that the select would not be blocked very long. If that were
the case, where the select was blocked too long, wouldn't
> > there
> > > > be a timeout instead of a deadlock?
> > > >
> > > > The other factor here is that if we only edit 5 records we never get
a deadlock. If we edit more than that, we always get a
> > > > deadlock.
> > > >
> > > > Any insight into this perplexing problem would be very welcomed.
> > > >
> > > > Mikesql