是否可以创建具有只读访问权限的死锁？ [英] Is it possible to create a deadlock with read-only access?
问题描述
我有一个VB6应用程序，它通过ADO访问MSSQL2000服务器上的单个表。我正在使用只读访问权限（adOpenStatic，adLockReadOnly）网络中还有其他应用程序确实对表进行了更改。
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?
更新时间：2009-06-15 我仍然对解决此问题感兴趣。因此，我提供了更多信息：
UPDATE: 2009-06-15 I'm still interested in a solution to this problem. So I'm providing some more information:
- 如果我选择adOpenForwardOnly或adOpenStatic
- 光标位置是客户端还是服务器都没有区别。
推荐答案
由于存在非聚集索引，单个SELECT语句可能会针对单个UPDATE或DELETE语句造成死锁，请考虑以下情况：
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:
读者（您的app）首先获取非聚集索引的共享锁以执行查找，然后尝试在包含数据的页面上获取共享锁以返回数据本身。
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.
writer（其他应用程序）首先在包含数据的数据库页面上获得排他锁，然后尝试在索引上获得排他锁以更新索引。
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.
您可以在Microsoft KB插图中找到有关此（和其他）类型死锁的更多信息。条款Q169960（ http://support.microsoft.com/kb/q169960/ ）
You can find more information on this (and other) type of deadlock in the Microsoft KB article Q169960 (http://support.microsoft.com/kb/q169960/)
此外，您可能还想看看Google如何获取死锁跟踪信息（跟踪标志1222）-这将确切报告何时有哪些SQL语句与哪些对象发生冲突僵局发生者。这是一篇相当不错的文章- http：//blogs.msdn .com / bartd / archive / 2006/09/09 / 747119.aspx
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
这篇关于是否可以创建具有只读访问权限的死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
