Transaction Isolation Level and ACID Properties

Transaction Isolation Level and ACID Properties

Isolation levels represent how a database maintains data integrity against the problems like Dirty Reads, Phantom Reads 
and Non-Repeatable Reads which can occur due to concurrent transactions. You can avoid these problems by describing 
following isolation levels in vendor's deployment descriptor file (web.xml or any xml/properties file):
        TRANSACTION_READ_UNCOMMITED
        TRANSACTION_READ_COMMITED
        TRANSACTION_REPEATABLE_READ
        TRANSACTION_SERIALIZABLE

Databases use Read and Write locks depending on above isolation levels to control transactions. 

Dirty Read:  This occurs when one Transaction is modifying the record and another Transaction is reading the record before 
             the first Transaction either commits or rolls back. Here the second Transaction always read an invalid value
             if the first Transaction does rollback. Reason is: isolation attribute is set to READ UNCOMMITTED.

Phantom Read: Phantom read occurs when in a transaction same query executes twice, and the second query result includes 
              rows that weren't present in the first result set. This situation is caused by another transaction inserting 
              new rows between the execution of the two queries. Reason is: isolation attribute is set to READ COMMITTED.

Non Repeatable Read: Non Repeatable Reads happen when in a same transaction same query yields different results. This 
                     happens when another transaction updates the data returned by first transaction.              

Problems due to concurrent transactions: 
Transaction Level                Dirty_Read       Non_Repeatable_Read         Phantom_Read     Performance_Impact 
TRANSACTION_READ_UNCOMMITED         YES              YES                         YES             FASTEST
TRANSACTION_READ_COMMITED             NO               YES                         YES             FAST
TRANSACTION_REPEATABLE_READ         NO               NO                            YES             MEDIUM
TRANSACTION_SERIALIZABLE             NO               NO                          NO              SLOW

YES: means the transaction level does not prevent the problem.
NO: means the transaction level prevents the problem.
By setting isolation levels, you are having an impact on the performance as mentioned in the above table. 


Dirty Read:
You can control this problem by setting isolation level as TRANSACTION_READ_COMMITED.

Unrepeatable read problem :
You can control this problem by setting isolation level as TRANSACTION_REPEATABLE_READ.

Phantom read problem : 
You can control this problem by setting isolation level as TRANSACTION_SERIALIZABLE.

Examples of Isolation Levels:
1) Displaying a Product Catalog in UI: Use TRANSACTION_READ_UNCOMMITED
    As you are not worried if some records comes or goes out from display page. This is fastest in performance.

2) Writing a critical program like bank or stocks analysis program where you want to control all of the 
isolation problems, you can choose TRANSACTION_SERIALIZABLE for maximum safety. However this is Slow.

3) If your application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the choice. 

4) If your application needs to read a row exclusively till you finish your work, then use TRANSACTION_REPEATABLE_READ.


Note: Database servers may not support all of these isolation levels. Oracle server supports only two isolation levels: 
    TRANSACTION_READ_COMMITED and TRANSACTION_SERIALIZABLE. Default is TRANSACTION_READ_COMMITED.
    Databases have their own locking strategy. However you can design locking in your application too. 
    Like Optimistic lock in Hibernate.
    
    
ACID Properties:
ACID is a set of properties which guarantee that database transactions are processed reliably, in fact these 
are Desirable Properties of any Transaction Processing Engine. A transaction is a collection of read/write 
operations. ACID is an acronym for:
    Atomicity
    Consistency
    Isolation
    Durability

    Atomicity: An entire document gets printed or nothing at all. Either commit all or nothing.
    Consistency: All printed pages will have same width and height as border/space. Make consistent records 
                 in terms of validating all rules and constraints of transactions.
     
    Isolation: No two documents get mixed up while printing. Two transactions are unaware to each other.
    Durability: The printer can guarantee that it was not "printing" with empty cartridges. This means 
                committed data is stored forever and post Server/DB restart also it can be accessed.     
----------------------End------------------