Skip site navigation (1) Skip section navigation (2)

Re: autovacuum and default_transaction_isolation

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Dan Ports" <drkp(at)csail(dot)mit(dot)edu>,<pgsql-hackers(at)postgresql(dot)org>
Subject: Re: autovacuum and default_transaction_isolation
Date: 2011-11-29 21:54:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Dan Ports <drkp(at)csail(dot)mit(dot)edu> wrote:
> I was doing some tests recently with default_transaction_isolation
> set to 'serializable' in postgresql.conf when I noticed pg_locks
> filling up with SIReadLocks rather more quickly than I expected.
> After some investigation, I found that an autovacuum worker was
> starting a transaction at the default isolation level. While using
> a serializable transaction doesn't affect its behavior (because
> it's not using a MVCC snapshot), having a serializable transaction
> open prevents other concurrent serializable transactions and their
> predicate locks from being cleaned up. Since VACUUM on a large
> table can take a long time, this could affect many concurrent
> transactions.
> My one-liner fix for this was to set
>   DefaultXactIsoLevel = XACT_READ_COMMITTED;
> in AutoVacWorkerMain.
Ouch!  I think this needs to be considered a significant bug!  Is it
too late to get this into 9.1.2?

In response to

pgsql-hackers by date

Next:From: Andres FreundDate: 2011-11-29 22:00:50
Subject: Re: Avoiding repeated snapshot computation
Previous:From: Dan PortsDate: 2011-11-29 21:44:28
Subject: autovacuum and default_transaction_isolation

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group