SQL Server: getting deadlock victim error on a stored procedure without transaction

1
votes

I have a table called Storage with this design:

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]

and I get a "deadlock victim error" in stored procedure 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

My error is:

Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I use SQL Server Profiler to trace deadlock, as you can see in deadlock report generated by 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>

I'm wondering why I get deadlock in an update query without transaction and a read query with nolock!!

Can someone tell me why I'm getting this error and how can I fix it?

1
sql-servertsqldeadlocksql-server-profiler
MMO_Storage_Set != SAPI_Storage_Set - Ivan Starostin
@IvanStarostin Thank you, I fixed the issue - Imran Shamszadeh
I bet on missing index (GameID, UserId) that causes scans and each update requires full table to be locked. - Ivan Starostin
@IvanStarostin Let me try it - Imran Shamszadeh
The UPDATE statement always runs in a transaction! If you don't specify you explicitly yourself, then there's an implicit transaction that is used - marc_s

1 Answers

0
votes

Not an answer to locks but I see a problem here 

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

But if it does exists you update 

AND ([Status] = 1)

You have not checked for status = 1 so that record may not be there

Search on upsert which uses merge. You can eliminate that IF (NOT EXISTS. This is not a clean up the code type suggestion. It may fix your lock problem. For sure it cannot hurt.

Maybe take an explicit rowlock on the update but table hint is should be a last ditch effort.