Potential issues regarding unique jobs

[elridion]

Current Behavior

Similar to #1087 and the fix in 2ff529d this is about concurrent inserts of unique jobs in Oban.

When inserting unique jobs the immanent assumption seems to be that

1. the advisory lock is acquired based on a feasible hash for the given unique constraints, and
2. if transaction attempt A fails to acquire the lock because transaction B has acquired it, then
3. its safe to assume that B will commit an insertion that will lead to a conflict and thus A doesn't have to insert one on its own

The thing is, should the transaction B terminate with a rollback the insertion which the lock was acquired for is never committed and in consequence no job will be inserted into the database.
Im not very familiar with using :replace with job insertions but I would guess that there is another issue there since the respective update can't take place.

Expected Behavior

Im not entirely sure how to best handle this but it seems to me that this behaviour is currently pretty obfuscated.
From my perspective this should lead to an error for insert/3,5 and an exception for insert!/3 instead of returning the job as if it was inserted.

[elridion]

Another problem I ran into is that Postgres repeatable read isolation messes with job uniqueness since concurrent transactions are unable to succsesfully SELECT the respective conflicting jobs from each other and thus both jobs will be inserted.

Really no sure how to handle this one but maybe its worth to be noted in the docs.

[sorentwo]

There's a lot of thought and discussion about how to handle uniqueness without advisory locks going on currently, partly because of the push for interop with CockroachDB and Yugabyte.

Changing the return to an error in this circumstance may be necessary, but I don't want to introduce a potentially breaking change just to change it again soon. For now, documenting the caveats in the README seems like the best option.

[sorentwo]

The hybrid and simple unique modes in Oban Pro v1.5 avoid use of advisory locks entirely and aren't susceptible to transaction races.

There won't be any changes to uniqueness in OSS Oban.