Is it possible to create a deadlock with read-only access?
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
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