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/
