主页 / dba / 问题 / 132410
Accepted
saroff
Asked: 2016-03-17 07:46:59 +0800 CST

长时间运行的 SELECT 查询作为 SQL SERVER 上的 DELETE 的死锁牺牲品失败 - 为什么以及如何避免这种情况？

  • 772

首先 1 个线程从表中选择大量数据。

然后，在 SELECT 查询尚未完成之前，另一个线程从该表中删除数据。

它会导致 SELECT 查询失败，错误为 errorCode=1205 sqlState=40001 并显示发生死锁的消息。

我认为第二个事务必须等到第一个事务完成，或者第一个事务必须返回数据而没有第二个事务所做的更改。我怎样才能避免这种情况？

我无法更改交易的顺序或以某种方式控制它们（应用程序的用户很多，他们可以随时以任何顺序做任何他们想做的事情）。为什么会这样？

查询看起来是这样的（我稍微混淆了它们）：

SELECT distinct P.pID, (LastNAME+' '+FirstName+' '+SurName) as NAME, 'person' as Type, CA.OrgName, PC.personemail as Email
FROM Persons P
INNER JOIN WORK PW ON (P.mainwork = PW.pwId)
INNER JOIN CONTACT PC ON (PC.pID = P.pID)
LEFT OUTER JOIN ORG CA ON (CA.orgId = PW.orgId)
WHERE (P.Status = ?)

和

DELETE FROM persons WHERE id = 1234
sql-server transaction

1 个回答

  1. Best Answer

    我认为第二笔交易必须等到第一笔交易完成

    基本上，这是不正确的。如果一次只有一个人可以对它做某事，那么您（或任何人）的数据库将不会非常有效。

    为什么会这样？

    简单地：

    一个 SELECT 查询在它正在查看的任何行上都使用共享锁，所以虽然它不会与另一个 SELECT 查询冲突，但它肯定会与来自 DELETE 的排他锁冲突。由于 SELECT 查询没有更新任何内容，它通常会成为死锁的受害者，因为它可能是回滚成本最低的。

    如果没有进一步的信息，无法说明为什么会在您所谈论的情况下特别发生这种情况。我将在最后添加一些死锁检测的链接，但我怀疑这不是问题的主要目的，所以......

    该怎么办？

    很难知道最适合您的方法是什么（没有更多信息）。您可能应该查看一下您的隔离级别。

    您可以使用 READ_COMMITTED_SNAPSHOT，但这可能会导致更多开销，或者使用 READ_UNCOMMITED/使用 NOLOCK 提示 - 我通常不喜欢这些选项，因为返回的数据可能实际上不是您想要的，但它们可能适合你的情况。很难确切地说什么是最好的，因为它取决于您的系统及其使用的许多不同变量。

    在这方面，您可以做的最好的事情是阅读有关隔离级别的信息，这些是一些类似的帖子/资源，可以让您开始并更详细地了解事情。

    MSDN - select 语句如何导致被选为死锁受害者？, SQL Server 中央, MSDN - 了解隔离级别

    但...

    不要急于进行更改，以便完成查询。更合适的探索途径可能是您自己的查询。

    为什么 SELECT 语句需要这么长时间才能运行？为什么经常从表中删除东西？也许一些简单的查询调优会让它们更快地完成并减少死锁的机会。或许您需要对您的数据库服务器进行一些一般性的性能调整（我不会在这里详细介绍，因为它已经离题很远了）。

    您必须确保您的查询适合业务目的，而不仅仅是“不惜一切代价完成”的查询，数据库首先是一种业务工具，需要解决这个问题（无论我们多么不喜欢这样）！

    只是继续更改隔离级别，甚至只是使用 NOLOCK，都会对您的数据库以及查询返回的确切结果产生重大影响——这可能会对业务产生影响。

    还...

    为了帮助检测死锁，请查看StackOverflow上的这篇文章并查看MSDN 站点上的死锁信息工具，该工具将解释如何使用跟踪标志来提供有关死锁的更多信息。

    最后...

    如果您将 SQL Server 版本放在您的帖子中，可以提供更具体的建议，以及有关 SELECT 查询到底是什么的更多详细信息（查看是否有任何可能导致它花费这么长时间并打开以被锁定） .

    • 6

相关问题