Re: DeadLocks..., DeadLocks...

Tom Allison wrote:
> Terry Fielder wrote:
> >
> >My 2 cents:
> >
> >I used to get a lot of these sharelock problems.
> >Users using different records, but same tables in different order.
> >(apparently 7.x was not as good as 8.x at row level locking)
> >
> >I was advised to upgrade from 7.x to 8.x
> >I did, and all those sharelock problems went away.
>
> I'm on version 8.2 and not all the problems have gone away.

Right -- the problems that went away were those where the FK locks were
conflicting with other FK locks. This has been solved by making the FK
lock be shared instead of exclusive. The case you have here is
different: the FK lock is conflicting with an UPDATE or DELETE lock. So
even if the FK lock is now shared, the other lock is still exclusive,
and conflicts with the shared lock so eventually there is a deadlock.

> All I can do right now is just trap the error and retry...
> Gets bogged down after a while. Not sure how much of a limitation the
> hardware is but 6 users and I start to run into a deadlock almost every 10
> seconds.

To solve this problem we would have to rearchitect a whole lot of the FK
code and tuple locks, so don't hold your breath. Searching for
alternative solutions would be a good idea; for example trying to avoid
the UPDATEs whenever possible.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
One man's impedance mismatch is another man's layer of abstraction.
(Lincoln Yeoh)