Обсуждение: Allowing multiple DDL commands to run simultaneously

We use the word CONCURRENTLY to describe DDL that executes without
preventing select, insert, update or delete against a table. That is
not the topic discussed here.

I've been asked if we could consider allowing more types of DDL to run
at the same time as each other. Specifically, that all/most of the DDL
commands that currently take ShareUpdateExclusive lock should be able
to run side-by-side with each other, i.e. any of the following could
run together

VACUUM (without FULL), ANALYZE, CREATE/DROP INDEX CONCURRENTLY, CREATE
STATISTICS, ALTER TABLE VALIDATE, ALTER TABLE(options etc).

So you would be able to run a VACUUM while an ALTER TABLE VALIDATE was
running, or run an ANALYZE while running CREATE INDEX CONCURRENTLY.

The main blocker to this is that we hold ShareUpdateExclusiveLock on
each command until the end of the transaction.

Obviously there are places we need locks, such as the part of VACUUM
that reads the indexes does need a lock for the duration of the index
scan. The proposal here is to minimize the duration of those locks,
e.g. make VACUUM lock each index in turn and then drop the lock before
moving to the next index, so a DROP INDEX CONCURRENTLY would only be
blocked by a VACUUM if it was scanning that particular index at the
time of the command, and only for the duration of that scan. No doubt
other cases exist.

Removing such locks from the VACUUM/ANALYZE path would prevent autovac
cancelling itself when other forms of DDL are executed.

Proposal would be to add a new lock mode "ShareUpdate", which does not
conflict with itself and yet conflicts with "ShareUpdateExclusive" or
higher. (Hence, it is a strong lock type). DDL would take a
ShareUpdateLock on the table, then during critical portions of
commands it would take a ShareUpdateExclusiveLock and then release it
again before commit.

We would still want to prevent two VACUUMs or two ANALYZEs from
running concurrently, so we would still need a lock mechanism to
prevent that. I'll leave that implementation point open for now since
it could lead to sidetracking away from the main idea.

Implementation would be to introduce the new infrastructure, then make
it work for the VACUUM/CREATE INDEX CONCURRENTLY case, then work
through other commands one by one.

Thoughts?

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Jul 9, 2018 at 6:00 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Proposal would be to add a new lock mode "ShareUpdate", which does not
> conflict with itself and yet conflicts with "ShareUpdateExclusive" or
> higher. (Hence, it is a strong lock type). DDL would take a
> ShareUpdateLock on the table, then during critical portions of
> commands it would take a ShareUpdateExclusiveLock and then release it
> again before commit.

I think this would be quite prone to deadlocks.  Suppose someone tries
to grab an AccessExclusiveLock on the table during a window in which
we hold only ShareUpdateLock.  The next attempt to upgrade to
ShareUpdateExclusiveLock will cause a simple deadlock.  In general,
any approach that involves upgrading our lock strength is likely to
have this problem.

You might be able to work around this by inventing a whole new lock
type, say "Relation Maintenance".  Make a rule that you can only take
the "Relation Maintenance" lock while holding a Relation lock with
strength >= ShareUpdateLock and that you do not need to bother
acquiring it if you hold a self-exclusive lock that conflicts with
ShareUpdateLock.  I think that works out to about the same thing as
what you're proposing, except without the deadlock hazard.

In general, though, +1 for trying to do something about this.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 17 July 2018 at 19:47, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Jul 9, 2018 at 6:00 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> Proposal would be to add a new lock mode "ShareUpdate", which does not
>> conflict with itself and yet conflicts with "ShareUpdateExclusive" or
>> higher. (Hence, it is a strong lock type). DDL would take a
>> ShareUpdateLock on the table, then during critical portions of
>> commands it would take a ShareUpdateExclusiveLock and then release it
>> again before commit.
>
> I think this would be quite prone to deadlocks.  Suppose someone tries
> to grab an AccessExclusiveLock on the table during a window in which
> we hold only ShareUpdateLock.  The next attempt to upgrade to
> ShareUpdateExclusiveLock will cause a simple deadlock.  In general,
> any approach that involves upgrading our lock strength is likely to
> have this problem.
>
> You might be able to work around this by inventing a whole new lock
> type, say "Relation Maintenance".  Make a rule that you can only take
> the "Relation Maintenance" lock while holding a Relation lock with
> strength >= ShareUpdateLock and that you do not need to bother
> acquiring it if you hold a self-exclusive lock that conflicts with
> ShareUpdateLock.  I think that works out to about the same thing as
> what you're proposing, except without the deadlock hazard.

Yes, it seems better to invent a new orthogonal lock type than have a
new lock level. Thanks.

Seems more like a critical section than a lock.

I'd make code take that lock, even if they have a self-exclusive lock,
just to avoid later problems when the lock level changes.

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services