I have 'jobs' table with a tasks that needs to be processed.
A simple query like SELECT * FROM jobs WHERE status='new' LIMIT 1 FOR UPDATE SKIP LOCKED can be used by a set of workers to process rows in parallel one by one.
But what I need is to lock all rows linked to the single person by single worker.
Something like this would do the job SELECT * FROM jobs WHERE status='new' GROUP BY person_id LIMIT 1 FOR UPDATE SKIP LOCKED but locking does not work with aggregate functions.
In case of using subquery SELECT * FROM jobs WHERE person_id in (SELECT person_id FROM jobs WHERE status='new' LIMIT 1 FOR UPDATE SKIP LOCKED) FOR UPDATE SKIP LOCKED I have no guaranty all rows would be locked by same thread.
Locking a 'person' row in 'persons' table instead of job is not a preferable solution as this table is used by other processes as well.
Please advice if there is a simple way to do this.