Help on Deadlock issue in SQL Server 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help on Deadlock issue in SQL Server 2005

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>

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |