Isolation

Read Phenomenon. Please understand this. This definition/phenomenon is independent of any DB concept.

1. Dirty Reads: It occurs when a transaction is allowed to read data that has been modified (by another transaction) but not yet committed.

2. Non-repeatable Reads: occurs when during a course of a transaction, one (select) query returns different results.

3. Phantom Reads: occurs when during a course of a transaction, one collection/aggregate query returns different results (because something is added/deleted)

Why database concurrency is required?

It the transactions can be executed serially, no transaction concurrency is required. But since, we want them to execute in parallel/concurrently, we need to handle the concurrency.

Lets take an example of two transactions:

1. 10:01 User-A executes: select * from tab1 and support it takes 3 seconds for this query.

2. 10:02 User-B executes: update tab1 set col1 = 'someValue' where col2='otherVal' and then commit.

Now, will User-A see the updated record or not. The answer lies in the isolation levels defined at Database Level.

What is Database Isolation?

Database transaction should not get disturbed when running concurrently. But running one transaction after another is not good for performance. Thus, Database defined few isolation levels that defines how/when the changes made by one operation become visible to other concurrent operation.

Wiki: To maintain the highest level of isolation, a DBMS usually acquires locks on data, which may result in a loss of concurrency and a high locking overhead. This isolation level relaxes this property(by defining Read Phenomenon).

What are the Isolation Levels defined by SQL standards?

Please note that the below are the (conceptual) ISOLATION Levels defined by ANSI/ISO SQL standards. Actual databases may choose to define more or different implementations of these. E.g. Oracle has little different meaning of READ_COMMITTED than defined by standards.

SQL standard defines following 4 isolation levels:

1. Serializable

2. Repeatable Read

3. Read Committed

4. Read Uncommitted.

SQL standard does not define the the actual locking algorithms or other mechanisms for the isolation levels. It only defines the isolation levels in terms of Read Phenomenon and Locks as defined below:

What is default isolation level in Oracle?

Read Committed.

How does Isolation Level impacts consistency, recoverablilty.

Isolatation level defines the READ Phenomenon for transactions. E.g. One transaction can read some data that is never existed (using READ_UNCOMMITTED). But the consistency & recoverability will NEVER be compromised from database perspective.

What is concurrency control and what is its relationship with Isolation Levels?

Concurrency Control generally would mean Concurrency Control Algorithm to handle the appropriate isolation level. E.g. An algorithm MutliVersionConcurencyControl (MVCC) can be used to handle first 3 Isolation levels (except Serializable) in Oracle.