Hello,

We have 2 custom objects: 'Property' and 'Marketed_Property'. These objects are related with a look up field on Marketed_Property. So in this relationship, Property is the Parent object and Marketed_Property is the Child object. We have a trigger on the 'Marketed Property' object called 'updateStatusOnProperty' which updates all parent Property records when fields on the child Marketed_Property object are updated. There are also 2 triggers on Property which amend fields on the Property object when updated. The Marketed_Property object is populated by 10 @future calls carrying out a bulk update, fed by an input file of approx 2000 rows of data.

While processing the data using the @future jobs, we are get an "UNABLE_TO_LOCK_ROW" error for one of the Property record updates in one of the @future jobs. The other 9 @future jobs complete successfully. The error is reproducible but only on our live org and only sporadically, and with the lock occurring against different single records each time. We have cloned our live environment in a full size test org but cannot recreate the problem here, nor in any sandbox or DE org.

The trigger code is 1) doing a select on Property for all records where there is a child Marketed_Property object, 2) doing some comparisons on the Marketed_Property data to determine which Property rows/fields should be updated, and 3) updating the relevant Property records....and it's this step that's failing.

The code is below:

if(mpIds.size() == 0){return;}
private List<Property__c> RecordsBatch=new List<Property__c>();
List<Property__c> props = [Select id,Property_Status__c,Asking_Price__c,Estate_Agent__c,Beds__c,Weeks_On_Market__c,Date_Marketed__c,Property_Type__c,Type__c,Last_Update_Date__c,Matched__c,(Select id,Property_Status__c,Asking_Price__c,Estate_Agent__c,Beds__c,Weeks_On_Market__c,Date_Marketed__c,Property_Type__c,Type__c,Last_Updated__c from Properties__r order by LastModifiedDate desc)from Property__c where Id IN : mpIds];
for(Property__c p : props){
Property__c p1 = new Property__c(ID = p.Id);
List<Marketed_Property__c> listMP = p.Properties__r;
if(listMP.size()>0)
{
if(listMP.size()==2)
{
if(listMP[0].Asking_Price__c < listMP[1].Asking_Price__c)
{
p1.Asking_Price__c = listMP[0].Asking_Price__c;
}
else
{
p1.Asking_Price__c = listMP[1].Asking_Price__c;
}
if(listMP[0].Property_Status__c == 'For Sale' && listMP[1].Property_Status__c == 'For Sale')
{
p1.Property_Status__c = 'For Sale';
}
else if((listMP[0].Property_Status__c == 'For Sale' && listMP[1].Property_Status__c == 'Sold STC')||(listMP[0].Property_Status__c == 'Sold STC' && listMP[1].Property_Status__c == 'For Sale'))
{
p1.Property_Status__c = 'Sold STC';
}
else if((listMP[0].Property_Status__c == 'For Sale' && listMP[1].Property_Status__c == 'Sold')||(listMP[0].Property_Status__c == 'Sold' && listMP[1].Property_Status__c == 'For Sale'))
{
p1.Property_Status__c = 'Sold';
}
else if(listMP[0].Property_Status__c == 'Withdrawn' && listMP[1].Property_Status__c == 'Withdrawn')
{
p1.Property_Status__c = 'Withdrawn';
}
Marketed_Property__c MP = null;
if(listMP[0].Date_Marketed__c == listMP[1].Date_Marketed__c){
list<String> forEA = new List<String>();
forEA.add(listMP[0].Estate_Agent__c);
forEA.add(listMP[1].Estate_Agent__c);
forEA.sort();
if(forEA[0] == listMP[0].Estate_Agent__c){
MP = listMP[0];
}else{
MP = listMP[1];
}
} else if(listMP[0].Date_Marketed__c > listMP[1].Date_Marketed__c){
MP = listMP[1];
}else
{MP = listMP[0];}

p1.Estate_Agent__c = MP.Estate_Agent__c;
p1.Beds__c = MP.Beds__c;
p1.Weeks_On_Market__c = MP.Weeks_On_Market__c;
p1.Date_Marketed__c = MP.Date_Marketed__c;
p1.Property_Type__c = MP.Property_Type__c;
p1.Type__c = MP.Type__c;
p1.Last_Update_Date__c = MP.Last_Updated__c;
}
else
{
p1.Property_Status__c = listMP[0].Property_Status__c;
p1.Asking_Price__c = listMP[0].Asking_Price__c;
p1.Estate_Agent__c = listMP[0].Estate_Agent__c;
p1.Beds__c = listMP[0].Beds__c;
p1.Weeks_On_Market__c = listMP[0].Weeks_On_Market__c;
p1.Date_Marketed__c = listMP[0].Date_Marketed__c;
p1.Property_Type__c = listMP[0].Property_Type__c;
p1.Type__c = listMP[0].Type__c;
p1.Last_Update_Date__c = listMP[0].Last_Updated__c;
}
}
if(p.Matched__c == false){
//p.Matched__c = true;
p1.Matched__c = true;
}
RecordsBatch.add(p1);
if(RecordsBatch.size()== 1000)
{
update RecordsBatch;
RecordsBatch.clear();
}
}
if(RecordsBatch.size()> 0)
{
update RecordsBatch;
RecordsBatch.clear();
}

The error message is below:

18:3:38.13|CODE_UNIT_FINISHED
18:3:38.631|CODE_UNIT_STARTED|[EXTERNAL]updateStatusOnProperty on Marketed_Property trigger event AfterUpdate for a0DA0000000ukYY, a0DA0000000ukYZ, <snip> 186 IDs </snip>
18:3:38.719|DML_BEGIN|[62,2]|Op:Insert|Type:MatchingProHistory__c|Rows:187
18:3:39.338|DML_END|[62,2]|
18:3:39.339|SOQL_EXECUTE_BEGIN|[112,28]|Aggregations:1|Select id,Property_Status__c,Asking_Price__c,Estate_Agent__c,Beds__c,Weeks_On_Market__c,Date_Marketed__c,Property_Type__c,Type__c,Last_Update_Date__c,Matched__c,(Select id,Property_Status__c,Asking_Price__c,Estate_Agent__c,Beds__c,Weeks_On_Market__c,Date_Marketed__c,Property_Type__c,Type__c,Last_Updated__c from Properties__r order by LastModifiedDate desc)from Property__c where Id IN : mpIds
18:3:39.427|SOQL_EXECUTE_END|[112,28]|Rows:139|Duration:88
18:3:39.605|DML_BEGIN|[280,5]|Op:Update|Type:Property__c|Rows:139
18:3:49.7|DML_END|[280,5]|
18:3:49.8|EXCEPTION_THROWN|[280,5]|System.DmlException: Update failed. First exception on row 0 with id a0CA0000000Y27WMAS; first error: UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record: []
18:3:49.13|FATAL_ERROR|System.DmlException: Update failed. First exception on row 0 with id a0CA0000000Y27WMAS; first error: UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record: []

We've been trying to rewrite this query but it isn't improving things. Our current theory is that since the asynchronous @future calls are running in parallel across the Marketed_Property object records, and since some of these child records have the same parent then the same Property record is being updated multiple times and being locked as a result. However opinion is divided as some of the team thinks Salesforce execution controls and prevents such a situation occurring.

Anyone seen this before and can see something we're missing?

Thanks.