Thank you very much. I missed the advisory locks feature. That feels like the perfect thing; the BIGINT keys are a bit clunky but that's minor.
Your description of the sequence of events for the two SELECT FOR UPDATE transactions makes sense. To apply it to my pseudo code, the second transaction would unblock when the row was deleted in the first rather than when the first transaction commits or rolls back.
One question though:
You've informed the system you are going to be updating rows on the table but as yet have not given it specific rows to protect.
Without #*1, a simple experiment shows that two processes can be in the critical section at the same time. Add #*1 seems to achieve the desired behavior, but is it really?
The index will not allow duplicates to be inserted and the first one to try forces all other potential insertions to wait until the first one commits;
Is this true in all situations? That is, will an index insertion in one transaction block the index insertion in another transaction? Presumably only only if the index entries match, but how does this work in practice? Does the second transaction block when it sees the conflict? Or does it proceed on its own version of the index proceeding until it tries to commit?
