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?
If so, then the problem is with the serialization code. Sounds like
that's pretty much what you're saying.
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).
--
Kevin Brown kevin@sysexperts.com