Oracle 19c中如何限制多用户并发执行同一查询（等待/告警）

核心实现思路 #

利用Oracle自带的DBMS_LOCK包，为需要限制的查询创建一个唯一的自定义排他锁。当用户要执行查询时，先尝试获取这个锁：

• 如果锁没人使用，就拿到锁执行查询，完成后释放锁；
• 如果锁已被其他用户持有，要么等待锁释放，要么直接弹出告警提示。

具体操作步骤 #

1. 为用户分配DBMS_LOCK权限 #

首先需要让执行查询的用户拥有使用DBMS_LOCK的权限，执行以下SQL：

GRANT EXECUTE ON DBMS_LOCK TO 你的用户名;

2. 将查询封装为存储过程 #

不能直接让用户执行原始SQL，需要把查询和锁逻辑封装到存储过程中。以下是两种场景的实现代码：

场景1：等待前序查询完成后再执行

后发起的查询请求会等待，直到前序查询完成并释放锁：

CREATE OR REPLACE PROCEDURE 执行统计查询
IS
  锁句柄 VARCHAR2(128);
  锁申请结果 INTEGER;
BEGIN
  -- 为目标查询生成唯一锁标识（此处用固定名称对应你要限制的查询）
  DBMS_LOCK.ALLOCATE_UNIQUE(
    lockname => '统计表行数的查询',
    lockhandle => 锁句柄
  );
  
  -- 申请排他锁，最多等待300秒（可根据需求调整时长，比如60表示等待1分钟）
  锁申请结果 := DBMS_LOCK.REQUEST(
    lockhandle => 锁句柄,
    lockmode => DBMS_LOCK.X_MODE, -- 排他锁，同一时间仅一个会话可持有
    timeout => 300,
    release_on_commit => FALSE
  );
  
  IF 锁申请结果 = 0 THEN
    -- 获取锁成功，执行查询
    DBMS_OUTPUT.PUT_LINE('开始执行查询...');
    FOR 查询结果 IN (SELECT COUNT(*) AS 行数 FROM 你的表名) LOOP
      DBMS_OUTPUT.PUT_LINE('查询结果：表共有 ' || 查询结果.行数 || ' 行');
    END LOOP;
    
    -- 查询完成后释放锁
    DBMS_LOCK.RELEASE(锁句柄);
    DBMS_OUTPUT.PUT_LINE('查询完成，锁已释放');
  ELSE
    -- 未获取到锁（超时或被占用），触发告警
    DBMS_OUTPUT.PUT_LINE('⚠️ 告警：当前已有用户在执行该查询，请稍后再试');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常时强制释放锁，避免锁长期占用
    DBMS_LOCK.RELEASE(锁句柄);
    RAISE; -- 抛出错误便于排查问题
END;
/

场景2：不等待，直接返回告警

若不想让用户等待，直接告知无法执行，只需将上述代码中的timeout参数改为0：

锁申请结果 := DBMS_LOCK.REQUEST(
  lockhandle => 锁句柄,
  lockmode => DBMS_LOCK.X_MODE,
  timeout => 0, -- 0表示不等待，立即返回结果
  release_on_commit => FALSE
);

3. 用户通过存储过程执行查询 #

用户无需再直接执行原始SQL，改为调用存储过程即可：

SET SERVEROUTPUT ON; -- 开启输出提示，才能查看结果和告警信息
EXEC 执行统计查询;

额外说明 #

• 多查询适配：如果需要限制多个不同查询，为每个查询设置唯一的lockname即可（比如“查询用户表数据”“统计订单数量”），确保每个查询对应独立的锁。
• 多用户共享：若要让多个用户使用该存储过程，可将其创建在公共用户（如PUBLIC）下，再授权所有用户执行：GRANT EXECUTE ON 执行统计查询 TO PUBLIC;。
• 结果缓存替代方案：如果仅需复用查询结果而非限制并发，可在SQL中添加RESULT_CACHE提示：SELECT /*+ RESULT_CACHE */ COUNT(*) FROM 你的表名;，Oracle会自动缓存结果，重复查询直接返回缓存数据，但不会限制并发执行。

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