10.2. User isolation

In multi-user systems with a single database, multiple users or application programs can work simultaneously. The ultimate goal of the system is to ensure user isolation, i.e. creating a reliable and reliable illusion that each user works from the database alone.

In connection with the property of preserving the integrity of the database, transactions are appropriate units of user isolation. Indeed, if a transaction is associated with each database session, each user begins to work with a consistent database state, i.e. with such a state in which the database could be, even if the user worked with her alone.

Subject to the mandatory requirements of maintaining database integrity, the following levels of transaction isolation are possible:

• The first level is the absence of lost changes. Consider the following scenario for the joint execution of two transactions. Transaction 1 modifies database object A. Until transaction 1 completes, transaction 2 also modifies object A. Transaction 2 is terminated with a ROLLBACK statement (for example, due to a violation of integrity constraints). Then, when re-reading object A, transaction 1 does not see any changes to this object made earlier. This situation is called a lost change situation. Naturally, it contradicts the requirement of user isolation. To avoid such a situation in transaction 1, it is required that prior to the completion of transaction 1 no other transaction could change object A. The absence of lost changes is the minimum requirement for the DBMS in terms of synchronization of concurrently running transactions.
• The second level is the lack of reading "dirty data". Consider the following scenario for sharing transactions 1 and 2. Transaction 1 modifies database object A. In parallel, transaction 2 reads object A. Because the change operation is not yet complete, transaction 2 sees inconsistent “dirty” data (in particular, transaction 1 can be unscrewed when checking an immediately verifiable integrity constraint). This also does not correspond to the requirement of user isolation (each user starts his or her transaction when the database is in a consistent state and has the right to expect to see consistent data). To avoid the situation of reading “dirty” data, until the completion of transaction 1, which changed object A, no other transaction should read object A (the minimum requirement is to block the reading of object A until the operation is completed to change it in transaction 1).
• The third level is the absence of non-repeating readings. Consider the following scenario. Transaction 1 reads database object A. Until transaction 1 completes, transaction 2 modifies object A and successfully completes with a COMMIT statement. Transaction 1 re-reads object A and sees its changed state. To avoid non-recurring reads, until the completion of transaction 1, no other transaction should change object A. In most systems, this is the maximum requirement for transaction synchronization, although, as we will see a little later, the absence of non-repeatable readings does not guarantee real user isolation.

Note that it is possible to provide different levels of isolation for different transactions running in the same database system (in particular, the corresponding operators are provided for in the SQL 2 standard). As we have already noted, the first level is sufficient to maintain integrity. There are a number of applications for which the first level is sufficient (for example, application or system statistical utilities, for which the incorrectness of individual data is insignificant). At the same time, it is possible to significantly reduce the overhead costs of the DBMS and increase the overall efficiency.

More subtle problems of isolation of transactions include the so-called problem of phantom tuples, which causes situations that also contradict the isolation of users. Consider the following scenario. Transaction 1 performs the operator A to sample the tuples of the relation R with the condition of the sample S (that is, select the part of the tuples of the relation R that satisfy condition S). Before the completion of transaction 1, transaction 2 inserts a new tuple r in relation to R that satisfies condition S and completes successfully. Transaction 1 reruns statement A, and the result is a tuple that was missing when the statement was first executed. Of course, this situation is contrary to the idea of ​​isolation of transactions and can occur even at the third level of isolation of transactions. To avoid phantom tuples, a higher “logical” level of transaction synchronization is required. The ideas of such synchronization (predicate synchronization captures) have been known for a long time, but are not implemented in most systems.