10.1. Transactions and Database Integrity

The concept of a transaction has a direct connection with the concept of database integrity. Very often, a database can have such integrity constraints that it is impossible not to violate integrity by executing only one DB change operator. For example, in the database EMPLOYEES-DEPARTMENTS, the natural integrity constraint is the coincidence of the value of the attribute DEPARTMENT in the tuple of the relationship DEPARTMENTS describing this department (for example, department 320) with the number of tuples of the relationship EMPLOYEES such that the value of the attribute COTR_OTD_NOMER is 320. As in this case, take to work in the department 320 new employee? Regardless of which operation is performed first, the insertion of a new tuple in relation to the EMPLOYEES or a modification of the existing tuple with respect to the DEPARTMENT, after the operation is completed, the database will be in a non-integral state.

Therefore, in order to maintain such integrity constraints, their violation within the transaction is allowed, with the condition that by the time the transaction is completed, the integrity conditions are met. In systems with advanced means of restriction and integrity control, each transaction begins with a complete database state and must leave this state complete after its completion. Non-observance of this condition leads to the fact that instead of fixing the results of the transaction, it is rolled back (i.e., instead of the COMMIT statement, the ROLLBACK statement is executed), and the database remains in the state in which it was at the start of the transaction, i.e. in a holistic state.

If to be a little more accurate, two types of integrity constraints are distinguished: immediately checked and postponed. Immediately verifiable integrity constraints include those whose verification is meaningless or even impossible to delay. An example of a restriction, the check of which is meaningless to postpone, is a domain restriction (an employee’s age cannot exceed 150 years). A more complicated restriction, the check of which cannot be postponed, is the following: an employee’s salary cannot be increased in a single operation by more than 100,000 rubles. Immediately verifiable integrity constraints correspond to the level of individual DBMS language level operators. If they are violated, the transaction is not rolled back, but only the corresponding statement is rejected.

Deferred integrity constraints are constraints on the database, not on any individual operation. By default, such constraints are checked at the end of the transaction, and breaking them will automatically replace the COMMIT statement with the ROLLBACK statement. However, in some systems, a special operator is enforced to verify the integrity constraints within a transaction. If, after executing such an operator, it is found that the integrity conditions are not met, the user can himself execute a ROLLBACK statement or try to eliminate the causes of the non-integral state of the database within the transaction (apparently, this is meaningful only when using interactive mode of operation).

And one more note. From the point of view of the external representation at the time of completion of the transaction, all pending integrity constraints defined in this database are checked. However, the implementation seeks to dynamically identify those integrity constraints that might actually be violated when performing a transaction. For example, if, while performing a transaction on the database, EMPLOYEES-DEPARTMENTS did not execute insertion or deletion of tuples from the EMPLOYEES relationship, then the integrity constraint mentioned above is not required to be checked (and checking such constraints causes a fairly large amount of work).