I am wondering if anyone knows the history of why READ COMMITTED is the default transaction isolation level for PostgreSQL, SQL Server, Oracle, Vertica, DB2, Informix, and Sybase.

MySQL uses default REPEATABLE READ, at least with InnoDB, as do SQLite and NuoDB (they call it "Consistent Read").

Again, I am not asking for what the differences are between different isolation levels, but rather for some explanation of why the default was chosen to be READ COMMITTED in so many SQL databases. My wild guesses are: small performance benefit, ease of implementation, some recommendation in the SQL standard itself, and/or "that's the way it's always been". The obvious downside of this choice is that READ COMMITTED tends to be quite counterintuitive for developers and can lead to subtle bugs.

Though, I don't remember any reference mentioned in BOL thus can't provide it here but as per my understanding it is related to locking. Higher level of isolation level can cause locking issues. READ COMMITTED ISOLATION is more towards write locks than read, which fits good in OLTP environment when compared to OLAP. Commercial databases decides default isolation level which fits perfect for their internal algorithm. Choosing isolation level depends upon how RDBMS wants to deal with locking and care about reading correct data. Most of the RDBMS prefers READ COMMITTED for faster read, performance and to minimize locking.

A lower isolation level increase concurrency and descrease waiting for other transaction but increase the chances of reading incorrect data. However, a higher isolation level decreases concurrency and increases waiting for other transaction, but decreases the chance of reading incorrect data.

As per Wikipedia

The default isolation level of different DBMS's varies quite widely. Most databases that feature transactions allow the user to set any isolation level. Some DBMS's also require additional syntax when performing a SELECT statement to acquire locks (e.g. SELECT ... FOR UPDATE to acquire exclusive write locks on accessed rows).

However, the definitions above have been criticized [3] as being ambiguous, and as not accurately reflecting the isolation provided by many databases:

This paper shows a number of weaknesses in the anomaly approach to defining isolation levels. The three ANSI phenomena are ambiguous. Even their broadest interpretations do not exclude anomalous behavior. This leads to some counter-intuitive results. In particular, lock-based isolation levels have different characteristics than their ANSI equivalents. This is disconcerting because commercial database systems typically use locking. Additionally, the ANSI phenomena do not distinguish among several isolation levels popular in commercial systems.

You can read the complete synopsis here.

  • isolation level should not cause any lock issues - it should only (in worst case) cause rollback(s) - also please note that locking (select for update) is entirely different concept (often used with isolation levels lower than serializable) – Kamil Tomšík May 11 '16 at 16:29

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.