patternsqlMinor
What exactly is "only a read lock on the target table"
Viewed 0 times
thetargetwhatreadexactlyonlytablelock
Problem
I'm trying to find information on whether it's safe to run
The PG documentation states:
ANALYZE requires only a read lock on the target table, so it can run
in parallel with other activity on the table.
What exactly is this "other activity" that can run in parallel if a read lock is acquired? Only other reads?
I then tried looking at the PG documentation to find what exactly a "read lock" is, and the docs for table locks don't event mention a "read lock" existing, but use some completely different terminology like "ACCESS SHARE", "ROW SHARE", etc.
Additionally, a few independent articles I found all mention that it's safe to use because it doesn't lock, and then proceed to quote the docs where it's clearly stated that it does lock.
I'm sure I'm missing something here. Does this "read lock" only prevent definition changes to the table while it's being analyzed (like a schema lock)?
ANALYZE on a large table that has stale statistics. The table is accessed in an "append-only" fashion and is constantly written to by numerous writers, so I'm concerned whether analyzing the table can block those writers for the duration of ANALYZE.The PG documentation states:
ANALYZE requires only a read lock on the target table, so it can run
in parallel with other activity on the table.
What exactly is this "other activity" that can run in parallel if a read lock is acquired? Only other reads?
I then tried looking at the PG documentation to find what exactly a "read lock" is, and the docs for table locks don't event mention a "read lock" existing, but use some completely different terminology like "ACCESS SHARE", "ROW SHARE", etc.
Additionally, a few independent articles I found all mention that it's safe to use because it doesn't lock, and then proceed to quote the docs where it's clearly stated that it does lock.
I'm sure I'm missing something here. Does this "read lock" only prevent definition changes to the table while it's being analyzed (like a schema lock)?
Solution
The answer, as is often the case, can be found in the source code for the
and similarly for the
We can then find in the documentation this fragment:
Conflicts with the
protects a table against concurrent schema changes and
Acquired by
certain
documentation of these commands).
VACUUM command: * Determine the type of lock we want --- hard exclusive lock for a FULL
* vacuum, but just ShareUpdateExclusiveLock for concurrent vacuum. Either
* way, we can be sure that no other backend is vacuuming the same table.
and similarly for the
ANALYZE command: * Open the relation, getting ShareUpdateExclusiveLock to ensure that two
* ANALYZEs don't run on it concurrently.
We can then find in the documentation this fragment:
SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)Conflicts with the
SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This modeprotects a table against concurrent schema changes and
VACUUM runs.Acquired by
VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, andcertain
ALTER INDEX and ALTER TABLE variants (for full details see thedocumentation of these commands).
Context
StackExchange Database Administrators Q#321032, answer score: 2
Revisions (0)
No revisions yet.