unordained wrote:

>> Why only honour referential integrity? Don't you want to honour check
>> constraints, domain constraints, etc. ?? Perhaps 4. should read
>> "Constraints must be honoured"; 3. becomes superfluous then, as
>> primary & unique are constraints too.
>>
> ------- End of Original Message -------
>
> No transaction is allowed to commit changes that would cause any constraints
> not to validate, if they could see exactly the data from all committed
> transactions (previously fully committed, or currently past phase 1 of 2pc and
> waiting in limbo), plus the transaction requesting commit. General enough?
>
> (loophole that might not be a loophole:
> - tx 1 starts
> - tx 2 starts
> - tx 1 creates master record with id 17
> - tx 2 creates detail record with master_id 17, assuming deferred-constraints
> - tx 1 commits
> - tx 2 commits -> tx 2 couldn't see record from tx 1, but global database
> state is still consistent after commit
> )
>

I don't think that works. Tx 2 is inconsistent at commit time, which is
a no-no. True, the global database is consistent if tx 2 would be
allowed to commit. That said, there is a risk that a Tx 3 that saw the
Tx 2 commit before the Tx 1 would see an inconsistent view of the database.

The this brave, new, Einsteinian world, there is no fixed cross node
ordering of events in the absence of a constraint. Constraints must be
enforced around* the time of an update, and continue to be true until
commit if the transaction can be allowed to commit.

* the timing of constraint violation is more a function of mandatory
SQL error reporting, though I supposed almost anything could be left as
a last minute surprise. I prefer to have a statement fail with a proper
diagnostic, but maybe this isn't required by theory. Still, getting a
referential integrity error from a commit is unsettling.

--
Jim Starkey
Founder, NimbusDB, Inc.
978 526-1376