0

In fairly simple T-SQL code deadlock happens:

INSERT INTO dbo.logs ( 
    source,
    status,
    description,
    aId
) VALUES ( @source, @status, @description, @aid )
set @i = SCOPE_IDENTITY()
if @aid is null begin
    update dbo.logs set aid = @i where id = @i
end

This code is part of stored procedure and is invoked from many places (SSIS, procedures etc.) The culprit is update part I assume. Could you explain me how this causes deadlock? I tried to put the code inside transaction so insert and following update are not interrupted by other process inserting data, but it made the lock graph even bigger. Thanks in advance.

Additional info:

  • no index on [aid] column, no foreign keys referencing logs table, there is no primary key
  • Lock XML:
<deadlock>
    <victim-list>
        <victimProcess id="process1d6c7047468" />
    </victim-list>
    <process-list>
        <process id="process1d6c7047468" taskpriority="0" logused="0" waitresource="PAGE: 11:1:614345 " waittime="876" ownerId="2357289810" transactionname="UPDATE" lasttranstarted="2023-01-09T07:20:00.573" XDES="0x1d6c513b810" lockMode="U" schedulerid="6" kpid="61824" status="suspended" spid="59" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2023-01-09T07:20:00.573" lastbatchcompleted="2023-01-09T07:20:00.573" lastattention="1900-01-01T00:00:00.573" clientapp="SQLAgent - TSQL JobStep (Job 0x18783977A1563943AFD84F2C9789B793 : Step 1)" hostname="EUGRPVSOT00940" hostpid="13200" isolationlevel="read committed (2)" xactid="2357289810" currentdb="11" currentdbname="crmdb" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128048">
            <executionStack>
                <frame procname="crmdb.dbo.addLog" line="35" stmtstart="1630" stmtend="1712" sqlhandle="0x03000b00241ae6147af1b2006baf000001000000000000000000000000000000000000000000000000000000">update dbo.logs set aid = @i where id = @</frame>
                <frame procname="crmdb.dbo.process_data" line="40" stmtstart="2586" stmtend="2666" sqlhandle="0x03000b00ce795103b87ad4007eaf000001000000000000000000000000000000000000000000000000000000">EXEC @batchId = addlog @logSrc, 's', @ms</frame>
                <frame procname="adhoc" line="1" stmtend="58" sqlhandle="0x01000b006c45f82bd07eca9cc501000000000000000000000000000000000000000000000000000000000000">exec dbo.process_data 'offers</frame>
            </executionStack>
            <inputbuf>exec dbo.process_data 'offers'</inputbuf>
        </process>
        <process id="process1d6c703f468" taskpriority="0" logused="0" waitresource="PAGE: 11:1:613314 " waittime="876" ownerId="2357289810" transactionname="UPDATE" lasttranstarted="2023-01-09T07:20:00.573" XDES="0x1bb98201900" lockMode="U" schedulerid="5" kpid="26988" status="suspended" spid="59" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2023-01-09T07:20:00.573" lastbatchcompleted="2023-01-09T07:20:00.573" lastattention="1900-01-01T00:00:00.573" clientapp="SQLAgent - TSQL JobStep (Job 0x18783977A1563943AFD84F2C9789B793 : Step 1)" hostname="EUGRPVSOT00940" hostpid="13200" isolationlevel="read committed (2)" xactid="2357289810" currentdb="11" currentdbname="crmdb" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128048">
            <executionStack>
                <frame procname="crmdb.dbo.addLog" line="35" stmtstart="1630" stmtend="1712" sqlhandle="0x03000b00241ae6147af1b2006baf000001000000000000000000000000000000000000000000000000000000">update dbo.logs set aid = @i where id = @</frame>
                <frame procname="crmdb.dbo.process_data" line="40" stmtstart="2586" stmtend="2666" sqlhandle="0x03000b00ce795103b87ad4007eaf000001000000000000000000000000000000000000000000000000000000">EXEC @batchId = addlog @logSrc, 's', @ms</frame>
                <frame procname="adhoc" line="1" stmtend="58" sqlhandle="0x01000b006c45f82bd07eca9cc501000000000000000000000000000000000000000000000000000000000000">exec dbo.process_data 'offers</frame>
            </executionStack>
            <inputbuf>exec dbo.process_data 'offers'</inputbuf>
        </process>
        <process id="process1beb4467c28" taskpriority="0" logused="252" waitresource="PAGE: 11:1:161716 " waittime="879" ownerId="2357289812" transactionname="UPDATE" lasttranstarted="2023-01-09T07:20:00.573" XDES="0x1ba62f00040" lockMode="U" schedulerid="3" kpid="41428" status="suspended" spid="61" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2023-01-09T07:20:00.573" lastbatchcompleted="2023-01-09T07:20:00.573" lastattention="1900-01-01T00:00:00.573" clientapp="SQLAgent - TSQL JobStep (Job 0x1E496967FE1ACA49A6BB93FF34EF3DEF : Step 1)" hostname="EUGRPVSOT00940" hostpid="13200" isolationlevel="read committed (2)" xactid="2357289812" currentdb="11" currentdbname="crmdb" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128048">
            <executionStack>
                <frame procname="crmdb.dbo.addLog" line="35" stmtstart="1630" stmtend="1712" sqlhandle="0x03000b00241ae6147af1b2006baf000001000000000000000000000000000000000000000000000000000000">update dbo.logs set aid = @i where id = @</frame>
                <frame procname="crmdb.dbo.process_data" line="40" stmtstart="2586" stmtend="2666" sqlhandle="0x03000b00ce795103b87ad4007eaf000001000000000000000000000000000000000000000000000000000000">EXEC @batchId = addlog @logSrc, 's', @ms</frame>
                <frame procname="adhoc" line="1" stmtend="72" sqlhandle="0x01000b00a1873e0550be34a2c701000000000000000000000000000000000000000000000000000000000000">exec dbo.process_data 'another_import</frame>
            </executionStack>
            <inputbuf>exec dbo.process_data 'another_import'</inputbuf>
        </process>
        <process id="process1c388608ca8" taskpriority="0" logused="252" waitresource="PAGE: 11:1:54528 " waittime="879" ownerId="2357289812" transactionname="UPDATE" lasttranstarted="2023-01-09T07:20:00.573" XDES="0x1d6c513bb20" lockMode="U" schedulerid="6" kpid="55900" status="suspended" spid="61" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2023-01-09T07:20:00.573" lastbatchcompleted="2023-01-09T07:20:00.573" lastattention="1900-01-01T00:00:00.573" clientapp="SQLAgent - TSQL JobStep (Job 0x1E496967FE1ACA49A6BB93FF34EF3DEF : Step 1)" hostname="EUGRPVSOT00940" hostpid="13200" isolationlevel="read committed (2)" xactid="2357289812" currentdb="11" currentdbname="crmdb" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128048">
            <executionStack>
                <frame procname="crmdb.dbo.addLog" line="35" stmtstart="1630" stmtend="1712" sqlhandle="0x03000b00241ae6147af1b2006baf000001000000000000000000000000000000000000000000000000000000">update dbo.logs set aid = @i where id = @</frame>
                <frame procname="crmdb.dbo.process_data" line="40" stmtstart="2586" stmtend="2666" sqlhandle="0x03000b00ce795103b87ad4007eaf000001000000000000000000000000000000000000000000000000000000">EXEC @batchId = addlog @logSrc, 's', @ms</frame>
                <frame procname="adhoc" line="1" stmtend="72" sqlhandle="0x01000b00a1873e0550be34a2c701000000000000000000000000000000000000000000000000000000000000">exec dbo.process_data 'another_import</frame>
            </executionStack>
            <inputbuf>exec dbo.process_data 'another_import'</inputbuf>
        </process>
        <process id="process1d6c7fb1088" taskpriority="0" logused="10000" waittime="876" schedulerid="18" kpid="56060" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-01-09T07:20:00.573" lastbatchcompleted="2023-01-09T07:20:00.573" lastattention="1900-01-01T00:00:00.573" clientapp="SQLAgent - TSQL JobStep (Job 0x1E496967FE1ACA49A6BB93FF34EF3DEF : Step 1)" hostname="EUGRPVSOT00940" hostpid="13200" loginname="GROUPDOMAIN\SQLSERVERAGENT_PROD" isolationlevel="read committed (2)" xactid="2357289812" currentdb="11" currentdbname="crmdb" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128048">
            <executionStack>
                <frame procname="crmdb.dbo.addLog" line="35" stmtstart="1630" stmtend="1712" sqlhandle="0x03000b00241ae6147af1b2006baf000001000000000000000000000000000000000000000000000000000000">update dbo.logs set aid = @i where id = @</frame>
                <frame procname="crmdb.dbo.process_data" line="40" stmtstart="2586" stmtend="2666" sqlhandle="0x03000b00ce795103b87ad4007eaf000001000000000000000000000000000000000000000000000000000000">EXEC @batchId = addlog @logSrc, 's', @ms</frame>
                <frame procname="adhoc" line="1" stmtend="72" sqlhandle="0x01000b00a1873e0550be34a2c701000000000000000000000000000000000000000000000000000000000000">exec dbo.process_data 'another_import</frame>
            </executionStack>
            <inputbuf>exec dbo.process_data 'another_import'</inputbuf>
        </process>
    </process-list>
    <resource-list>
        <pagelock fileid="1" pageid="614345" dbid="11" subresource="FULL" objectname="crmdb.dbo.logs" id="lock1bcaced1980" mode="U" associatedObjectId="72057594112770048">
            <owner-list>
                <owner id="process1d6c7fb1088" mode="U" />
            </owner-list>
            <waiter-list>
                <waiter id="process1d6c7047468" mode="U" requestType="wait" />
            </waiter-list>
        </pagelock>
        <pagelock fileid="1" pageid="613314" dbid="11" subresource="FULL" objectname="crmdb.dbo.logs" id="lock1cc0be87e00" mode="U" associatedObjectId="72057594112770048">
            <owner-list>
                <owner id="process1d6c7fb1088" mode="U" />
            </owner-list>
            <waiter-list>
                <waiter id="process1d6c703f468" mode="U" requestType="wait" />
            </waiter-list>
        </pagelock>
        <pagelock fileid="1" pageid="161716" dbid="11" subresource="FULL" objectname="crmdb.dbo.logs" id="lock1bcadc22f00" mode="U" associatedObjectId="72057594112770048">
            <owner-list>
                <owner id="process1d6c7047468" mode="U" />
            </owner-list>
            <waiter-list>
                <waiter id="process1beb4467c28" mode="U" requestType="wait" />
            </waiter-list>
        </pagelock>
        <pagelock fileid="1" pageid="54528" dbid="11" subresource="FULL" objectname="crmdb.dbo.logs" id="lock1bc6b07c680" mode="U" associatedObjectId="72057594112770048">
            <owner-list>
                <owner id="process1d6c703f468" mode="U" />
            </owner-list>
            <waiter-list>
                <waiter id="process1c388608ca8" mode="U" requestType="wait" />
            </waiter-list>
        </pagelock>
        <exchangeEvent id="Pipe1c8220ca500" WaitType="e_waitPipeGetRow" waiterType="Coordinator" nodeId="1" tid="0" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
            <owner-list>
                <owner id="process1c388608ca8" />
                <owner id="process1beb4467c28" />
            </owner-list>
            <waiter-list>
                <waiter id="process1d6c7fb1088" />
            </waiter-list>
        </exchangeEvent>
    </resource-list>
</deadlock>
3
  • 1
    Add an example deadlock report XML to your question. See this answer for a query get the deadlock info from the system_health trace. Do you have an index on the aid column? Without one, a scan will be required, which is prone to deadlocking with other processes.
    – Dan Guzman
    Commented Jan 10, 2023 at 10:18
  • 2
    I should have asked if you have an index on the id column. The deadlock resources suggest not but I would like to verify.
    – Dan Guzman
    Commented Jan 10, 2023 at 13:20
  • Thanks, I thought I had [id] as primary key, thus indexed, turned out I had not. Making it indexed fixed the problem.
    – mmierzwa
    Commented Jan 11, 2023 at 16:30

1 Answer 1

2

With no primary key this

update dbo.logs set aid = @i where id = @i

requires a full scan of the log table, with lots of locks and opportunities for deadlocks. Updating a table without a key isn't really a thing you can do with any concurrency.

If you don't want to put a clustered index on Id, use a sequence starting with the next value instead of an IDENTITY column

create sequence log_seq start with 292839182 increment by 1

then

declare @id int = next value for log_seq 

INSERT INTO dbo.logs (
    Id,
    source,
    status,
    description,
    aId
) VALUES ( @source, @status, @description, coalesce(@aid,@id) )

You'll have to rebuild the log table to remove the IDENTITY specification.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.