Тупик между INSERT и SELECT SP
У меня проблемы с приложением, в котором я захожу в тупик, который, как я думал, никогда не случится. У меня есть две хранимые процедуры. Один из них просто читает части таблицы, а другой добавляет строку в таблицу или обновляет существующую строку в случае нарушения первичного ключа.
Я читал о хранимых процедурах, которые могут получить общую блокировку чтения, и что если обе хранимые процедуры перейдут в монопольную блокировку, возникнет взаимоблокировка. Я не вижу, чтобы это произошло здесь, поскольку хранимая процедура GetList просто читает и никогда не пишет. Есть ли другая возможная причина этих хранимых процедур для взаимоблокировки?
Следующий отчет о тупике:
<?xml version="1.0" encoding="UTF-8"?>
<deadlock-list>
<deadlock victim="process3e37f62c8">
<process-list>
<process id="process3e37f62c8" taskpriority="0" logused="0" waitresource="KEY: 7:72057594152681472 (de2613e7782e)" waittime="2535" ownerId="7732055781" transactionname="SELECT" lasttranstarted="2014-03-17T17:48:31.437" XDES="0x1298bc1c0" lockMode="S" schedulerid="11" kpid="21372" status="suspended" spid="121" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-03-17T17:48:31.437" lastbatchcompleted="2014-03-17T17:48:31.437" clientapp=".Net SqlClient Data Provider" hostname="STO2AP07" hostpid="4908" loginname="(hidden)" isolationlevel="read committed (2)" xactid="7732055781" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="(hidden).dbo.GetList" line="18" stmtstart="1060" stmtend="1942" sqlhandle="0x03000700072cfd1b7b8be800cfa200000100000000000000">
SELECT
TOP (@NumRows)
[T1].[Id] ,
[T1].[A] ,
[T1].[B] ,
[T1].[C] ,
[T1].[D] ,
[T1].[E] ,
[T1].[F] ,
[T1].[G] ,
[T1].[H] ,
[T1].[I] ,
[T1].[J]
FROM [Item] AS [T1] where [J] > @LastUpdatedDateTime ORDER BY [T1].[LastUpdatedField] ASC
</frame>
</executionStack>
<inputbuf>Proc [Database Id = 7 Object Id = 469576711]</inputbuf>
</process>
<process id="process5a514c8" taskpriority="0" logused="244" waitresource="KEY: 7:72057594152615936 (16f70bd264f5)" waittime="2535" ownerId="7732055725" transactionname="user_transaction" lasttranstarted="2014-03-17T17:48:31.437" XDES="0x6602dfa00" lockMode="X" schedulerid="13" kpid="21196" status="suspended" spid="267" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-03-17T17:48:31.437" lastbatchcompleted="2014-03-17T17:48:31.437" clientapp=".Net SqlClient Data Provider" hostname="STO2AP07" hostpid="4908" loginname="(hidden)" isolationlevel="read committed (2)" xactid="7732055725" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="(hidden).dbo.AddToList" line="52" stmtstart="2484" stmtend="3294" sqlhandle="0x030007005cbf2019598be800cfa200000100000000000000">
UPDATE [Item]
SET
[A] = @A ,
[B] = @B ,
[C] = @C ,
[D] = @D ,
[E] = @E ,
[F] = @F ,
[G] = @G ,
[H] = @H ,
[I] = @I ,
[J] = @J
WHERE [Id] = @Id
</frame>
</executionStack>
<inputbuf>Proc [Database Id = 7 Object Id = 421576540]</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594152681472" dbid="7" objectname="(hidden).dbo.Item" indexname="PK_Item_1" id="lock47c381b80" mode="X" associatedObjectId="72057594152681472">
<owner-list>
<owner id="process5a514c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process3e37f62c8" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594152615936" dbid="7" objectname="(hidden).dbo.Item" indexname="IX_LastUpdatedField" id="lock402a95800" mode="S" associatedObjectId="72057594152615936">
<owner-list>
<owner id="process3e37f62c8" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process5a514c8" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Ниже приведена хранимая процедура с именем GetList:
ALTER PROCEDURE [dbo].[GetList]
@LastUpdatedDateTime datetime,
@NumRows int
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (@NumRows)
[T1].[Id]
, [T1].[A]
, [T1].[B]
, [T1].[C]
, [T1].[D]
, [T1].[E]
, [T1].[F]
, [T1].[G]
, [T1].[H]
, [T1].[I]
, [T1].[J]
FROM [Item] AS [T1] where J >= @LastUpdatedDateTime
ORDER BY [T1].[J] ASC
END
Ниже приведена хранимая процедура с именем AddToList:
ALTER PROCEDURE [dbo].[AddToList]
@Id int,
@A varchar(30),
@B decimal(18,2),
@V varchar(30),
@D varchar(512),
@E datetime,
@F datetime,
@G bit,
@H int,
@I int
@J datetime,
AS
IF NOT EXISTS(SELECT Id FROM Item WHERE [Id] = @Id)
BEGIN
SET NOCOUNT ON;
INSERT INTO [Item]
( [Id]
, [A]
, [B]
, [C]
, [D]
, [E]
, [F]
, [G]
, [H]
, [I]
, [J]
)
VALUES
( @Id
, @A
, @B
, @C
, @D
, @E
, @F
, @G
, @H
, @I
, @J
)
END
ELSE
BEGIN
UPDATE [Item] SET
[A] = @A
, [B] = @B
, [C] = @C
, [D] = @D
, [E] = @E
, [F] = @F
, [G] = @G
, [H] = @H
, [I] = @I
, [J] = @J
WHERE [Id] = @Id
END
1 ответ
Выбор делает это:
- Чтение из индекса LastUpdatedDateTime (S-блокировка)
- Посмотрите на соответствующий ряд CI (S-замок)
Обновление делает это:
- Написать в CI (X-Lock)
- Запись в индекс LastUpdatedDateTime (X-блокировка)
Они обращаются к двум ресурсам несовместимо в обратном порядке. Тупик.
Работает ли считыватель с уровнем изоляции выше READ COMMITTED? READ COMMITTED следует снять блокировки сразу после прочтения заблокированного ряда.
Если вы можете заставить транзакцию чтения использовать модель изоляции моментального снимка, тупик исчезнет простым, надежным и постоянным способом.
Если это невозможно, используйте READ COMMITTED уровень изоляции.
Если это невозможно, вам придется либо возиться с подсказками блокировки (сложно поддерживать), либо реализовать логику повторных попыток блокировки.