> Well, it seems to me that there are a number of places this can be taken
> then:
>
> 1. Don't worry about these cases and apply the patch anyways.
> The patch will work correctly in most real world situations.
>
> 2. Add a counter / timer to detect livelock / alternating deadlock
> conditions. If the counter / timer elapses, abort the offending
> transaction (i.e. the one doing the multiple locking) and roll back.
> The patch then will always work correctly, AFAICT.
>
> 3. Go with the original patch I posted.
> Aside from not locking in user order (which is a debatable issue), this
> patch seems correct.
>
> 4. Go with the original patch but sans OID sort. In this case, LOCK a,b;
> degrades into a short form for LOCK a; LOCk b; This is still useful for
> Oracle compatibility.
> Satisfies the TODO list item.
>
> 5. Go with a major Lock manager overhaul. (which would be added to the
> TODO list.) Defer this functionality until then.
> A lock manager overhaul will likely take a while so probably it won't
> be done for some time. This means the multiple lock syntax will continue
> to be missing from PostgreSQL, possibly for several years.
>
> Personally, I think 1 is acceptable, and 2 is a better idea. 3/4 are
> also
> doable, but lose some of the advantages of the command. 5 is reasonable,
> but disappointing, especially from a user standpoint.
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.
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.
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.
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.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
