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

autovacuum and default_transaction_isolation

From: Dan Ports <drkp(at)csail(dot)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: autovacuum and default_transaction_isolation
Date: 2011-11-29 21:44:28
Message-ID: 20111129214428.GD24208@csail.mit.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

Dan

-- 
Dan R. K. Ports              MIT CSAIL                http://drkp.net/

Responses

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2011-11-29 21:54:49
Subject: Re: autovacuum and default_transaction_isolation
Previous:From: Greg JaskiewiczDate: 2011-11-29 21:43:40
Subject: Re: Inlining comparators as a performance optimisation

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