本文已参与「新人创作礼」活动，一起开启掘金创作之路
前言
今天在利用Salesforce Inspector导入Contact数据时，出现了难以预料的问题：
UNABLE_TO_LOCK_ROW: unable to obtain exclusive access to this record or 2 records: a050k000003FcnCAAS,a050k000003FddmAAC
出现上面问题数据的内部结构是这样的：我们使用了Config对象去管理记录的Picklist值，即在Contact上有一个Lookup(Config)的关系字段，然后导入Contact数据（3w+）时，会以Role__r:Config__c:Name的形式基于数据的Value值去找到父记录的ID填充到关系字段，可能像下面所说没有对关系字段的选项列表值进行排序，就诱发了记录锁，详细解释请看下文。
这个问题常见的解决方法是retry多几次，可是直到有一天我被一个类似的问题卡住，retry n次也没成功，这个时候我真真意识到有必要去重新认识它。
官方案例
General Guidelines for Data Loads
Case: Organize Batches to Minimize Lock Contention
For example, when an AccountTeamMember record is created or updated, the account for this record is locked during the transaction. If you load many batches of AccountTeamMember records and they all contain references to the same account, they all try to lock the same account and it's likely that you'll experience a lock timeout. Sometimes, lock timeouts can be avoided by organizing data in batches. If you organize AccountTeamMember records by AccountId so that all records referencing the same account are in a single batch, you minimize the risk of lock contention by multiple batches.
The Bulk API doesn't generate an error immediately when encountering a lock. It waits a few seconds for its release and, if it doesn't happen, the record is marked as failed. If there are problems acquiring locks for more than 100 records in a batch, the Bulk API places the remainder of the batch back in the queue for later processing. When the Bulk API processes the batch again later, records marked as failed are not retried. To process these records, you must submit them again in a separate batch.
If the Bulk API continues to encounter problems processing a batch, it's placed back in the queue and reprocessed up to 10 times before the batch is permanently marked as failed. Even if the batch failed, some records could have completed successfully. If errors persist, create a separate job to process the data in serial mode, which ensures that only one batch is processed at a time.
Solution:
Minimize Number of Fields
Minimize Number of Workflow Actions
Minimize Number of Triggers
Optimize Batch Size
Minimize Number of Batches in the Asynchronous Queue
参考文献
Understanding the common UNABLE TO LOCK ROW issue with bulk data job
Every time a record is inserted or updated, Salesforce must lock the target records that are selected for each lookup field; this practice ensures that, when the data is committed to the database, its integrity is maintained.
Learn how to avoid the "Unable to lock row" error while uploading large records in Salesforce. Below, are some sample scenarios to help you better understand the issue and how to resolve it:
Sample scenario :
----------------------
Here, the "Order Detail" object is the main object on which we want to perform a DML operation and "Order__c" is the parent of "Order Detail" object.
A. Process:
---------------
- User can make an upsert/insert call on "Order Detail" object, lets take an example of upsert.\
- Wherein external ID is "Order_Detail_Id__c"
B. What's happening at the back end?
--------------- - When we do an Upsert call on "Order Detail" object, it updates "Order__c" object as well.\
- Order__c is parent of "Order Detail."\
- "Order__c" can have Triggers/Workflows as associated with it. These triggers/workflows process as event of "after/before update/insert."\
- When we load bulk "order Detail" it updates parent object records as well resulting in lock on child and parent both. This lock get released in micro seconds though.\
Due to large number of child records, two child simultaneously update same parent which in turn generates "Row Lock,". In a layman language we can say that it is a kind of Dead lock.
Example:
| Record | ParentRecord (Order__c) | Result |
|---|---|---|
| A | X | Succeed |
| B | X | Succeed |
| C | Y | Succeed |
| D | Z | Succeed |
| E | X | Locked |
*Suppose Record is "Order Detail" and object is ParentRecord is Order__c
Here, A, B and E have same parent X, A & B locks X, C locks Y, and D locks Z. Because the process is parallel and happens within micro second, E also tried to lock X however it is already locked by A & B.
C. Why X (Parent Record) is taking time to release?
---------------
If X has a Trigger or Workflow and because we are Upserting A, it will update X (We'll discuss why it updates X later), so the Trigger also fires and lock takes some time to get released. At the same time there is another request made by E which causes an error. It'll never generate an error if it's in sorted mode as parent record. Like mentioned below:
| Record | ParentRecord(Order__c) |
| A | X |
| B | X |
| E | X |
| C | Y |
| D | Z |
D. Why A,B and E are updating X (Parent record)?
---------------
Parent might have Roll-Up Summary field for child "Order Detail," so whenever you insert or update the child Roll-up Summary will fire, which calls the parent trigger.
[** There might be other components of child as well like child object's trigger or its workflows which can play a role in calling the parent's trigger ]
E. Solutions to resolve this issue:
---------------
- You can either reduce the batch size and try again or create separate smaller files to be imported if this issue keep occurring.
- "Unable to lock row error" will occur when concurrent Users are trying to access the same record or parent record. To resolve this error User needs to change the "Concurrency Mode" of processing batches. It should be changed from "Parallel" to "Serial" mode. If you go with serial mode it will be resolved.
- [Most concrete]Sorting main records as per parent records, or you can order by your records as per parent records. In this scenario, you can take this example with SOQL query, which sorts your main records [Select id, name, so on.. fields from Order_Detail__c order by Order__c]. It processes records for a particular parent record. Actually it divides in chunks and processes main records (which has 1 parent) at a time. It will be in this format: | Record | ParentRecord (Order__c) | Result | | ------- | ------------------------ | --------- | | A | X | Succeed | | B | X | Succeed | | E | X | Succeed | | C | Y | Succeed | | D | Z | Succeed |
Refer below links for more information:
---------------
a) An Overview of unable to lock row issue.
b) Force.com Record Locking Cheatsheet
