Re: What Causes Access Exclusive Lock?

On Fri, 24 Jun 2016, 1:54 a.m. Sameer Kumar, <sameer(dot)kumar(at)ashnik(dot)com>
wrote:

>
>
> 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.
>

Thanks!
I could reproduce this.

The test setup-

1. I have master and standby databases. To get the error I reduced my
max_streaming_delay to 10s
2. On standby start a new transaction and read data from a very huge table

Begin transaction;
Select count(*) from table_with10k_rows;

3. On master delete rows from the bottom of this table (i.e. the rows
inserted last)

4. Run a vacuum on the table in master (normal vacuum).

5. Go back to the transaction on standby, fire
Select 1;

6. You will see session is disconnected

I repeated this a few times and if I don't run vacuum manually (and wait
for a while) autovacuum would fire and results in similar situation.

I repeated the same steps with REPEATABLE READ isolation level on standby
transaction and I got SQLSTATE 40001 but with detail "User Query might have
needed to see riw versions that must be removed". I have
hot_standby_feedback on.

Thanks!

> 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
>
--
--
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