+ Start a Discussion
Ken_KoellnerKen_Koellner 

On Insert why would you get UNABLE_TO_LOCK_ROW?

 

A client of one of our web service written in Apex reported an error.  It turns out there was an error --

 

Insert failed. First exception on row 9; first error: UNABLE_TO_LOCK_ROW, unable to obtain exclusie access to this record: []

 

I'm trying to think of what would cause an lock problem on an insert.  The method does to a start transaction and then inserts a row in Opportunity and then several in OpportunityLineItem. 

 

I don't see how the problem could be with the row being inserted.  Could it be with something locked a parent record like account?  Could something have already read the Opportunity record while the OpportunityLineItem records where being written?

 

imuino2imuino2

Check this doc its very useful here you can see all the soql exceptions

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_concepts_core_data_objects.htm

 

 

 

Ignacio.

Ken_KoellnerKen_Koellner

I noticed that that document states that the UNABLE_TO_LOCK_ROW could be thrown on a timeout.

 

That means it might not actually be transactions interfering with each other, huh?

 

I would think that deadlock would be very hard to hit on inserts so that may be it.

 

 

Roger WickiRoger Wicki
Hi folks

I have the same issue and can't find out why. The error appears on line 14 here:
static testMethod void testDeletion()
{
    	map<String, Id> profileMap = ArcUtil.getProfiles();
        
        // Signature: UserCreator.getUsers( Integer numUsers, String prefix )
        User clientConsultant = UserCreator.getUsers(1, 'clientC').get(0);
        System.assert(UserCreator.setProfile('System Administrator'));
        User adminer = UserCreator.getUsers(1, 'adminer').get(0);
        System.assert(UserCreator.setProfile('Personal'));
        User personel = UserCreator.getUsers(1, 'person').get(0);
		
	list<User> users = new list<User>{ personel, clientConsultant, adminer };
	System.runAs(new User(Id = UserInfo.getUserId()))
	{
		insert users;
		insert new PermissionSetAssignment(AssigneeId = adminer.Id,
				PermissionSetId = [ SELECT Id FROM PermissionSet WHERE Name = 'Attachment_Deletion_Prevention_Bypass' LIMIT 1 ].Id);
	}
// Further irrelevant steps
}
I use constructor-like classes for objects I use most including the user object. It does noting else than to create sample user objects with all required fields filled with sample data and returns them in a list. When I run this test class all by itself it goes fine, but as soon as I run all tests it fails on the "insert users" with UNABLE_TO_LOCK_ROW.

Any ideas?
Roger WickiRoger Wicki
*line 15
Andrew B. DavisAndrew B. Davis
Set your Apex and Database debug log levels to FInest, re-create the problem and then review the logs. I have seen the 
UNABLE_TO_LOCK_ROW exception occur when there was an exception thrown in a Before Insert trigger. There was not a deadlock condition, just some other code that encountered an exception. The debug log showed the actual issue.
Ken KoellnerKen Koellner
Gosh, this is a pretty ancient question.  But I learned years ago that some parent records like Account will be locked on the insert to Opporunity.

To the best of my knowlege SF uses a poor-man's techinque of lock detection via time out so it doesn't have the ability to tell you what the locked record that is blocking the transaction is.
Mani SampathkumarMani Sampathkumar
Hi Ken,

I am facing the same issue, I have scheduled a batch apex to create opportunities and system throws an error UNABLE_TO_LOCK_ROW with the account id(meaning account is locked and my opty creation code is trying to access it). May I know how did you resolve this issue? Please let me know. Thanks! 
Ken KoellnerKen Koellner
There was a very good talk on dealing with locks in Apex at DF about two years ago.  I believe the recording is online;  you should look for it.

There's one technique we do use.  For certain updates we put the DMLs in a loop and try three times.  Virtually all the time we get the DML to stick within three tries.  At the DF talk, I asked the presenter if this technique is a good idea or not.  The comment was that effectively what I'm doing is extended the timeout.  As you know from the comments earlier this thread, Apex uses poor man's deadlock detection via timeout.  So if you just wait a bit longer, you may get your DML to stick.

A technique I have not tried would be to try to grab a write lock on whatever is causing the deadlock.  The thing is, you may not be easily determine that.  If you are inserting a child record and the child object as one parent that you are fairly sure is creating the issue, you could try reading FOR UPDATE the parent record prior to the DML  That might help.  The thing would be, you might not know whether you are grabbing the parent that is indeed causing the problem.  So maybe doing a read FOR UPDATE on an Account might help avoid deadlock when inserting an Opportunity under that Account.
Mani SampathkumarMani Sampathkumar
Thanks Ken for your response. I will try FOR UPDATE by using SELECT SOQL for the account before I create an opportunity.

I will look for the DF video and will watch it. I got your point regarding if you cannot identify the parent which causes this issue. In my case, debug log showed account id after the error hence I think putting a read FOR UPDATE on the account might work.

Thanks!