The arguments of the previous section notwithstanding, the conventional wisdom is that multirelvar constraint checking, at least, does have to be deferred to commit time. By way of example, suppose the suppliers-and-parts database is subject to the following constraint:
CONSTRAINT CX8
COUNT ( ( S WHERE SNO = 'S1' ) { CITY }
UNION
( P WHERE PNO = 'P1' ) { CITY } ) < 2 ;This constraint says that supplier S1 and part P1 must never be in different cities. To elaborate: If relvars S and P contain tuples for supplier S1 and part P1, respectively, then those tuples must contain the same CITY value (if they didn’t, the COUNT invocation would return the value two); however, it’s legal for relvar S to contain no tuple for S1, or relvar P to contain no tuple for P1, or both (in which case the COUNT invocation will return either one or zero). Given this constraint and our usual sample values, then, each of the following SQL UPDATEs will fail under immediate checking:
UPDATE S SET CITY = 'Paris' WHERE SNO = 'S1' ;
UPDATE P SET CITY = 'Paris' WHERE PNO = 'P1' ;I show these UPDATEs in SQL rather than Tutorial D precisely because checking is immediate in Tutorial D and the conventional solution to the problem therefore doesn’t work in Tutorial D. What is that conventional solution? Answer: We defer the checking of the constraint to commit time,[121] and we make sure the two UPDATEs are part of the same transaction, as in this SQL code:
START TRANSACTION ;
UPDATE S SET CITY = 'Paris' WHERE SNO = 'S1' ;
UPDATE P SET CITY = 'Paris' WHERE PNO = 'P1' ;
COMMIT ;In this conventional solution, the constraint is checked at end of transaction, and the database is inconsistent between the two UPDATEs. In particular, if the transaction were to ask the question “Are supplier S1 and part P1 in different cities?” between the two UPDATEs (and assuming rows for S1 and P1 do exist), it would get the answer yes.
A better solution to the foregoing problem is to support a multiple form of assignment, which allows any number of individual assignments to be performed “simultaneously,” as it were. For example (switching back now to Tutorial D):
UPDATE S WHERE SNO = 'S1' : { CITY := 'Paris' } ,
UPDATE P WHERE PNO = 'P1' : { CITY := 'Paris' } ;Explanation: First, note the comma separator, which means the two UPDATEs are part of the same overall statement. Second, UPDATE is really assignment, as we know, and the foregoing “double UPDATE” is thus just shorthand for a double assignment of the following form:
S := ... , P := ... ;
This double assignment assigns one value to relvar S and another to relvar P, all as part of the same overall operation. In general, the semantics of multiple assignment are as follows:
First, all of the source expressions on the right sides are evaluated.
Second, the individual assignments (to the variables on the left sides) are executed.
(Actually this definition requires a slight refinement in the case where two or more of the individual assignments specify the same target variable, but that refinement needn’t concern us here.) Observe that, precisely because all of the source expressions are evaluated before any of the individual assignments are executed, none of those individual assignments can depend on the result of any other (and so the sequence in which they’re executed is irrelevant; in fact, you can think of them as being executed in parallel, or “simultaneously”). Moreover, since multiple assignment is considered to be a semantically atomic operation, no integrity checking is performed “in the middle of” any such assignment—indeed, this fact is the major rationale for supporting the operation in the first place. In the example, therefore, the double assignment succeeds where the two separate single assignments failed. Note in particular that there’s now no way for the transaction to see an inconsistent state of the database between the two UPDATEs, because the notion of “between the two UPDATEs” now has no meaning. Note further that there’s now no need for deferred checking at all.
Aside: Perhaps I should state for the record here that all statements are semantically atomic in the relational model. In fact, most statements are syntactically atomic too; multiple assignment is an exception, because it’s semantically atomic but not syntactically so. End of aside.
So what about multiple assignment in SQL? Well, SQL does have some support for this operation; in fact, it’s had some such support for many years. First of all, referential actions such as CASCADE imply, in effect, that a single DELETE or UPDATE statement can cause several base tables to be updated “simultaneously,” as part of a single operation. Second, the ability to update (for example) certain join views, if it’s supported, implies the same thing. Third, FETCH INTO and SELECT INTO are both multiple assignment operations, of a kind. Fourth, SQL explicitly supports a multiple assignment form of the SET statement (indeed, that’s exactly what row assignment is—see Chapter 2 and Chapter 3). And so on (this isn’t an exhaustive list). However, the one kind of multiple assignment that SQL doesn’t currently support is an explicit “simultaneous” assignment to several different tables—which is precisely the case illustrated by the foregoing example, and precisely what we need in order to avoid having to do deferred checking.[122]
One last point: Please understand that support for multiple assignment doesn’t mean we can discard support for transactions. Transactions are still necessary for recovery and concurrency purposes, if nothing else. All I’m saying is that transactions aren’t the “unit of integrity” they’re usually supposed to be.
Recommendation: In SQL, use immediate checking whenever you can. Given the state of today’s products, however, some checking (especially for constraints that involve more than one table) will almost certainly have to be deferred. In such a case, you should do whatever it takes—which in practice might mean terminating the transaction—to force the check to be done before any operation is executed that might rely on the constraint being satisfied.
[121] In case you’re wondering how the deferring is done, I should explain that in general—there are some exceptions that don’t need to concern us here—every SQL constraint is defined at compile time to be (a) either DEFERRABLE or NOT DEFERRABLE and (b) if DEFERRABLE, either INITIALLY DEFERRED or INITIALLY IMMEDIATE. Then, at run time, the statement SET CONSTRAINTS <constraint name commalist> <option>, where <option> is either DEFERRED or IMMEDIATE, sets the “mode” of the specified constraint(s) accordingly. (Of course, the constraint(s) in question must have been defined to be DEFERRABLE.) COMMIT forces all DEFERRABLE constraints into immediate mode; if some integrity check then fails, the COMMIT fails, and the transaction is rolled back.
[122] I’m told, however, that this functionality is likely to be provided in some future version of the standard.