Re: Reducing relation locking overhead

Tom Lane wrote:
> Kevin Brown <kevin(at)sysexperts(dot)com> writes:
> > Tom Lane wrote:
> >> Even ignoring that, you *still* have a lock upgrade problem
> >> in this sketch.
>
> > Hmm, well, I can see a deadlock potential for those operations that
> > have to acquire multiple locks simultaneously, and I suppose that the
> > table + FSM lock would qualify in the sequence here, but the rest of
> > it involves just a single read lock against the table. What am I
> > missing?
>
> At some point you have to lock out writers, else you can never be
> certain you have all the tuples. I was taking your "read lock" to
> actually mean "lock out writers"; otherwise the sketch doesn't work
> at all.

Right, but the idea is to lock out writers for as brief a time as
possible. That not only minimizes the possibility of lock contention
but guarantees that REINDEX will get a complete view of the database.

That said, it hinges on some sort of efficient way of identifying the
new tuples created by other transactions that are/were running during
the bulk of the time RINDEX was running. If there's no good way to do
that, then there's no way to avoid blocking writers for an extended
period of time.

> The real situation is that you must hold at least AccessShareLock on the
> table throughout the entire operation, else you have no defense against
> (say) someone dropping the index or the entire table out from under you.
> And when you add onto this lock in order to lock out writers
> temporarily, you are engaging in a lock upgrade, which can deadlock
> against any sort of exclusive lock request.

But won't that depend on the order in which the lock requests appear?
If locks A, B, and C are taken in that same order by every
transaction, then there's no possibility of deadlock, right?

> The fact that you've been holding the AccessShareLock for quite a
> long time means that the window for deadlock problems is very wide.

But with respect to deadlocks, that's true only if deadlocks are
possible, which is true only if the order of lock acquisition differs
between transactions.

I guess the real question here is: is it possible to, in code,
guarantee the order of lock acquisition by any given transaction? For
REINDEX, the problem is simplified somewhat because it's operating
against a single index and a single table, so the locks it has to
acquire are somewhat limited in scope compared with a generic
transaction.

An endeavor to acquire all locks in the same order throughout the code
would not only take care of this REINDEX deadlock problem but would
essentially eliminate all possible deadlocks arising from code-ordered
lock acquisition in the system, which I expect would be considered a
very good thing indeed. But I expect it would be a lot of effort and
wouldn't be worth it just to make REINDEX behave differently than it
does now.

So what am I missing/overlooking here?

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