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