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

#sql #oracle

Несколько потоков модифицируют таблицу. Хочу выбрать из таблицы 100 не заблокированных
строк и заблокировать их. Написал запрос:

select * from table where rownum <= 100 for update skip locked


Однако при таком варианте сначала применяется условие rownum<=100 и только потом
выбрасываются заблокированные строки из результата запроса. Обернуть этот запрос не
разрешается. Как можно выбрать первые 100 не заблокированных строк из таблицы и заблокировать их ?
    

Ответы

Ответ 1

Существующее ограничения на 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 строк */


Также приемлимо завернуть подобный курсор в функцию возврающую результат в виде массива
залоченых строк в клиент для дальнейшей обработки и завершения трансакции.