Re: locks held during commit with synchronous replication

=?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