2

From what I understand, the answer, broadly speaking is "no", but here's the problem:

I need to run a query that needs to do the following things:

  1. Select rows in a table, sorted by a column
  2. Of these rows, pick and lock the first one, skipping other locked rows.

An example query would look something like this:

SELECT
    E.*
FROM
   polled_table E
WHERE 
    E.ID IN 
    (SELECT ID FROM
        (SELECT 
            ID
         FROM
            polled_table
         WHERE 
            condition = 'value'
         ORDER BY priority DESC)
     WHERE rownum < 2)
FOR UPDATE SKIP LOCKED;

This of course doesn't work in the sense that the skip locked essentially doesn't work as that part of the query only ever sees one row.

Bringing the "rownum < 2" outside doesn't seem to be possible (it triggers errors described for example here: ORA-00907 Missing right parenthesis issue - select with order by inside insert query)

I've tried several combinations, but they either don't do what I want or they straight up generate errors. The only thing that might work would be removing the rownum condition, but that would lock a large number of rows.

At this point, as I mentioned, I'm thinking it just be done, not as a single query at least.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
GeckoOBac
  • 31
  • 7

1 Answers1

1

If you push the query into a cursor, then just fetch the first row. That should accomplish this task with the skip locked intact.

Something like (I'm away from my DB so pardon any minor syntax errors):

Cursor C is SELECT
    E.*
FROM
   polled_table E
WHERE
    E.ID IN
    (SELECT ID FROM
        (SELECT
            ID
         FROM
            polled_table
         WHERE
            condition = 'value'
         ORDER BY priority DESC))  for update skip locked;

var c%rowtype;

Open C
Fetch C into var;
close C;

Then process that first row fetched in the VAR variable.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Michael Broughton
  • 4,045
  • 14
  • 12
  • Yes this probably would work, however my specific need is for it to be a single statement as it's run by a polling software that we'd prefer to avoid having to modify. I'm not entirely sure whether I could use a cursor in that context. – GeckoOBac Jan 23 '20 at 14:54
  • Well, then you could go even further and push this cursor open/fetch down into a pipelined function that you can then select from in your software.A complicated solution, but you may have to do something that sneaky for this. See here for pipelined functions if you've never used them: https://oracle-base.com/articles/misc/pipelined-table-functions – Michael Broughton Jan 23 '20 at 15:10