Understanding architecture designs and data distributions that can contribute to reduced database concurrency is an important consideration for all application architects and developers, no matter what platform you use. This post uncovers a common situation that you should watch out for when managing Salesforce solutions because it has the potential to produce unwanted lock contention and reduced database concurrency: account data skew.

Salesforce and Database Concurrency

The Salesforce Platform frequently processes more than one billion transactions per day with an average response time of less than 300 milliseconds. Pretty impressive! Transactions that add, modify, or delete data rely on the underlying database system to lock records and serialize database changes. In general, the database holds record locks for a very short amount of time, which provides for excellent concurrency among multiple users accessing the shared database.

Salesforce Account Data Skew Defined

There are Salesforce-specific situations to be aware of that can lead to lock contention and reduced database concurrency: one such situation is referred to as “account data skew.” Account data skew occurs when an Account’s parent object has more than 10,000 child objects. How might account data skew happen?

How Salesforce Account Data Skews Can Happen

To illustrate how account data skew can happen, consider the following scenario. Assume that a company’s salespeople are adding new contacts into an account. When they click Save, the database automatically locks the parent account when it begins the DML operation and before it actually inserts the Contact. The database releases the lock after executing the triggers and standard save operations. This scenario illustrates the locking that can occur in parent-child relationships.

The next thing to understand is a side-effect known as parent implicit sharing. In a private sharing model, something else occurs when Force.com creates the contact. The built-in implicit sharing feature provides record accessibility, and its parent implicit sharing provides read access to an account for users who have access to standard child objects, such as Contacts, Cases, and Opportunities.

So when salespeople create a Contact, sharing calculations determine during the save operation if a parent implicit share to the Account should be created. In this example, the calculations happen quickly. Assume that the salespeople have been very active and have created 300,000 child objects under a single generic account. They now have a skewed account.

The Impact of Account Data Skew

To understand the impact of account data skew, consider the following scenario. User Jane has access to contact Bob Smith and has a parent share to the single generic account. Her manager changes ownership of the contact Bob Smith to another salesperson and clicks Save.

The sharing calculations now run for a longer period of time because they have to determine whether to delete the parent implicit share. The calculations check if Jane has access to the remaining 299,999 contacts under the single generic account.

If another salesperson tries to add a new contact for the same account while the sharing calculations are occurring, that request will wait for Force.com to release the lock on the account, resulting in lock contention and reduced database concurrency. Because this is a synchronous request, this request starts counting against the concurrent Apex request limit if the wait exceeds 5 seconds. If the wait exceeds 10 seconds, the salesperson will get an “UNABLE_TO_LOCK_ROW” error.

How to Avoid Account Data Skew

You should always strive to identify and avoiding account data skew situations. Correcting a data skew can be painful because you’re changing ownership, which in turn triggers sharing calculations.

• Design architecture to limit account objects to 10,000 children. Some possible methods include creating a pool of Accounts and assigning children in a round robin fashion or using Custom Settings for the current Account and the number of children.
• If possible, consider a Public Read/Write sharing model in which the parent account stays locked, but sharing calculations don’t occur.
• If you have a skewed account, redistribute child objects in chunks during off-peak hours to lessen the impact of record-level lock contention.  Batch Apex or the Bulk API are useful ways to re-parent.

Related Resources

• A Guide to Sharing Architecture
• Designing Record Access for Enterprise Scale
• Record-Level Access: Under the Hood
• Other architecture-related papers at the Architect Core Resources page

About the Author and CCE Technical Enablement

John Tan is a member of Technical Enablement within the Salesforce Customer Centric Engineering group. The team’s mission is to help customers understand how to implement technically sound Salesforce solutions. Check out all of the resources that this team maintains on the Architect Core Resources.