Re: Revised Patch to allow multiple table locks in "Unison" - Mailing list pgsql-patches

Bruce Momjian wrote:

> I have been thinking about this too.  I have two ideas.
>
> One, we could have you sleep on the lock, and when you get it, release
> it and then start acquiring the locks in the order specified again.  You
> could lose the lock by the time you get back to the table you had a lock
> on, but I think this reduces the chances of getting in a loop with
> others.
>

I think this could work. But I worry it makes starvation even more
likely. This might be acceptable if a "passive" lock grabber is what we
want to have here, though.

One idea I've been floating is to allow some syntax whereby multiple
locks can be grabbed in either this manner, that is a passive manner, or
instead in an aggressive manner (i.e. grab locks in order, and hold them
-- in other words LOCK a,b; -> LOCK a; LOCK b;). This could be done by
means of some additional keywords, perhaps "WITH AGGRESSIVE" or "WITH
PASSIVE", or something to this effect. This shifts some of the burden to
the database programmer, with regards to trading off throughput for
fairness.

> Another idea is to change the lock code so instead of returning a lock
> failure on first try, it goes to sleep for DEADLOCK seconds, and once it
> wakes up, and determines there is no deadlock, returns a lock failure.
> That way, it can release all the locks and do a non-timeout lock on the
> table that failed.  We would then need to release the lock and do the
> steps outlined above.

This is interesting. I'd like to hear what other people think about
this.

>
> One advantage of this last idea is that it would make multi-table lock
> better in cases where there is more than one table that is high-use
> because we are waiting a little to see if we get the lock before
> failing.  The downside is that we hold the previously aquired locks
> longer.  I think I can help you modify the lock manager to do the delay.

In other words it sounds like you are making a tradeoff for greater
throughput in exchange for possibly reduced concurrency. This can be a
design decision on our part, and might be a reasonable thing to do. How
hard do you think it will be to tune the DEADLOCK timer to a reasonable
value? Would it have to vary based on load? This could be as simple as
having the timeout could elapse early, if a certain number of lock
attempts are registered by the lock manager while the backend is
sleeping.

> I know it is frustrating to develop a patch and then have to contort it
> to meet everyones ideas, but in the long run, it makes for better code
> and a more reliable database.

I think I can agree that a reliable database with good code is what
everyone wants!

Neil

--
Neil Padgett
Red Hat Canada Ltd.                       E-Mail:  npadgett@redhat.com
2323 Yonge Street, Suite #300,
Toronto, ON  M4P 2C9