SQL 选择查询的事务死锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5826600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transaction deadlock for select query
提问by peanut
Occasionally, I have the following error for a stored procedure which is only a Select query: Transaction (Process ID 91) was deadlocked on lock
有时，对于仅是 Select 查询的存储过程，我会遇到以下错误： Transaction (Process ID 91) was deadlocked on lock
My initial understanding was that a select query won't lock a table, or won't cause a deadlock even if the table it tries to query is being updated/locked by another process, but it seems that a select query can cause deadlocks as well.
我最初的理解是，选择查询不会锁定表，或者即使它尝试查询的表正在被另一个进程更新/锁定也不会导致死锁，但似乎选择查询可能导致死锁好。
If I set the isolation level to read uncommitted for the query, will that solve the problem?
如果我将隔离级别设置为查询未提交的读取，是否可以解决问题？
回答by Remus Rusanu
My init understanding is that a Select query won't lock a table, or won't cause a deadlock
我的初始理解是 Select 查询不会锁定表，也不会导致死锁
This understanding is wrong. SELECT queries take shared locks on the rows they analyze. Shared locks may conflict exclusive locks from update/delete/insert statements. Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an UPDATE. When such deadlock occurs, the SELECT is usually the victim as it did not perform any update so is always going to loose the draw.
这种理解是错误的。SELECT 查询在它们分析的行上使用共享锁。共享锁可能会与更新/删除/插入语句中的排他锁冲突。两个 SELECT 语句不会死锁，但一个 SELECT 可以与 UPDATE 死锁。当发生这种死锁时，SELECT 通常是受害者，因为它没有执行任何更新，因此总是会失去平局。
As with any deadlock, you need to post the exact schema of the tables involved, the exact T-SQL statements and the deadlock graph. See How to: Save Deadlock Graphs (SQL Server Profiler). With this information you can receive guidance how to fix the deadlock.
与任何死锁一样，您需要发布所涉及表的确切模式、确切的 T-SQL 语句和死锁图。请参见如何：保存死锁图 (SQL Server Profiler)。有了这些信息，您可以获得如何解决死锁的指导。
回答by Ghlouw
Like Remus says, you are getting the deadlocks because SELECT and UPDATE (or other) operations deadlocking each other, not SELECT vs SELECT. You will have to look at all your queries touching that table and create proper covering indexes for those queries and that will solve your problems. Good covering indexes is the preferred solution rather than using WITH (NOLOCK) table hints.
就像 Remus 说的那样，您之所以会陷入死锁，是因为 SELECT 和 UPDATE（或其他）操作彼此死锁，而不是 SELECT 与 SELECT。您将不得不查看涉及该表的所有查询，并为这些查询创建适当的覆盖索引，这将解决您的问题。良好的覆盖索引是首选解决方案，而不是使用 WITH (NOLOCK) 表提示。
See the following linkfor a good tutorial on how to create covering indexes and how it affects deadlocks.
有关如何创建覆盖索引及其如何影响死锁的优秀教程，请参阅以下链接。
回答by RC_Cleland
If you are using SQL Server 2008 you can set the isolation level to read uncommitted to prevent the deadlock. See this link. When reading uncommitted or WITH (NOLOCK) one must be aware the data retruned by the query may not be REAL!
如果您使用的是 SQL Server 2008，您可以将隔离级别设置为未提交读取以防止死锁。请参阅此链接。当读取未提交或 WITH (NOLOCK) 时，必须意识到查询返回的数据可能不是真实的！
