sql - 在 Oracle 中选择带有 "for update skip locked"的前 N ​​个

Question

在 Oracle 中，我可以选择排序表中的前 1 条消息

select messageid from(
    select 
        messageid, 
        RANK() over (order by messageid asc) as msg_rank 
    from messages
) where msg_rank=1;

正如我在 previous question 中发现的那样我可以只选择一行

select * from messages where rownum < 2 for update skip locked;

但是我不能将这两个概念合并在一起

select messageid from(
    select 
        messageid, 
        RANK() over (order by messageid asc) as msg_rank 
    from messages
) where msg_rank=1 for update skip locked;

-- results in error
-- ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

如何选择带有 readpast 锁定的前 N ​​个？

Answer 1

这会起作用吗？

select messageid from messages 
    where messageid in (
       select messageid from(
         select 
          messageid, 
          RANK() over (order by messageid asc) as msg_rank 
          from messages
       ) where msg_rank=1
    )
  for update skip locked;