SQL and Relational Theory, 2nd Edition

WHY DATABASE CONSTRAINT CHECKING MUST BE IMMEDIATE

I have at least five reasons for taking the position I do (viz., that database constraints must be satisfied at statement boundaries). The first and biggest one is this: As we know from Chapter 5, a database can be regarded as a collection of propositions, propositions we believe to be true ones. And if that collection is ever allowed to include any inconsistencies, then all bets are off; as I’ll show in the section CONSTRAINTS AND PREDICATES later, we can never trust the answers we get from an inconsistent database. And while it might be true, thanks to the isolation property, that no more than one transaction ever sees any particular inconsistency, the fact remains that that particular transaction does see the inconsistency and can therefore produce wrong answers.

Now, I think this first argument is strong enough to stand on its own, but for completeness I’ll give the other arguments as well. Second, then, I don’t agree that any given inconsistency can be seen by only one transaction, anyway; that is, I don’t really believe in the isolation property. Part of the problem here is that the word isolation doesn’t mean quite the same in the world of transactions as it does in ordinary English—in particular, it doesn’t mean that transactions can’t communicate with one another. For if transaction TX1 produces some result, in the database or elsewhere, that’s subsequently read by transaction TX2, then TX1 and XT2 aren’t truly isolated ...