2 users running a stored procedure concurrently - what happens if DML statements in one execution affect tests/conditions in the parallel execution?

Question

Let's say I have a PL/SQL stored procedure for creating sales orders as follows:

CREATE OR REPLACE PROCEDURE save_order 
(
  o_output_status               OUT BINARY_INTEGER,
  i_customer                    IN  VARCHAR2,
  i_product_code                IN  VARCHAR2,
  i_quantity                    IN  BINARY_INTEGER
)
IS

  l_stock_available               BINARY_INTEGER;

BEGIN

  o_output_status := -1;

  SELECT available
    INTO l_stock_available
    FROM stock
   WHERE product = i_product_code
  ;

  IF l_stock_available >= i_quantity THEN

    INSERT INTO sales_orders (order_number, customer, product, quantity)
         VALUES (order_seq.nextval, i_customer, i_product_code, i_quantity)
    ;

    -- hello

    UPDATE stock
       SET available = available - i_quantity
     WHERE product = i_product_code
    ;

    o_output_status := 0;

  END IF;

END save_order;

I think that's all pretty straightforward. But what I'd like to know is what happens when 2 users run this stored procedure concurrently. Let's say there's only 1 unit of some product left. If user1 runs the stored procedure first, attempting to create an order for 1 unit, l_stock_available gets a value of 1, the IF condition evaluates to true and the INSERT and UPDATE get executed.

Then user2 runs the stored procedure a moment later, also trying to create an order for 1 unit. Let's say the SELECT INTO for user2 gets executed by Oracle at the instant that user1's execution has reached the comment hello. At this point, user2 will also get a value of 1 for l_stock_available, the IF condition will evaluate to true, and the INSERT and UPDATE will be executed, bringing the stock level down to -1.

Do I understand correctly? Is this what will happen? How can I avoid this scenario, where 2 orders can be created for the last item in stock?

Justin_Cave_10397 · Answer

Yes, you understand correctly that the code as written has a race condition.

The simplest fix, assuming that performance requirements permit pessimistic locking, is to add a FOR UPDATE to the initial SELECT statement. That locks the particular row in the STOCKS table which would cause the second session to block until the first session's transaction either commits or rolls back. The second session would then see that the stock on hand had been decreased to 0.

user_4808122 · Answer

If you want to implement DIY optimistic locking (using the similar mechanisms as e.g. JPA uses) add a VERSION column to your stock table with data type INT initialized with zero.

The initial selectof your procedure returns not only the available quantity but also the current VERSION:

  select available, version
  into l_available,l_version
  from stock
  where product_id = i_product_code;

If the table has the required quantity you first UPDATE it, but only if the VERSION has the same value as returned from the previous query.

update stock
   set available = available - i_quantity,
       version = version + 1
where product_id = 1 and
/* optimistick locking */
version = l_version;

Note that 1) the update will fail if the version does not match the value you selected.

If the update succeeds you increase the version to block others from performing an concurrent UPDATE.

The last step is to check if the update was succesful and if so you process the quantity you got.

rn := SQL%rowcount; 
if rn = 1 then  /* success */
  insert into sales_orders (thread_id, product_id,quantity,create_ts)
  values(i_thread_id, i_product_code,i_quantity,current_timestamp);
  commit;
end if;

If the UPDATE changed zero rows (if failed due to changed VERSION) you should either retry of return a failure.

2 users running a stored procedure concurrently - what happens if DML statements in one execution affect tests/conditions in the parallel execution?

2 Answers