Clarence Gardner <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate