Oracle锁查询优化：无锁时返回0而非空结果的实现方法

可以通过两种方式实现需求，根据你是否需要保留所有锁的等待记录来选择：

方法1：保留所有锁记录，无锁时返回单行0 #

这种方式会在有锁时返回所有符合条件的等待时间，无锁时返回一行0：

WITH lock_results AS (
    SELECT b.seconds_in_wait AS TIME 
    FROM sys.v_$session b
    JOIN sys.dba_blockers c ON c.holding_session = b.sid
    JOIN sys.dba_lock a ON c.holding_session = a.session_id
    WHERE b.username LIKE '%MOBILE%'
)
SELECT TIME FROM lock_results
UNION ALL
SELECT 0 AS TIME FROM dual
WHERE NOT EXISTS (SELECT 1 FROM lock_results);

逻辑说明： #

1. 用WITH子句先把原查询的结果暂存到lock_results中，同时把旧的隐式连接改成显式JOIN，语法更清晰且避免潜在的笛卡尔积问题。
2. 先返回lock_results的所有记录（有锁时的正常结果）。
3. 通过NOT EXISTS判断如果lock_results为空（无锁），则从dual表返回一行值为0的记录。

方法2：返回最大等待时间，无锁时返回0 #

如果你只需要知道当前最久的锁等待时间，不需要所有记录，可以用聚合函数结合NVL实现：

SELECT NVL(MAX(b.seconds_in_wait), 0) AS TIME
FROM sys.v_$session b
JOIN sys.dba_blockers c ON c.holding_session = b.sid
JOIN sys.dba_lock a ON c.holding_session = a.session_id
WHERE b.username LIKE '%MOBILE%';

逻辑说明： #

• MAX(b.seconds_in_wait)会获取所有符合条件的锁等待时间中的最大值，无锁时这个聚合值为NULL。
• NVL函数会把NULL替换成0，从而实现无锁时返回0的需求。

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