更新和插入查询导致死锁

Question

我的问题是关于两个查询（一个插入和一个更新）导致死锁。我有两个应用程序使用相同的数据库：Web应用程序和Windows服务。Web应用程序在每个请求中调用存储过程向Impressions表中插入多条记录。Windows服务则会计算前一分钟内每分钟完成的所有印象，并通过一个存储过程设置每个已计算完成的Impressions的标志。Web应用程序在不使用事务的情况下插入印象记录，而Windows服务在使用IsolationLevel.ReadUncommitted事务计算印象。因为该存储过程非常大，所以这里只展示了代码片段。

DECLARE @nowTime datetime = convert(datetime, @now, 21) 
DECLARE dailyCursor CURSOR FOR

SELECT  Daily.dailyId, 
        Daily.spentDaily, 
        Daily.impressionsCountCache ,
        SUM(Impressions.amountCharged) as sumCharged, 
        COUNT(Impressions.impressionId) as countImpressions
FROM    Daily INNER JOIN Impressions on Impressions.dailyId = Daily.dailyId
WHERE   Impressions.isCharged=0 AND Impressions.showTime < @nowTime AND Daily.isActive = 1
GROUP BY Daily.dailyId, Daily.spentDaily, Daily.impressionsCountCache

OPEN dailyCursor

DECLARE @dailyId int, 
        @spentDaily decimal(18,6), 
        @impressionsCountCache int, 
        @sumCharged decimal(18,6), 
        @countImpressions int

FETCH NEXT FROM dailyCursor INTO @dailyId,@spentDaily, @impressionsCountCache, @sumCharged, @countImpressions

WHILE @@FETCH_STATUS = 0
    BEGIN   

        UPDATE Daily 
        SET spentDaily= @spentDaily + @sumCharged, 
            impressionsCountCache = @impressionsCountCache + @countImpressions
        WHERE dailyId = @dailyId

        FETCH NEXT FROM dailyCursor INTO @dailyId,@spentDaily, @impressionsCountCache, @sumCharged, @countImpressions
    END
CLOSE dailyCursor
DEALLOCATE dailyCursor

UPDATE Impressions 
SET isCharged=1 
WHERE showTime < @nowTime AND isCharged=0

Web App存储过程：

这个存储过程非常简单，它只是将记录插入表中。以下是缩短的代码片段：

INSERT INTO Impressions 
(dailyId, date, pageUrl,isCalculated) VALUES 
(@dailyId, @date, @pageUrl, 0)

代码

调用这些存储过程的代码非常简单，它只是创建SQL命令并传递所需的参数，然后执行它们。

//i send the date like this
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", 
CultureInfo.InvariantCulture);

SqlCommand comm = sql.StoredProcedureCommand("storedProcName", 
parameters, values);

我经常遇到死锁问题（异常情况发生在Web应用程序而非Windows服务中），在使用SQL Profiler之后，我发现这两个查询可能会导致死锁（我的分析能力有限）。

可以在本问题底部找到从SQL服务器Profiler收集的最新跟踪数据。

理论上，这两个存储过程应该能够一起工作，因为第一个存储过程将记录逐个插入，日期为DateTime.Now，而第二个存储过程计算日期小于DateTime.Now的印象数。

编辑：

以下是在Windows服务应用程序中运行的代码：

SQL sql = new SQL();
DateTime endTime = DateTime.Now;
//our custom DAL class that opens a connection
sql.StartTransaction(IsolationLevel.ReadUncommitted);
try
{
    List<string> properties = new List<string>() { "now" };
    List<string> values = new List<string>() { endTime.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) };
    SqlCommand comm = sql.StoredProcedureCommannd("ChargeImpressions", properties, values);
    comm.Transaction = sql.Transaction;
    ok = sql.CheckExecute(comm);
}
catch (Exception up)
{
    ok = false;
    throw up;
}
finally
{
    if (ok)
      sql.CommitTransaction();
    else
      sql.RollbackTransactions();
    CloseConn();
}

编辑：

我按照马丁·史密斯的建议在两个表上都添加了索引，方法如下：

CREATE NONCLUSTERED INDEX [IDX_Daily_DailyId] ON [dbo].[Daily] 
(
    [daily] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

并且

CREATE NONCLUSTERED INDEX [IDX_Impressions_isCharged_showTime] ON [dbo].[Impressions] 
(
    [isCharged] ASC,
    [showTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

目前没有例外情况，稍后会有报告。

编辑：

不幸的是，这并没有解决死锁问题。我将在分析器中开始死锁跟踪，以查看死锁是否与之前相同。

编辑：

粘贴了新的跟踪信息（对我来说看起来和以前的一样），无法捕捉执行计划的屏幕截图（太大了），但这里是执行计划的xml，这是插入查询的执行计划截屏：

 <deadlock victim="process14e29e748">
  <process-list>
   <process id="process14e29e748" taskpriority="0" logused="952" waitresource="KEY: 6:72057594045071360 (f473d6a70892)" waittime="4549" ownerId="2507482845" transactionname="INSERT" lasttranstarted="2011-09-05T11:59:16.587" XDES="0x15bef83b0" lockMode="S" schedulerid="1" kpid="2116" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-05T11:59:16.587" lastbatchcompleted="2011-09-05T11:59:16.587" clientapp=".Net SqlClient Data Provider"  hostpid="2200"  isolationlevel="snapshot (5)" xactid="2507482845" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="dbo.InsertImpression" line="27" stmtstart="2002" stmtend="2560" sqlhandle="0x03000600550e30512609e200529f00000100000000000000">
INSERT INTO Impressions 
    (dailyId, languageId, showTime, pageUrl, amountCharged, age, ipAddress, userAgent, portalId, isCharged,isCalculated) VALUES 
    (@dailyId, @languageId, @showTime, @pageUrl, @amountCharged, @age, @ip, @userAgent, @portalId, 0, 0)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 1362103893]    </inputbuf>
   </process>
   <process id="process6c9dc8" taskpriority="0" logused="335684" waitresource="KEY: 6:72057594045464576 (5fcc21780b69)" waittime="4475" ownerId="2507482712" transactionname="transaction_name" lasttranstarted="2011-09-05T11:59:15.737" XDES="0x1772119b0" lockMode="U" schedulerid="2" kpid="3364" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-05T11:59:15.737" lastbatchcompleted="2011-09-05T11:59:15.737" clientapp=".Net SqlClient Data Provider"  hostpid="1436" isolationlevel="read uncommitted (1)" xactid="2507482712" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="dbo.ChargeImpressions" line="60" stmtstart="4906" stmtend="5178" sqlhandle="0x03000600e3c5474f0609e200529f00000100000000000000">
UPDATE Impressions 
    SET isCharged=1 
    WHERE showTime &amp;lt; @nowTime AND isCharged=0

    </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 1330103779]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594045071360" dbid="6" objectname="dbo.Daily" indexname="PK_Daily" id="lock14c6aab00" mode="X" associatedObjectId="72057594045071360">
    <owner-list>
     <owner id="process6c9dc8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process14e29e748" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594045464576" dbid="6" objectname="dbo.Impressions" indexname="IDX_Impressions_isCharged_showTime" id="lock14c901200" mode="X" associatedObjectId="72057594045464576">
    <owner-list>
     <owner id="process14e29e748" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6c9dc8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>

编辑：

根据Jonathan Dickinson的建议：

1. 我更改了存储过程(移除了游标)
2. 我更改了IDX_Impressions_isCharged_showTime以不允许PAGE_LOCKS
3. 我在Windows服务应用程序中将@now属性减少了1秒，以避免边缘死锁情况。

更新：

最近的更改降低了查询执行时间，但异常数量没有减少。

希望这是最后一次更新:

Martin Smith提出的更改现已上线，插入查询现在使用非聚集索引，理论上这应该解决问题。目前没有报告任何异常(手指交叉)

- Atzoya

---

本文介绍了Read Uncommitted和锁定行为：http://beyondrelational.com/blogs/jacob/archive/2008/08/28/sql-server-transaction-isolation-level-read-committed.aspx - Adam Houldsworth

问题似乎出现在与dbo.Daily的FK关系上，你在这两个表上都有哪些索引？ - Martin Smith

另外，为什么 Windows 服务在 dbo.Daily 上有一个 X 锁定呢？从您发布的代码中并不清楚。您能添加一下吗？ - Martin Smith

粘贴完整的存储过程。 - Atzoya

如果模式可以修改，您是否考虑使用索引视图来支持spentDaily和impressionsCountCache列。这样，SQL Server会自动执行维护，您就不需要编写任何代码，也不会出现死锁问题... - Damien_The_Unbeliever

Answer 1

避免使用游标，该查询不需要它们。SQL不是一种命令式语言（这也是为什么它会被诟病，因为每个人都将其用作命令式语言）- 它是一种集合语言。

首先，您可以加快SQL的基本执行，减少解析/执行查询的时间意味着死锁的可能性更小：

• 使用[dbo]前缀来命名所有表 - 这可削减30%的解析阶段。
• 给表取别名 - 可以在规划阶段削减少量。
• 引用标识符可能会加速操作。
• 这些都是来自前SQL-PM的技巧，如果有人想要质疑，请注意了。

您可以使用公共表表达式（CTE）获取要更新的数据，然后使用UPDATE ... FROM ... SELECT语句进行实际更新。这比使用游标更快，因为与干净的集合操作相比，游标的速度非常慢（即使是像您的“消防水龙带”游标最快的）。更新所需的时间越少，死锁的机会就越小。注意：我没有您的原始表，无法验证此方法-请在开发数据库中进行测试。

DECLARE @nowTime datetime = convert(datetime, @now, 21);

WITH [DailyAggregates] AS
(
    SELECT  
        [D].[dailyId] AS [dailyId],
        [D].[spentDaily] AS [spentDaily],
        [D].[impressionsCountCache] AS [impressionsCountCache],
        SUM([I].[amountCharged]) as [sumCharged],
        COUNT([I].[impressionId]) as [countImpressions]
        FROM [dbo].[Daily] AS [D]
            INNER JOIN [dbo].[Impressions] AS [I]
               ON [I].[dailyId] = [D].[dailyId]
        WHERE [I].[isCharged] = 0
          AND [I].[showTime] < @nowTime 
          AND [D].[isActive] = 1
    GROUP BY [D].[dailyId], [D].[spentDaily], [D].[impressionsCountCache]
)
UPDATE [dbo].[Daily]
    SET [spentDaily] = [A].[spentDaily] + [A].[sumCharged],
        [impressionsCountCache] = [A].[impressonsCountCache] + [A].[countImpressions]
    FROM [Daily] AS [D]
    INNER JOIN [DailyAggregates] AS [A]
       ON [D].[dailyId] = [A].[dailyId];

UPDATE [dbo].[Impressions]
SET [isCharged] = 1 
WHERE [showTime] < @nowTime 
  AND [isCharged] = 0;

此外，您可以禁用索引上的页面锁定，这将减少由于锁定升级而导致的少数行锁定整个页面的可能性（只需要锁定一定百分比的行，整个页面就会被锁定）。

CREATE NONCLUSTERED INDEX [IDX_Impressions_isCharged_showTime] ON [dbo].[Impressions]              
(
    [showTime] ASC, -- I have a hunch that switching these around might have an effect.
    [isCharged] ASC  
)
WITH (ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY] 
GO

这只是减少死锁的可能性。你可以尝试限制@now为过去的日期（例如今天 - 1天），以确保插入的行不会落入更新谓词中；很有可能完全避免死锁。

- Jonathan Dickinson

---

我现在会尝试这个，但我真的不知道这会有多大的改进。印象表通常有大约 400,000 条记录，并且每天会出现大约 20 个异常。我还会尝试将 DateTime.Now 往回移动 1 秒钟以防万一。 - Atzoya

我也曾这样想过，但经过查证发现并非如此。我在应用程序中声明DateTime是因为我需要它进行其他操作（在Web应用程序中，多个印象以相同的showDate逐个插入）。但是从DateTime.Now中减去一秒钟将解决任何同步问题。 - Atzoya

谢谢您提供的解决方案和加速查询执行的技巧（我之前不知道游标会拖慢执行速度）。我已经在我们的生产系统上实施了这些方法，现在正等待是否会出现异常（祈祷中）。 - Atzoya

@Martin，我对你的解决方案很感兴趣，因为如果这个系统的负载比现在更大，死锁的概率会增加。目前，在最新上传后，我已经遇到了2个异常，所以我不认为这是解决方案。它确实加快了执行速度，但并没有解决死锁问题。 - Atzoya

@Atzoya - 希望那是QAT！:) 正如我所说，我无法测试该查询的结果。 - Jonathan Dickinson

Answer 2

您的Windows服务光标会更新各个行，这些行在“日报表”中，需要获取“X”锁。这些锁定只有当事务结束时才会释放。

然后，您的Web应用程序会插入“展示次数”并在等待另一个进程锁定的“日报表”行上获取“S”锁时，保持对新插入行的“X”锁定。它需要读取该行以验证FK约束。

接下来，您的Windows服务将对“展示次数”进行更新，并且在扫描行的同时获取“U”锁定。没有索引可以让其查找到行，因此此扫描包括Web应用程序添加的行。

所以：

(1) 您可以在“展示次数，是否收费”或其反向组合上为“展示次数”添加复合索引，或相反（请检查执行计划），以便通过索引搜索而不是全表扫描找到Windows服务将要更新的行。

-或

(2) 您可以在“日报表（Daily）”上添加冗余非聚集索引（DailyId）。这将比聚集索引小得多，因此FK验证可能会优先使用它而不需要在聚集索引行上获取“S”锁定。

编辑

免责声明：以下基于假设和观察，而不是我发现的任何文件！

似乎像（2）的想法不起作用。执行计划显示FK验证仍然继续发生在聚集索引上，即使现在有一个更窄的索引可用。sys.foreign_keys具有列referenced_object_id，key_index_id，我推测验证将始终发生在其中列出的索引上，查询优化器目前不会考虑其他选项，但没有找到任何记录此事的文件。

我发现，在删除并重新添加外键约束后，sys.foreign_keys中相关值和查询计划发生了变化，开始使用更窄的索引。

CREATE TABLE Daily(
    DailyId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED  NOT NULL,
    Filler CHAR(4000) NULL,
) 

INSERT INTO Daily VALUES ('');


CREATE TABLE Impressions(
    ImpressionId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    DailyId INT NOT NULL CONSTRAINT FK REFERENCES Daily (DailyId), 
    Filler CHAR(4000) NULL,
)

/*Execution Plan uses clustered index - There is no NCI*/ 
INSERT INTO Impressions VALUES (1,1) 

ALTER TABLE Daily ADD CONSTRAINT
    UQ_Daily UNIQUE NONCLUSTERED(DailyId) 

/*Execution Plan still use clustered index even after NCI created*/    
INSERT INTO Impressions VALUES (1,1) 

ALTER TABLE Impressions DROP CONSTRAINT FK
ALTER TABLE Impressions  WITH CHECK ADD  CONSTRAINT FK FOREIGN KEY(DailyId)
REFERENCES Daily (DailyId)    

/*Now Execution Plan now uses non clustered index*/    
INSERT INTO Impressions VALUES (1,1)    

- Martin Smith

---

感谢您提供的解决方案，以及您的帮助和时间。我已经创建了索引，目前没有出现任何异常情况。如果我不再遇到任何异常，我将接受您的答案。 - Atzoya

不幸的是，添加这些索引并没有解决问题（周末期间抛出了大约200个异常）:( 我将运行一个跟踪来查看死锁是否有任何变化。 - Atzoya

@Atzoya - 我注意到 Insert 语句不同了。你的 Web 应用程序仍然将其作为单个语句调用吗？ - Martin Smith

我在第一篇帖子中为了简洁起见省略了一些属性。 - Atzoya

啊 - 好的。如果您能查看执行计划并确认它们是否正在使用新索引，那就太好了。啊 - 我刚刚发现死锁的“UPDATE”正在过滤“date>@now”，而不是“showTime”，所以除非这是您的另一个编辑，否则其他索引也无济于事。 - Martin Smith

Answer 3

我相信其他答案建议的更改是必要的，因为例如在您的情况下不需要使用游标...从您提供的代码中甚至没有必要使用WHILE...

我不是SQL Server专家...如果我需要执行您的存储过程所做的事情，我会确保@nowTime = DateTime.Now.AddSeconds(-1)并编写类似以下代码：

BEGIN

UPDATE Daily D SET 
D.spentDaily= D.spentDaily + (SELECT SUM(I.amountCharged) FROM Impressions I WHERE I.isCharged=0 AND I.showTime < @nowTime AND I.DailyId = D.DailyId), 
D.impressionsCountCache = D.impressionsCountCache + (SELECT COUNT(I.impressionId) FROM Impressions I WHERE I.isCharged=0 AND I.showTime < @nowTime AND I.DailyId = D.DailyId)
WHERE D.DailyId IN (SELECT I.DailyId FROM Impressions I WHERE I.isCharged=0 AND I.showTime < @nowTime AND I.DailyId = D.DailyId) AND D.isActive = 1;

UPDATE Impressions I SET
I.isCharged=1 
WHERE I.showTime < @nowTime AND I.isCharged=0;

COMMIT;

END

即使在高负载下，通过这种方式在Impressions上并行执行任何INSERT/UPDATE/DELETE也从未遇到死锁问题（虽然那是通过Oracle实现的）... 希望有所帮助。

- Yahia