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

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

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

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

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查询。我们在所有SQL查询中添加了with(nolock)。这可能有所帮助。我昨天还修复了一个(非常)写得不好的徽章查询，每次都要花费20秒以上的时间，而且每分钟都在运行。我希望这是一些锁定问题的根源！

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

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

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

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

我们正在调情使用

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)，在大多数情况下，我们在基本控制器中共享一个静态数据上下文：

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;
    }
}

我们是否建议我们为每个控制器，每个页面或者..更频繁地创建一个新的上下文？

解决方案

回答

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

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

回答

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

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

Begin Tran
Insert into Table (Columns) Values (Values)
Select Max(ID) From Table
Commit Tran

回答

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

回答

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

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

回答

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

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

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

回答

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

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

public static class AppData
{
    /// <summary>
    /// Gets a new database context
    /// </summary>
    public static CoreDataContext DB
    {
        get
        {
            var dataContext = new CoreDataContext
            {
                DeferredLoadingEnabled = true
            };
            return dataContext;
        }
    }
}

然后我做这样的事情：

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)...

因此，无论如何，可能并非如此，但还是值得一试，也许还要结合一些负载测试。

回答

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

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

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

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

回答

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

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

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

回答

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

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

解决这种类型的死锁问题而无需进行大量工作且不启用脏读的一种简单方法是使用"快照隔离模式"(SQL 2005中的新增功能)，它将始终为我们提供最新未修改数据的干净读取。 。如果我们想优雅地处理死锁的语句，也可以很容易地捕获并重试死锁的语句。

回答

既然我看到了杰里米的答案，我想我记得曾经听说过，最佳实践是对每个数据操作使用新的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中的源代码视图)：

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);
    }
  }
}

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

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);
}

回答

我们应该实现脏读。

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

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

回答

根据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将掩盖我们数据层的基本问题，并可能导致选择不可靠的数据，而自动选择/更新行版本控制似乎是解决方案。

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

回答

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

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

保罗·尼尔森(Paul Nielson)的Sql Server 2005圣经中有很好的例子。

这是我使用的快速模板：

-- 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;

回答

在烧毁房屋以让NOLOCK泛滥成灾之前，我们可能想看看应该使用Profiler捕获的死锁图。

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

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

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

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

回答

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

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

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

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

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

SELECT p.post_id, 
     p.<additional post fields>,
     a.AnswerCount
FROM Posts p
    INNER JOIN AnswersCount_view a
    ON <join criteria>
WHERE <home page criteria>

但这通常会导致"索引扫描"，并且在资源使用上可能比使用"读取隔离"更有效。

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

回答

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

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

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

回答

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

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

同时，无需进行上述操作，每个人都可以继续从该表中进行选择。如果我们不熟悉MVCC，我们会感到震惊，因为我们没有它就可以生活。严重地。

回答

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

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

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

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

DBCC TRACEON (1222, -1)

这样，当发生死锁时，我们将获得更好的诊断。

回答

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

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

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

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

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

回答

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

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

回答

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

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

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

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

using (MyDataContext myDS = new MyDataContext())
{
   myDS.SetNoLock();

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

欢迎进餐！

回答

典型的读/写死锁来自索引顺序访问。读取(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不是朋友在这里...