Re: LOCK TABLE .. DEFERRABLE

On 5 April 2016 at 18:34, Rod Taylor <rod(dot)taylor(at)gmail(dot)com> wrote:
>
>
> On Tue, Apr 5, 2016 at 1:10 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>
>>> If a lock is successfully obtained on one table, but not on all tables,
>>> it releases that lock and will retry to get them as a group in the future.
>>> Since inheritance acts as a group of tables (top + recursive cascade to
>>> children), this implementation is necessary even if only a single table is
>>> specified in the command.
>>
>>
>> I'd prefer to see this as a lock wait mode where it sits in the normal
>> lock queue BUT other lock requestors are allowed to queue jump past it. That
>> should be just a few lines changed in the lock conflict checker and some
>> sleight of hand in the lock queue code.
>>
>> That way we avoid the busy-wait loop and multiple DEFERRABLE lock waiters
>> queue up normally.
>
>
> Yeah, that would be better. I can see how to handle a single structure in
> that way but I'm not at all certain how to handle multiple tables and
> inheritance is multiple tables even with a single command.

Agreed; neither can I.

> X1 inherits from X
>
> There is a long-running task on X1.
>
> Someone requests LOCK TABLE X IN ACCESS EXCLUSIVE MODE WAIT PATIENTLY.
> Internally this also grabs X1.
>
> The lock on X might be granted immediately and now blocks all other access
> to that table.
>
> There would need be a Locking Group kind of thing so various LockTags are
> treated as a single entity to grant them simultaneously. That seems pretty
> invasive; at least I don't see anything like that today.

Multiple locktags would likely be behind different LWLocks anyway, so
I don't see a way to make that work.

So the only way to handle multiple locks is to do this roughly the way
Rod suggests.

The only thing I would add at this stage is that if one lock is
unavailable, unlocking all previous locks is unnecessary. We only need
to unlock if there are lock waiters for the locks we already hold.

The use cases I am thinking of require only one table at a time, so
I'm still inclined towards the non-looping approach.

Thoughts?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services