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

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 (AT) nospam (DOT) com> wrote

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 (AT) nospam (DOT) com> wrote

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:

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 (AT) REMOVETHIS (DOT) solidqualitylearning.com> 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 (AT) toomuchspamalready (DOT) nl> wrote

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 (AT) acn (DOT) gr> wrote

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: