Re: do foreign key checks lock parent table ?

swalker(at)iglou(dot)com wrote:
>
> If you try the example I have below - the second transaction will block
> waiting for the first one to complete. This doesn't seem correct to me.
> Is this a known problem / feature? If I create the table w/ a deferrable
> intially deferred foreign key - I don't get the problem. But this is a
> very unexpected default behavior ( At least to me :) ).

It is a known 'requirement' (read minor problem), because in
order to prevent someone else from removing the PK row, your
transaction needs to place a shared read lock at least. Now
PostgreSQL does not have this sort of shared read lock, so
the lightest lock a FK trigger can set is one FOR UPDATE.

The fact that this behaviour is annoying to you is somehow
suspicious (at least to me :). Does your application try to
hold transactions across user interaction? If so, locking
issues will not be your biggest problem, so don't worry about
them too much.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #