The database concurrency violation errors may be thrown on Sitecore web sites that have Sitecore DMS enabled.
The following entries in the Sitecore log files are the common symptoms of the concurrency violation errors:
ERROR Error committing data row Visits.[GUID] : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. ERROR Error committing data row Pages.[GUID] : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. ERROR Error committing data row [TableName].[GUID]: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
There may be multiple scenarios leading to the concurrency violation errors in Sitecore DMS.
The current article explains known scenarios of when such errors may occur, their causes and recommended solutions.
Configuration
Description
DMS maintains the data for active visits in the Session storage. When the InProc session state is enabled, each Sitecore solution will maintain its own Session data, and, therefore, its own DMS visit data. As a result, some Analytics data may be submitted to the Analytics database in a conflicting way, and a concurrency violation errors may appear.
Solution 1
On all Content Delivery instances, change session state mode to SQLServer, StateServer, or any other shared out-of-process session state.
Solution 2
Ensure that the sticky session strategy is used on the load balancer for all Content Delivery instances.
Please note that some load balancers do not guarantee 100% stickiness of the user session, so in this configuration the errors may still appear from time to time.
DMS does not flush visitor data changes directly to the persistent data storage at the end of each request.
Instead, visitor data is saved in the interim storage and is committed asynchronously. There are 2 storage types that maintain analytics data:
In case of data de-synchronization between the DataQueue storages on different servers, inconsistent save command may be generated and a concurrency violation may be raised.
Check the Sitecore log files. If the concurrency violation error for a specific GUID appears only once, it could be safely ignored.
That means that Sitecore was able to resolve the conflict automatically by processing DataQueue from other server(s).
For the concurrency errors with the repeated GUID, please check the Scenario 4 section.
Configuration
WARN Analystics: Max size of insert queue reached
Description
DMS is designed to drop certain analytics data in case of an unusually high traffic load. This feature prevents the system to fail during peak load times. When the analytics data is dropped, some analytics data updates may become inconsistent, causing concurrency violation errors and not being fully written to the DMS database.
Solution
Horizontal scaling of the system is recommended - the number or performance of the Content Delivery servers is to be increased. More information about scaling Sitecore sites can be found here:
http://sdn.sitecore.net/Reference/Sitecore%207/Scaling%20Guide.aspx
Symptom
Sitecore log files contain a lot of concurrency violation errors with identical GUIDs.
For example:
ManagedPoolThread #2 09:10:04 ERROR Error committing data row Visitors.71363913-344d-424b-adf7-8a1fa27f6ec6 : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. ... ManagedPoolThread #11 09:20:05 ERROR Error committing data row Visitors.71363913-344d-424b-adf7-8a1fa27f6ec6 : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
This solution does not eliminate the source of the concurrency issue - it performs the recovery operation instead. In case a concurrency violation arise, the patch attempts to run the INSERT operation instead of the UPDATE operation.
To apply this solution, please follow these steps:
2. Copy the attached Sitecore.Support.406447.dll assembly into the /bin folder of your web site.