3

I've been encountering and reading a lot about data skew issues, particularly lookup skew, and I've noticed that most information I've found doesn't explicitly indicate whether this affects master-detail relationships only or also lookup ones.

According to this cheatsheet the risk of lock contention is high on master-detail relationships when a detail record is created, deleted or the relationship changes. Yet about lookup relationships it mentions the scenario of lock contention for a specific configuration scenario:

Locks only occur if lookup relationship is not configured to clear the value of this field if the lookup record is deleted.

Yet many articles doesn't make the clarification that it only affects master-detail or not, leading to think that it may not matter and therefore affect also lookup relationships:

https://developer.salesforce.com/blogs/engineering/2013/04/managing-lookup-skew-to-avoid-record-lock-exceptions.html

https://salesforcelightningblog.wordpress.com/2018/03/27/data-skew-in-salesforce-account-data-skew-ownership-skew-lookup-skew/

https://www.qualityclouds.com/how-can-data-skew-can-kill-the-performance-of-your-salesforce-org-and-2/

https://www.janbask.com/blog/four-effective-ways-to-avoid-data-skew/

https://sfdcbeginner.com/what-is-data-skew-in-salesforce.html

Therefore, for lookup relationships no matter the configuration of the lookup field, will be affected by lookup skew?

4

Skew and lock contention are generally two different issues.

About skew

In a nutshell, skew is a matter of probability distribution. A good database index is one that has a uniform-ish distribution. Roughly the same number of records should have "blue" in an index field as they do "red" (and all other colors). If you have index key(s) that show up a lot more than the average, then you have skew.

The reason why skew matters is because Salesforce uses relationship fields (Master-Detail, Lookup, External Ids (a pseudo-relationship in my eyes)) as indices.

Database indices allow fast (back-end) access to a (usually) small subset of the overall data pool. Smaller datasets are easier to process, which is important for multi-tenant services like what Salesforce provides. If your index doesn't eliminate as much of the overall data pool as the average index does, then it's not as effective.

Anything that can be used as an index for a query can experience skew. This includes M-D relationships, lookup relationships, the standard Name field on objects, the standard email field on objects, RecordTypeId, etc...

Skew mostly comes into play with query selectivity. You can have skew with both M-D and lookup relationships

About lock contention

Row locking is used as a way to ensure data integrity.

A record lock prevents any other processes from making changes to the locked record, so if we start with an Opportunity with an Amount of $10, and two Line Items are added (one $3, one $5), we end up with $18 as the amount on the Opp (instead of potentially $13 or $15).

If a process tries to access a record, and it's locked, Salesforce will wait/retry for up to 10 seconds. If a lock is required and cannot be obtained, you'll get a UNABLE_TO_LOCK_ROW exception.

When these issues combine

M-D relationships have a few big consequences.

  • The Detail record cannot exist without a Master record to be related to
  • Record ownership and sharing is determined by the master object
  • You can define rollup summary fields on the Master object
  • The Parent (and potentially grandparent) record undergoes a save procedure as part of executing a trigger on the Detail object (if there is a rollup summary field or if the child record is part of a cross-object workflow)

Executing a trigger on a record means that that particular record is locked for the duration of that trigger.

The more child records you have related to a parent record, the higher the chance that you'll be trying to simultaneously operate on a locked, parent record.

I'm not sure if we should take the "lookup record is locked unless the lookup field is not configured to clear the field if the lookup record is deleted" literally, but the same principle applies.

Takeaway

The more records you have related (M-D or lookup) to a given record, the more likely you are to run into an issue with lock contention.

  • Thank you for the detailed explanation Derek – jonathanwiesel Jan 18 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.