Lecture
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:
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.
Comments
To leave a comment
Databases IBM System R - relational DBMS
Terms: Databases IBM System R - relational DBMS