views:

58

answers:

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.

How can I select the top N with readpast locking?

+1  A: 

Will this work?

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;
Thilo
If it's done in two steps, aren't I introducing a potential race condition? I follow your logic, but I find it hard to believe that Oracle can't do what SQL Server can.
Synesso
That query executed fine. I'll go on to test that the locking is working as intended, but it seems good to go. Thanks!
Synesso
Well, the semantics of skip locked are probably a bit tricky in this case, even on SQL Server. I would expect the query above to first find the top N messages, and then skip amongst them (so that you could end up with less than N records). And I do not think that there is a way around that in general (short of locking the whole table).
Thilo