在SQL Server 2005中诊断死锁 [英] Diagnosing Deadlocks in SQL Server 2005

问题描述

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

We're seeing some pernicious, but rare, deadlock conditions in the Stack Overflow SQL Server 2005 database.

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

I attached the profiler, set up a trace profile using this excellent article on troubleshooting deadlocks, and captured a bunch of examples. The weird thing is that the deadlocking write is always the same:

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

其他死锁语句有所不同，但这通常是一些琐碎，简单的 read 帖子表。这个总是在僵局中被杀死。这是一个示例

The other deadlocking statement varies, but it's usually some kind of trivial, simple read of the posts table. This one always gets killed in the deadlock. Here's an example

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

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

To be perfectly clear, we are not seeing write / write deadlocks, but read / write.

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

We have a mixture of LINQ and parameterized SQL queries at the moment. We have added with (nolock) to all the SQL queries. This may have helped some. We also had a single (very) poorly-written badge query that I fixed yesterday, which was taking upwards of 20 seconds to run every time, and was running every minute on top of that. I was hoping this was the source of some of the locking problems!

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

Unfortunately, I got another deadlock error about 2 hours ago. Same exact symptoms, same exact culprit write.

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

The truly strange thing is that the locking write SQL statement you see above is part of a very specific code path. It's only executed when a new answer is added to a question -- it updates the parent question with the new answer count and last date/user. This is, obviously, not that common relative to the massive number of reads we are doing! As far as I can tell, we're not doing huge numbers of writes anywhere in the app.

我意识到NOLOCK有点像一把大锤子，但是大多数我们在这里运行的查询不需要那么精确。

I realize that NOLOCK is sort of a giant hammer, but most of the queries we run here don't need to be that accurate. Will you care if your user profile is a few seconds out of date?

与Linq一起使用NOLOCK会更困难，因为斯科特·汉塞尔曼（Scott Hanselman）在这里讨论。

Using NOLOCK with Linq is a bit more difficult as Scott Hanselman discusses here.

我们对使用

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

on the base database context so that all our LINQ queries have this set. 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.

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

I guess I'm a little frustrated that trivial reads in SQL 2005 can deadlock on writes. I could see write/write deadlocks being a huge issue, but reads? We're not running a banking site here, we don't need perfect accuracy every time.

想法？有想法吗？

您是否为每个操作实例化一个新的LINQ to SQL DataContext对象？您也许为所有调用共享相同的静态上下文？

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，我们在基本Controller中共享一个静态datacontext最多部分：

Jeremy, we are sharing one static datacontext in the base Controller for the most part:

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创建一个新的上下文，或每页，或..更频繁？

Do you recommend we create a new context for every Controller, or per Page, or .. more often?

推荐答案

根据MSDN：

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

当
读取已提交快照或
允许快照隔离数据库时
选项为ON，为
数据库中执行的所有数据
修改保留逻辑副本
（版本）。每次通过特定事务修改
时，数据库引擎的
实例都会将
版本的该行先前提交的
映像存储在tempdb中。每个
版本均标有进行更改的交易
的交易
序列号。
修改行的版本使用链接
列表链接。最新的行值始终是
存储在当前数据库中，而
链接到在tempdb中存储的
的版本化行中。

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.

运行事务时，修改后的行的
版本可能会在缓冲池中缓存
，而不会将
写入tempdb数据库的
磁盘文件中。如果对
的版本化行的需求是短暂的，则
只会从
缓冲池中删除，而不一定
会产生I / O开销。

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.

对于额外的开销，似乎会有一些性能损失，但它可以忽略不计。

There appears to be a slight performance penalty for the extra overhead, but it may be negligible. We should test to make sure.

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

Try setting this option and REMOVE all NOLOCKs from code queries unless it’s really necessary. NOLOCKs or using global methods in the database context handler to combat database transaction isolation levels are Band-Aids to the problem. NOLOCKS will mask fundamental issues with our data layer and possibly lead to selecting unreliable data, where automatic select / update row versioning appears to be the solution.

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

这篇关于在SQL Server 2005中诊断死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！