таким образом (хотя это был Oracle) ... HTH
тараюсь объяснить мою проблему как можно более подробно, и я был бы признателен за любую помощь / предложение. Моя проблема касается взаимоблокировки, вызванной двумя запросами (одна вставка и одно обновление). Я использую MS-SQL Server 2008
У меня есть два приложения, использующие одну базу данных:
Веб-приложение (при каждом запросе несколько записей вставляются в таблицу Impressions путем вызова хранимой процедуры)Служба Windows (вычисляет все показы, выполненные за одну минуту, каждую минуту, за предыдущую минуту и устанавливает флаг для каждого из показов, рассчитанных также с помощью хранимой процедуры)Веб-приложение вставляет записи показов без использования транзакции, а приложение-служба Windows вычисляет показы при использованииIsolationLevel.ReadUncommitted
сделка. Хранимая процедура в приложении службы Windows делает что-то вроде этого:
Хранимая процедура службы Windows:
Петли через все впечатления, которые имеютisCalculated
флаг установлен в false и date <@now, увеличивает счетчик и другие данные в другой таблице, связанной с таблицей показов, и устанавливаетisCalculated
установить значение true для показов с датой <@now. Поскольку эта хранимая процедура довольно большая, нет смысла вставлять ее, вот сокращенный фрагмент кода того, что делает proc:
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
Хранимая процедура веб-приложения:
Эта процедура довольно проста: она просто вставляет запись в таблицу. Вот сокращенный фрагмент кода:
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);
Я часто испытываю взаимоблокировки (исключения возникают в веб-приложении, а не в службе Windows), и после использования SQL-Profiler я обнаружил, что взаимоблокировки, вероятно, возникают из-за этих двух запросов (у меня нет большой опыт анализа данных профилировщика).
Последние данные трассировки, собранные с помощью профилировщика SQL-сервера, можно найти в нижней части этого вопроса.
Теоретически эти две хранимые процедуры должны быть в состоянии работать вместе, потому что первая вставляет записи одну за другой с date = 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>
Редактировать:
После предложений Джонатана Дикинсона:
Я изменил хранимую процедуру (убрал курсор),Я изменил IDX_Impressions_isCharged_showTime, чтобы не разрешать PAGE_LOCKS иЯ добавил -1 секунду к свойству @now в приложении службы Windows, чтобы избежать пограничных случаев взаимоблокировки.Обновить:
Время выполнения запроса было уменьшено после последних изменений, а количество исключений - нет.
Надеюсь, последнее обновление:
Изменения, предложенные Мартином Смитом, теперь действительны, запрос вставки теперь использует некластеризованный индекс, и теоретически это должно решить проблему. На данный момент исключений не поступало (скрестив пальцы)