November Happy Hour will be moved to Thursday December 5th.

AI OnAI Off

Deadlock on tblFindIndexQueue, Find 13.2.10

Fredrik Johansson
Fredrik Johansson
Vote:
 

Hello, there are certain race conditions that result in a deadlock on tblFindIndexQueue. I have posted XDL and EPi-errorlog entry below (certain attributes have been anonymized).

kind regards // Fredrik Johansson

XDL:

<deadlock-list>
 <deadlock victim="process2448a4044e8">
  <process-list>
   <process id="process2448a4044e8" taskpriority="0" logused="0" waitresource="KEY: 9:72057594408468480 (3084bb9263e0)" waittime="1127" ownerId="303675627" transactionname="UPDATE" lasttranstarted="2021-04-12T09:01:23.133" XDES="0x247179a7900" lockMode="U" schedulerid="3" kpid="8088" status="suspended" spid="89" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-12T09:01:23.123" lastbatchcompleted="2021-04-12T09:01:23.137" lastattention="1900-01-01T00:00:00.137" clientapp=".Net SqlClient Data Provider" hostname="host" hostpid="4892" loginname="user" isolationlevel="read committed (2)" xactid="303675627" currentdb="9" currentdbname="database" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="database.dbo.findIndexQueueSave" line="23" stmtstart="1136" stmtend="1270" sqlhandle="0x030009004506412b36000801e6a9000001000000000000000000000000000000000000000000000000000000">
update tblFindIndexQueue set TimeStamp = @timeStamp where Hash=@has     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 9 Object Id = 725681733]    </inputbuf>
   </process>
   <process id="process24729057c28" taskpriority="0" logused="272" waitresource="KEY: 9:72057594466598912 (db631e97330e)" waittime="1127" ownerId="303675625" transactionname="UPDATE" lasttranstarted="2021-04-12T09:01:23.133" XDES="0x24724e6ee80" lockMode="X" schedulerid="8" kpid="2848" status="suspended" spid="78" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-12T09:01:23.123" lastbatchcompleted="2021-04-12T09:01:23.137" lastattention="1900-01-01T00:00:00.137" clientapp=".Net SqlClient Data Provider" hostname="host" hostpid="4892" loginname="user" isolationlevel="read committed (2)" xactid="303675625" currentdb="9" currentdbname="database" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="database.dbo.findIndexQueueLoadItems" line="9" stmtstart="306" stmtend="984" sqlhandle="0x03000900d3bd582936aaa50080ab000001000000000000000000000000000000000000000000000000000000">
WITH cte AS 
( 
    SELECT TOP (@items) * 
    FROM tblFindIndexQueue WHERE (LastRead IS NULL OR LastRead &lt; @acceptLastReadOlderThan) ORDER BY TimeStamp
) 
UPDATE cte SET LastRead = @currentTime
OUTPUT deleted.Action ,deleted.[Cascade], 
deleted.EnableLanguageFilter, deleted.Item, deleted.[Language], deleted.TimeStamp, deleted.Has     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 9 Object Id = 693681619]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594408468480" dbid="9" objectname="database.dbo.tblFindIndexQueue" indexname="IDX_tblFindIndexQueue_Clustered" id="lock246fe7dfe80" mode="X" associatedObjectId="72057594408468480">
    <owner-list>
     <owner id="process24729057c28" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2448a4044e8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594466598912" dbid="9" objectname="database.dbo.tblFindIndexQueue" indexname="IDX_tblFindIndexQueue_Indexed" id="lock24729739700" mode="U" associatedObjectId="72057594466598912">
    <owner-list>
     <owner id="process2448a4044e8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process24729057c28" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

EPi-errorlog:

2021-04-12 09:01:24,381 [42] ERROR EPI: Unexpected error when saving 95302_323605
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 89) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Find.Cms.ContentChangedEventStore.<>c__DisplayClass11_0.<ExecuteNonQuery>b__0(SqlConnection con)
   at EPiServer.Find.Cms.ContentChangedEventStore.SqlConnect(Action`1 action)
   at EPiServer.Find.Cms.ContentChangedEventStore.ExecuteNonQuery(String spName, IEnumerable`1 parameters)
   at EPiServer.Find.Cms.ContentChangedEventStore.Save(IndexingQueueReference indexingQueueReference)
   at EPiServer.Find.Cms.ContentEventIndexer.SaveToIndexingQueueItem(IndexingAction indexingAction, IContent content, Boolean cascade)
   at EPiServer.Find.Cms.ContentEventIndexer.Index(IContent content, Boolean cascade, Func`2 cascadeFilter)
   at EPiServer.Find.Cms.ContentEventIndexer.SavingContent(IContent content)
   at EPiServer.Find.Cms.Module.IndexingModule.DataFactory_PublishedContent(Object sender, ContentEventArgs e)
   at System.EventHandler`1.Invoke(Object sender, TEventArgs e)
   at EPiServer.Core.Internal.DefaultContentEvents.RaiseContentEvent(String key, ContentEventArgs eventArgs)
   at EPiServer.Core.Internal.DefaultContentRepository.RaisePostSaveEvents(SaveContentEventArgs eventArgs, StatusTransition transition, Boolean isNew, Boolean isNewLanguageBranch)
   at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
   at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction, AccessLevel accessLevel)
   at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction)
   at EPiServer.Cms.Shell.Workspace.Committers.Internal.ContentDataCommitter.Commit(EditContentData change, CommitArguments argument)
ClientConnectionId:258cc4c2-ee4f-414c-a741-270e62592677
Error Number:1205,State:51,Class:13
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 89) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Find.Cms.ContentChangedEventStore.<>c__DisplayClass11_0.<ExecuteNonQuery>b__0(SqlConnection con)
   at EPiServer.Find.Cms.ContentChangedEventStore.SqlConnect(Action`1 action)
   at EPiServer.Find.Cms.ContentChangedEventStore.ExecuteNonQuery(String spName, IEnumerable`1 parameters)
   at EPiServer.Find.Cms.ContentChangedEventStore.Save(IndexingQueueReference indexingQueueReference)
   at EPiServer.Find.Cms.ContentEventIndexer.SaveToIndexingQueueItem(IndexingAction indexingAction, IContent content, Boolean cascade)
   at EPiServer.Find.Cms.ContentEventIndexer.Index(IContent content, Boolean cascade, Func`2 cascadeFilter)
   at EPiServer.Find.Cms.ContentEventIndexer.SavingContent(IContent content)
   at EPiServer.Find.Cms.Module.IndexingModule.DataFactory_PublishedContent(Object sender, ContentEventArgs e)
   at System.EventHandler`1.Invoke(Object sender, TEventArgs e)
   at EPiServer.Core.Internal.DefaultContentEvents.RaiseContentEvent(String key, ContentEventArgs eventArgs)
   at EPiServer.Core.Internal.DefaultContentRepository.RaisePostSaveEvents(SaveContentEventArgs eventArgs, StatusTransition transition, Boolean isNew, Boolean isNewLanguageBranch)
   at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
   at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction, AccessLevel accessLevel)
   at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction)
   at EPiServer.Cms.Shell.Workspace.Committers.Internal.ContentDataCommitter.Commit(EditContentData change, CommitArguments argument)
ClientConnectionId:258cc4c2-ee4f-414c-a741-270e62592677
Error Number:1205,State:51,Class:13

#253129
Apr 13, 2021 7:34
Quan Mai
Quan Mai
Vote:
 

Hi,

Thank you for the information. We are aware of this problem (rumor has it I might have something to do with it). It should be fixed/reduced/mitigated in EPiServer.Find 13.4.1, so please upgrade if you can

#253144
Apr 13, 2021 10:15
Fredrik Johansson - Apr 13, 2021 10:34
Thank you Quan, we will deploy the upgrade =)
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.