Re: Reducing relation locking overhead

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.

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. The fact that you've been
holding the AccessShareLock for quite a long time means that the window
for deadlock problems is very wide.

regards, tom lane