Facing issue with our Tridion eventing module on component save and close with multiple user

Question

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

1. ‘A database error occurred while executing Stored Procedure “EDA_ORG_ITEMS_FINDUNIQUENESSCONFLICTS.”

2. 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.

3. 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;
    }

Answer 1

Where-used queries are notoriously sensitive to out-of-date optimiser statistics. You need to ensure that sp_updatestats (or the ORACLE equivalent) is run regularly on your database.

In fact, whenever you get a database timeout on a Tridion system, this is the first thing you should check. Do not listen to database administrators who insist it should not be necessary. They are wrong. It is.

Answer 2

My suggestion would be use of locking in the event code and do not allow more than few threads to work simultaneously.

You can use a semaphore to restrict this to a few threads of work at a given time and all other events will wait for those ones to finish first.

Answer 3

I would look into the logic for how you set the name for the new page and where you store that page. It looks like you have very many pages in the same structure group? (and maybe inheritance down to a lot of publications?)

If that is the case try to put the pages in different structure groups, separate on year/subject depending on type of information. My experience is that will speed up the file name check in the blueprint.