诊断SQL Server 2005中的死锁

根据msdn:

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

无论何时 读取提交的快照或 允许快照隔离数据库 选项已打开,逻辑副本 为所有数据维护(版本) 在中执行的修改 数据库。每次修改行时 通过特定的交易, 数据库引擎存储的实例 以前提交的版本 tempdb中的行的图像。各 版本用事务标记 事务的序列号 这就改变了。版本 修改后的行使用链接链接链接 名单。最新的行值总是 存储在当前数据库中,并且 链接到存储的版本化行 在TEMPDB中。

对于短时间运行的事务,a 修改行的版本可能 缓存在缓冲池中 写入的磁盘文件 tempdb数据库。如果需要 版本化的行是短期的,它 只会从 缓冲池,可能不一定 产生I/O开销。

对于额外的开销似乎有轻微的性能损失,但可以忽略不计。我们应该测试以确保。

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

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

诺洛克 和 读取未提交 是一个很滑的斜坡。除非你理解为什么死锁首先发生,否则永远不要使用它们。如果您说“我们已经向所有SQL查询添加了(nolock)”,我会很担心。需要添加 用诺洛克 任何地方都是数据层出现问题的可靠标志。

update语句本身看起来有点问题。您是在事务处理的早期确定计数,还是只从对象中提取它? AnswerCount = AnswerCount+1 添加问题可能是更好的处理方法。然后,您不需要事务来获得正确的计数,也不必担心可能暴露在您面前的并发性问题。

解决这种死锁问题的一个简单方法是使用 "Snapshot Isolation Mode" (在SQL 2005中是新的)它将始终为您提供最后未修改数据的清晰读取。如果您想优雅地处理死锁语句,也可以很容易地捕获并重试它们。

操作问题是问为什么会发生这个问题。这篇文章希望能回答这个问题,同时让其他人去解决可能的问题。

这可能是一个与索引相关的问题。例如,假设表posts有一个非聚集索引x,其中包含parentID和一个(或多个)要更新的字段(answercount、lastactivitydate、lastactivityuserid)。

如果select命令对索引x执行共享读取锁定以按parentid搜索,然后需要对聚集索引执行共享读取锁定以获取其余列,而update命令对聚集索引执行写排它锁定,并且需要对索引x执行写排它锁定以更新索引,则会发生死锁。

你现在有一种情况,锁定的x试图得到y,而锁定的b试图得到x。

当然,我们需要OP更新他的文章,以了解更多关于什么索引起作用的信息,以确认这是否是真正的原因。

这个问题和服务员的回答让我很不舒服。有很多“尝试这个魔法尘埃!不,那神奇的灰尘!

我看不到您对所取锁进行了Anyled化,并确定死锁的确切类型。

您所指出的只是发生了一些锁——而不是死锁。

在SQL 2005中,您可以使用以下命令获取有关正在取出哪些锁的详细信息:

DBCC TRACEON (1222, -1)

这样,当死锁发生时,您将得到更好的诊断。

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

在烧掉房子去抓一只到处都是诺洛克的苍蝇之前,您可能想看看应该用分析器捕获的死锁图。

记住,死锁需要(至少)2个锁。连接1有锁A,需要锁B,反之亦然。这是无法解决的情况,必须有人给予。

到目前为止,您所展示的是通过简单的锁定来解决的,SQL Server很乐意整天这样做。

我怀疑您(或Linq)正在使用其中的更新语句启动一个事务,并在处理之前选择其他信息。但是,您真的需要回溯死锁图来找到锁 举行 通过每个线程,然后通过探查器回溯,找到导致授予这些锁的语句。

我期望至少有4个语句来完成这个难题(或者一个需要多个锁的语句——也许在posts表上有一个触发器?).

您是否关心您的用户配置文件是否过期几秒钟?

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

典型的读/写死锁来自索引顺序访问。read(T1)查找索引A上的行,然后查找索引B上的投影列(通常是聚集的)。WRITE(t2)更改索引B(集群),然后必须更新索引A。T1在A上有S-LCK,需要B上的S-LCK,T2在B上有X-LCK,需要A上的U-LCK。死锁,Puff。T1被杀死。 这在OLTP流量大且索引太多的环境中很普遍:)。解决方案是使读不必从A跳到B(即A中包含列,或从投影列表中删除列),或者使t2不必从B跳到A(不更新索引列)。 不幸的是,Linq不是你的朋友…

@杰夫-我绝对不是这方面的专家,但我在几乎每一个电话上都实例化了一个新的上下文,取得了很好的效果。我认为这类似于在每次调用ADO时创建一个新的连接对象。开销并没有你想象的那么严重,因为连接池仍然会被使用。

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

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;

我也会为更新做同样的事情。不管怎么说,我没有你那么多流量,但是我在早期使用共享数据上下文时确实得到了一些锁定,只有少数用户。没有保证,但可能值得一试。

更新 :然后,再看看您的代码,您只是在特定控制器实例的生命周期内共享数据上下文,这基本上看起来很好,除非它以某种方式被控制器内的多个调用同时使用。关于这个话题,斯科特古说:

控制器仅为单个请求活动-因此在处理请求结束时,它们被垃圾收集(这意味着数据上下文被收集)。

所以,不管怎样,这可能不是它,但同样值得一试,也许结合一些负载测试。

问:为什么要存储 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>

但这通常会导致 INDEX SCAN 在使用资源方面可能比使用 READ ISOLATION .

给猫剥皮的方法不止一种。数据库模式过早地去规范化可能会带来可伸缩性问题。

您肯定希望将read_committed_snapshot设置为on,这不是默认设置。这就给了您MVCC语义。这与Oracle默认使用的相同。拥有一个MVCC数据库是非常有用的,不使用它是疯狂的。这允许您在事务内部运行以下内容:

update users set firstname='foobar'; //决定睡一年。

同时,如果不提交上述内容,每个人都可以继续从该表中进行选择。如果你不熟悉MVCC,你会惊讶于没有它你也能活下去。说真的。

将默认值设置为“未提交”不是一个好主意。毫无疑问,您将引入不一致性,并最终导致比现在更糟的问题。快照隔离可能会很好地工作,但这是对SQL Server工作方式的一个重大更改,并将 巨大的 在TEMPDB上加载。

下面是您应该做的:使用Try-Catch(在T-SQL中)检测死锁条件。当它发生时,只需重新运行查询。这是标准的数据库编程实践。

在保罗·尼尔森的书中有很多这种技巧的例子。 Sql Server 2005 Bible .

以下是我使用的快速模板:

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

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

也就是说,如果一个查询按表1、表2的顺序更新,而另一个查询按表2、表1的顺序更新,那么您可能会看到死锁。

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

您是否关心您的用户配置文件是否过期几秒钟?

几秒钟绝对可以接受。不管怎么说,似乎不会有那么长的时间,除非有大量的人同时提交答案。

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

我目前正在构建一个解决方案,它像您一样实现静态上下文,当我在压力测试期间向Beast of a Server(百万以上)抛出大量请求时,我还随机获得了读/写锁。

当我改变策略,在每个查询的LINQ级别使用不同的数据上下文,并且相信SQL Server可以发挥其连接池魔力时,锁似乎消失了。

当然,我有一段时间的压力,所以尝试了很多东西,同时,所以我不能100%确定这是什么解决它,但我有很高的信心-让我们这么说吧。

您应该实现脏读。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

如果您的查询不完全需要完美的事务完整性,那么在访问具有高并发性的表时应该使用脏读。我想你的职位表就是其中之一。

这可能会给您提供所谓的“幻影读取”,即当您的查询对尚未提交的事务中的数据进行操作时。

我们不是在这里经营银行网站,我们不需要每次都有完美的准确性。

使用脏读。你是对的,他们不会给你完美的准确性,但他们应该清除你死锁的问题。

如果没有这一点,我们就必须将我们所做的每一个LINQ调用(好吧,简单的读取调用,这是其中绝大多数)包装在一个3-4行事务代码块中,这很难看。

如果在“基础数据库上下文”上实现脏读,那么如果需要事务完整性,则可以始终使用更高的隔离级别来包装单个调用。

那么,实现重试机制有什么问题呢?总是有可能出现死锁,所以为什么不具有一些逻辑来识别它,然后再试一次呢?

当一个重试系统很少启动时,至少其他一些选项会不会引入性能惩罚呢?

另外,当一次重试发生时,不要忘记某种类型的日志记录,这样你就不会陷入这种罕见的情况。

现在我看到了Jeremy的答案,我想我记得我听到的最佳实践是为每个数据操作使用一个新的数据上下文。RobConery写了几篇关于DataContext的文章,他总是把它们更新,而不是使用一个单独的。

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

这是我们用于视频的模式。显示( link to source view in 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);
}

我必须同意Greg的观点,只要将隔离级别设置为“未提交读取”不会对其他查询产生任何不良影响。

Jeff,我想知道在数据库级别设置它将如何影响查询,如:

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

如果我的个人资料甚至过期几分钟,我也可以。

你是在读失败后尝试它吗?当然,当触发大量的随机读取时,有一些会在无法读取时命中。与读取次数相比,我使用的大多数应用程序的写入次数都非常少,而且我确信读取次数与您获得的次数不接近。

如果实现“未提交读取”并不能解决您的问题,那么在不了解更多有关处理的信息的情况下很难提供帮助。可能还有一些其他的调优选项可以帮助这种行为。除非有MSSQL专家前来救援,否则我建议将问题提交给供应商。

我将继续优化所有内容;磁盘子系统的性能如何?平均磁盘队列长度是多少?如果I/O正在备份,真正的问题可能不是这两个查询死锁,而是另一个导致系统瓶颈的查询;您提到了一个经过20秒优化的查询,还有其他查询吗?

专注于缩短长时间运行的查询,我敢打赌死锁问题将消失。

有相同的问题,不能在TransactionScope上使用“isolationLevel=isolationLevel.readUncommitted”,因为服务器没有启用DTS!!).

这就是我用扩展方法所做的:

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

欢迎提出建议!