Sunday, February 12, 2012

About Isolation levels & Transaction levels

The isolation level measures concurrent transaction's capacity to view data that have been updated, but not yet committed, by another transaction if other transactions were allowed to read data that are as yet uncommitted, those transactions could end up with inconsistent data were the transaction to roll back, or end up waiting unnecessarily were the transaction to commit successfully.

A higher isolation level means less concurrence and a greater likelihood of performance bottleneck, but also a decreased chance of reading inconsistent data. A good rule of thumb is to use the highest isolation level that yields an acceptable performace level. The following are commin issilation levels, arranged from lowest to highest.

  1. ReadUncommitted: Data that have been updated but not yet committed by a transaction my be read by other transactions
  2. Readcommitted: Only data that have been committed by a transaction can be read by other transactions.
  3. Repeatable Read: Only data that have been commited by a trasaction can be read by other transactions, and multiple reads will yield the same result as log as the data have been committed.
  4. Serializable: This highest possible iosolation level, ensures a transaction's execlusive read-write access to data, it includes the conditions of ReadCommitted and Repeatable Read and stiplulates that all transactions run serially to achieve maximum data integrity. This yields the slowest performance and least concurrency. The term serializable in this context is absolutely unrelated to the database.