Why Database Constraint Checking Must Be Immediate

I have at least five reasons for taking the position that database constraints must be satisfied at statement boundaries. The first and biggest one is as follows. As we know from Chapter 4, a database can be regarded as a collection of propositions, propositions that we believe are true. 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 in this chapter, we can never trust the answers we get from an inconsistent database! 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 I'll give the other four arguments as well, for purposes of reference if nothing else. Second, then, I don't agree that any given inconsistency can be seen by only one transaction, anyway; that is, I don't believe in the isolation property. Part of the problem here is that the word isolation doesn't mean 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 T1 produces some result, in the database or elsewhere, that's subsequently read by transaction T2, then T1 and T2 aren't truly isolated from each other (and this remark applies regardless of whether T1 and T2 run concurrently or otherwise). In particular, therefore, if (a) T1 sees an inconsistent state of the database and therefore produces an incorrect result, and (b) that result is then seen by T2, then (c) the inconsistency seen by T1 has effectively been propagated to T2. In other words, it can't be guaranteed that a given inconsistency, if permitted, will be seen by just one transaction, anyway.

Third, we surely don't want every program (or other "code unit") to have to cater for the possibility that the database might be inconsistent when it's invoked. There's a severe loss of orthogonality if a program that assumes consistency can't be used safely while constraint checking is deferred. In other words, I want to be able to specify code units independently of whether they're to be executed as a transaction as such or just as part of a transaction. (In fact, I'd like support for nested transactions, but that's a topic for another day.)

Fourth, The Principle of Interchangeability (of base relvars and views see Chapter 4) implies that the very same constraint might be a single-relvar constraint with one design for the database and a multi-relvar constraint with another. For example, recall these two views from Chapter 4:

     VAR LS  VIRTUAL ( S WHERE CITY = 'London' ) ;     VAR NLS VIRTUAL ( S WHERE CITY  'London ) ; 

These views satisfy the constraint that no supplier number appears in both. However, there's no need to state that constraint explicitly, because it's implied by the single-relvar constraint that {SNO} is a key for relvar S (along with the fact that every supplier has exactly one city, which is implicit in the design of relvar S). But suppose we made LS and NLS base relvars and defined their union as a view called S. Then the constraint would have to be stated explicitly:

     CONSTRAINT C6 IS_EMPTY   |  CREATE ASSERTION C6 CHECK      ( LS { SNO } JOIN       |  ( NOT EXISTS       NLS { SNO } ) ;        |    ( SELECT *                              |    FROM   LS, NLS                              |  WHERE  LS.SNO = NLS.SNO ) ) ; 

Now what was previously a single-relvar constraint has become a multi-relvar constraint instead. Thus, if we agree that single-relvar constraints must be checked immediately, we must surely agree that multi-relvar constraints must be checked immediately as well.

Fifth and last, there's an optimization technique called semantic optimization (it involves expression transformation, but I deliberately didn't discuss it in the section of that name in Chapter 5). For example, consider the expression (SP JOIN S){PNO}. Clearly, the join here is a foreign-to-matching-primary-key join; as a consequence, every SP tuple does join to some S tuple and therefore contributes a part number to the overall result. So there's no need to do the join! the expression can be simplified to just SP{PNO}. The point to note, however, is that this transformation is valid only because of the semantics of the situation; in general, each operand to a join will include some tuples that have no counterpart in the other operand and so don't contribute to the overall result, and transformations such as the one just shown therefore won't be valid. In the case at hand, however, every SP tuple must have a counterpart in S, because of the integrity constraint actually a foreign key constraint that says that every shipment must have a supplier, and so the transformation is valid after all. A transformation that's valid only because a certain integrity constraint is in effect is called a semantic transformation, and the resulting optimization is called a semantic optimization.

In principle, any constraint whatsoever can be used in semantic optimization (we're not limited to foreign key constraints). For example, suppose the suppliers-and-parts database is subject to the constraint "All red parts must be stored in London," and consider the query:

Get suppliers who supply only red parts and are located in the same city as at least one of the parts they supply.

This is a fairly complex query. But thanks to the integrity constraint, we see that it can be transformed transformed by the optimizer, I mean, not by the user into this much simpler one:

Get London suppliers who supply only red parts.

We could easily be talking about several orders of magnitude improvement in performance here. And so, while few products do much in the way of semantic optimization at the time of writing (as far as I know), I certainly expect them to do more in the future, because the payoff is so dramatic.

To get back to the main thread of the discussion, I now observe that if a given constraint is to be usable in semantic optimization, that constraint must be satisfied at all times (more precisely, at statement boundaries), not just at transaction boundaries. As we've seen, semantic optimization means using constraints to simplify queries in order to improve performance. Clearly, then, if some constraint is violated at some time, then any simplification based on that constraint won't be valid at that time, and query results based on that simplification will be wrong at that time (in general).


Of course, we could adopt the weaker position that "deferred constraints" (meaning constraints for which the checking is deferred) just can't be used in semantic optimization but I think such a position would effectively just mean we've shot ourselves in the foot, that's all.

In sum, then, database constraints must be satisfied that is, they must evaluate to TRUE, given the values currently appearing in the database at statement boundaries (or, very informally, "at semicolons"); in other words, they must be checked at the end of any statement that might cause them to be violated. If any such check fails, the effects on the database of the offending statement are undone and an exception is raised.

Database in Depth
Database in Depth: Relational Theory for Practitioners
ISBN: 0596100124
EAN: 2147483647
Year: 2006
Pages: 127
Authors: C.J. Date

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net