Describe situations where each of the different isolation levels would be useful for transaction processing.

Question

What will be an ideal response?

Answer 1

Transaction isolation levels provide a measure of the influence of other concurrent transactions on a given transaction. This affects the level of concurrency, that is, the level of concurrency is the highest in Read Uncommitted and the lowest in Serializable.

Isolation level Serializable: This isolation level preserves consistency in all situations, thus it is the safest execution mode. It is recommended for execution environment where every update is crucial for a correct result. For example, airline reservation, debit credit, salary increase, and so on.

Isolation level Repeatable Read: This isolation level is similar to Serializable except
Phantom problem may occur here. Thus, in record locking (finer granularity), this isolation level must be avoided. It can be used in all types of environments, except in the environment where accurate summary information (e.g., computing total sum of a all different types of account of a bank customer) is desired.

Isolation level Read Committed: In this isolation level a transaction may see two different values of the same data items during its execution life. A transaction in this level applies write lock and keeps it until it commits. It also applies a read (shared) lock but the lock is released as soon as the data item is read by the transaction. This isolation level may be used for making balance, weather, departure or arrival times, and so on.

Isolation level Read Uncommitted: In this isolation level a transaction does not either apply a shared lock or a write lock. The transaction is not allowed to write any data item, thus it may give rise to dirty read, unrepeatable read, and phantom. It may be used in the environment where statistical average of a large number of data is required.