Несколько потоков модифицируют таблицу. Хочу выбрать из таблицы 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
<
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 строк */
Также приемлимо завернуть подобный курсор в функцию возврающую результат в виде массива залоченых строк в клиент для дальнейшей обработки и завершения трансакции.
Комментариев нет:
Отправить комментарий