0

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>
Improve this question
2
  • Is there something else done in the same transaction earlier that locks CustomerOrderEquipment? Could there be triggers that do that?
    – James Z
    Commented May 14, 2015 at 14:07
  • Your blocking process application is EzyserveImport.exe. The block seems to be a single row insert, not some bulk load process. If there are many rows being inserted, then I would wonder whether the process has a BEGIN TRAN that wraps all the inserts into a single transaction, thus holding a long block. Your blocked process shows 1 transaction and the import shows 2 transactions.
    – RLF
    Commented May 14, 2015 at 15:13

1 Answer 1

Reset to default
1

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:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.