On Fri, 26 Sep 2003, Kevin Brown wrote:
> Stephan Szabo wrote:
> > The case at hand (with *'s on the ri queries) assuming pk already
> > has an id=1 row would be.
> > T1: begin;
> > T1: set transaction isolation level serializable;
> > T1 ... (something that does a select, not necessarily on either pk or fk)
> > T2: begin;
> > T2: insert into fk values (1);
> > T2*:select * from pk where id=1 for update;
> > T2: commit;
> > T1: delete from pk where id=1;
> > T1*:select * from fk where id=1 for update;
> > T1: commit;
> >
> > If you want to treat the serial execution as T1 followed by T2. Then
> > T2* would have to show no rows for pk and T2 rolls back.
> >
> > If you want to treat the order as T2,T1, then T1* would have to see the
> > row that T2 inserted and T1 rolls back.
> >
> > Right now, you won't get that, you'll get T2* showing 1 row and T1*
> > showing 0 rows.
>
> Does it also behave this way *without* any actual foreign key
> constraints in place? In other words, if you perform the RI queries
> explicitly?
Yeah, that wasn't clear from what I'd wrote, but that was from two psql
sessions with non-ri constraint tables (excepting that I used a real
select in place of the holder).
> The problem in the scenario you described should be solved if we mark any
> rows that are selected with the "for update" option (either implicitly,
> as with RI triggers, or explicitly) as having been modified by the
> selecting transaction, the equivalent of (in the case of T2*) "update pk
> set id=id where id=1" but without firing any of the ON MODIFY triggers.
> A rollback would, of course, not have any effect on the data in those
> rows since there weren't any real changes. This "fix" won't work,
> of course, if the serialization code is so broken that it doesn't work
> properly even in the face of updates (something I'd find hard to believe).
That fixes the case above which will fix the ri constraints for right now
(although they really have to stop using for update eventually), but
doesn't really solve the serialization problem since it still exists
AFAICS without for update. Without the for update, you still have T2*
getting 1 row and T1* getting 0 which can't happen for either ordering of
the transactions. It gets worse if that select as a holder at the
beginning of T1 was say select * from fk where id=1 because SQL tells us
that the later select can't see a different set of rows from the earlier
one, so T2 shouldn't be allowed to commit before T1.