Thread: do foreign key checks lock parent table ?

do foreign key checks lock parent table ?

From

Date:

02 April 2002, 22:01:22

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;

Re: do foreign key checks lock parent table ?

From

Stephan Szabo

Date:

02 April 2002, 22:45:48

On Tue, 2 Apr 2002 swalker@iglou.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.

Re: do foreign key checks lock parent table ?

From

Date:

03 April 2002, 03:06:25

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.

Re: do foreign key checks lock parent table ?

From

Jan Wieck

Date:

03 April 2002, 14:31:34

swalker@iglou.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@Yahoo.com #

Re: do foreign key checks lock parent table ?

From

Stephan Szabo

Date:

03 April 2002, 14:35:56

On Wed, 3 Apr 2002 swalker@iglou.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.
>

Re: do foreign key checks lock parent table ?

From

Jan Wieck

Date:

03 April 2002, 14:38:31

swalker@iglou.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@Yahoo.com #

Re: do foreign key checks lock parent table ?

From

Date:

03 April 2002, 19:01:19

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@iglou.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@Yahoo.com #
>
>
>