Transaction isolation

Transaction isolation (the "I" in ACID) is a critical part of any transactional system.

Transaction isolation is defined in terms of isolation conditions called dirty reads, non repeatable reads, and phantom reads . These conditions describe what can happen when two or more transactions operate on the same data.

A dirty read occurs when the first transaction reads uncommitted changes made by a second transaction. If the second transaction is rolled back, the data read by the first transaction becomes invalid because the rollback undoes the changes. The first transaction won't be aware that the data it has read has become invalid.

A non repeatable read is like one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read").

Phantom reads is like one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read.

We can save our application with above un-wanted situations by setting our connection/database isolation level-
1. Read Committed - TRANSACTION_READ_COMMITTED(2) - saves from dirty read
2. Repeatable Read - TRANSACTION_REPEATABLE_READ(4) - saves from dirty and non-repeatable read
3. Serializable - TRANSACTION_SERIALIZABLE(8) - saves from above two and phantom read also.
4. Read Uncommitted - TRANSACTION_READ_UNCOMMITTED(1) - will not save you from any one

we can do these setting either at database end or at application side.

in java application even you can change it dynamically, by conn.setTransactionIsolation(1 or 2 or 4 or 8);

keep one thing in mind higer the lock, will lower the performance. so wisely decide the Isolation level for your application.

if any doubt blog me.

regards,
sumit agarwal