+ Start a Discussion
HGSHGS 

Record Locking not working

We have a program that requires locking on records. Here is the scenario:

 

Product__c : that contains products 

issue__c : issue transactions

availability__c : availability, sort of stock, used to track how many records in inventory. 

 

Now, when issuing, we want to make sure that  two people, working on different issue transactions, dont use the same products at the same time. And, here is what we are doing in the "Save" button:

 

 

1. Product__c tempLockList = [select id from product where name = 'xx' for update];

2. /*

3.  All update logic, which inserts records in issue__c and updates other objects, but not product object.

4. */

 

 

Now, the same code is called by two people, at the same time.

 

Ideal behavior would have been, that the  system would have come on line 2 in one call, and waited for first transaction to finish in the second call, to come to line 2. 

 

But, somehow, nothing is waiting, and seems like there is no locking at all. both code are executing and causing inconsistent data.

 

To  summarize:

 

1. I am not updating same sobjects. 

2. I need a lock such that when one transaction is going on, the other one should wait.. 

 

Any ideas would be appreciated. 

 

 

 

 

 

 

Ron HessRon Hess

you are locking the wrong object.

 

try this sequence :

insert the issue without the product,

then query that exact issue, using for update,

then verify that no other issue uses that product,

then update the issue.

 

 

should work.

HGSHGS

Thanks for answering.

 

The answer seems good, but there are probably a few things that I probably missed to mention. 

 

- The same product can be used by multiple issues, this is perfectly valid.

- What I am worried about, is availability.

- The availability is taken out by taking out products__c.available_qty__c..., then taking out all records of the products from the availability__c (pardon me for the wrong name, the name should be products_used__c).., then deduction the availability__c.(sum of quantities) from the product__c.available_qty__c. 

 

The problem is, that if two transactions do the same query at the same time.., both get available qty same, and try to do the transaction, thus chances of using double quantity. 

 

So, what I need is an effective mechanism to have one transaction wait, till the other is over.

 

 

 

 

Ron HessRon Hess

in your first description you specify that you are not updating products, so i was confused. 

Now it looks like you are updating products__c.available_qty__c

 

So, you will query products__c and be sure to grab the field products__c.available_qty__c in your query AND add for update to your SOQL

 

 then decrement  available_qty__c (allocate the resource) and then

 

   --> perform whatever operations you need to ( critical section)

 

then update products__c.

 

any other code that also uses for update and grabs this field should wait and would therefore never get the same value (same instance) of  available_qty__c.

 

If you feel that locking is not working at all, please boil this down in a simple exampe that uses account or contact, not custom objects, and post your snippet of code (decrement number of employees or something...)  so that we can easily reproduce the issue and we can open a case for it.

 

i don't have any locking examples handy.

 

anyone else have a locking example ?

 

 

 

 

 

HGSHGS

Thanks for your response.

 

Here is a scenario taking standard objects

 

Opportunity, Contacts 

 

Business Scenario:

 

  • For every contact, we have a limit to how many opportunities can a contact be added to in one region. This means, if the value is 4, then this contact can be added to every region, but a maximum of 4 opportunities in one region. This limit is stored in a custom field on Contact Object : max_opp__c.
  • We have a custom VF page, that is called from Opportunity Detail page, and displays a list of available contacts in that area (available is calculated by checking the usage of contacts in opportunities in that region, and listing only those contacts which are available)
  • User selects contacts (using checkboxes), and pressing save:
    1. take out all opportunities that contain the selected contacts in this region, and re-verify that the contacts are available before proceeding.
    2. add the contacts to opportunities.
    3. add records to some of our custom objects, for the purpose of reporting, summarizing data, etc. This record addition is a heavy process and consumes around 2 seconds, as there are a lot of records to be searched and added.
    4. Give the user a success message.

 

 

And now, here is the description of the problem:

 

  • In Step 1 (after user presses save button), we recheck the availability
  • If two users the save on two different opportunities in the same region, after selecting the same records, step 1 says "Available"
  • And, by the time both are saved, there are chances that we have over-booked !

 

So, we need an effective locking mechanism to lock-wait in the beginning and unlock in the end.

 

This is as close to the business reuqirement as possible, and almost matches our conditions:

 

1. We are not inserting / editing any common records

2. The record which we are referring (for available quantity) is not being updated in the process

3. We need some mechanism to put lock on contact records, so that who-ever asks for those records has to wait till the transaction that locked them releases them.

 

------------

 

Ron HessRon Hess

i think it's simply a matter of putting a semaphore on the opportunity

 

you read the value, and then upon save, re-read the value is the same before you write the record.

 

now you have a field on the common object, and you can lock (for update) on that.

 

 

this will have to change:

  The record which we are referring (for available quantity) is not being updated in the process

 

 

here is a refresher

http://en.wikipedia.org/wiki/Semaphore_%28programming%29