validate data existence in oracle/oracle forms for multiple users

Question

I got a screen (block) in which users make requests for a specific product and I validate the number of items available in the database at the moment of the request, before they commit, which is suposed to reduce the existence ot the item requested.

The problem I'm facing is... if several users are making the request at the same time they all get the existence available before a complete request has been done by a user. So when another user makes the request, the existence in the datase is unreal for that particular user.

I think of validating the existence of the product again before commiting the request to the database, and display a message to the user that the existence has chaged since the he first logged in. I don't know if that is a good solution. I need your expiience in this type of situation.

How can I control the existence of an item while several users are making requests at the same time?

I just need the basic idea so I can continue with the code.I think the problem is independent on language I am using.

Sylvain Leroux Sylvain Leroux · Accepted Answer · 2014-09-24T16:41:09

I'm not sure this is really the answer to your question as I never used Oracle Form. But, generally speaking, to face that kind of issue, you should use atomic test and set queries. Sounds scary, isn't it? But really simple:

Say at a given point, one of your users saw that inventory:

SELECT * FROM INVENTORY WHERE PRODUCT='LEMON';
ID  PRODUCT QTY
2   LEMON   200

There was 200 lemons in stock at the time of the SELECT query. But maybe, those has been sold out by now. Maybe. Maybe not.

If the user seeing the 200 lemons decides to check out, say 100 of them. You can only do that if there is still 100 or more lemon at the time of UPDATE. Concretely you will write:

UPDATE INVENTORY 
  SET QTY=QTY-100 
  WHERE PRODUCT='LEMON' AND QTY >= 100;

That way, you only update your table if enough lemon left at that time. Since Oracle ensure that only one statement can update a row at a time, all concurrent requests to update that row are handled sequentially. In addition, by the use of the WHERE ... QTY >= 100 Oracle will check the number of items remaining at UPDATE time.

You have to be prepared (and you must check!) the outcome of the statement to see if one row was updated or not. It no row was updated, that means there wasn't no longer enough lemons in stock at the time of update.

validate data existence in oracle/oracle forms for multiple users

1 Answers