Sql-server – Blocked Process Report -two updates causing a long block

blockinglockingsql server

I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the database and found some potential culprits: 17 seconds worth of blocking.

Below is such log ( there are many similar ):

<blocked-process-report>
 <blocked-process>
  <process id="process308898748" taskpriority="0" logused="0" waitresource="OBJECT: 99:774293818:0 " waittime="17146" ownerId="66317995993" transactionname="UPDATE" lasttranstarted="2015-06-15T12:59:05.817" XDES="0x3bc9cd970" lockMode="IX" schedulerid="7" kpid="11204" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.817" lastbatchcompleted="2015-06-15T12:59:05.817" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG10" hostpid="6832" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995993" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000075c8f5236facd1f18bca0258f9d4babad99091d6"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3111573   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 99:774293818:0 " waittime="17182" spid="278" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.780" lastbatchcompleted="2015-06-15T12:59:05.780" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG12" hostpid="9944" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995589" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000001c6452aee248fd8f9f2be0e27c7038cfa4e334f"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3807096   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

As you can see it appears that two updates are blocking each other and I'm struggling to see why.

Here are the database specifics that might help you help me:

TABLE: SP_ScheduleEvent
------
|ScheduleEventID| PK| bigint| not null|
...
|DateLastProcessed| | datetime| not null|
...

INDEX:
ScheduleEventID, "use page locks when accessing the index is set to" FALSE ( row locks is on )

Typically I have many processes running on different machines that would trigger this UPDATE, I run the update from a DBDataContext within a .NET application ( Linq to SQL) like so:

string sqlUpdateString = "UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = " + scheduleEvent.ScheduleEventID;
db.ExecuteCommand(sqlUpdateString);

Thanks

Best Answer

The Blocked process report (which you have) can be a little confusing at first. There are a few important sections

Blocking-Process This is the process(SP, query etc) that is causing the blocking. The process is using a resource that is required by the Blocked-Process

Blocked-Process This is the process(SP, query etc) that is blocked and is waiting on a a resource that is in use by the Blocking-Process.

waitresource This details the resource that the process is waiting on. Taking your example of waitresource="OBJECT: 99:774293818:0 "

The 99 refers to the Database ID of your data and the 774293818 refers to the table. Using the ID 774293818 and systables you should be able to find the table that is causing the blocking.

I would also set up an Extended Events Trace to capture the parameters of the offending blocking process and review the execution plans.

A good starting point is https://support.microsoft.com/en-us/kb/224453

Related Question