On 21/10/13 20:46, Tom Lane wrote: > =?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@xxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
