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

Accepted answer
Score: 16

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

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

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

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

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

Score: 3

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

Your 22 shared locks are incompatible with another 21 process taking exclusive locks. The scenario 20 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 19 can have more players than just 2. Sometimes 18 there's a whole chain of interwoven activity 17 that results in a deadlock, but the principle 16 is the same.

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

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

Score: 0

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

Score: 0

Do you get the same behaviour with adOpenForwardOnly ?

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

More Related questions