SQL Server: получение ошибки жертвы взаимоблокировки для хранимой процедуры без транзакции

Question

У меня есть стол Storage с этим дизайном:

CREATE TABLE [dbo].[Storage]
(
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [GameId] [INT] NOT NULL,
    [UserId] [INT] NOT NULL,
    [Status] [TINYINT] NOT NULL,
    [CreatedAt] [DATETIME] NOT NULL,
    [UpdatedAt] [DATETIME] NULL,
    [Data] [NVARCHAR](MAX) NOT NULL,

    CONSTRAINT [PK_Storage] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

и я получаю "ошибку жертвы тупика" в хранимой процедуре MMO_Storage_Set:

CREATE PROCEDURE [dbo].[MMO_Storage_Set] 
    @Data NVARCHAR(MAX),
    @GameKey INT,
    @UserId INT,
    @ErrorCode INT OUT 
AS
BEGIN
    SET NOCOUNT ON;

    -- user must have 1 active session at minimum
    IF NOT EXISTS(SELECT Id FROM dbo.[Session] WITH(NOLOCK) 
                  WHERE UserId = @UserId AND ([Status] = 1))
    BEGIN
        SET @ErrorCode = -3
        RETURN
    END

    DECLARE @GameId INT = NULL
    DECLARE @GameStatus TINYINT = NULL

    SELECT @GameStatus = [Status], @GameId = Id
    FROM dbo.[Game] WITH(NOLOCK)
    WHERE ([Key] = @GameKey)

    -- Game not found
    IF @GameStatus IS NULL
    BEGIN
        SET @ErrorCode = -5
        RETURN
    END

    -- Game is not valid
    IF @GameStatus != 1
    BEGIN
        SET @ErrorCode = -6
        RETURN
    END

    SET @ErrorCode = 0

    IF (NOT EXISTS (SELECT ID FROM [Storage] WITH (NOLOCK) 
                    WHERE [UserID] = @UserId AND [GameId] = @GameId))
    BEGIN
        INSERT INTO dbo.Storage (GameId, UserId,[Status], CreatedAt, UpdatedAt, Data)
        VALUES (@GameId, @UserId, 1, GETDATE(), NULL, @Data)
    END
    ELSE
    BEGIN       
        UPDATE dbo.Storage 
        SET Data = @Data, UpdatedAt = GETDATE()
        WHERE (GameID = @GameId) AND (UserId = @UserId) AND ([Status] = 1)
    END

    SET @ErrorCode = 1
END

Моя ошибка:

Транзакция (ID процесса 55) заблокирована при блокировке | ресурсы буфера связи с другим процессом и были выбраны в качестве жертвы тупика. Перезапустите транзакцию.

Я использую SQL Server Profiler для отслеживания взаимоблокировок, как вы можете видеть из отчета о взаимоблокировках, созданного SQL Server Profiler:

<deadlock>
 <victim-list>
  <victimProcess id="process10754aca8" />
 </victim-list>
 <process-list>
  <process id="process10754aca8" taskpriority="0" logused="0" waitresource="PAGE: 9:1:1167 " waittime="1302" ownerId="501754107" transactionname="UPDATE" lasttranstarted="2018-02-18T02:21:16.990" XDES="0x270741590" lockMode="U" schedulerid="3" kpid="6700" status="suspended" spid="63" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2018-02-18T02:21:16.810" lastbatchcompleted="2018-02-18T02:21:16.817" lastattention="1900-01-01T00:00:00.817" clientapp=".Net SqlClient Data Provider" hostname="APP-SOCCER-VAS" hostpid="11332" isolationlevel="read committed (2)" xactid="501754107" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="SoccerChampion.dbo.MMO_Storage_Set" line="60" stmtstart="2870" stmtend="3132" sqlhandle="0x030009008b0bd114d1f2a4004da8000001000000000000000000000000000000000000000000000000000000">
Update dbo.Storage 
        Set Data = @Data, UpdatedAt = GETDATE()
        WHERE (GameID = @GameId) AND (UserId = @UserId) AND ([Status] = 1    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 349244299]   </inputbuf>
  </process>
  <process id="process136d8d088" taskpriority="0" logused="5384" waitresource="PAGE: 9:1:1167 " waittime="1056" ownerId="501753989" transactionname="UPDATE" lasttranstarted="2018-02-18T02:21:16.773" XDES="0x1767f9ce0" lockMode="U" schedulerid="1" kpid="9920" status="suspended" spid="52" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2018-02-18T02:21:16.590" lastbatchcompleted="2018-02-18T02:21:16.597" lastattention="1900-01-01T00:00:00.597" clientapp=".Net SqlClient Data Provider" hostname="APP-SOCCER-VAS" hostpid="11332" isolationlevel="read committed (2)" xactid="501753989" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="SoccerChampion.dbo.MMO_Storage_Set" line="60" stmtstart="2870" stmtend="3132" sqlhandle="0x030009008b0bd114d1f2a4004da8000001000000000000000000000000000000000000000000000000000000">
Update dbo.Storage 
        Set Data = @Data, UpdatedAt = GETDATE()
        WHERE (GameID = @GameId) AND (UserId = @UserId) AND ([Status] = 1    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 349244299]   </inputbuf>
  </process>
  <process id="process1001b7848" taskpriority="0" logused="10000" waittime="654" schedulerid="4" kpid="10028" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-02-18T02:21:16.590" lastbatchcompleted="2018-02-18T02:21:16.597" lastattention="1900-01-01T00:00:00.597" clientapp=".Net SqlClient Data Provider" hostname="APP-SOCCER-VAS" hostpid="11332" loginname="SC_Core" isolationlevel="read committed (2)" xactid="501753989" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="SoccerChampion.dbo.MMO_Storage_Set" line="60" stmtstart="2870" stmtend="3132" sqlhandle="0x030009008b0bd114d1f2a4004da8000001000000000000000000000000000000000000000000000000000000">
Update dbo.Storage 
        Set Data = @Data, UpdatedAt = GETDATE()
        WHERE (GameID = @GameId) AND (UserId = @UserId) AND ([Status] = 1    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 349244299]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="1167" dbid="9" subresource="FULL" objectname="SoccerChampion.dbo.Storage" id="lock205c8bb00" mode="U" associatedObjectId="72057594084524032">
   <owner-list>
    <owner id="process1001b7848" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process10754aca8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="1167" dbid="9" subresource="FULL" objectname="SoccerChampion.dbo.Storage" id="lock205c8bb00" mode="U" associatedObjectId="72057594084524032">
   <owner-list>
    <owner id="process10754aca8" mode="U" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process136d8d088" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <exchangeEvent id="Pipeb1ff6380" WaitType="e_waitPipeGetRow" nodeId="2">
   <owner-list>
    <owner id="process136d8d088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1001b7848" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>
</deadlock>

Мне интересно, почему я попал в тупик в update запрос без транзакции и запрос на чтение с nolock!!

Может кто-нибудь сказать мне, почему я получаю эту ошибку и как я могу это исправить?

Answer 1

Не ответ на блокировки, но я вижу здесь проблему

IF (NOT EXISTS (SELECT ID FROM [Storage] WITH (NOLOCK) 
                WHERE [UserID] = @UserId AND [GameId] = @GameId))  

Но если он существует, вы обновляете

AND ([Status] = 1) 

Вы не проверили статус = 1, поэтому запись может отсутствовать

Поиск по upsert, который использует слияние. Вы можете устранить это IF (NOT EXISTS, Это не очистка предложения типа кода. Это может исправить вашу проблему с блокировкой. Наверняка это не повредит.

Возможно, в обновлении используется явная блокировка строки, но подсказка таблицы должна быть последней попыткой.