Deadlocks

• gary1

  SSC-Insane

  Points: 21152

  More actions

  September 27, 2013 at 4:11 pm

  #395574

  Hi,

  We have ProjectServer 2010 databases on SQL Server 2008 R2 standard edition. Occasionally, we are getting blow dead lock. Please advise why this is occurring.

  - <deadlock-list>

  - <deadlock victim="process80739048">

  - <process-list>

  - <process id="process80739048" taskpriority="0" logused="0" waitresource="KEY: 12:72057594307477504 (cc0e552c4e6e)" waittime="3451" ownerId="1671535010" transactionname="SELECT" lasttranstarted="2013-09-27T14:55:40.077" XDES="0xdd664c90" lockMode="S" schedulerid="1" kpid="3856" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-09-27T14:55:40.070" lastbatchcompleted="2013-09-27T14:55:40.070" clientapp=".Net SqlClient Data Provider" hostname="PROSERVER" hostpid="2212" loginname="abc\spadmin" isolationlevel="read committed (2)" xactid="1671535010" currentdb="12" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

  - <executionStack>

  <frame procname="ProjectServer_Published.dbo.MSP_WEB_SP_QRY_GetTimeSheetLinesByTSUID" line="5" stmtstart="182" stmtend="2638" sqlhandle="0x03000c001c5dca4d29371901e0a000000100000000000000">SELECT TS_LINE_UID,TS_UID,TL.ASSN_UID,TL.PROJ_UID,TL.TASK_UID,TS_LINE_COMMENT,TS_LINE_VALIDATION_TYPE,TS_LINE_CACHED_ASSIGN_NAME,TS_LINE_CACHED_PROJ_NAME,C.TS_LINE_CLASS_UID,C.TS_LINE_CLASS_NAME,C.TS_LINE_CLASS_NEED_APPROVAL, TS_LINE_CLASS_TYPE,TS_LINE_ACT_SUM_VALUE,TS_LINE_CACHED_PROJ_REVISION_COUNTER,TS_LINE_CACHED_PROJ_REVISION_RANK,TS_LINE_IS_CACHED,TS_LINE_STATUS,A.WRES_UID_MANAGER as MGR_UID,R2.RES_NAME AS TS_LINE_STATUSMANAGER,T1.TASK_START_DATE AS SUMMARY_TASK_START_DATE,T1.TASK_FINISH_DATE AS SUMMARY_TASK_FINISH_DATE,PINT.TASK_START_DATE as PIN_TASK_START_DATE,PINT.TASK_FINISH_DATE as PIN_TASK_FINISH_DATE,PINT.TASK_IS_MANUAL,TS_LINE_TASK_HIERARCHY FROM MSP_TIMESHEET_LINES TL INNER JOIN MSP_TIMESHEET_CLASSES C ON TL.TS_LINE_CLASS_UID= C.TS_LINE_CLASS_UID LEFT JOIN MSP_ASSIGNMENTS_SAVED A ON TL.ASSN_UID = A.ASSN_UID LEFT JOIN MSP_RESOURCES R2 ON R2.RES_UID = A.WRES_UID_MANAGER LEFT JOIN MSP_TASKS_SAVED PINT ON A.TASK_UID = PINT.TASK_UID LEFT JOIN MSP_TASKS T1 ON TL</frame>

  <frame procname="ProjectServer_Published.dbo.MSP_WEB_SP_QRY_ReadTimeSheetAndStatusingByTSUID" line="11" stmtstart="562" stmtend="678" sqlhandle="0x03000c008feb475870371901e0a000000100000000000000">EXEC dbo.MSP_WEB_SP_QRY_GetTimeSheetLinesByTSUID @tsUID</frame>

  </executionStack>

  <inputbuf>Proc [Database Id = 12 Object Id = 1481108367]</inputbuf>

  </process>

  - <process id="process43f4c8" taskpriority="0" logused="68848" waitresource="KEY: 12:72057594325172224 (c6d47f5ec337)" waittime="3429" ownerId="1671534835" transactionname="user_transaction" lasttranstarted="2013-09-27T14:55:39.960" XDES="0x183c93950" lockMode="X" schedulerid="1" kpid="3256" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-27T14:55:40.100" lastbatchcompleted="2013-09-27T14:55:40.100" clientapp=".Net SqlClient Data Provider" hostname="PROSERVER" hostpid="2880" loginname="abc\spadmin" isolationlevel="read committed (2)" xactid="1671534835" currentdb="12" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

  - <executionStack>

  <frame procname="adhoc" line="1" stmtstart="110" sqlhandle="0x02000000d3ccce116d786ebb7663144d5e6290cea5ae14f1">UPDATE MSP_TIMESHEET_LINES SET TS_LINE_STATUS = @TS_LINE_STATUS WHERE TS_LINE_UID = @TS_LINE_UID</frame>

  <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>

  </executionStack>

  <inputbuf>(@TS_LINE_UID uniqueidentifier,@TS_LINE_STATUS tinyint)UPDATE MSP_TIMESHEET_LINES SET TS_LINE_STATUS = @TS_LINE_STATUS WHERE TS_LINE_UID = @TS_LINE_UID</inputbuf>

  </process>

  </process-list>

  - <resource-list>

  - <keylock hobtid="72057594307477504" dbid="12" objectname="ProjectServer_Published.dbo.MSP_ASSIGNMENTS_SAVED" indexname="CL_MSP_ASSIGNMENTS_SAVED" id="lock16f27b180" mode="X" associatedObjectId="72057594307477504">

  - <owner-list>

  <owner id="process43f4c8" mode="X" />

  </owner-list>

  - <waiter-list>

  <waiter id="process80739048" mode="S" requestType="wait" />

  </waiter-list>

  </keylock>

  - <keylock hobtid="72057594325172224" dbid="12" objectname="ProjectServer_Published.dbo.MSP_TIMESHEET_LINES" indexname="IX_MSP_TIMESHEET_LINES_TS_UID_TS_LINE_UID" id="lockd936f600" mode="U" associatedObjectId="72057594325172224">

  - <owner-list>

  <owner id="process80739048" mode="S" />

  </owner-list>

  - <waiter-list>

  <waiter id="process43f4c8" mode="X" requestType="convert" />

  </waiter-list>

  </keylock>

  </resource-list>

  </deadlock>

  </deadlock-list>