Обсуждение: locks held during commit with synchronous replication

Hi,

I noticed that with synchronous replication I often see locks like this:

-[ RECORD 10 ]-----+--------------------
locktype           | object
database           | 0
relation           |
page               |
tuple              |
virtualxid         |
transactionid      |
classid            | 1262
objid              | 0
objsubid           | 0
virtualtransaction | 13/42806
pid                | 9794
mode               | AccessExclusiveLock
granted            | t
fastpath           | f

According to the manual classid is an oid in pg_class. If so, the 1262
resolves to pg_database. What objid=0 means, I have no idea.

For how long is this lock held?

Am I right in assuming that it is held from the start of the commit
until the transaction is streamed and written to disk by the synchronous
replica?

Does that mean that only one transaction can be committed at a time? I
have to admit I haven't thought much about that before. Lock contention
has never been a problem here.

Thanks,
Torsten

=?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.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
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.

            regards, tom lane

On 21/10/13 20:46, Tom Lane wrote:
> =?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.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