I am facing some issue with our Tridion eventing module. Please let me explain the issue.
Background:
We have approx. 70-80 users who create content via CMS where it’s expected that more than 70% of users create content on a high traffic day. We have different content types including “Article” creation. In “Article” creation we have implemented the event module so content is automatically created/published on save and close of an “Article” component.
Issue:
On high traffic day when approx. 70% of users try to create “Articles” via the CMS we receive a lot of “Database” deadlocks.
We receive below 2 main dead locks
‘A database error occurred while executing Stored Procedure “EDA_ORG_ITEMS_FINDUNIQUENESSCONFLICTS.”
A database error occurred while executing Stored Procedure "EDA_PUBLICATIONS_READ". Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Unable to save component ….
1 more observation is we have noticed a lot of sleeping connections were found in the “Tridion_CM” database.
This issue get resolved once we do a database switch (failover) from our primary DB to the secondary DB, but that is not the ideal solution in a production environment.
I have also debugged my event code using “SP_WHO” and “SQL Profiler” but haven’t found any reason to optimize my code.
Can you please suggest what action we may take to solve this issue?
Here are the 2 main functions I use in my event code
1) Page creation
private void UpdatePageName(Page subjectPage, Component component)
{
if (subjectPage.IsCheckedOut)
{
subjectPage.UndoCheckOut(true);
}
subjectPage.CheckOut(true);
subjectPage.FileName = PageFileName;
subjectPage.Title = PageTitle;
subjectPage.Save(true);
subjectPage.CheckIn(true);
}
2) where use code to decide if add/edit the component
private List<IdentifiableObject> GetPageListUsingComponent(Component component,
string targetPubId)
{
List<IdentifiableObject> usingPageList = new List<IdentifiableObject> { };
Filter filter = new Filter();
UsingItemsFilter usingPagesFilter = null;
IdentifiableObject repository = latestEditorSession.GetObject(
string.Format("tcm:0-{0}-1", targetPubId));
filter.Conditions[CommonConstants.ItemType] = ItemType.Page;
filter.Conditions[CommonConstants.OnlyLatestVersions] = true;
usingPagesFilter = new UsingItemsFilter(filter, latestEditorSession);
if (repository != null)
usingPagesFilter.InRepository = (Repository)repository;
usingPageList = (List<IdentifiableObject>)component.GetUsingItems(usingPagesFilter);
return usingPageList;
}