Re: do foreign key checks lock parent table ?

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 :) ).

create table parent
(key integer primary key,
description varchar(50) not null);
create table child
(key integer primary key,
child_key integer not null references parent(key),
description varchar(50) not null);

insert into parent values (1,'Parent Record #1');
insert into child values (101,1,'Child Record #1');
insert into child values (102,1,'Child Record #2');

// From one psql
begin;
update child set child_key = 1 where key = 101;

//second session
begin;
update child set child_key = 1 where key = 102;

On Tue, 2 Apr 2002 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 :) ).
>
> create table parent
> (key integer primary key,
> description varchar(50) not null);
> create table child
> (key integer primary key,
> child_key integer not null references parent(key),
> description varchar(50) not null);
>
> insert into parent values (1,'Parent Record #1');
> insert into child values (101,1,'Child Record #1');
> insert into child values (102,1,'Child Record #2');
>
>
> // From one psql
> begin;
> update child set child_key = 1 where key = 101;
>
>
> //second session
> begin;
> update child set child_key = 1 where key = 102;

The locks being grabbed are a bit stronger than they need to be
(if you want more info, there was a bit of discussion on -hackers
recently). The updates should block updates on parent of the
referenced row but don't need to block other child updates but there's
no current lock level that is quite right.

So turning intially deferred on is a suitable solution? It seems
to work OK in this simple test case.

On Tue, 2 Apr 2002, Stephan Szabo wrote:
> The locks being grabbed are a bit stronger than they need to be
> (if you want more info, there was a bit of discussion on -hackers
> recently). The updates should block updates on parent of the
> referenced row but don't need to block other child updates but there's
> no current lock level that is quite right.

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 #

swalker(at)iglou(dot)com wrote:
>
> So turning intially deferred on is a suitable solution? It seems
> to work OK in this simple test case.

It might change the behaviour of your application. If you set
the constraints deferred, they aren't checked at insert time
and the PK's aren't locked then either. So after doing the
INSERT or UPDATE successfully, someone else can still remove
the primary key referenced in your rows without any problems.
When you commit your transaction then, the deferred
constraints will fail and the COMMIT results in a ROLLBACK.

Jan

>
> On Tue, 2 Apr 2002, Stephan Szabo wrote:
> > The locks being grabbed are a bit stronger than they need to be
> > (if you want more info, there was a bit of discussion on -hackers
> > recently). The updates should block updates on parent of the
> > referenced row but don't need to block other child updates but there's
> > no current lock level that is quite right.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--

#======================================================================#
# 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 #

On Wed, 3 Apr 2002 swalker(at)iglou(dot)com wrote:

>
> So turning intially deferred on is a suitable solution? It seems
> to work OK in this simple test case.

That'll hide this particular locking issue (since the locks are held
for much less time in that case). There are some issues with
initially deferred constraints right now in some cases if you're
doing actions on both tables in one transaction (like deleting a
pk row and then re-adding it).

>
> On Tue, 2 Apr 2002, Stephan Szabo wrote:
> > The locks being grabbed are a bit stronger than they need to be
> > (if you want more info, there was a bit of discussion on -hackers
> > recently). The updates should block updates on parent of the
> > referenced row but don't need to block other child updates but there's
> > no current lock level that is quite right.
>

We are not holding a transaction open.

But now that I understand how postgres is implementing fk constraints I
can see the problem.

Our table structure is bascially something like this.

order_header
- Just basic order info

order_detail
- A product
- and it's quantity

order_detail_status
- if an order detail has a quantity of 5 - there will be 5
order_detail_status rows in this table

So, when we insert a record - we go from the top down. But as the
products are made - a process marks each order_detail_status as complete.
Once all the order_detail_status's for a give order_header are complete -
the order_header status is updated to complete.

So, we are inserting from the top down - but updating from the bottom up.
But I didn't forsee this as a problem - because none of the status's
should overlap. Status's are for instance 'New Order', 'In progress',
'Complete'.

We have many processes taking orders - and a process updating them to
the 'Complete' status.

Any ideas on how to remedy this? Is this a problem that is being
addressed ? I'd be glad to help!

Steve

On Wed, 3 Apr 2002, Jan Wieck wrote:

> 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 #
>
>
>