[SalesForce] Does Lookup Skew affect only master-detail relationships or also lookup ones

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?

Best Answer

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.

Related Solutions

[SalesForce] Adding Custom Objects + Master-Detail Relationships then Rolling Up Objects That Already Contain Data

Actually, the easy fix is just do what the message says, populate the lookup for the existing purchase records, once all the purchases have a parent you will be able to change the field type.

If you want to add another child object in the future you just need to make sure that from the beginning you populate the lookup field that references the parent record.

[SalesForce] Why can’t I create a master-detail relationship between Product and the custom object

I can't offer you any insight into why you cannot do this, you just can't unfortunately, it's a seemingly arbitrary limitation of the platform and the Product2 object. Unless someone who knows about the internals of the platform comes across this I doubt you'll ever get a definitive answer.

In terms of working around it though, the only way to achieve this is to create your lookup as a standard non-required Lookup and then enforce that the value of this lookup cannot be blank using a validation rule.

Your validation rule will look something like this:

ISBLANK(ProductId__c)

If the Product is deleted then this will result in the value of the field being cleared, as per the default 'Clear the value of this field.' behaviour, which will prevent you updating the child record until a valid lookup ID is entered.

If you want to change this behaviour you'll have to write a before delete trigger on Product2 with your desired logic.