Troubleshooting Deadlock, Victim holding on to shared lock
-
I am trying to troubleshoot the below deadlock situation
Looking at the deadlock xml, both session are using read committed isolation level.
Question is, in read committed isolation level, in this situation, why is the victim holding on to the shared lock? My understanding and I just tested it, is that while reading tables, it would acquire shared lock on page/row level and then release it immediately. In what situation in read committed isolation level, would a session want to hold on to shared lock on page that it had acquired?All the locks are on a nonclustered index on a table.
<deadlock>
<victim-list>
<victimProcess id="process11634c19088" />
</victim-list>
<process-list>
<process id="process11634c19088" taskpriority="0" logused="0" waitresource="PAGE: 11:13:52372905 " ownerId="57900000774" transactionname="SELECT" lasttranstarted="2018-10-12T09:48:05.360" XDES="0x3e7feedc00" lockMode="S" schedulerid="12" kpid="8544" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-10-12T09:48:05.360" lastbatchcompleted="2018-10-12T09:48:05.377" lastattention="1900-01-01T00:00:00.377" clientapp=".Net SqlClient Data Provider" hostname="hostname1" hostpid="6912" loginname="ENETTAD\SVC_APP" isolationlevel="read committed (2)" xactid="57900000774" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="database1">
<executionStack>
<frame procname="database1.dbo.uspBatch_Search" line="88" stmtstart="5068" stmtend="7128" sqlhandle="0x03000b00fad68c201937da0036a9000001000000000000000000000000000000000000000000000000000000">
SELECT @TotalRowCount = COUNT(*)
FROM dbo.Batch
JOIN #child ce
ON ce.ECN = requestednce
WHERE BatchId = ISNULL(@BatchId, BatchId)
AND RequestedUsername = ISNULL(@RequestedUsername, RequestedUsername)
AND requestednce = ISNULL(@requestednce, requestednce)
AND BatchStatusId = ISNULL(@StatusId, BatchStatusId)
AND BatchStatusId != @NewStatusId and BatchStatusId != @CancelledStatusId
AND ( (@IsReportRequired = @true AND ReportBlobId IS NOT NULL)
OR (@IsReportRequired = @false))
AND ( (
(@DateType = @CreatedDate AND CreatedDate BETWEEN cast(@FromDate AS varchar(20)) AND cast(@ToDate AS varchar(20)) )
OR
(@IncludeCurrent = @true AND ( BatchStatusId IN (@QueuedStatus,@InProgressStatus,@ReportingStatus)
OR
CompletedDate BETWEEN cast(@FromDate AS varchar(20)) AND cast(@ToDate AS varchar(20))) )
)
OR @DateType = @IgnoreDateRange </frame>
<frame procname="adhoc" line="1" stmtstart="386" sqlhandle="0x01000b005101d12e207be87f2100000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[uspBatch_Search] @ECN = @p0, @BatchId = @p1, @requestednce = @p2, @RequestedUsername = @p3, @DateType = @p4, @FromDate = @p5, @ToDate = @p6, @StatusId = @p7, @IsReportRequired = @p8, @IncludeCurrent = @p9, @StartRowIndex = @p10, @SortExpression = @p11, @MaximumRows = @p12, @TotalRowCount = @p13 OUTPUT </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 int,@p1 int,@p2 int,@p3 varchar(8000),@p4 varchar(8000),@p5 datetime,@p6 datetime,@p7 int,@p8 tinyint,@p9 bit,@p10 int,@p11 varchar(8000),@p12 int,@p13 int output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[uspBatch_Search] @ECN = @p0, @BatchId = @p1, @requestednce = @p2, @RequestedUsername = @p3, @DateType = @p4, @FromDate = @p5, @ToDate = @p6, @StatusId = @p7, @IsReportRequired = @p8, @IncludeCurrent = @p9, @StartRowIndex = @p10, @SortExpression = @p11, @MaximumRows = @p12, @TotalRowCount = @p13 OUTPUT </inputbuf>
</process>
<process id="process2e950984e8" taskpriority="0" logused="10880" waitresource="PAGE: 11:13:14044552 " waittime="241" ownerId="57900000848" transactionname="user_transaction" lasttranstarted="2018-10-12T09:48:05.367" XDES="0xf7c3c0c3b0" lockMode="IX" schedulerid="2" kpid="5476" status="suspended" spid="234" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-10-12T09:48:05.370" lastbatchcompleted="2018-10-12T09:48:05.367" lastattention="1900-01-01T00:00:00.367" clientapp=".Net SqlClient Data Provider" hostname="hostname1" hostpid="6912" loginname="ENETTAD\SVC_APP" isolationlevel="read committed (2)" xactid="57900000848" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="database1">
<executionStack>
<frame procname="database1.dbo.uspBatch_Update" line="32" stmtstart="1934" stmtend="2586" sqlhandle="0x03000b00b243467b8df206000ba7000001000000000000000000000000000000000000000000000000000000">
UPDATE Batch
SET BatchStatusId = @BatchStatusId,
ReportBlobId = @ReportBlobId,
CompletedDate = @CompletedDate,
ImportReportVersion = @ImportReportVersion,
ExportReportVersion = @ExportReportVersion,
ErrorCode = @errorcode
WHERE BatchId = @BatchI </frame>
<frame procname="adhoc" line="1" stmtstart="236" sqlhandle="0x01000b009c24d728c05a7d06ab00000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[uspBatch_Update] @RequestId = @p0, @BatchId = @p1, @BatchStatusId = @p2, @ReportBlobId = @p3, @CompletedDate = @p4, @errorcode = @p5, @ImportReportVersion = @p6, @ExportReportVersion = @p7 </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 uniqueidentifier,@p1 int,@p2 int,@p3 varchar(8000),@p4 datetime,@p5 int,@p6 int,@p7 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[uspBatch_Update] @RequestId = @p0, @BatchId = @p1, @BatchStatusId = @p2, @ReportBlobId = @p3, @CompletedDate = @p4, @errorcode = @p5, @ImportReportVersion = @p6, @ExportReportVersion = @p7 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="13" pageid="52372905" dbid="11" subresource="FULL" objectname="database1.dbo.Batch" id="lock9f21680580" mode="IX" associatedObjectId="72060857777979392" databaseName="database1">
<owner-list>
<owner id="process2e950984e8" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process11634c19088" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="13" pageid="14044552" dbid="11" subresource="FULL" objectname="database1.dbo.Batch" id="lock1506883db00" mode="S" associatedObjectId="72060857777979392" databaseName="database1">
<owner-list>
<owner id="process11634c19088" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process2e950984e8" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock> -
Have you looked as the stored procedures to see if there's a BEGIN TRAN anywhere?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Probably because it's in the process of reading the table and needs locks on both pages as it's locating the data. The ad-hoc SELECT, which is the one that has and wants the shared locks has no transaction (the other process does), but within the single statement it may well need more than one page.
Read committed promises that the shared locks will be released no later than the end of the statement. In this case, the statement hasn't finished.You'll probably solve this deadlock if you fix the catch-all query you have there.
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply