Incoming email Concurrency Issue

[hardik1512]

We are on Alfresco Enterprise version 4.2.4. We have one folder email alias for incoming emails to alfresco. We have lots of emails that comes this way into system.
Now the problem is that for some random case, email processing fails and produces below error. This only happens when at a same time there are many incoming emails from mail server to alfresco. So this seems to be some concurrency issue.

com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "fk_alf_cass_cnode". The conflict occurred in database "alfresco424", table "dbo.alf_node", column 'id.

After analysis, it is been found this error occurs while creating child node.

After checking Alfresco code, they have written this operation in RetryingTransactionHelper. So why does not it retry?

How to check whether RetryingTransactionHelper is retrying or not?

Attaching full stack trace. Kindly assist.

[afaust]

You are using Alfresco Enterprise Edition, so you must be working for an organisation that has a support subscription. Did you report your issue to Alfresco Support and ask for advice?

Are you sure that there is no retrying? Your log output is very short and you did not include anything immediately before the log statements just before the exception, so we cannot be sure that there isn't any retrying before that. Also, did you (or any addon) by any chance reconfigure the property for the default retrying count? If you change the default value (via server.transaction.max-retries) then there may effectively be no retrying.

[hardik1512]

Hello Axel,

Thanks for your response.

I checked AbstractNodeDAOImpl class. They are using RetryingCallbackHelper and not RetryingTransactionHelper.

They are setting the properties like this

         childAssocRetryingHelper = new RetryingCallbackHelper();
        childAssocRetryingHelper.setRetryWaitMs(10);
        childAssocRetryingHelper.setMaxRetries(5);

The retry wait is just 10 ms. Do you think it is too small??

For transaction helper, they have minimum 100 ms.

I have enabled RetryingCallbackHelper debug logging for further details. And yes I have also raised ticket with Alfresco and waiting for their response.

Thanks.

[hardik1512]

Hello ‌

Please help with this if you got any idea.

[afaust]

Did Support have to say anything with regards to this?

10 ms retry wait is perfectly fine for a localized use case, though I am not at all a fan of the RetryingCallbackHelper which is a very brute-force way to deal with timing-based issues. It is not comparable to the RetryingTransactionHelper because it does not retry the exception - it only catches Exceptions and retries the callback within the same, currently active transaction.

[hardik1512]

Hello Axel Faust‌,

Thanks for your last comment,

Further debugging I have found out that this seems to be MSSQL DB issue.  Please find attached logs snippet.

On top it states that Duplicate child name not allowed. But we dont have any child with that name.
And the root cause mentions some isolation issue.

Can you please have a look and provide your feedback. Thanks!!

[afaust]

Well, setting the transaction level to SNAPSHOT is recommended by Alfresco, so they should have understood the implications on parts of their product. It looks like there have been various similar issues over the course of time, so I'd expect Support to be forthcoming in filing this as another issue - provided it can be reproduced.

I hope that some day they will drop support for MS SQL altogether - nothing but pain with this **** of a database (3 out of 4 customers where I had database issues were running MS SQL, the other were either running DB2 or Oracle - almost never had any serious issues with the open source databases).

[hardik1512]

Hello Axel Faust‌,

Here is another scenario for email failure.

As you can see in attached stack-trace, In first attempt we got ConcurrencyFailureException.
So RetryingTransactionHelper tried for second iteration. But during that we get Stream closed Exception.

Can you please have a look and suggesst what might have caused it?