oracle - 2 个用户同时运行一个存储过程 - 如果一次执行中的 DML 语句影响并行执行中的测试/条件会怎样？

Question

假设我有一个用于创建销售订单的 PL/SQL 存储过程，如下所示:

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;

我认为这一切都非常简单。但我想知道的是当 2 个用户同时运行这个存储过程时会发生什么。假设某种产品仅剩 1 件。如果 user1 首先运行存储过程，尝试创建 1 个单位的订单，l_stock_available 获得值 1，IF 条件评估为 true，并且 INSERT 和 UPDATE 被执行。

然后 user2 稍后运行存储过程，同时尝试为 1 个单位创建订单。假设用户 2 的 SELECT INTO 由 Oracle 在用户 1 的执行到达注释 hello 的瞬间执行。此时，user2 也将为 l_stock_available 获取值 1，IF 条件将评估为真，并且 INSERT 和 UPDATE 将被执行，将库存水平降至 -1。

我的理解正确吗？这是会发生什么吗？如何避免这种情况，即可以为库存中的最后一件商品创建 2 个订单？

Answer 1

是的，您正确理解所编写的代码存在竞争条件。

假设性能要求允许悲观锁定，最简单的修复方法是将 FOR UPDATE 添加到初始 SELECT 语句。这将锁定 STOCKS 表中的特定行，这将导致第二个 session 阻塞，直到第一个 session 的事务提交或回滚。然后第二个 session 将看到手头库存已减少到 0。