我的问题是关于两个查询(一个插入和一个更新)导致死锁。我有两个应用程序使用相同的数据库: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 &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的建议:
- 我更改了存储过程(移除了游标)
- 我更改了IDX_Impressions_isCharged_showTime以不允许PAGE_LOCKS
- 我在Windows服务应用程序中将@now属性减少了1秒,以避免边缘死锁情况。
更新:
最近的更改降低了查询执行时间,但异常数量没有减少。
希望这是最后一次更新:
Martin Smith提出的更改现已上线,插入查询现在使用非聚集索引,理论上这应该解决问题。目前没有报告任何异常(手指交叉)
dbo.Daily
的FK关系上,你在这两个表上都有哪些索引? - Martin Smithdbo.Daily
上有一个X
锁定呢?从您发布的代码中并不清楚。您能添加一下吗? - Martin SmithspentDaily
和impressionsCountCache
列。这样,SQL Server会自动执行维护,您就不需要编写任何代码,也不会出现死锁问题... - Damien_The_Unbeliever