选择“用于更新跳过锁定"的顶部N.在甲骨文 [英] Select top N with "for update skip locked" in Oracle

问题描述

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

In Oracle, I can select the top 1 message in a sorted table with

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

正如我在上一个问题中发现的那样，可以专门选择一个行

And as I discovered in a previous question I can select a row exclusively with

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

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

However I can't merge these two concepts together

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.

我该如何选择前读取锁定的前N个?

How can I select the top N with readpast locking?

推荐答案

这项工作可行吗?

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;

这篇关于选择“用于更新跳过锁定"的顶部N.在甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！