多线程调用Oracle同一存储过程，如何强制串行执行？

兄弟，ALTER SESSION DISABLE PARALLEL DML完全不是干这个用的！它只是禁用当前会话的并行DML操作，和跨会话的并发互斥半毛钱关系都没有。你要实现同一时间只有一个线程执行存储过程，得用Oracle的锁机制，给你几个靠谱的方案：

方案1：用Oracle自带的DBMS_LOCK包（最推荐） #

这是Oracle专门提供的自定义锁工具，适合这种跨会话的互斥场景。你只需要在存储过程开头请求一个独占锁，处理完业务逻辑后释放锁即可。

示例代码：

CREATE OR REPLACE PROCEDURE your_target_procedure AS
  l_lock_handle VARCHAR2(128);
  l_return_code NUMBER;
BEGIN
  -- 给存储过程分配一个唯一的锁标识（锁名称用存储过程名保证唯一性）
  DBMS_LOCK.ALLOCATE_UNIQUE(lockname => 'YOUR_TARGET_PROCEDURE_LOCK', lockhandle => l_lock_handle);
  
  -- 请求独占锁，timeout设为-1表示无限等待（也可以设具体秒数，比如3600表示等1小时）
  l_return_code := DBMS_LOCK.REQUEST(
    lockhandle => l_lock_handle,
    lockmode => DBMS_LOCK.X_MODE, -- 独占锁模式
    timeout => -1,
    release_on_commit => FALSE -- 手动释放锁，而非提交事务自动释放
  );
  
  -- 检查锁是否获取成功
  IF l_return_code != 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '无法获取执行锁，返回码: ' || l_return_code);
  END IF;
  
  -- 👇这里写你的存储过程核心业务逻辑
  DBMS_OUTPUT.PUT_LINE('存储过程开始执行，时间: ' || SYSTIMESTAMP);
  -- 模拟业务耗时（比如5秒）
  DBMS_LOCK.SLEEP(5);
  
  -- 执行完释放锁
  l_return_code := DBMS_LOCK.RELEASE(l_lock_handle);
  IF l_return_code != 0 THEN
    RAISE_APPLICATION_ERROR(-20002, '无法释放执行锁，返回码: ' || l_return_code);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常时必须确保锁被释放，避免死锁
    BEGIN
      DBMS_LOCK.RELEASE(l_lock_handle);
    EXCEPTION
      WHEN OTHERS THEN NULL; -- 忽略释放时的异常，避免覆盖原错误
    END;
    RAISE; -- 抛出原异常
END;
/

注意事项： #

• 执行存储过程的数据库用户需要有DBMS_LOCK的执行权限，用这个语句授权：GRANT EXECUTE ON DBMS_LOCK TO your_db_user;
• 如果不想让线程无限等待，可以把timeout设为具体秒数，超时后会返回错误码1，你可以根据这个逻辑处理（比如直接返回或者重试）

方案2：用表级排他锁（适合简单场景） #

如果不想用DBMS_LOCK，可以找一个业务无关的空表（或者专门建一个锁表），对它加排他锁，这样同一时间只有一个会话能拿到锁。

步骤：

1. 先建一个空锁表（如果没有合适的表）：

CREATE TABLE procedure_lock_table (lock_id NUMBER PRIMARY KEY);
INSERT INTO procedure_lock_table VALUES (1); -- 插入一条数据即可
COMMIT;

2. 修改存储过程：

CREATE OR REPLACE PROCEDURE your_target_procedure AS
BEGIN
  -- 对锁表加排他锁，无限等待（去掉NOWAIT就是等待，加NOWAIT的话会直接报错）
  LOCK TABLE procedure_lock_table IN EXCLUSIVE MODE;
  
  -- 👇核心业务逻辑
  DBMS_OUTPUT.PUT_LINE('存储过程开始执行，时间: ' || SYSTIMESTAMP);
  DBMS_LOCK.SLEEP(5);
  
  -- 提交事务后锁会自动释放
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- 回滚事务释放锁
    RAISE;
END;
/

注意事项： #

• 不要用业务表来加锁，否则会影响其他对该表的正常操作
• 如果用NOWAIT选项，后面的线程会直接抛出ORA-00054: resource busy and acquire with NOWAIT specified错误，你可以根据这个错误判断是否有线程在执行

方案3：用自定义状态表（直观可控） #

建一个状态表记录存储过程的运行状态，通过行级锁来控制并发。

步骤：

1. 创建状态表：

CREATE TABLE procedure_run_status (
  proc_name VARCHAR2(100) PRIMARY KEY,
  is_running CHAR(1) DEFAULT 'N' CHECK (is_running IN ('Y', 'N'))
);
-- 初始化目标存储过程的状态
INSERT INTO procedure_run_status (proc_name, is_running) VALUES ('YOUR_TARGET_PROCEDURE', 'N');
COMMIT;

2. 修改存储过程：

CREATE OR REPLACE PROCEDURE your_target_procedure AS
  l_current_status CHAR(1);
BEGIN
  -- 锁定该行，防止其他会话同时修改
  SELECT is_running INTO l_current_status
  FROM procedure_run_status
  WHERE proc_name = 'YOUR_TARGET_PROCEDURE'
  FOR UPDATE;
  
  -- 检查是否已有线程在执行
  IF l_current_status = 'Y' THEN
    RAISE_APPLICATION_ERROR(-20003, '存储过程正在执行中，请稍后再试');
  END IF;
  
  -- 更新状态为运行中
  UPDATE procedure_run_status
  SET is_running = 'Y'
  WHERE proc_name = 'YOUR_TARGET_PROCEDURE';
  COMMIT;
  
  -- 👇核心业务逻辑
  DBMS_OUTPUT.PUT_LINE('存储过程开始执行，时间: ' || SYSTIMESTAMP);
  DBMS_LOCK.SLEEP(5);
  
  -- 执行完重置状态
  UPDATE procedure_run_status
  SET is_running = 'N'
  WHERE proc_name = 'YOUR_TARGET_PROCEDURE';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常时重置状态，避免死锁
    BEGIN
      UPDATE procedure_run_status
      SET is_running = 'N'
      WHERE proc_name = 'YOUR_TARGET_PROCEDURE';
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
    RAISE;
END;
/

注意事项： #

• 这个方案的好处是可以直观看到存储过程的运行状态，方便排查问题
• 如果想让后面的线程直接跳过而不是等待，可以把FOR UPDATE改成FOR UPDATE SKIP LOCKED，这样如果该行被锁定，查询会直接返回空，你可以据此处理

总结 #

如果追求简洁高效，优先选方案1（DBMS_LOCK），它是Oracle专门为自定义锁场景设计的，不依赖额外表，也不会影响业务。如果你的环境限制不能用DBMS_LOCK，再考虑方案2或3。

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