On Wed, 13 Nov 2002, Peter Schindler wrote:
> But, if a lot of inserts happens into the child table and there is a
> mix of short and long running transactions, the likelihood of blocking
> is very high, even the inserts are independent and everything is ok
> (prim. key etc.). This is even more extreme, the smaller parent table
> is.
>
> FYI, I've tried the same with Oracle and there is no such problem. The
> insert in the second session will come back immediately without
> blocking, though it will still maintain the integrity from other txns.
>
> I wonder if there is a lower level way to maintain the locking and
> having the same behavior as oracle. So, instead of using a "SELECT ...
> FOR UPDATE", using some pg function to lock a row with a different
> mode?
I've been working on something of the sort. I've got a test patch
(against about 7.3b2) that I'm trying to validate which cases it does and
does not work for. I'm still looking for more volunteers if you've got a
dev system you're willing to use. :)
Right now, I know that it has a hole that lets through invalid data in one
case that it got while trying to fix a deadlock case. Hopefully in the
next week or so I'll have figured out a way around it.