Thanks again Greg, I really appreciated all information.
On Friday, February 2, 2024 at 08:16:41 p.m. EST, Greg Sabino Mullane
<[email protected]> wrote:
On Fri, Feb 2, 2024 at 3:25 PM abrahim abrahao <[email protected]> wrote:
SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) should not be blocked by
ACCESS SHARE (AccessShareLock). Am I wrong about it? If I am not wrong why it
still locking it?
Those locks with no other context are somewhat of a red herring. The important
part is not that the AccessShare is somehow blocking ShareUpdateExclusive, but
that the ShareUpdateExclusive process is NOT blocking new AccessShare
processes! In the internals of postgres, vacuumlazy.c tries to grab a buffer
lock (different concept from all the user-visible locks above). It politely
tries to wait[1] until nobody else is grabbing it (aka pinning it), then
proceeds. The problem is that other processes are allowed to come along and put
a pin in it as well - the vacuum's shareupdateexclusive lock does not prevent
that.
So the timeline is:
Process X runs a long select and pins the buffer
Process V runs a vacuum freeze and tries to lock the buffer. It detects other
pins, so it waits. It assumes that whoever is holding the pin will release it
someday.Process Y runs another long select and also pins the buffer.Process X
ends, and removes its pins.Process V still cannot move - it just knows there
are still pins. Where they come from does not matter.
As long as there is at least one other process holding a pin, the vacuum freeze
cannot continue[2].
That's my understanding of the code, anyway. This could be argued as a bug. I
am not sure what a solution would be. Cancelling user queries just for a vacuum
would not be cool, but we could maybe pause future pin-creating actions somehow?
For the time being, forcing a super-quick moment of no table access would seem
to be your best bet, as described earlier.
Cheers,Greg
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/vacuumlazy.c;hb=HEAD#l975
See backend/storage/buffer/bufmgr.c for LockBufferForCleanup()
[2] Quick duplication script:drop table if exists foobar;
create table foobar as select 1 as id;
alter table foobar set (autovacuum_enabled = off);
update foobar set id = id;
Process 1:begin; select *, pg_sleep(111111) from foobar;
Process 2:vacuum(freeze,verbose) foobar; /* blocked */
Process 3:begin; select *, pg_sleep(333333) from foobar;
Run in order. Kill Process 1 and Process 2 is still blocked. Kill Process 3 and
Process 2 finished the vacuum.Note that a regular vacuum (without a freeze)
will not get blocked.
Cheers,Greg
