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. Another main change in patch `0004-support-global-unique-index-insert-and-update.patch`, + search_global: + stack = _bt_search(iRel, insertstate.itup_key, + &insertstate.buf, BT_READ, NULL); + xwait = _bt_check_unique_gi(iRel, &insertstate, + hRel, checkUnique, &is_unique, + &speculativeToken, heapRel); + if (unlikely(TransactionIdIsValid(xwait))) + { ... ... + goto search_global; + }
Here, I am trying to use `BT_READ` to require a LW_SHARED lock on the buffer block if a match found using `itup_key` search key. The cross-partition uniqueness checking will wait if the index tuple insertion on this buffer block has not done yet, otherwise runs the uniqueness check to see if there is an ongoing transaction which may insert a conflict value. Once the ongoing insertion is done, it will go back and check again (I think it can also handle the case that a potential conflict index tuple was later marked as dead in the same transaction). Based on this change, my test results are:
1) a select-only query will not be blocked by the ongoing insertion on index X
2) insertion happening on index Y may wait for the buffer block lock when inserting a different value but it does not wait for the transaction lock held by insertion on index X.
3) when an insertion inserting a conflict value on index Y, 3.1) it waits for buffer block lock if the lock has been held by the insertion on index X. 3.2) then, it waits for transaction lock until the insertion on index X is done.
