SQL Server Performance

Help on Deadlock issue in SQL Server 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DilliGrg, Mar 15, 2012.

  1. DilliGrg Member

    Hello Everyone,
    This is the deadlock graph I have from one of our systems. I will use all the help I can get.

    Code:
    <deadlock-list>
    <deadlock victim="process8e31d68">
    <process-list>
    <process id="process3c092e8" taskpriority="0" logused="768" waitresource="OBJECT: 6:277576027:8 " waittime="4140" ownerId="899661708"
    transactionname="implicit_transaction" lasttranstarted="2012-03-14T21:17:58.287" XDES="0x13f19fd130" lockMode="IS" schedulerid="9" kpid="8124" status="suspended"
    spid="72" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-03-14T21:17:58.287" lastbatchcompleted="2012-03-14T21:17:58.283" clientapp="jTDS"
    hostname="PLAXORIN003" hostpid="123" loginname="GBuser" isolationlevel="read committed (2)" xactid="899661708" currentdb="6" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128058">
    <executionStack>
    <frame procname="Gradebook.dbo.ut_Score_Update_ScoreHistory" line="9" stmtstart="248" stmtend="992" sqlhandle="0x03000600d64334038c2b1500949900000000000000000000">
    INSERT INTO ScoreHistory(
    ScoreID,
    Score,
    InstructorNote,
    StudentNote
    )
    SELECT
    ScoreID,
    Score,
    (SELECT InstructorNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID),
    (SELECT StudentNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID)
    FROM deleted
    
    WHERE
    (Select StatusID from Score where ScoreID = deleted.ScoreID) = 1 </frame>
    <frame procname="adhoc" line="1" stmtstart="68" sqlhandle="0x020000009a464b032ceb25e89690e3370e8b5daa50655776">
    
    UPDATE [Score] set [Score] = @1 WHERE [ScoreID]=@2 </frame>
    <frame procname="adhoc" line="1" stmtend="116" sqlhandle="0x0200000043f132175f8903b116b6e4fd01571c1032098b0c">
    
    update Score set Score = 3.58 where ScoreID = 142601340998 </frame>
    </executionStack>
    <inputbuf>
    
    update Score set Score = 3.58 where ScoreID = 142601340998
    
    update ScoreNote set StudentNote = &apos;No assignment submitted. Please see course information for due dates. The COO is an old form that is no longer being used by the university.
    Please always submit the most current forms for your courses. - 3/14/12 - Assignment submitted two days late. Please see comments on document.
    A Turnitin report is required for every paper assignment.&apos;, InstructorNote = &apos;&apos;
    where ScoreID = 142601340998 </inputbuf>
    </process>
    <process id="process8e1d2e8" taskpriority="0" logused="20012" waittime="3515" schedulerid="3" kpid="5480" status="suspended" spid="289" sbid="0" ecid="35" priority="0"
    transcount="0" lastbatchstarted="2012-03-14T21:17:41.343" lastbatchcompleted="2012-03-14T21:17:41.343" clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)"
    hostname="PWXDSQLV105" hostpid="5968" isolationlevel="read committed (2)" xactid="899645666" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
    <frame procname="Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks" line="104" stmtstart="6336" stmtend="6930" sqlhandle="0x0300060065a2f55062d00c00289f00000100000000000000">
    
    DELETE TOP (@BatchSize) FROM sn
    FROM dbo.ScoreNote sn
    JOIN dbo.Score s
    ON s.ScoreID = sn.ScoreID
    JOIN dbo.StudentCourseOffering sco
    ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
    JOIN #tempCourseOffering tmp
    ON tmp.CourseOfferingID = sco.CourseOfferingID </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x0100060028047a02f0511560110000000000000000000000">
    
    EXEC dbo.usp_DeleteOlderGradeBookRecords_TenWeeks
    @CutOffDate = NULL
    ,@BatchSize = 1000 </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
    </process>
    <process id="process8e31d68" taskpriority="0" logused="0" waitresource="KEY: 6:297569039745024 (3400381188ae)" waittime="3656" ownerId="899645666" transactionname="DELETE"
    lasttranstarted="2012-03-14T21:17:48.510" XDES="0x91e01a760" lockMode="S" schedulerid="5" kpid="9148" status="suspended" spid="289" sbid="0" ecid="52" priority="0" transcount="0"
    lastbatchstarted="2012-03-14T21:17:41.343" lastbatchcompleted="2012-03-14T21:17:41.343" clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)"
    hostname="PWXDSQLV105" hostpid="5968" isolationlevel="read committed (2)" xactid="899645666" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
    <frame procname="Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks" line="104" stmtstart="6336" stmtend="6930" sqlhandle="0x0300060065a2f55062d00c00289f00000100000000000000">
    
    DELETE TOP (@BatchSize) FROM sn
    FROM dbo.ScoreNote sn
    JOIN dbo.Score s
    ON s.ScoreID = sn.ScoreID
    JOIN dbo.StudentCourseOffering sco
    ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
    JOIN #tempCourseOffering tmp
    ON tmp.CourseOfferingID = sco.CourseOfferingID </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x0100060028047a02f0511560110000000000000000000000">
    
    EXEC dbo.usp_DeleteOlderGradeBookRecords_TenWeeks
    @CutOffDate = NULL
    ,@BatchSize = 1000 </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
    </process>
    <process id="process8e4f048" taskpriority="0" logused="10014" waittime="3593" schedulerid="8" kpid="2472" status="suspended" spid="289" sbid="0" ecid="18" priority="0"
    transcount="0" lastbatchstarted="2012-03-14T21:17:41.343" lastbatchcompleted="2012-03-14T21:17:41.343" clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)"
    hostname="PWXDSQLV105" hostpid="5968" isolationlevel="read committed (2)" xactid="899645666" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
    <frame procname="Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks" line="104" stmtstart="6336" stmtend="6930" sqlhandle="0x0300060065a2f55062d00c00289f00000100000000000000">
    
    DELETE TOP (@BatchSize) FROM sn
    FROM dbo.ScoreNote sn
    JOIN dbo.Score s
    ON s.ScoreID = sn.ScoreID
    JOIN dbo.StudentCourseOffering sco
    ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
    JOIN #tempCourseOffering tmp
    ON tmp.CourseOfferingID = sco.CourseOfferingID </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x0100060028047a02f0511560110000000000000000000000">
    
    EXEC dbo.usp_DeleteOlderGradeBookRecords_TenWeeks
    @CutOffDate = NULL
    ,@BatchSize = 1000 </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
    </process>
    </process-list>
    <resource-list>
    <objectlock lockPartition="8" objid="277576027" subresource="FULL" dbid="6" objectname="Gradebook.dbo.ScoreNote" id="lockaa0e66280" mode="X" associatedObjectId="277576027">
    <owner-list>
    <owner id="process8e1d2e8" mode="X"/>
    </owner-list>
    <waiter-list>
    <waiter id="process3c092e8" mode="IS" requestType="wait"/>
    </waiter-list>
    </objectlock>
    <keylock hobtid="297569039745024" dbid="6" objectname="Gradebook.dbo.Score" indexname="PK_Score" id="lock12830f3d00" mode="X" associatedObjectId="297569039745024">
    <owner-list>
    <owner id="process3c092e8" mode="X"/>
    </owner-list>
    <waiter-list>
    <waiter id="process8e31d68" mode="S" requestType="wait"/>
    </waiter-list>
    </keylock>
    <exchangeEvent id="port8025fd40" nodeId="14">
    <owner-list>
    <owner event="e_waitNone" type="producer" id="process8e31d68"/>
    </owner-list>
    <waiter-list>
    <waiter event="e_waitPipeGetRow" type="consumer" id="process8e4f048"/>
    </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="port8025e480" nodeId="12">
    <owner-list>
    <owner event="e_waitPipeGetRow" type="consumer" id="process8e4f048"/>
    </owner-list>
    <waiter-list>
    <waiter event="e_waitPipeNewRow" type="producer" id="process8e1d2e8"/>
    </waiter-list>
    </exchangeEvent>
    </resource-list>
    </deadlock>
    </deadlock-list>
    

Share This Page