Re: Concurrency question

Scott Marlowe <scott.marlowe@gmail.com> writes:
> 2009/7/7 Mark Steben <msteben@autorevenue.com>:
>> I ran a vacuum verbose analyze on a database over the weekend. �It ran fine
>> until it tried to vacuum a table less than 2000 pages. �It successfully
>> acquired a ShareUpdateExclusiveLock as I would expect.
>> There was an idle thread that had an AccessSharelock on the same table.
>> Compatible locks I would think. But the vacuum hung until the
>> AccessSharelock thread was cancelled - 11 hours in all.
>> This table normally vacuums in less than 15 seconds. � This AccessSharelock
>> came from a query that formerly was part of a transaction sent from a remote
>> server.

> Not sure what you mean by formerly was part of a transaction.  If the
> transaction has rolled back, then the vacuum can proceed.  If the
> transaction is till open, then it's not formerly a part of it, it IS a
> part of it.  Either way, open transactions block vacuum on updated
> tables.

Uh, no, they don't.

The described situation is impossible: AccessSharelock doesn't block
ShareUpdateExclusiveLock.  There must have been some other lock or
attempted lock involved (perhaps at a page or tuple level rather than
the whole-relation level).  But we can't tell much from this much detail.

            regards, tom lane