Обсуждение: What Causes Access Exclusive Lock?
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.
Regards
Sameer
--
--
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
On 06/23/2016 08:14 AM, Sameer Kumar 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. https://www.postgresql.org/docs/9.4/static/explicit-locking.html ACCESS EXCLUSIVE > > > > Regards > Sameer > -- > -- > 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 > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Jun 23, 2016 at 11:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 06/23/2016 08:14 AM, Sameer Kumar 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.
https://www.postgresql.org/docs/9.4/static/explicit-locking.html
ACCESS EXCLUSIVE
Thanks!
I had checked that and arrived at the list above.
Why I wanted to confirm because, I am facing a situation similar (or rather same) as what is described in two threads below-
pg_stat_database_conflicts.confl_lock is *non-zero* and connections on standby (idle in transaction or executing SELECT) are disconnected.
I *do not* see the message -
"User query might have needed to see row versions that must be removed."
But I see disconnection on standby because of a "relation lock" being held for long.
From what I understood that if there is a LOCK conflict on standby (between a session an a WAL replay), it might cause even cause disconnection of an "idle in transaction" session (which is causing conflict on standby). Is this right?
My understanding is only Access Exclusive Locks will cause conflicts against a read-only query. Is that right?
So I checked and confirmed that there is no such operation on master which would result in Access Exclusive lock.
I am using v9.4.4. Is there a bug which is hitting me or is there any other kind of query which might cause lock conflict on standby?
The threads above seem to have same issue, but I did not see any conclusive reason explained in the hacker or admin thread.
>
>
>
> Regards
> Sameer
> --
> --
> 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
>
--
Adrian Klaver
adrian.klaver@aklaver.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
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. 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
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.
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
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
On Thu, Jun 23, 2016 at 10:54 AM, 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. > > Is this part about regular vacuum acquiring an AccessExclusive Lock > documented? I did not see a reference to it on page for Explicit Locking. Not that I know of. I don't think any part of the user documentation attempts to make an exhaustive list of all actions which take which level of locks. It only provides some illustrative examples. Cheers, Jeff
On Fri, 24 Jun 2016, 6:23 a.m. Jeff Janes, <jeff.janes@gmail.com> wrote:
On Thu, Jun 23, 2016 at 10:54 AM, 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.
>
> Is this part about regular vacuum acquiring an AccessExclusive Lock
> documented? I did not see a reference to it on page for Explicit Locking.
Not that I know of. I don't think any part of the user documentation
attempts to make an exhaustive list of all actions which take which
level of locks. It only provides some illustrative examples.
Thanks!
But is it something which is worth mentioning on the page about VACUUM?
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