Sql-server – SQL Blocking causing timeouts

ado.netblockingsql server

We have a long running transaction (with nested trans), it runs for about 2min. In this time it does a heap of insert, update and selects. At the same time users still need to be able to use the system. Some of the tables used are the same as the ones in the batch program.

In the below profiler trace I am getting a block from an unrelated table. The interesting thing is, the query that is blocking my transaction, the table is not used by the second task.

Here is the profiler output, the blocked process (CustomerOrderEntry) is the quick one while the blocking process is the longer running one.

We are using MSDTC with MS SQL Server and ADO.Net

<blocked-process-report monitorLoop="546369">
 <blocked-process>
  <process id="process9fbc51c28" taskpriority="0" logused="10464" waitresource="PAGE: 40:1:182902 " waittime="20839" ownerId="232311233" transactionname="user_transaction" lasttranstarted="2015-05-14T11:52:46.997" XDES="0x12838563b0" lockMode="S" schedulerid="1" kpid="7508" status="suspended" spid="118" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-05-14T11:52:47.023" lastbatchcompleted="2015-05-14T11:52:47.023" lastattention="1900-01-01T00:00:00.023" clientapp="CustomerOrderEntry.exe" hostname="QIT-TS" hostpid="116616" loginname="QIT\tgower.admin" isolationlevel="read committed (2)" xactid="232311233" currentdb="40" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="52" stmtend="512" sqlhandle="0x02000000946ebc1dc9d4bf7eefd3826e6a744d9b92931d1a0000000000000000000000000000000000000000"/>
    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
(@OrderNumber varchar(20))SELECT        Id, OrderNumber, Line, Equipment, Site, Quantity, EngineHours, GPSLongitude, GPSLatitude, OffRoad, Route, Eta, Etd
FROM            CustomerOrderEquipment
WHERE        (OrderNumber = @OrderNumber)
ORDER BY Equipment   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="40:1:2670699" waittime="49" spid="95" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-05-14T11:53:07.817" lastbatchcompleted="2015-05-14T11:53:07.817" lastattention="1900-01-01T00:00:00.817" clientapp="EzyserveImport.exe" hostname="QIT-TS" hostpid="25480" loginname="QIT\tgower.admin" isolationlevel="read committed (2)" xactid="232308068" currentdb="40" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="1050" stmtend="2498" sqlhandle="0x0200000050f50e11ba633c312db084db39d3e5c7ff5b8a2d0000000000000000000000000000000000000000"/>
    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
(@Id uniqueidentifier,@Account varchar(12),@Date datetime,@Details varchar(39),@Amount decimal(18,2),@Quantity decimal(18,4),@Source int,@PostingBatch uniqueidentifier,@BASPosition varchar(3),@BASPosted bit,@BASPostedPeriodDate date,@Site int,@Financed bit,@FinancedDate date,@FinancedBatch varchar(8000),@BankRecReference varchar(8000),@BankRecType int,@BankRecPayMedium varchar(8000),@ExcludeFromFinancing bit,@Notes varchar(8000),@Reconciled bit,@ReconciledDate datetime,@FuelSubsidyClaimed bit,@FuelSubsidyClaimDate date)INSERT INTO [LedgerTransactions] ([Id], [Account], [Date], [Details], [Amount], [Quantity], [Source], [PostingBatch], [BASPosition], [BASPosted], [BASPostedPeriodDate], [Site], [Financed], [FinancedDate], [FinancedBatch], [BankRecReference], [BankRecType], [BankRecPayMedium], [ExcludeFromFinancing], [Notes], [Reconciled], [ReconciledDate], [FuelSubsidyClaimed], [FuelSubsidyClaimDate]) VALUES (@Id, @Account, @Date, @Details, @Amount, @Quantity, @Source, @PostingBatch, @BASPosition, @BASPosted,    </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

Best Answer

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated.

I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all of the inserts and updates are done that would cause the blocking. I would also look at where the data is coming from to perform the inserts and updates. If the data is coming from a select that is nested in the transaction that might explain the locking on a table that is not involved in an insert or update action.

This increases the usage of the tempdb so make sure it can handle the extra load:

Related Question