Re: What Causes Access Exclusive Lock?

On Fri, 24 Jun 2016, 1:47 a.m. Jeff Janes, <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
> wrote:
> >
> > Hi,
> >
> > I just wanted to understand what are the commands which will acquire
> Access
> > Exclusive Lock on a table? In my knowledge below operations will acquire
> > access exclusive lock:-
> >
> > 1. VACUUM FULL
> > 2. ALTER TABLE
> > 3. DROP TABLE
> > 4. TRUNCATE
> > 5. REINDEX
> > 6. LOCK command with Access Exclusive Mode (or no mode specified)
> >
> > I am using PostgreSQL v9.4.
>
> A regular VACUUM (not a FULL one), including autovac, will take an
> ACCESS EXCLUSIVE lock if it believes there are enough empty
> (truncatable) pages at the end of the table to be worth truncating and
> returning that storage to the OS. On master it will quickly abandon
> the lock if it detects someone else wants it, but that does not work
> on a standby.
>

Thanks! This is helpful. I believe going by this explaination I can try to
reproduce this issue manually.

Is this part about regular vacuum acquiring an AccessExclusive Lock
documented? I did not see a reference to it on page for Explicit Locking.

> Before version 9.6, if there are bunch of all-visible (but non-empty)
> pages at the end of the table, then every vacuum will think it can
> possibly truncate those pages, take the lock, and immediately realize
> it can't truncate anything and release the lock. On master, this is
> harmless, but on a standby it can lead to spurious cancellations. In
> 9.6, we made it check those pages to see if they actually are
> truncatable before it takes the lock, then check again after it has
> the lock to make sure they are still truncatable. That should greatly
> decrease the occurrence of such cancellations.
>
>
> Cheers,
>
> Jeff
>
--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com