Oracle database read lock

Question

I have an issue I am trying to address. I need to lock reads on a table temporarily.

Here's the scenario.

I hve to read a table to determine the maximum sales order used that starts with a base number. I then have to add numbers after the decimal. So if we have order 123.1 and 123.2 I need to determine that the next one I need to create is 123.3. I then call the API to create this order number.

The problem is when two users simultaneously want to add a new sales order to the base 123 order number. One user's logic determines the number is 123.3 and calls the API to have the order created; once created I commit the record. But while the API call is started, the second user's logic is trying to determine the next number and depending on timing, it can also select 123.3 as the next available number.

Then once the second user's logic calls the API, the number is duplicated and errors out.

I want to have the first logic lock the table from reads while it determines the order number and creates it. Then I can release the lock and the second user can proceed.

Everything I have read seems to say that I cannot block reads of a table.

Answer 1

You could use two tables: ORDER and ORDER_LINE.

Your procedure that generates the numbers would do a SELECT * FROM order WHERE order_id = 123 FOR UPDATE, then determine the appropriate number from the lines in ORDER_LINE.

If two sessions call the API simultaneously with the same order_id, one of them will wait until the first one commits/rollbacks. Once the first session commits, the second session will get the lock on the master table and then the next number in line.

The session can be called concurrently on different order_id without waits.

If you don't want to wait in case of concurrent update, use FOR UPDATE NOWAIT and return with a message that the order is locked by another user.