Hello, 2013/9/21 <[email protected]>
> Hey, > thanks for this great library! > At the moment I'm developing an application where I need to assign jobs to > workers. As this happens in multiple threads, I need to make sure that no > job gets assigned to two workers. > My idea was to use a query like > >> UPDATE jobs SET worker_id=xxx WHERE worker_id IS NULL LIMIT 1 >> > > As far as I can see UPDATE...LIMIT is not supported by jooq. Did I miss > anything? > You're right, this feature addition is on the roadmap, but hasn't been a priority so far: https://github.com/jOOQ/jOOQ/issues/714 > Do you have an idea how to implement this using jooq? > I'd personally prefer some logical form of data partitioning over using LIMIT in such cases. Even with Oracle's FOR UPDATE SKIP LOCKED clause, I haven't found a solution to this problem so far, that is completely reliable and predictable. If you can live with the odd race condition, though, I suggest you rephrase your query as such: UPDATE jobs SET worker_id = xxx WHERE id = ( SELECT id FROM jobs WHERE worker_id IS NULL LIMIT 1 ) The above is easy to express with jOOQ: DSL.using(configuration) .update(JOBS) .set(JOBS.WORKER_ID, xxx) .where(JOBS.ID.eq( select(JOBS.ID).from(JOBS).where(JOBS.WORKER_ID.isNull()).limit(1) )) .execute(); Hope this helps, Cheers Lukas -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
