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?
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.
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.
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).