Thread: locking of referenced table during constraint construction

Hi,

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);


ss

Scott Shattuck
Technical Pursuit Inc.

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.

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

ss


> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Scott Shattuck <ss@technicalpursuit.com> writes:
> ... I don't understand why an exclusive table-level lock is being
> taken out to add a trigger.

Well, that's a schema change; it makes sense to me to forbid access
while we're changing a table's schema.

I think this discussion may just be a miscommunication: it's not clear
to me whether you're complaining about adding a trigger or just firing
a trigger.  The former is not a time-critical task in my book ...
        regards, tom lane

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.

On Wed, 2002-09-04 at 22:49, Tom Lane wrote:
> Scott Shattuck <ss@technicalpursuit.com> writes:
> > ... I don't understand why an exclusive table-level lock is being
> > taken out to add a trigger.
> 
> Well, that's a schema change; it makes sense to me to forbid access
> while we're changing a table's schema.
> 

No. In my book a schema change would alter the data a query would see --
as in drop column, or add column, etc. This is simply a "don't let a
delete/update get past this trigger from this point forward". That's not
a bar-the-gates kind of scenario to me. More like "for any DML operating
after the current version stamp make sure this trigger runs." Why lock
anything? 

One scenario I can see. A delete starting at T0 doesn't see a trigger.
The alter occurs at T1 but, due to ACID, the delete doesn't see it. The
delete tries to commit at T2. Unfortunately, in that scenario you can
envision an issue since it would seem the delete should fail since the
alter is done, but the delete's transaction shouldn't be able to be
affected by things starting after it does. So, a conflict. But only for
a delete or update. Selects already have transaction isolation
levels...why don't they allow the selects to read through adding a
constraint?

I have other serious issues with locking and FK constraints as it is.
They often cause us serious performance problems. Sadly, the longer I
use PG and get hammered by locking issues surrounding the FK constraint
implementation the less I find myself likely to suggest PG for similar
customers in the future.

> I think this discussion may just be a miscommunication: it's not clear
> to me whether you're complaining about adding a trigger or just firing
> a trigger.  The former is not a time-critical task in my book ...
> 

It becomes time critical when the table has 3 million user account
entries and the lock blocks people from having their login name
verified, causing what's supposed to be a 24x7 e-commerce site to
essentially go offline to users for 5 minutes or more just so you can
add a constraint to a new table with no rows. Sorry, but that sucks.

ss

Scott Shattuck <ss@technicalpursuit.com> writes:
> ...why don't they allow the selects to read through adding a
> constraint?

Hmm.  We could probably allow that --- at least for some forms of
ALTER TABLE, a ShareRowExclusive lock ought to be good enough.
(That would allow SELECT and SELECT FOR UPDATE to run in parallel,
but not any actual data changes.)  Offhand I think this would be okay
for trigger changes, since SELECT and SELECT FOR UPDATE are unaffected
by triggers.  I'm less sure that it's safe for any other kind of ALTER.

> It becomes time critical when the table has 3 million user account
> entries and the lock blocks people from having their login name
> verified, causing what's supposed to be a 24x7 e-commerce site to
> essentially go offline to users for 5 minutes or more just so you can
> add a constraint to a new table with no rows. Sorry, but that sucks.

The only way ALTER TABLE ADD CONSTRAINT could take five minutes is if
you are putting a new constraint on a large existing table.  I don't
really see how you can expect that to be a free operation --- the system
has to look through all the existing rows to verify the constraint is
met.  Fooling with the schema of large production tables is not
something you're going to do without downtime in *any* DB.
        regards, tom lane