这个并发场景下的凭证分配问题，核心要解决两个关键点：避免多个用户拿到同一张凭证，同时不能锁整个表影响其他操作。结合Oracle的特性，我们可以通过原子化的更新操作+行级锁机制来实现，这里给你两种适配不同Oracle版本的方案：

方案一：Oracle 12c+ 推荐用 SKIP LOCKED 特性 #

Oracle 12c及以后引入的SKIP LOCKED是处理这类并发分配场景的最优解，它可以让并发请求直接跳过已经被其他会话锁定的行，不用等待，也不会触发表级锁。

修改你的存储过程，把查询和更新合并成一个原子操作：

CREATE OR REPLACE PROCEDURE credential_assign(
    in_company_id IN NUMBER,
    out_credential_id OUT NUMBER
) AS
BEGIN
    -- 原子完成：锁定未被占用的可用凭证 → 更新状态 → 返回ID
    UPDATE credential_table_v
    SET status = 'blocked',
        last_modified_dt = SYSTIMESTAMP
    WHERE company_id = in_company_id
      AND status = 'Available'
      AND ROWNUM = 1 -- 只取一条凭证
    SKIP LOCKED -- 跳过已被其他会话锁定的行
    RETURNING credential_id INTO out_credential_id;

    -- 处理无可用凭证的情况
    IF out_credential_id IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'No available credentials for company ID: ' || in_company_id);
    END IF;
END;
/

为什么这个方案有效？ #

• 原子性：整个操作在一条UPDATE语句里完成，数据库会保证锁定行和更新状态的操作是原子的，不会出现“查到凭证但还没更新就被其他用户抢走”的情况。
• 行级锁而非表锁：Oracle默认对更新的行加行级锁，不会锁定整个表，其他用户操作其他公司的凭证或者未被锁定的凭证完全不受影响。
• 无等待并发：SKIP LOCKED让每个请求直接找下一条未被锁定的可用凭证，100个并发用户可以同时拿到不同的凭证，不会互相阻塞。

方案二：Oracle 11g及更早版本的兼容方案 #

如果你的数据库版本低于12c，没法用SKIP LOCKED，可以用FOR UPDATE NOWAIT来避免等待，不过需要处理资源忙的异常：

CREATE OR REPLACE PROCEDURE credential_assign(
    in_company_id IN NUMBER,
    out_credential_id OUT NUMBER
) AS
BEGIN
    -- 先锁定一条可用凭证，NOWAIT表示如果行被锁定直接报错，不等待
    SELECT credential_id
    INTO out_credential_id
    FROM credential_table_v
    WHERE company_id = in_company_id
      AND status = 'Available'
      AND ROWNUM = 1
    FOR UPDATE NOWAIT;

    -- 锁定成功后更新状态
    UPDATE credential_table_v
    SET status = 'blocked',
        last_modified_dt = SYSTIMESTAMP
    WHERE credential_id = out_credential_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'No available credentials for company ID: ' || in_company_id);
    WHEN ORA-00054 THEN -- 捕获资源忙的异常
        RAISE_APPLICATION_ERROR(-20002, 'No available credentials at this moment, please try again later.');
END;
/

这种方式需要客户端在收到ORA-20002错误时重试，体验不如SKIP LOCKED流畅，但也能解决并发分配的问题。

额外优化建议 #

• 加索引提升性能：给company_id和status字段联合建索引，让数据库快速定位到可用凭证，减少锁的持有时间：

  CREATE INDEX idx_credential_company_status ON credential_table_v(company_id, status);
  

• 保持事务简短：尽量不要在存储过程里做其他耗时操作，更新完成后尽快提交事务，释放行级锁，提升并发能力。

内容的提问来源于stack exchange，提问作者Anish Gopinath