Re: Row locks, SKIP LOCKED, and transactions

Steven Winfield <Steven(dot)Winfield(at)cantabcapital(dot)com> writes:
>> There are various ways you could deal with this, but I'd probably go for a
>> simple scheme where you only have to consult a single row to know if you
>> can claim it. You could still put the results into a separate table, but
>> use job.state to find work, and set it to DONE when you insert the result.
>> It may also be possible to add no new columns but do a dummy update to the
>> job row to get the join qual rechecked, but I'm not sure if that'd work.
>> Another reason to add a state column to the job table is so that you can
>> put a conditional index on it so you can find jobs to be done very
>> quickly, if you're not planning to remove the ones that are done.

> Thanks. I rejected the idea of doing a dummy update to the locked row as I wanted to avoid too much extra WAL - the real table originally had quite a few more columns than the toy example, but it's much slimmer now so this could be a viable option.

Yeah ... the fundamental reason why this isn't working for you is that
the FOR UPDATE will only lock/check conflicts in the "job" table.
You could add a FOR UPDATE in the sub-select to lock the "result" table,
but that will still only lock rows it read, not rows it didn't read
because they weren't there yet :-(. Updating the state of the job row
to show that it's claimed is much the most reliable way to fix this.

(Or you could use serializable mode, but that feels like using a hammer
to swat a fly.)

regards, tom lane