But Doesn t Some Checking Have to Be Deferred?

But Doesn't Some Checking Have to Be Deferred?

As I've said, the conventional wisdom is that multi-relvar constraint checking, at least, has to be deferred to commit time (the arguments of the previous section notwithstanding). By way of example, suppose the suppliers-and-parts database is subject to the following constraint:

   CONSTRAINT C7     COUNT ( ( S WHERE SNO = SNO('S1') ) { CITY }         UNION         ( P WHERE PNO = 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 our usual sample values, then, each of the following SQL UPDATEs will fail under immediate checking:^[*]

^[*] I show those UPDATEs in SQL precisely because checking is immediate in Tutorial D and the conventional solution I'll be discussing in a moment therefore doesn't work in Tutorial D.

   UPDATE S SET CITY = 'Paris' WHERE SNO = SNO('S1') ;   UPDATE P SET CITY = 'Paris' WHERE PNO = PNO('P1') ; 

Now, the conventional solution to this problem is to defer the checking and to bundle up the two UPDATEs into a transaction, like this:

   BEGIN TRANSACTION ;   UPDATE S SET CITY = 'Paris' WHERE SNO = SNO('S1') ;   UPDATE P SET CITY = 'Paris' WHERE PNO = PNO('P1') ;   COMMIT ; 

In this conventional solution, the constraint is checked at commit time, 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 if we assume that tuples for S1 and P1 do exist), it would get the answer yes.

Multiple Assignment

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." For example (switching back now to Tutorial D):

   UPDATE S WHERE SNO = SNO('S1') ( CITY := 'Paris' ) ,   UPDATE P WHERE PNO = 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, of course, and the foregoing "double UPDATE" is thus just shorthand for a double assignment of the following general 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 expressions on the right sides are evaluated.

• Second, all of the constituent assignments are then executed in sequence as written.^[*]

  ^[*] This definition requires some slight refinement in the case where two or more of the individual assignments specify the same target. The details are beyond the scope of this book.

Observe that, precisely because the expressions on the right sides are evaluated before any constituent assignment is executed, none of those constituent assignments can depend on the result of any other. Moreover, since multiple assignment is considered to be a single operation, no integrity checking is performed "in the middle of" any such assignment. (Indeed, this fact is the major rationale for supporting multiple assignment in the first place.) In the example, therefore, the double assignment succeeds where the two separate single assignments failed.

Note in particular in the example 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.

By the way, SQL does have some support for multiple assignment; in fact, it's had some support for many years. First, referential actions such as CASCADE imply, in effect, that a single DELETE or UPDATE statement can cause several tables to be updated "at the same time," as part of a single operation. Second, the ability to update (for example) certain join views implies the same thing. Third, FETCH INTO and SELECT INTO are both multiple assignment operations, of a kind. Fourth, SQL:2003 introduced a multiple-assignment form of the SET statement. 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 assignment to several different tables (precisely the case illustrated by the foregoing example, of course).

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, at least. All I'm saying is that transactions aren't the "unit of integrity" they're usually supposed to be.