Oracle如何锁定并获取表中n条未被锁定的可用空闲号码记录

正确的实现语句如下：

SELECT * 
FROM (
    -- 内层先过滤所有空闲记录，跳过已被其他会话锁定的行
    SELECT PHONE, STATUS 
    FROM PHONE_NUMBER 
    WHERE STATUS = 'O'
    FOR UPDATE SKIP LOCKED
) 
-- 外层再限制返回条数，保证拿到指定数量的可用记录
WHERE ROWNUM <= :sqli_n;

原写法失效原因 #

你最初的写法SELECT * FROM PHONE_NUMBER WHERE STATUS='O' and rownum<=:sqli_n FOR UPDATE ;执行逻辑是：先匹配STATUS条件后取前n条记录，再对这n条记录尝试加锁。如果这n条里存在已被其他会话锁定的记录，要么会出现阻塞等待，要么搭配SKIP LOCKED时会跳过被锁记录，最终返回的可用记录数会少于需求值甚至为0。

优化建议 #

• 给STATUS列创建索引，数百万数据量下可以快速过滤出空闲记录，避免全表扫描，大幅提升查询性能
• 拿到查询返回的n条记录后，直接执行UPDATE操作把对应记录的STATUS改为'U'即可，已加的行锁可以保证并发场景下不会出现重复预留的问题
• 如果当前空闲可用的未锁定记录总数不足n条，语句会直接返回当前所有可用的记录，你可以根据返回的条数判断是否满足预留需求

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