6

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?

Community
  • 1
  • 1
Synesso
  • 37,610
  • 35
  • 136
  • 207
  • I think this blog post has some relevant thoughts: http://markjbobak.wordpress.com/2010/04/06/unintended-consequences/ – Jeffrey Kemp Oct 16 '10 at 03:16

1 Answers1

3

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
  • 257,207
  • 101
  • 511
  • 656
  • 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 Oct 15 '10 at 04:05
  • 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 Oct 15 '10 at 04:08
  • 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 Oct 15 '10 at 04:08
  • So you actually select top n <= N rows here. – Zonko Oct 20 '11 at 14:18