ISOLATION levels in Transaction

Question

Hi,

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

@skaffman I find the explanation given in the wiki little daunting and difficult to understand. Could you please simplify it. Thanks.

Answer 2

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 3

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 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.