SQL Deadlock on product import when using Find catalog events

Janaka Fernando

Vote:

Hi we are running a project with Avensia storefront and Episerver Commerce (latest version 10.x) & Find

We are using the standard automatic Find indexing of catalog content. There is a scheduled job that imports catalog data from Microsoft Dynamics AX into the commerce catalog. Due to the nature of the project it needs to use the API methods, rather than say use the service API. So in effect it is mapping data and then using the API to create new product and save it using the content repository. It is working in batches of about 100 products at a time, but overall there is a large dataset with 500,000 products.

After processing around 120,000 products it ground to a halt due to SQL server deadlock issues. This looks related to the fact that Find is using the catalog event listener to be notified of changes. This issue did not occur before using Find and the default lucine search was in place.

Anyone come across this before or have any ideas on resolution? In the interim we will be disabling product event updates using the appsetting episerver:FindCatalogEventUpdated and then having to reapply after the import is done. This is not a long-term solution.

Stack trace below

2017-08-13 19:01:51,282 [7] ERROR Catalog - System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 62) was deadlocked on lock | communication buffer 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.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)
   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.SqlConnect(Action`1 action)
   at EPiServer.Find.Cms.ContentEventIndexer.SavingContent(IContent content)
   at EPiServer.Find.Commerce.CatalogContentEventListener.IndexContentsIfNeeded(IEnumerable`1 contents, Action`1 indexAction, IDictionary`2 cachedReindexContentOnEventForType, Func`1 isReindexingContentOnUpdates)
   at EPiServer.Find.Commerce.CatalogContentEventListener.IndexContentsIfNeeded(IEnumerable`1 contentLinks, Action`1 indexAction)
   at EPiServer.Find.Commerce.CatalogContentEventListener.CatalogEventUpdated(Object sender, EventNotificationEventArgs e)
   at EPiServer.Events.EventNotificationHandler.Invoke(Object sender, EventNotificationEventArgs e)
   at EPiServer.Events.Clients.Event.Raise(Guid raiserId, Object param, EventRaiseOption raiseOption)
   at Mediachase.Commerce.Catalog.Events.CatalogEventBroadcaster.RaiseEvent(CatalogContentUpdateEventArgs e)
   at Mediachase.Commerce.Catalog.Events.CatalogEventBroadcaster.SendRelationUpdateEvent(CatalogRelationDto dto, String eventType)
   at Mediachase.Commerce.Catalog.Events.CatalogEventBroadcaster.RelationUpdated(Object source, RelationEventArgs args)
   at Mediachase.Commerce.Catalog.EventContext.RelationEventHandler.Invoke(Object sender, RelationEventArgs e)
   at Mediachase.Commerce.Catalog.EventContext.RaiseRelationUpdatedEvent(CatalogRelationDto sender, RelationEventArgs args)
   at Mediachase.Commerce.Catalog.Managers.CatalogRelationManager.RaiseUpdatedEvents(EventContext eventContext, CatalogRelationDto dataset, IList`1 entryRelationRowInfos, IList`1 nodeRelationRowInfos, IList`1 nodeEntryRelationRowInfos)
   at Mediachase.Commerce.Catalog.Managers.CatalogRelationManager.<>c__DisplayClass6_0.b__1()
   at Mediachase.Data.Provider.TransactionScope.ExecuteActions(IEnumerable`1 actions)
   --- End of inner exception stack trace ---
   at Mediachase.Data.Provider.TransactionScope.ExecuteActions(IEnumerable`1 actions)
   at Mediachase.Data.Provider.TransactionScope.ThreadStaticTransactionScope.Commit()
   at Mediachase.Data.Provider.TransactionScope.Complete()
   at EPiServer.Commerce.Catalog.Provider.Persistence.EntryContentBaseCommitter.CreateNew(EntryContentBase content, Boolean syncDraft)
   at EPiServer.Commerce.Catalog.Provider.Persistence.EntryContentBaseCommitter.Save(CatalogContentBase content, Boolean syncDraft)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentCommitterHandler.SaveVersionInternal(CatalogContentBase content, SaveAction action, String currentUser)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentCommitterHandler.Save(CatalogContentBase content, SaveAction action, String currentUser)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentProvider.Save(IContent content, SaveAction action)
   at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
   at Avensia.Storefront.Starter.Infrastructure.NoPublishProductWriter`3.SaveProduct(TProduct product)
   at Avensia.Storefront.Connector.Catalog.ProductWriter`3.<>c__DisplayClass3.b__0()
   at Avensia.Storefront.Connector.Diagnostics.ProfilingItem.Measure(Action enclosedAction, Boolean isStep)
   at Avensia.Storefront.Connector.Catalog.ProductWriter`3.d__5.MoveNext()
   at Avensia.Storefront.Connector.Extensions.EnumerableExtensions.ChunkElements[T](IEnumerator`1 source, Int32 batchSize)
   at Avensia.Storefront.Connector.Extensions.EnumerableExtensions.d__0`1.MoveNext()
   at Avensia.Storefront.Connector.PipelineImportTask`2.ExecuteSteps(ImportContext context, IRuntimeProfilingItem profilingItem)
   at Avensia.Storefront.Connector.MultiLanguagePipelineImportTask`2.<>c__DisplayClass5.b__0()
   at Avensia.Storefront.Connector.Diagnostics.ProfilingItem.Measure(Action enclosedAction, Boolean isStep)
   at Avensia.Storefront.Connector.MultiLanguagePipelineImportTask`2.Execute(ImportContext context, IRuntimeProfilingItem profilingItem)
   at Avensia.Storefront.Connector.Diagnostics.ProfilingItem.Measure(Action enclosedAction, Boolean isStep)
   at Avensia.Storefront.Connector.DefaultImporter.Execute(ImportContext context, IReadOnlyCollection`1 importTasks, IRuntimeProfilingItem profilingItem)
   at Avensia.Storefront.Connector.Diagnostics.ProfilingItem.Measure(Action enclosedAction, Boolean isStep)
   at Avensia.Storefront.Connector.StorefrontJobBase.Execute(IStorefrontEngine engine, IChannelConfiguration configuration, String group)
   at Avensia.Storefront.Connector.StorefrontJobBase.InternalExecute()
   at Avensia.Storefront.Connector.StorefrontJobBase.Execute()
---> (Inner Exception #0) System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 62) was deadlocked on lock | communication buffer 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.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)
   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.SqlConnect(Action`1 action)
   at EPiServer.Find.Cms.ContentEventIndexer.SavingContent(IContent content)
   at EPiServer.Find.Commerce.CatalogContentEventListener.IndexContentsIfNeeded(IEnumerable`1 contents, Action`1 indexAction, IDictionary`2 cachedReindexContentOnEventForType, Func`1 isReindexingContentOnUpdates)
   at EPiServer.Find.Commerce.CatalogContentEventListener.IndexContentsIfNeeded(IEnumerable`1 contentLinks, Action`1 indexAction)
   at EPiServer.Find.Commerce.CatalogContentEventListener.CatalogEventUpdated(Object sender, EventNotificationEventArgs e)
   at EPiServer.Events.EventNotificationHandler.Invoke(Object sender, EventNotificationEventArgs e)
   at EPiServer.Events.Clients.Event.Raise(Guid raiserId, Object param, EventRaiseOption raiseOption)
   at Mediachase.Commerce.Catalog.Events.CatalogEventBroadcaster.RaiseEvent(CatalogContentUpdateEventArgs e)
   at Mediachase.Commerce.Catalog.Events.CatalogEventBroadcaster.SendRelationUpdateEvent(CatalogRelationDto dto, String eventType)
   at Mediachase.Commerce.Catalog.Events.CatalogEventBroadcaster.RelationUpdated(Object source, RelationEventArgs args)
   at Mediachase.Commerce.Catalog.EventContext.RelationEventHandler.Invoke(Object sender, RelationEventArgs e)
   at Mediachase.Commerce.Catalog.EventContext.RaiseRelationUpdatedEvent(CatalogRelationDto sender, RelationEventArgs args)
   at Mediachase.Commerce.Catalog.Managers.CatalogRelationManager.RaiseUpdatedEvents(EventContext eventContext, CatalogRelationDto dataset, IList`1 entryRelationRowInfos, IList`1 nodeRelationRowInfos, IList`1 nodeEntryRelationRowInfos)
   at Mediachase.Commerce.Catalog.Managers.CatalogRelationManager.<>c__DisplayClass6_0.b__1()
   at Mediachase.Data.Provider.TransactionScope.ExecuteActions(IEnumerable`1 actions)
ClientConnectionId:2113d6f1-6a7a-409c-b651-55d29c32674b
Error Number:1205,State:45,Class:13<--->

#181492

Aug 22, 2017 13:19

Jonas Bergqvist

Vote:

Which version of Find is used?

#181499

Aug 22, 2017 16:24

Janaka Fernando

Vote:

Episerver Find 12.5.1

Episerver Find Commerce 9.7.2

Episerver Commerce 10.7.2

#181501

Aug 22, 2017 16:26

Jeroen Stemerdink

Vote:

Maybe you could try to stop and start the eventindexing when importing and maybe even the scheduledpageque? No idea if it will help with the deadlock, but it's worth a try.

Before starting your import:

EPIServer.Find.Cms.EventedIndexingSettings.Instance.EventedIndexingEnabled = false ;
EPiServer.Find.Cms.EventedIndexingSettings.Instance.ScheduledPageQueueEnabled = false;

When finished your import:

EPIServer.Find.Cms.EventedIndexingSettings.Instance.EventedIndexingEnabled = true;
EPiServer.Find.Cms.EventedIndexingSettings.Instance.ScheduledPageQueueEnabled = true;

#181502

Aug 22, 2017 17:05

Janaka Fernando

Vote:

Thanks Jeroen

It's something I was considering, though I was looking at CatalogContentEventListener but this might be better. I can index the products directly via IClient in the scheduled process which is probably more efficient also.

Still not sure if the locking is normal though or something we can fix with configuration/SQL set up?

#181503

Aug 22, 2017 17:19

Jonas Bergqvist

Vote:

One thing you also could do is to override ContentChangedEventStore, and hold the references in memory during import, instead of using the database.

#181508

Aug 23, 2017 8:20