Re: autovacuum and default_transaction_isolation - Mailing list pgsql-hackers

From Kevin Grittner
Subject Re: autovacuum and default_transaction_isolation
Date
Msg-id 4ED5004902000025000435FE@gw.wicourts.gov
Whole thread Raw
In response to autovacuum and default_transaction_isolation  (Dan Ports <drkp@csail.mit.edu>)
List pgsql-hackers
Dan Ports <drkp@csail.mit.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?
-Kevin


pgsql-hackers by date:

Previous
From: Dan Ports
Date:
Subject: autovacuum and default_transaction_isolation
Next
From: Andres Freund
Date:
Subject: Re: Avoiding repeated snapshot computation