Re: locking of referenced table during constraint

On 4 Sep 2002, Scott Shattuck wrote:

> On Wed, 2002-09-04 at 15:51, Stephan Szabo wrote:
> >
> > On 4 Sep 2002, Scott Shattuck wrote:
> >
> > > Under what conditions would the following statement cause the USERS
> > > table to lock out selects?
> > >
> > >
> > > alter table my_coupons
> > > add constraint FK_mc_user_id
> > > FOREIGN KEY (mc_frn_user_id)
> > > REFERENCES users(user_ID);
> >
> > If I'm reading code correctly, an exclusive lock
> > on the pk table is grabbed which will block selects
> > as well. You're effectively altering both tables
> > (you need to add triggers to both tables) and
> > both get locked.
> >
> >
>
> Ok, if I understand things correctly the USERS table gets a constraint
> that says don't delete/update the USER_ID in any way that would orphan a
> row in the MY_COUPONS table. The MY_COUPONS table gets one that says
> don't insert/update MC_FRN_USER_ID such that it isn't found in
> USERS.USER_ID.
>
> But...
>
> There are no rows in the my_coupons table so it's not possible to orphan
> a row there -- were it even the case that an update or delete were
> running...which they aren't. Even if there were rows in the referring
> table I don't understand why an exclusive table-level lock is being
> taken out to add a trigger. If I add user-level triggers to do the same
> task they go in without a hitch but cause other problems in 7.2 since I
> can't control their order of execution yet (thanks Tom for the 7.3
> patch! :)).

I see the same behavior with user triggers (on my 7.3 devel box) if
you don't commit the transaction that selects against the table that
is having the trigger added to it block until the transaction that
did the create trigger is committed or aborted. I think I must
be misunderstanding the symptoms.