Как выбрать и заблокировать 100 строк?

Несколько потоков модифицируют таблицу. Хочу выбрать из таблицы 100 не заблокированных строк и заблокировать их. Написал запрос:
select * from table where rownum <= 100 for update skip locked
Однако при таком варианте сначала применяется условие rownum<=100 и только потом выбрасываются заблокированные строки из результата запроса. Обернуть этот запрос не разрешается. Как можно выбрать первые 100 не заблокированных строк из таблицы и заблокировать их ?

Ответ

Существующее ограничения на for update не позволяют решить эту задачу только средствами SQL. В подзапросе этот конструкт недопустим, в 12c fetch first n rows only тоже не совместим с for update, т.е. такое не выполнится:
select * from (select * from table for update skip locked) where rownum <= 100;
Завернуть в табличную функцию также проблематично, так как она не может содержать DML операции, если её вызывать в запросе.
Исключение - автономные трансакции, но есть слабое место - выбранные строки между возвратом и основным запросом будут разлочены, что может привести к нежелательному результату. Следующий пример вроде решает задачу, но краш-тест с несколькими потоками в цикле без сомнения выявит недостатки:
create or replace type itemRow as object (id number, name varchar2 (32)); / create or replace type itemRows is table of itemRow; / drop table items; create table items (id number, name varchar2 (32)); / insert into items select level, 'item '||level from dual connect by level<=200 ; create or replace function getUnlockedItemsBlock return itemRows is pragma autonomous_transaction; cursor mycur is select itemRow(i.id, i.name) item from items i for update skip locked; ret itemRows; begin open mycur; fetch mycur bulk collect into ret limit 100; close mycur; commit; return ret; end; / update items set name=name||'*' where id between 5 and 104;
100 rows updated.
В другой сессии:
select i.id, i.name, count (1) over () "total locked by myself" from items i join table (getUnlockedItemsBlock) t on t.id = i.id for update of i.id ;
ID NAME total locked by myself ------ -------------------------------- ---------------------- 1 item 1 100 2 item 2 100 3 item 3 100 4 item 4 100 105 item 105 100 106 item 106 100 107 item 107 100 108 item 108 100 ...
Наиболее приемлиемое решение - надо, или отказаться от for update skip locked для разделения "зон влияния" между потоками, или как минимум от идеи "только одним SQL запросом" и остаться в PL/SQL контексте:
set serveroutput on size unlimited <> declare cursor mycur is select itemRow(i.id, i.name) item from items i for update skip locked; items itemRows; begin open mycur; fetch mycur bulk collect into items limit 100; close mycur; for idx in 1..items.count loop dbms_output.put_line ('processing item: '||items(idx).id||'/'||items(idx).name); end loop; end; /
processing item: 1/item 1 processing item: 2/item 2 processing item: 3/item 3 processing item: 4/item 4 processing item: 105/item 105 processing item: 106/item 106 processing item: 107/item 107 processing item: 108/item 108 ... /* всего 100 строк */
Также приемлимо завернуть подобный курсор в функцию возврающую результат в виде массива залоченых строк в клиент для дальнейшей обработки и завершения трансакции.