Re: Reducing relation locking overhead

Tom Lane wrote:
> Kevin Brown <kevin(at)sysexperts(dot)com> writes:
> > I guess the real question here is: is it possible to, in code,
> > guarantee the order of lock acquisition by any given transaction?
>
> Yes, but not in our code :-(. This is largely determined by what the
> application does.

Yeah, that's what I figured.

But what of the more limited problem of lock acquisition relative to
the locks that REINDEX might need to acquire? Since those locks are
limited to a single table and a single index, I'd think the problem
wouldn't be insurmountable. No?

Suppose the following rules were enforced in the code:

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

- one must always acquire a given lock type against the table before
acquiring it against the index.

The latter rule might be sufficient, if the former rule is already
implied by the lock types and how they're acquired.

Thus, acquisition of AccessExclusiveLock against the index
automatically implies acquisition of AccessShareLock(table), then
AccessShareLock(index), then AccessExclusiveLock(table), then
AccessExclusiveLock(index).

I could see that possibly causing performance problems (and would be
interested in knowing what performance problems it would likely
cause), but given the limited scope of the locks that REINDEX needs,
wouldn't the above be sufficient to keep REINDEX deadlock-free against
other transactions?

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