SQL and Relational Theory, 2nd Edition

TRANSACTIONS

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 ...