Re: Is there a problem running vacuum in the middle of a transaction?

Doug McNaught <doug(at)wireboard(dot)com> writes:
> Hmmm--AFAIK, VACUUM is supposed to grab locks on the tables it
> processes, which will block until all open transactions against that
> table are finished. So either VACUUM or your transactions will have
> to wait, but they shouldn't interfere with each other.

Well, it's uglier than that. Normally, read and write locks are not
mutually exclusive, so if you have a client that is holding an open
transaction and not doing anything, it doesn't matter if it read or
wrote a table earlier in the transaction. Other clients can proceed
to read or write that table despite the existence of a lock owned by
the open transaction.

But VACUUM wants an exclusive lock on the table, so it will block
until all clients holding read or write locks commit. Once VACUUM has
blocked, subsequent read or write requests also block, because they
queue up behind the VACUUM exclusive-lock request. (We could allow
them to go in front, but that would create the likelihood that VACUUM
could *never* get its lock, in the face of a steady stream of read
or write lockers.)

Upshot: a client holding an open transaction, plus another client trying
to do VACUUM, can clog up the database for everyone else.

Restarting the whole database is severe overreaction; aborting the
transaction of either of the clients at fault would be sufficient to
clear the logjam.

7.2 will be less prone to this problem, since the default form of VACUUM
in 7.2 will not require exclusive lock. But you'd still see it if you
have some clients that want to acquire exclusive table locks for some
reason. Bottom line is that dawdling around with an open transaction is
bad form if you have a heavily concurrent application. Once you've done
something, you should commit or roll back within a reasonably short
interval.

regards, tom lane