Re: Reducing relation locking overhead

On Sun, Dec 04, 2005 at 10:40:55PM -0800, Kevin Brown wrote:
> 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?

Although there is a general case, I don't believe it is a common case.
INSERT creates the rows invisible to other transactions. UPDATE and
DELETE would lock the row for writing. Normally, lock promotion isn't
required.

If, however, a person locks the row for reading using SELECT, and
then attempts to upgrade the lock by SELECT, UPDATE, or DELETE,
this would result in a lock promotion.

> 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?

I think the problem is the 'efficiently discovering the additional
modifications during' REINDEX processing. A few ideas have been
floating around - but none proven. Even the (rather clever in my
opinion) solution that would create the index incomplete, allowing
new updates to be written to the index automatically, while REINDEX
fills in the rest of it in the background before marking it
complete, still has problems. If it is a new unique index, then
for a time, the unique constraint would not be enforced.

I think it has to be tweaked some, such that the new incomplete
index would be created along-side the other index, and once the
indexes match up, remove the original, and rename the new one
into place.

> 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?

REINDEX needs to see visible and invisible rows. This goes back to the
previous point. Efficiently and reliably discovering newly created rows.

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/