Re: Reducing relation locking overhead

Tom Lane wrote:
> Kevin Brown <kevin(at)sysexperts(dot)com> writes:
> > - when requesting a type of lock, one must first acquire all lesser
> > lock types against the object in order of strength. Hence, one must
> > acquire AccessShareLock before acquiring AccessExclusiveLock.
>
> This is exactly wrong ...

And now I see why, since it will introduce deadlocks (sigh). But what
of the other rule (always acquiring locks against the table before the
index)? You may have stopped reading at the above...

One thing I don't quite understand about the discussion is why there's
particular attention being paid to deadlocks with respect to REINDEX
when it clearly can happen in the general case when lock promotion is
involved. Why is REINDEX special here?

If the problem is that REINDEX has to hold an AccessShareLock to
prevent the table or index from being dropped, but does not need to
prevent writers in general (because the presumption is that there is
some way of efficiently discovering the addtional modifications made
during the bulk of REINDEX processing), then doesn't that mean that an
AccessShareLock is the wrong kind of lock for REINDEX to be holding,
and that the appropriate type of lock should be created if it doesn't
already exist?

Additionally, I was under the impression that normal INSERTs, UPDATEs,
and DELETEs didn't generally need to acquire AccessExclusiveLock,
because of MVCC. If that's the case, then aren't the operations that
could deadlock REINDEX relatively rare? And if those operations *do*
need to acquire that lock type, then what exactly does MVCC buy you?

--
Kevin Brown kevin(at)sysexperts(dot)com