Re: locks held during commit with synchronous replication

On 21/10/13 20:46, Tom Lane wrote:
> =?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten(dot)foertsch(at)gmx(dot)net> writes:
>> > I noticed that with synchronous replication I often see locks like this:
>> > [ AccessExclusiveLock on "database 0" ]
> You did not say what PG version you're talking about, but if it's recent

It's 9.3.1

> then this must be coming from PreCommit_Notify, which takes such a lock
> while pushing entries into the shared notification-event queue:
>
> /*
> * 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 has nothing to do with synchronous replication, only with use of
> LISTEN/NOTIFY.
>
>> > Does that mean that only one transaction can be committed at a time?
> If they're sending notifies, yes.

Thanks, Tom! I think that explains it. We are also using bucardo. So
when a transaction commits the bucardo notification acquires the lock.
But now with synchronous replication and synchronous_commit=on commit
waits for the remote host to complete. Thus, the lock is held longer
than before and I see it.

The problem is we use synchronous replication over long distances on the
internet to bring the data closer to the customer. Turns out, this
combined with bucardo creates quite a bottleneck.

Best,
Torsten