> 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.
