Re: Patch: Global Unique Index

On 2022-11-29 6:16 p.m., Tom Lane wrote:
> Assuming that you are inserting into index X, and you've checked
> index Y to find that it has no conflicts, what prevents another
> backend from inserting a conflict into index Y just after you look?
> AIUI the idea is to prevent that by continuing to hold an exclusive
> lock on the whole index Y until you've completed the insertion.
> Perhaps there's a better way to do that, but it's not what was
> described.

During inserts, global unique index patch does not acquire exclusive
lock on the whole index Y while checking it for the uniqueness; it
acquires a low level AccessShareLock on Y and will release after
checking. So while it is checking, another backend can still insert a
duplicate in index Y. If this is the case, a "transaction level lock"
will be triggered.

For example.

Say backend A inserts into index X, and checks index Y to find no
conflict, and backend B inserts a conflict into index Y right after. In
this case, backend B still has to check index X for conflict and It will
fetch a duplicate tuple that has been inserted by A, but it cannot
declare a duplicate error yet. This is because the transaction inserting
this conflict tuple started by backend A is still in progress. At this
moment, backend B has to wait for backend A to commit / abort before it
can continue. This is how "transaction level lock" prevents concurrent
insert conflicts.

There is a chance of deadlock if the conflicting insertions done by A
and B happen at roughly the same time, where both backends trigger
"transaction level lock" to wait for each other to commit/abort. If this
is the case, PG's deadlock detection code will error out one of the
backends.  It should be okay because it means one of the backends tries
to insert a conflict. The purpose of global unique index is also to
error out backends trying to insert duplicates. In the end the effects
are the same, it's just that the error says deadlock detected instead of
duplicate detected.

If backend B did not insert a conflicting tuple, no transaction lock
wait will be triggered, and therefore no deadlock will happen.

Regards
Cary Huang
-----------------------
HighGo Software Canada