Despite the defect identified at the end of the previous section, I do need to assume for the rest of the chapter (just as the relational model does, in fact) that database constraints of arbitrary complexity can be stated declaratively. The question now arises: When are such constraints checked? Conventional wisdom has it that single relvar constraint checking is immediate (meaning it’s done whenever the relvar in question is updated), while multirelvar constraint checking is deferred to end of transaction (“commit time”). I want to argue, however, that all checking should be immediate, and deferred checking—which is supported in the SQL standard, and indeed in at least one SQL product to my knowledge—is a logical mistake. In order to explain this unorthodox view, I need to digress for a moment to discuss transactions.
Transaction theory is a large topic in its own right. But it doesn’t have much to do with the relational model as such (at least, not directly), and for that reason I don’t want to discuss it in detail here. In any case, you’re a database professional, and I’m sure you’re familiar with basic transaction concepts.[119] All I want to do here is briefly review the so called ACID properties of transactions. ACID is an acronym, standing for atomicity - consistency - isolation - durability, where:
Atomicity means that transactions are “all or nothing.”
Consistency means that any given transaction transforms a consistent state of the database into another consistent state, without necessarily preserving consistency at all intermediate points. Note: A database state is consistent if and only if it satisfies all defined constraints (consistency in this context is just another word for integrity).
Isolation means that any given transaction’s updates are concealed from all other transactions until such time as the given transaction commits.
Durability means that once a given transaction commits, its updates survive in the database, even if there’s a subsequent system crash.
Now, one argument in favor of transactions has always been that they’re supposed to act as “a unit of integrity” (that’s what the consistency property is all about). But I don’t believe that argument. Rather, as I’ve more or less said already, I believe statements have to be that unit; in other words, I believe database constraints must be satisfied at statement boundaries. The section immediately following gives my justification for this position.
[119] The standard reference—highly recommended, by the way—is Transaction Processing: Concepts and Techniques, by Jim Gray and Andreas Reuter (see Appendix G).