SQL Locking on SNCOMM DB with MS SQL Server

Recently I was asked by a customer to investigate the poor performance of their Connections 4.5 CR3 environment. They had noticed that the servers were slowing, and eventually Connections ground to a halt.

Checking the System.out logs on the InfraCluster servers revealed that issues were being experienced with process hangs and process queuing.We then traced this back to the Databases, as it appeared like anything that relied on Communities were slowly failing. We found our problem.

The SNCOMM database had a lock from a long running task. Analysing the lock showed that this was the select statement it was running:

@P0 nvarchar(4000))   SELECT       TC.COMMUNITY_UUID as COMMUNITY_UUID,   TC.NAME as NAME,   TC.CREATED as CREATED,   TC.CREATED_BY as CREATED_BY,   TC.LASTMOD as LASTMOD,   TC.LASTMOD_BY as LASTMOD_BY,   TC.LOWER_NAME as LOWER_NAME,   TC.PLAIN_DESCR as PLAIN_DESCR,        '' as IMAGE,        '' as DESCRIPTION,   '' as DESCRIPTIONEX,       TC.MEMBER_COUNT as MEMBER_COUNT,      TC.PEOPLE_COUNT as PEOPLE_COUNT,       TC.GROUP_COUNT as GROUP_COUNT,   TC.COMMUNITY_THEME as COMMUNITY_THEME,       '' as CREATEDBY_DIRECTORY_UUID,   '' as CREATEDBY_DISPLAY,   '' as CREATEDBY_EMAIL,    '' as LASTMODBY_DIRECTORY_UUID,   '' as LASTMODBY_DISPLAY,   '' as LASTMODBY_EMAIL,     TC.COMMUNITY_TYPE as COMMUNITY_TYPE,    TC.HANDLE as HANDLE,     TC.PARENT_UUID as PARENT_UUID,   TC.ORG_ID AS ORG_ID,   TC.INTERNAL_ONLY AS INTERNAL_ONLY,   TC.OWNER AS OWNER,   TC.MEMBER_UPDATED as MEMBER_UPDATED,   TC.REF_UPDATED as REF_UPDATED,   TC.IMAGE_UPDATED as IMAGE_UPDATED,       TC.PRE_MODERATION as PRE_MODERATION,       TC.POST_MODERATION as POST_MODERATION   FROM   SNCOMM.COMMUNITY TC       WHERE      TC.COMMUNITY_UUID=@P0

Removing the lock resolved the issue, everything in the environment “trued” and all was well with the world again.

The customer then did some investigation into what changes were made in the environment. Turns out through some pretty awesome sleuth work that a new user had been brought on that day. This user was added to a Community that had >10 sub-communities. This was the clue..

In Connections 4.5 (unsure which version) a new feature to update the membership of sub-communities when a user was added to the parent community was introduced. Running this code for under 10 sub-communities, no problem. More than 10; LOCK.

PMR was logged and the resulting investigation showed that this was a reproducible error. Bad news is that it also exists in version 5, but this will be resolved in an upcoming fixpack.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s