Data Concurrency and Consistency

Data Concurrency and Consistency

Data Concurrency
- Many users can access data at the same time.

Data consistency
- Each user sees a consistent view of the data, including visible changes made
by the user's own transactions and transactions of other users.


The three preventable phenomena are:
- Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
- Nonrepeatable(fuzzy) reads: A transacton rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
- Phantom reads: A transaction re-executes a query returing a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition


SQL92 defines four levels of isolation

---------------------------------------------------------------------
Isolation Level     Dirty Read      Nonrepeatable Read   Phantom Read
---------------------------------------------------------------------
Read uncommitted    Possible        Possible             Possible
Read committed      Not possible    Possible             Possible
Repeatable read     Not possible    Not possible         Possible
Serializable        Not possible    Not possible         Not possible
----------------------------------------------------------------------

Oracle offers
- the read committed and
- serializable isolation

Oracle Isolation Levels
------------------------
- Read Committed
-- default
-- Each query executed by a transaction sees only data that was committed before
the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

- Serializable
-- Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

- Read-only