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