Re: unexpected lock waits (was Re: Do not understand why this happens)

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> I do wonder what else is happening in the transaction that you're
> calling NOTIFY within; and that some other statement could be causing
> the lock wait.

FWIW, the lock seems to be the one taken to serialize insertions into
the shared NOTIFY queue, from this bit in commands/async.c:

/*
* Serialize writers by acquiring a special lock that we hold till
* after commit. This ensures that queue entries appear in commit
* order, and in particular that there are never uncommitted queue
* entries ahead of committed ones, so an uncommitted transaction
* can't block delivery of deliverable notifications.
*
* We use a heavyweight lock so that it'll automatically be released
* after either commit or abort. This also allows deadlocks to be
* detected, though really a deadlock shouldn't be possible here.
*
* The lock is on "database 0", which is pretty ugly but it doesn't
* seem worth inventing a special locktag category just for this.
* (Historical note: before PG 9.0, a similar lock on "database 0" was
* used by the flatfiles mechanism.)
*/
LockSharedObject(DatabaseRelationId, InvalidOid, 0,
AccessExclusiveLock);

This lock is held while inserting the transaction's notify message(s),
after which the transaction commits and releases the lock. There's not
very much code in that window. So what we can conclude is that some
other transaction also doing NOTIFY hung up within that sequence for
something in excess of 3 seconds. We have been shown no data whatsoever
that would allow us to speculate about what's causing that other
transaction to take so long to get through its commit sequence.

regards, tom lane