Databases Reference
In-Depth Information
directly impact the level of concurrent access to the same database object and
how the different transactions interact with each other while accessing the same
data source. The different kinds of interaction possibilities are:
Lost updates
Two applications, Application A and Application B, read the same row and
calculate the new value for a column for that row. Application A updates the
row with the new value, and just after that Application B updates its new
value. The value updated by Application A is lost.
Access to uncommitted data
Application A updates a row without committing, and Application B reads this
uncommitted value. Now, if Application A rolled back the transaction,
Application B has already read the data and has the wrong set of data.
Nonrepeatable reads
Application A reads from the database and then goes on to process another
SQL statement in the transaction. In the meantime, Application B has
updated or deleted the same row. Now if Application A came back and read
the same data again, it found either the value is updated or deleted from the
database. This type of interaction is possible when Application B updates or
deletes the row, which was the part of the result set of transaction A.
Phantom read
Application A selects some data based on some condition. Application B
inserts another row to the same table, which satisfies the condition of
Application A and commits the changes. If Application A selects the data
again, it will find the extra rows in the result of the query. This kind of
transaction is possible if Application B adds some more rows to the table,
which can be part of the result set of transaction A if the result set is created
again (by running the statement again).
These interactions between different transactions can cause unpredictable
results. Setting isolation levels to appropriate values restricts these interactions.
DB2 provides the following isolation levels. Each isolation level is described on
two parameters:
How the data is seen by the other transactions while this transaction with this
specified isolation level is updating or reading the data.
How the current transaction with the specified isolation level can see the data
read or updated by other transactions.
All the transactions acquire an exclusive lock whenever they update a row in the
table, so the lost update is not possible at all.
Search WWH ::
Custom Search