findTaskForExecutionForUpdate
@Query(value = "
select
candidate.id
from task_executor_type tet
join task_executor_type_to_task_type tcon on tcon.task_executor_type_id = tet.id
join task_type tt on tt.id = tcon.task_type_id
join lateral (
select
t.id
from task t
where
t.type_id = tt.id and
t.running_status = 20 and
t.is_cancelled = false and
(
not tt.ordered or
(
t.order_key is not null and
not exists (
select 1
from task older
where
older.type_id = t.type_id and
older.order_key = t.order_key and
older.running_status = 20 and
older.is_cancelled = false and
older.id < t.id
) and
not exists (
select 1
from task running
where
running.type_id = t.type_id and
running.order_key = t.order_key and
running.running_status = 30
)
)
)
order by
t.id
limit 1
for update of t skip locked
) candidate on true
where
tet.hid = :executorTypeHid
order by
candidate.id
limit 1
", nativeQuery = true)