ISOLATION levels in Transaction

Question

I want to know that what is the best way to arrive at the isolation level of the transaction? This is a good link of the available ISOLATION levels.

Blockquote It will be nice if someone can explain the various isolation levels of a transaction

Answer 1

Update: Clarified and corrected explanation.

Isolation levels just indicate how much of your transaction is affected by other concurrent transactions. The higher the isolation level, the less affected it is.

The effort will be made manifest in cpu load, memory load, and perhaps commit latency. In addition, write conflicts can be more likely in higher isolation levels, which may mean that you have to abort your transaction and retry the whole thing. (This only affects transactions that perform updates or inserts, not transactions which only perform selects.)

In general, the rule of thumb is to use the lowest level that gives your application the consistency it needs.

The partial transaction isolation provided by Read Committed mode is adequate for many applications, and this mode is fast and simple to use; however, it is not sufficient for all cases. Applications that do complex queries and updates might require a more rigorously consistent view of the database than Read Committed mode provides.

The Serializable mode provides a rigorous guarantee that each transaction sees a wholly consistent view of the database. However, the application has to be prepared to retry transactions when concurrent updates make it impossible to sustain the illusion of serial execution. Since the cost of redoing complex transactions can be significant, serializable mode is recommended only when updating transactions contain logic sufficiently complex that they might give wrong answers in Read Committed mode. Most commonly, Serializable mode is necessary when a transaction executes several successive commands that must see identical views of the database.

( http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html is very nice. )

Answer 2

If you're not sure about the differences in isolation levels, then stick to the default. Changing the level can have peculiar side-effects. 99% of applications are fine with the default.

The default I think varies with each JDBC driver, although some frameworks like JPA may enforce it, I can't recall offhand. The most common default is read_committed, because it gives the best general-purpose balance between transactional safety and concurrency. If you pick a different isolation level, you sacrifice either safety or concurrency, and you have to be aware of the compromise.

Answer 3

Transaction Isolation Levels are about solving data reading problems in concurrent transactions (when, within one transaction, we read the same data that another transaction changes at the same time).

There are 4 isolation levels. Each solves 1 related problem + the problems of all previous levels:

#	Isolation level	Problem to be solved	Problem description
1	Read Uncommitted	Lost Update	Only the last of the concurrent transactions affects the read data. The impact of other transactions is lost
2	Read Committed	Dirty Read	The read data was changed by a transaction, that was then rolled back
3	Repeatable Read	Non-repeatable Read	Second read of the same data gives a result that is not equal to the first read, because the data was changed by another transaction between reads
4	Serialization	Phantom Read	Second data select by the same parameters is different from the first one, because the data was changed by another transaction between reads

• Isolation levels refer to the DB layer.
• Isolation levels are a tradeoff between data accuracy and performance: higher level gives higher accuracy, but lower speed.
• The default database level is usually Read Committed (PostgreSQL) or Repeatable Read (MySQL).

Answer 4

What the heck is the question?!

Isolation levels define the lock type and lock granularity used by the DBMS. Locking is essential in the context of DBMS's, as transactions are executed concurrently, by potentially many users. Higher transaction isolation--such as SERIALIZABLE--is safer--you can potentially eliminate dirty reads and phantom updates--but impose a penalty as serialized transactions limit concurrency and therefore preclude scalability.

What to do? Architect the application such that the logic limits the possibility of "bad data" by judiciously using serialized transactions when they're absolutely needed, but not such that concurrency is unnecessarily hampered.