> Whole point is to have multiple services accessing same table and
> dividing the work, so locking with waiting for lock to be released is
> out of question.
>
We are doing the same (newsletter) and there is no problem to lock the
whole table for a short time with an advisory lock as the java id
fetching worker is locking the table (that does not lock the table for
reading or writing, it is only locking his java worker brothers that
are using the same advisory lock), fetches, let's say, 50 id's of
records marked as CREATED and changes their status to PROCESSING. Then
several workers are getting the id's and fetch the needed data from
the table independently and process and update them in parallel. We
have 3 java machines getting id's for 10 parallel workers and
everything works just fine.
Getting the IDs is much much faster usually then real processing.