Re: Not your father's question about deadlocks

Clarence Gardner <clarence(at)silcom(dot)com> writes:
> That scenario seems quite simple, but I can't reproduce the deadlock with
> this seemingly-identical sequence.

This is a bug in 8.1 and up. The reason you couldn't reproduce it is
that it requires a minimum of three transactions involved, two of which
concurrently grab ShareLock on a tuple --- resulting in a MultiXact
being created to represent the concurrent lock holder. The third xact
then comes along and tries to update the same tuple, so it naturally
blocks waiting for the existing ShareLocks to go away. Then one of the
original xacts tries to grab share lock again. It should fall through
because it "already has" the lock, but it fails to recognize this and
queues up behind the exclusive locker ... deadlock!

Reproducer:

Session 1:
create table foo (f1 int primary key, f2 text);
insert into foo values(1, 'z');
create table bar (f1 int references foo);
begin;
insert into bar values(1);

Session 2:
begin;
insert into bar values(1);

Session 3:
update foo set f2='q';

Back to session 1:
insert into bar values(1);
ERROR: deadlock detected

Note that session 2 might actually have exited before the deadlock
occurs.

I think the problem is that HeapTupleSatisfiesUpdate() always returns
HeapTupleBeingUpdated when XMAX is a running MultiXact, even if the
MultiXact includes our own transaction. This seems correct for the
usages in heap_update and heap_delete --- we have to wait for the
multixact's other members to terminate. But in heap_lock_tuple
we need a special case when we are already a member of the MultiXact:
fall through without trying to reacquire the tuple lock.

Comments? Should we change HeapTupleSatisfiesUpdate's API to
distinguish this case, or is it better to have a localized change
in heap_lock_tuple?

regards, tom lane