Re: What Causes Access Exclusive Lock?
| От | Sameer Kumar |
|---|---|
| Тема | Re: What Causes Access Exclusive Lock? |
| Дата | |
| Msg-id | CADp-Sm7hktGDjLvMAExy5G_8H-PuFvY9fMsc6YsaLjcbK431vA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: What Causes Access Exclusive Lock? (Sameer Kumar <sameer.kumar@ashnik.com>) |
| Список | pgsql-general |
On Fri, 24 Jun 2016, 1:54 a.m. Sameer Kumar, <sameer.kumar@ashnik.com> wrote:
On Fri, 24 Jun 2016, 1:47 a.m. Jeff Janes, <jeff.janes@gmail.com> wrote:On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar <sameer.kumar@ashnik.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
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 RegardsSameer Kumar | DB Solution ArchitectASHNIK 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
В списке pgsql-general по дате отправления: