Re: Deadlock possibility in _bt_check_unique?

Gokulakannan Somasundaram <gokul007(at)gmail(dot)com> writes:
> Can you also explain how are we avoiding duplicates in this scenario?
> a) Say there are three pages(P,Q, R) full of duplicate tuples, that are
> deleted but not dead of id x(due to some long running transaction).
> b) Now Session A gets in and checks the duplicate tuples for their
> liveliness with the HeapTuple for id x with shared lock on all the pages P,
> Q and R. Since all are deleted, it will get the message, that it need not
> come back to check again for uniqueness Finally it again starts from P to
> check for freespace to insert its tuple. Say it inserts the tuple at page Q.
> c) Now Session B(with same id x) starts after Session A, but it passes Q
> before the insertion of the tuple by Session A. It will also get the
> response from _bt_check_unique, that it need not comeback for second time
> unique check. Now it checks for freespace from P and it finds freespace at
> P. Then it will insert the new record at P itself.

> So we have two duplicate records, eventhough there is a unique constraint.
> Is this a possible scenario?

Are you talking about exclusion constraints or btree uniqueness
constraints? This doesn't seem to be a particularly accurate
description of the implementation of either one. The way btree
deals with this is explained in _bt_doinsert:

* NOTE: obviously, _bt_check_unique can only detect keys that are already
* in the index; so it cannot defend against concurrent insertions of the
* same key. We protect against that by means of holding a write lock on
* the target page. Any other would-be inserter of the same key must
* acquire a write lock on the same target page, so only one would-be
* inserter can be making the check at one time. Furthermore, once we are
* past the check we hold write locks continuously until we have performed
* our insertion, so no later inserter can fail to see our insertion.
* (This requires some care in _bt_insertonpg.)

regards, tom lane