首页 / 知识

在SQL Server 2005中诊断死锁

2023-04-12 18:07:00

在SQL Server 2005中诊断死锁

Diagnosing Deadlocks in SQL Server 2005

我们在Stack Overflow SQL Server 2005数据库中看到了一些有害但罕见的死锁情况。

我附加了事件探查器，使用这篇有关解决死锁的出色文章来建立跟踪探查，并捕获了许多示例。奇怪的是，死锁写总是相同的：

1
2
3
UPDATE [dbo].[Posts]
SET [AnswerCount] = @p1, [LastActivityDate] = @p2, [LastActivityUserId] = @p3
WHERE [Id] = @p0

其他死锁语句有所不同，但通常是对posts表的一些琐碎，简单的读取。这个总是死于僵局。这是一个例子

1
2
3
4
5
6
7
SELECT
[t0].[Id], [t0].[PostTypeId], [t0].[Score], [t0].[Views], [t0].[AnswerCount],
[t0].[AcceptedAnswerId], [t0].[IsLocked], [t0].[IsLockedEdit], [t0].[ParentId],
[t0].[CurrentRevisionId], [t0].[FirstRevisionId], [t0].[LockedReason],
[t0].[LastActivityDate], [t0].[LastActivityUserId]
FROM [dbo].[Posts] AS [t0]
WHERE [t0].[ParentId] = @p0

完全清楚地说，我们没有看到写/写死锁，而是看到了读/写。

目前，我们混合使用LINQ和参数化SQL查询。我们已将with (nolock)添加到所有SQL查询中。这可能有所帮助。我昨天还修复了一个(写得很差)的徽章查询，每次都要花费20秒以上的时间，而且每分钟都在运行。我希望这是一些锁定问题的根源！

不幸的是，大约2小时前，我又遇到了另一个死锁错误。同样的症状，同样的罪魁祸首。

真正奇怪的是，您在上面看到的锁定write SQL语句是非常特定的代码路径的一部分。仅在将新答案添加到问题时才执行该操作-它将使用新答案计数和最后日期/用户更新父问题。显然，相对于我们正在执行的大量读取，这不是那么普遍！据我所知，我们没有在应用程序中的任何地方进行大量写入操作。

我意识到NOLOCK有点像大锤子，但是我们在这里运行的大多数查询并不需要那么精确。您是否会担心用户配置文件过时几秒钟？

正如Scott Hanselman在这里讨论的那样，将Linlock与NOLOCK一起使用会更加困难。

我们正在调情使用

1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

在基础数据库上下文上运行，以便我们所有的LINQ查询都具有此设置。否则，我们将必须将我们进行的每个LINQ调用(很简单的读取代码，占绝大多数)包装在3-4行的事务代码块中，这很丑陋。

我想我对SQL 2005中的琐碎读取会在写入时陷入僵局感到有些沮丧。我可以看到写/写死锁是一个很大的问题，但是读吗？我们不在这里运行银行网站，我们每次都不需要完美的准确性。

有想法吗？有什么想法吗？

Are you instantiating a new LINQ to SQL DataContext object for every operation or are you perhaps sharing the same static context for all your calls?

杰里米(Jeremy)，在大多数情况下，我们在基本控制器中共享一个静态数据上下文：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
private DBContext _db;
/// <summary>
/// Gets the DataContext to be used by a Request's controllers.
/// </summary>
public DBContext DB
{
    get
    {
        if (_db == null)
        {
            _db = new DBContext() { SessionName = GetType().Name };
            //_db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
        }
        return _db;
    }
}

您是否建议我们更频繁地为每个Controller，每个Page或..创建一个新的上下文？

根据MSDN：

http://msdn.microsoft.com/en-us/library/ms191242.aspx

When either the
READ COMMITTED SNAPSHOT or
ALLOW SNAPSHOT ISOLATION database
options are ON, logical copies
(versions) are maintained for all data
modifications performed in the
database. Every time a row is modified
by a specific transaction, the
instance of the Database Engine stores
a version of the previously committed
image of the row in tempdb. Each
version is marked with the transaction
sequence number of the transaction
that made the change. The versions of
modified rows are chained using a link
list. The newest row value is always
stored in the current database and
chained to the versioned rows stored
in tempdb.

For short-running transactions, a
version of a modified row may get
cached in the buffer pool without
getting written into the disk files of
the tempdb database. If the need for
the versioned row is short-lived, it
will simply get dropped from the
buffer pool and may not necessarily
incur I/O overhead.

额外的开销似乎对性能有轻微的影响，但它可以忽略不计。我们应该进行测试以确保。

除非确实需要，请尝试设置此选项并从代码查询中删除所有NOLOCK。 NOLOCK或在数据库上下文处理程序中使用全局方法来对抗数据库事务隔离级别是解决该问题的创可贴。 NOLOCKS将掩盖我们数据层的基本问题，并可能导致选择不可靠的数据，而自动选择/更新行版本控制似乎是解决方案。

1
ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

NOLOCK和READ UNCOMMITTED是一个滑坡。除非您了解死锁为什么首先发生，否则不要使用它们。您会说："我们对所有SQL查询添加了(nolock)，这会让我感到担心。"需要到处添加WITH NOLOCK的确定信号表明您的数据层存在问题。

更新语句本身看起来有点问题。您是在交易中更早确定计数，还是只是从对象中提取计数？添加问题时，AnswerCount = AnswerCount+1可能是处理此问题的更好方法。这样一来，您无需进行交易即可获得正确的计数，也不必担心潜在的并发问题。

解决这种类型的死锁问题而无需进行大量工作且不启用脏读的一种简单方法是使用"Snapshot Isolation Mode"(SQL 2005中的新增功能)，该方法将始终为您提供最新未修改数据的清晰读取。如果您想优雅地处理死锁的语句，还可以很容易地捕获并重试死锁的语句。

OP的问题是问为什么会出现此问题。这篇文章希望回答这个问题，同时让其他人可以解决可能的解决方案。

这可能是与索引相关的问题。例如，假设表Posts具有一个非聚集索引X，它包含ParentID和一个(或多个)要更新的字段(AnswerCount，LastActivityDate，LastActivityUserId)。

如果SELECT cmd对索引X进行共享读取锁定以通过ParentId搜索，然后需要对聚簇索引进行共享读取锁定以获取剩余的列，而UPDATE cmd进行不包含写操作，则将发生死锁。锁定聚集索引，并且需要获取对索引X的写独占锁定才能更新它。

您现在遇到的情况是，A锁定X并试图获取Y，而B锁定Y并试图获取X。

当然，我们将需要OP来更新他的帖子，以获取更多有关正在运行的索引的信息，以确认这是否是真正的原因。

我对这个问题和随之而来的答案感到非常不自在。有很多"尝试这种魔力！没有那个魔力！"

我看不到任何地方您已经分析了所使用的锁并确定了死锁的确切类型。

您所指示的只是发生了一些锁定，而不是死锁。

在SQL 2005中，您可以使用以下方法获取有关哪些锁被取出的更多信息：

1
DBCC TRACEON (1222, -1)

这样，当出现死锁时，您将可以进行更好的诊断。

您是为每个操作实例化一个新的LINQ to SQL DataContext对象，还是为所有调用共享相同的静态上下文？我最初尝试了后一种方法，据我所记得，它在数据库中造成了不必要的锁定。现在，我为每个原子操作创建一个新的上下文。

在烧毁房屋以让NOLOCK泛滥成灾之前，您可能需要查看一下应该使用Profiler捕获的死锁图。

请记住，死锁需要(至少)2个锁。连接1具有锁A，需要锁B-对于连接2反之亦然。这是无法解决的情况，必须有人付出。

到目前为止，您所显示的内容是通过简单的锁定解决的，而Sql Server却很乐意整日这样做。

我怀疑您(或LINQ)正在使用其中的UPDATE语句启动事务，并在事前选择其他一些信息。但是，您确实需要在死锁图中回溯以找到每个线程持有的锁，然后在Profiler中回溯以查找导致授予这些锁的语句。

我希望至少有4条语句来完成这个难题(或者一条语句需要多个锁-也许Posts表上有一个触发器？)。

Will you care if your user profile is a few seconds out of date?

不，这是完全可以接受的。设置基本事务隔离级别可能是最好/最干净的方法。

典型的读/写死锁来自索引顺序访问。读取(T1)在索引A上找到行，然后在索引B上查找投影列(通常是聚簇的)。写入(T2)更改索引B(群集)然后必须更新索引A。T1在A上具有S-Lck，在B上想要S-Lck，T2在B上具有X-Lck，想要在A上U-Lck。死锁，粉扑T1被杀死。
这在OLTP流量大且索引太多的环境中很普遍:)。解决方案是使读取不必从A跳到B(即，A中包含的列，或从投影列表中删除列)或T2不必从B跳到A(不必更新索引列)。
不幸的是，linq不是您的朋友在这里...

您绝对希望将READ_COMMITTED_SNAPSHOT设置为on，默认情况下不启用。这为您提供了MVCC语义。 Oracle默认情况下使用的是同一件事。拥有MVCC数据库是如此有用，而不是疯狂地使用数据库。这使您可以在事务中运行以下命令：

更新用户集FirstName ='foobar';
//决定睡一年。

同时，无需进行上述操作，每个人都可以继续从该表中进行选择。如果您不熟悉MVCC，您会感到震惊，因为您曾经没有过它。说真的

@Jeff-我绝对不是这方面的专家，但是我在几乎每个呼叫上实例化新上下文方面都取得了不错的效果。我认为这类似于在ADO的每次调用中创建一个新的Connection对象。开销并没有您想的那么糟，因为无论如何连接池仍然会被使用。

我只是使用像这样的全局静态助手：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public static class AppData
{
    /// <summary>
    /// Gets a new database context
    /// </summary>
    public static CoreDataContext DB
    {
        get
        {
            var dataContext = new CoreDataContext
            {
                DeferredLoadingEnabled = true
            };
            return dataContext;
        }
    }
}

然后我做这样的事情：

1
2
3
var db = AppData.DB;

var results = from p in db.Posts where p.ID = id select p;

我会为更新做同样的事情。无论如何，我所获得的流量几乎没有您那么多，但是当我初期只与少数用户使用共享DataContext时，我肯定会遇到一些锁定。没有任何保证，但是值得一试。

更新：然后，再次查看您的代码，您仅在该特定控制器实例的生命周期内共享数据上下文，这基本上看起来还不错，除非它在某种程度上被控制器内的多个调用并发使用。在一个有关该主题的话题中，ScottGu说：

Controllers only live for a single request - so at the end of processing a request they are garbage collected (which means the DataContext is collected)...

所以无论如何，也许不是，但是值得一试，也许与一些负载测试一起使用。

问：为什么首先将AnswerCount存储在Posts表中？

另一种方法是通过不在表中存储AnswerCount来消除对Posts表的"写回"，而是根据需要动态地计算对帖子的回答数。

是的，这意味着您正在运行其他查询：

1
SELECT COUNT(*) FROM Answers WHERE post_id = @id

或更常见的情况(如果要在主页上显示此内容)：

1
2
3
4
5
6
7
SELECT p.post_id,
     p.,
     a.AnswerCount
FROM Posts p
    INNER JOIN AnswersCount_view a
    ON <join criteria>
WHERE <home page criteria>

但这通常会导致INDEX SCAN，并且在资源使用方面可能比使用READ ISOLATION更有效。

剥猫的方法不止一种。过早地对数据库架构进行反规范化会引入可伸缩性问题。

将默认设置设置为未提交不是一个好主意。毫无疑问，您将引入不一致之处，并最终遇到比现在严重的问题。快照隔离可能会很好地起作用，但这是对Sql Server工作方式的巨大改变，并给tempdb带来了巨大的负担。

这是您应该执行的操作：使用try-catch(在T-SQL中)检测死锁条件。发生这种情况时，只需重新运行查询即可。这是标准的数据库编程实践。

Paul Nielson的Sql Server 2005圣经中有很好的例子。

这是我使用的快速模板：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Deadlock retry template

declare @lastError int;
declare @numErrors int;

set @numErrors = 0;

LockTimeoutRetry:

begin try;

-- The query goes here

return; -- this is the normal end of the procedure

end try begin catch
    set @lastError=@@error
    if @lastError = 1222 or @lastError = 1205 -- Lock timeout or deadlock
    begin;
        if @numErrors >= 3 -- We hit the retry limit
        begin;
            raiserror('Could not get a lock after 3 attempts', 16, 1);
            return -100;
        end;

        -- Wait and then try the transaction again
        waitfor delay '00:00:00.25';
        set @numErrors = @numErrors + 1;
        goto LockTimeoutRetry;

    end;

    -- Some other error occurred
    declare @errorMessage nvarchar(4000), @errorSeverity int
    select    @errorMessage = error_message(),
            @errorSeverity = error_severity()

    raiserror(@errorMessage, @errorSeverity, 1)

    return -100
end catch;

过去对我有用的一件事是确保所有查询和更新访问资源(表)的顺序相同。

也就是说，如果一个查询按Table1，Table2的顺序更新，而另一个查询按Table2，Table1的顺序更新，那么您可能会看到死锁。

由于您使用的是LINQ，因此不确定是否可以更改更新顺序。但这是值得一看的。

Will you care if your user profile is a few seconds out of date?

几秒钟绝对可以接受。无论如何，它看起来不会那么长，除非有大量的人同时提交答案。

我同意杰里米的观点。您询问是否应该为每个控制器或每个页面创建一个新的数据上下文-我倾向于为每个独立查询创建一个新的数据上下文。

我目前正在构建一个解决方案，该解决方案用于像您一样实现静态上下文，并且当我在压力测试期间向服务器的猛兽(数百万个)抛出大量请求时，我还随机获得了读/写锁。

一旦我改变了对每个查询在LINQ级别使用不同数据上下文的策略，并相信SQL Server可以发挥其连接池魔术的作用，这些锁似乎就消失了。

当然，我承受着一定的时间压力，因此要在同一时间尝试许多事情，所以我不能100％地确定这是解决问题的方法，但是我有很高的信心-这么说吧。

您应该实现脏读。

1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

如果您的查询绝对不要求完美的事务完整性，那么在访问具有高并发性的表时，应该使用脏读。我认为您的"帖子"表就是其中之一。

这可能会给您提供所谓的"幻像读取"，即您的查询对尚未提交的事务中的数据执行操作时。

We're not running a banking site here, we don't need perfect accuracy every time

使用脏读。您说对了，因为它们不会给您带来完美的准确性，但是它们应该可以解决死锁问题。

Without that, we'd have to wrap every LINQ call we make (well, the simple reading ones, which is the vast majority of them) in a 3-4 line transaction code block, which is ugly

如果在"基础数据库上下文"上实现脏读，则在需要事务完整性时，始终可以使用更高的隔离级别包装各个调用。

那么实施重试机制有什么问题？总是有发生死锁的可能性，那么为什么不具有一些逻辑来识别死锁并再次尝试呢？

至少其他一些选项是否会引入性能损失，而这种损失会在重试系统很少启动的情况下始终存在？

另外，重试发生时也不要忘记进行某种日志记录，这样您就不会陷入这种罕见的情况。

既然我看到了杰里米的答案，我想我记得曾经听说过，最佳实践是对每个数据操作使用新的DataContext。罗伯·科纳里(Rob Conery)写了几篇有关DataContext的文章，他总是将其新闻化，而不是使用单例。

  • http://blog.wekeroad.com/2007/08/17/linqtosql-ranch-dressing-for-your-database-pizza/
  • http://blog.wekeroad.com/mvc-storefront/mvcstore-part-9/(请参阅评论)

这是我们用于Video.Show的模式(链接到CodePlex中的源代码视图)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
using System.Configuration;
namespace VideoShow.Data
{
  public class DataContextFactory
  {
    public static VideoShowDataContext DataContext()
    {
        return new VideoShowDataContext(ConfigurationManager.ConnectionStrings["VideoShowConnectionString"].ConnectionString);
    }
    public static VideoShowDataContext DataContext(string connectionString)
    {
        return new VideoShowDataContext(connectionString);
    }
  }
}

然后在服务级别(或更精确地说，用于更新)：

1
2
3
4
5
6
7
8
9
10
11
private VideoShowDataContext dataContext = DataContextFactory.DataContext();

public VideoSearchResult GetVideos(int pageSize, int pageNumber, string sortType)
{
  var videos =
  from video in DataContext.Videos
  where video.StatusId == (int)VideoServices.VideoStatus.Complete
  orderby video.DatePublished descending
  select video;
  return GetSearchResult(videos, pageSize, pageNumber);
}

如果我的个人资料过时了几分钟，对我来说也很好。

读取失败后是否要重试？当触发大量随机读取时，有可能会击中一些无法读取的数据。与读取次数相比，我使用的大多数应用程序的写入次数很少，并且我确信读取次数与您获得的读取次数相差无几。

如果实现" READ UNCOMMITTED"无法解决您的问题，那么在不了解更多有关处理的情况下很难提供帮助。可能还有其他一些调整选项可以帮助解决此问题。除非有一些MSSQL专家来解救，否则我建议将问题提交给供应商。

我会继续调整一切；磁盘子系统的性能如何？平均磁盘队列长度是多少？如果I / O正在备份，则真正的问题可能不是这两个处于死锁状态的查询，可能是另一个使系统成为瓶颈的查询。您提到调整了20秒的查询，还有其他查询吗？

专注于缩短长期运行的查询，我敢打赌，僵局问题将消失。

遇到相同的问题，并且由于服务器未启用DTS(！)，因此不能在TransactionScope上使用" IsolationLevel = IsolationLevel.ReadUncommitted"。

那就是我用扩展方法所做的：

1
2
3
4
public static void SetNoLock(this MyDataContext myDS)
{
    myDS.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
}

因此，对于使用关键并发表的选择，我们启用" nolock"，如下所示：

1
2
3
4
5
6
using (MyDataContext myDS = new MyDataContext())
{
   myDS.SetNoLock();

   //  var query = from ...my dirty querys here...
}

欢迎进餐！

我必须同意Greg，只要将隔离级别设置为未提交就不会对其他查询产生不良影响。

杰夫，我很想知道，在数据库级别设置它如何会影响如下查询：

1
2
3
4
Begin Tran
Insert into Table (Columns) Values (Values)
Select Max(ID) From Table
Commit Tran

死锁诊断探查数据库

最新内容

相关内容

热门文章

推荐文章

标签云

猜你喜欢