Thread: locks within select
Hi,
To my understanding, a select on a table does acquire an access share lock on that table.
my question is: is there any other kind of lock that a select may acquire ?
obviously, all explicit locking schemes like select for update, etc... is out of this question scope.
I was wondering if for example, within a join, some kind of lock may be acquired so that the dataset concerned is not changed during execution ? (for example a delete then autovacuum ??)
thanks
On Monday, December 13, 2021, Marc Millas <marc.millas@mokadb.com> wrote:
I was wondering if for example, within a join, some kind of lock may be acquired so that the dataset concerned is not changed during execution ? (for example a delete then autovacuum ??)
Read this primer on MVCC from the docs:
David J.
I did read this, before asking my question...
but... when you do setup a streaming replication, there is, in postgresql.conf, a variable:
max_standby_streaming_delay = 30s # max delay before canceling queries when reading streaming WAL;
as the secondary is, by nature, read only, I was wondering why it can be necessary to cancel a read request...
So, I read another time some locking doc, which states that the access share lock positioned by a read request DO block demands of exclusive locking
like vacuum full, table reorg, etc. thus blocking the streaming replication flow.
also, on the real world POV, while monitoring streaming replication lag, I see lags of 90 seconds, quite frequently.
and I was guessing what can block the streaming flow for that long.
--neither cpu nor network nor I/O looks saturated
--as its a production instance, and we dont do vacuum full or table reorg or... during business hours, obviously !
--but a bunch of read request are sent to the secondary, some of them a bit big in terms of multiple join, etc..
hence my original question.
So, if its not a locking scheme, what can block hundreds of transaction to get to the secondary ???
On Tue, Dec 14, 2021 at 7:29 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, December 13, 2021, Marc Millas <marc.millas@mokadb.com> wrote:I was wondering if for example, within a join, some kind of lock may be acquired so that the dataset concerned is not changed during execution ? (for example a delete then autovacuum ??)Read this primer on MVCC from the docs:David J.
On Tuesday, December 14, 2021, Marc Millas <marc.millas@mokadb.com> wrote:
So, if its not a locking scheme, what can block hundreds of transaction to get to the secondary ???
The transactions are present on the secondary. It just realizes that applying them may cause problems (think drop table or truncate) and so it chooses not to until local queries have completed. Since WAL is strictly linear all sessions are affected. There is no locking needed, or IIUC even possible, and MVCC is not applicable as it only concerns itself with the behavior lf the writable primary system.
I suspect there are better attempts at describing this than my half-conscious effort here, though I’ve captured the crux of it - streaming WAL is its own unique thing.
David J.
On Tue, 2021-12-14 at 13:38 +0100, Marc Millas wrote: > but... when you do setup a streaming replication, there is, in postgresql.conf, a variable: > max_standby_streaming_delay = 30s # max delay before canceling queries when reading streaming WAL; > > as the secondary is, by nature, read only, I was wondering why it can be necessary to cancel a read request... Perhaps this can explain some of what is going on: https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Excellent !
thanks a lot, it was exactly what I was looking for.
On Tue, Dec 14, 2021 at 6:14 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2021-12-14 at 13:38 +0100, Marc Millas wrote:
> but... when you do setup a streaming replication, there is, in postgresql.conf, a variable:
> max_standby_streaming_delay = 30s # max delay before canceling queries when reading streaming WAL;
>
> as the secondary is, by nature, read only, I was wondering why it can be necessary to cancel a read request...
Perhaps this can explain some of what is going on:
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com