Is it possible to create a deadlock with read-only access?

Question

I have a VB6 application accessing a single table on a MSSQL2000 server via ADO. I'm using read-only access (adOpenStatic, adLockReadOnly) There are other applications in the network which do make changes to the table.

For some reason I'm getting errors about my application being chosen as a deadlock victim.

I'm really confused: Why can there be a deadlock when I'm just reading from a single table? I'd expect timeouts, because of the writing of the other applications, but not a deadlock...

Can someone shed some light on this?

UPDATE: 2009-06-15 I'm still interested in a solution to this problem. So I'm providing some more information:

• It makes no difference if I choose adOpenForwardOnly or adOpenStatic
• It makes no difference if the cursor position is client or server.

Answer 1

Wouldn't it be something like this?

Other Application: Write to table (acquire write lock on table)

Your Application: Read from table (acquire read lock on table, can't due to write lock).

Answer 2

It depends on both application's behavior. your app can surely wait on the other to release resources.

Answer 3

Reads can still incur locks, in order for the DB to ensure that a write isnt done in the middle of a non-atmic read. In other words the read lock ensures that you get an accurate consistent snapshot of whatever data you are slecting.

Answer 4

A deadlock refers to a condition when two or more processes are waiting for each other to release a resource, or more than two processes are waiting for resources in a circular chain. Sure you can create a deadlock with read-only access because the read will NOT wait.

There is a nice explanation about the deadlock conditions at the wikipedia

Answer 5

Do you get the same behaviour with adOpenForwardOnly ?

You might want to check that your SQL Server statistics are up to date. Or you could get your DBA to rebuild all indexes. Many locking problems are due to out of date statistics/indexes.

Answer 6

It is possible for a single SELECT statement to deadlock against a single UPDATE or DELETE statement due to the presence of a non-clustered index, consider the follwing scenario:

The reader (your app) first obtains a shared lock on the non-clustered index in order to perform a lookup, and then attempts to obtain a shared lock on the page contianing the data in order to return the data itself.

The writer (other app) first obtains an exlusive lock on the database page containing the data, and then attempts to obtain an exclusive lock on the index in order to update the index.

You can find more information on this (and other) type of deadlock in the Microsoft KB article Q169960 (http://support.microsoft.com/kb/q169960/)

Also you might want to take a look on Google on how to obtain deadlock trace information (trace flag 1222) - this will report on exactly what SQL statements are conflicting over what objects whenever a deadlock occurrs. This is a fairly decent looking article - http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

Answer 7

several cases described here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/03/when-index-covering-prevents-deadlocks.aspx

Answer 8

I think there are a number of possibilities in the answers already provided here. Since you only take shared locks, the deadlock can't be due to lock escalation, and must simply be acquiring locks that are incompatible with those acquired in another process, and acquiring those locks in a different order...

Your shared locks are incompatible with another process taking exclusive locks. The scenario might run something like this...

1. You take shared lock on resource A
2. Other process takes exclusive lock on resource B
3. Other process tries to take exclusive lock on resource A, and blocks waiting for you to release your shared lock on A.
4. You try to take shared lock on resource B, and would block waiting for the other process to release its exclusive lock on B, except that you're now in a deadlock situation, which is identified by the server and it chooses a process to kill.

N.B. deadlocks can have more players than just 2. Sometimes there's a whole chain of interwoven activity that results in a deadlock, but the principle is the same.

Often, if multiple applications access the same database, there is a DBA that manages all access via stored procedures, so he can ensure resources are always locked in the same order. If you're not in that situation, and the other applications use ad-hoc SQL statements you'd have to inspect their code to find out if they might conflict with your app in the way I've described. That doesn't sound like fun.

A pragmatic solution might be to catch the error when your transaction is killed as a deadlock victim, and simply re-try the transaction several times. Depending on how much activity the other apps are generating, you might achieve acceptable results this way.