更新和插入查询产生死锁 [英] update and insert queries creating a deadlock

问题描述

我会尽量解释我的问题尽可能详细，我就AP preciate任何帮助/建议。我的问题是关于一个僵局正在造成两个查询（一个插片和一个更新）。我使用的MS-SQL服务器2008

I will try to explain my problem as detailed as possible, and i would appreciate any help/suggestion. My problem is regarding a deadlock being caused by two queries (one insert and one update). I'm using MS-SQL server 2008

我必须使用同一个数据库的两个应用：

I have two applications using the same database:

1. 在Web应用（每个请求多个记录被调用存储过程插入IM pressions表）
2. 在Windows服务（计算所有在一分钟内，每分钟做了林pressions，为previous分钟，并设置一个标志，每通过一个存储过程以及计算的林pressions的）

在Web应用程序中插入的IM pressions记录不使用事务，而Windows服务应用程序在使用计算IM pressions一个 IsolationLevel.ReadUncommitted 交易。在Windows服务应用程序的存储过程确实是这样的：

The web app inserts the impressions records without using a transaction, while the windows service application calculates the impressions while using a IsolationLevel.ReadUncommitted transaction. The stored procedure in the windows service app does something like this:

Windows服务的存储过程：

循环槽都具有 isCalculated 标志设置为false，并且日期和LT的IM pressions; @now，递增计数器和其他数据连接到IM pressions表另一个表，并设置 isCalculated 标志设置为true在具有IM pressions日期＆LT; @现在。由于此存储过程是pretty的大，中粘贴没有意义，这里是一个缩短code什么样的PROC做片段：

Loops trough all the impressions that have the isCalculated flag set to false and date < @now , increments a counter and other data in another table connected to the impressions table, and sets the isCalculated flag to true on impressions that have date < @now. Because this stored procedure is pretty big, no point in pasting it, here is a shortened code snippet of what the proc does:

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应用程序的存储过程：

这个程序是pretty的简单，它只是插入记录在表格中。这里是一个缩短code片断：

This procedure is pretty simple it just inserts the record in the table. Here is a shortened code snippet:

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

的code

在code调用这些存储过程是pretty的简单，它只是创建的SQL命令传递所需的参数，并执行它们

The code that calls these stored procedures is pretty simple it just creates the SQL commands passing the needed parameters and executes them

//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-探查之后，我发现了死锁很可能发生，因为这两个查询（我不'T有分析探查数据很多经验）。

I'm experiencing deadlocks very often (the exceptions occur in the web app, not the windows service), and after using the SQL-Profiler, I found out that the deadlocks are probably happening because of these two queries (I don't have much experience in analyzing profiler data).

从SQL Server事件探查收集的最新跟踪数据可以在这一问题的底部找到

The latest trace data collected from the SQL server profiler can be found on the bottom of this question

在理论上这两个存储过程应该能够一起工作，因为第一个个日期= DateTime.Now插入一个记录，而第二个计算，有日期和其中的林pressions; DateTime.Now。

In theory these two stored procedures should be able to work together because the first one inserts the records one by one with date=DateTime.Now, and the second one calculates the Impressions that have date < DateTime.Now.

编辑：

下面是Windows服务程序的code运行：

Here is the code run in the windows service app:

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

编辑：

我添加两个表的索引所建议的马丁·史密斯是这样的：

I added the indexes on both of the tables as suggested by Martin Smith like this:

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

目前未见异常，会向警方报到

For now no exceptions, will report back later

编辑：

不幸的是这并没有解决死锁问题。我将开始在探查一个死锁跟踪，看是否死锁是和以前一样。

Unfortunately this did not solve the deadlock issue. I will start a deadlock trace in profiler to see if the deadlocks are the same as before.

编辑：

粘贴新的跟踪（对我来说，它看起来一样的previous之一），无法捕捉到的执行计划（它太大了），但是的这里是执行计划的XML 。而这里是插入查询的执行计划的截图：

Pasted the new trace (to me it looks the same as the previous one), couldn't capture a screen of the execution plan (its too big) but here is the xml from the execution plan.And here is a screenshot of the execution plan of the insert query:

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

编辑：

在自乔纳森·迪金森建议：

After suggestions from Jonathan Dickinson:

1. 我改变了存储过程（删除光标）
2. 我改变了IDX_Im pressions_isCharged_showTime不允许PAGE_LOCKS和
3. 在我加-1秒到Windows服务应用程序的@now属性，以避免临界僵局的情况下。

更新：

查询执行时间后终于改变下降，但例外的数量也没有。

The query execution time was decreased after the last changes, but the number of exceptions hasn't.

我希望最后一次更新：

提出的马丁·史密斯的变化是现在生活，插入查询现在使用的非聚集索引，并在理论上这应该可以解决这个问题。目前没有任何例外的报道（保持我的手指穿过）

The changes proposed by Martin Smith are now live, the insert query now uses the non-clustered index and in theory this should fix the issue. For now no exceptions have been reported (Keeping my fingers crossed)

推荐答案

您窗口服务游标更新各行每日为此它需要 X 锁。这些将不会被释放，直到事务结束。

Your windows service cursor updates various rows in Daily for which it takes X locks. These won't be released until the transaction ends.

您的Web应用程序，然后执行一个INSERT INTO 林pressions 和新保持的 X 锁插排，而它等待一个取值在每日被锁定由其他进程的行一个锁。它需要阅读本验证FK约束。

Your web app then does an Insert into Impressions and keeps an X lock on the newly inserted row whilst it waits for an S lock on one of the rows in Daily that are locked by the other process. It needs to read this to validate the FK constraint.

您的窗口服务的话，确实对林pressions 更新承担它扫描的行 U 锁一路上。有没有指标，允许其寻求到行，因此该扫描包括由Web应用程序添加的行。

Your windows service then does the Update on Impressions taking U locks on the rows it scans along the way. There is no index that allows it to seek into the rows so this scan includes the row added by the web app.

于是

（1）你可以添加一个综合指数林pressions 在欣欣isCharged 或副-versa（检查执行计划），以允许Windows服务将更新为通过索引中的行求，而不是一个完整的扫描。

(1) You could add a composite index to Impressions on showTime, isCharged or vice-versa (check the execution plans) to allow the rows that the windows service will update to be found by an index seek rather than a full scan.

- 或者

（2）您可以添加在日报（DailyId）冗余非聚集索引。这将是比集群之一，所以在FK验证可能会使用更窄了很多，在preference到需要聚集索引行的取值锁定

(2) You could add a redundant non clustered index on Daily(DailyId). This will be a lot narrower than the clustered one so the FK validation will likely use that in preference to needing an S lock on the clustered index row.

修改

免责声明：根据假设和观察，而不是什么我已经找到记录下面是

看来这个想法（2）不工作原样。执行计划显示，FK验证仍继续发生对聚集索引不顾事实，较窄的指数现已推出。 sys.foreign_keys 中的列 referenced_object_id，key_index_id ，我推测，验证将当前总是发生在索引中列出那里，查询优化工具目前不考虑替代方案，但没有发现任何记录本。

It seems that idea (2) does not work "as is". The execution plan shows that the FK validation still continues to happen against the clustered index regardless of the fact that a narrower index is now available. sys.foreign_keys has columns referenced_object_id, key_index_id and I speculate that the validation will currently always happen on the index listed there and the Query Optimiser doesn't currently consider alternatives but haven't found anything documenting this.

我发现，在 sys.foreign_keys 相关值和查询计划改为开始使用较窄的指数后，我删除并重新添加的外键约束。

I found that the relevant values in sys.foreign_keys and the query plan changed to start using the narrower index after I dropped and re-added the Foreign Key constraint.

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)    

这篇关于更新和插入查询产生死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！