Why and when should I use READ UNCOMMITTED isolation level in Spring transaction isolation level

1
votes

This might be a very generic question. But, still I'm not able to find a proper solution/answer when and why I should use READ UNCOMMITTED isolation level. Most of the articles and spring doc says, the most efficient way is to use SERIALIZABLE. In that case, why spring transaction management has come up with READ UNCOMMITTED & READ COMMITTED isolation levels if they are not efficient.

I hope at least here I could get answers.

Thanks in advance

1
springtransaction-isolation
Does this answer your question? Spring @Transactional - isolation, propagation – Julien
I understand how read uncommited works. I want a real time example where I have to implement. – Syed
It is a strange choice of words to say that serializable isolation level is “efficient”. The whole reason the other isolation levels exist is because serializable doesn’t perform well enough in many circumstances. Also should be mentioned that some dbms don’t even use read uncommitted (Postgres, Oracle come to mind). – Nathan Hughes
Best advice is never use read uncommitted. Sometimes you see it in clunky old mainframe systems. It is a dirty way to work around locking issues, it indicates nobody took sane measures like implementing caching or creating a separate reporting database. The example that is described in the accepted answer here is an abomination btw. – Nathan Hughes
@Filippo: sorry for being unclear, i meant the first sense. Though for mainframe systems lots of things we would otherwise think of as unacceptable are just normal life. – Nathan Hughes

1 Answers

3
votes

I will start by saying that I do share the belief that it's rather hard to encounter cases where such levels of isolation are necessary. Most of the time you want to start with either REPEATABLE READ or SERIALIZABLE.

One possible (albeit insane) use of READ UNCOMMITTED is for distributed systems that run different database sessions but all need to get prospect indexes for "loose" foreign keys. Say you have two services that communicate to each other through REST and that manage transactionality by coordinating one another:

  1. A inserts a row and makes a call to B passing the prospected key that the database generated, then waits for B
  2. B inserts in a different table using the key provided by A to retrieve some data, then commits its own transaction and finally returns a success for use by A
  3. A commits its own transaction

At this point rows in both tables have been inserted with correct keys. The problem is then shifted to the hopefully rare cases where something goes wrong in step (3).

I would argue among other things that the key shouldn't be generated by the database... but for existing systems you don't necessarily have the freedom to decide where keys are generated nor you have the freedom to re-implement something even when you are left flabbergasted by how bad the implementation is.

One possible use of READ COMMITTED could be a software that exposes one database session per user, while at the same time wanting the user to be able to refresh the contents and see new data generated by different transactions. One good example are database management frontends (Sql Server Management Studio, Toad, Squirrel and so on).

The following link explains a bit more in detail how these isolation levels are used in the industry. An excerpt is also copied here for easier referencing:

http://www.dbta.com/Columns/DBA-Corner/The-Danger-of-Dirty-Reads-98511.aspx

Programs that read database data can access numerous rows and are therefore susceptible to concurrency problems. To get around this issue, most major RDBMS products support read-through locks, also known as “dirty read” or “uncommitted read,” to help overcome concurrency problems. When using uncommitted reads (UR), an application program can access data that has been changed, but is not yet committed. Dirty read capability is commonly implemented using an isolation level, but the exact naming and implementation differs by DBMS vendor.

A program using dirty reads will read data without taking locks. This enables the application program to read data contained in the table as it is being manipulated. And it generally improves performance and availability of data because no locking mechanism is invoked during the process.

...

There are a few specific situations in which the dirty read capability may make sense. Consider the following cases:

  • Access is required to a reference, code, or look-up table that is basically static in nature. Due to the non-volatile nature of the data, a dirty read would be no different than a normal read the majority of the time. In those cases when the code data is being modified, any application reading the data would incur minimal, if any, problems.
  • Statistical processing must be performed on a large amount of data. For example, you may wish to determine the average age of female employees within a certain pay range. The impact of an uncommitted read on an average of multiple rows may be minimal because a single value changed may not greatly impact the result.
  • Dirty read can prove invaluable in a data warehousing environment. A data warehouse is used for online analytical processing and, other than periodic data propagation and/or replication, access is read-only. An uncommitted read is perfect in a read-only environment since it can cause little damage because the data is generally not changing.
  • In those rare cases when a table, or set of tables, is used by a single user only, UR can make a lot of sense. If only one individual can be modifying and accessing the data, locking is only adding overhead.
  • Finally, if the data being accessed is already inconsistent, little harm can be done using a dirty read to access the information.

The dirty read capability can provide relief to concurrency problems and deliver faster performance in very specific situations. Be certain to understand the implications of the UR isolation level and the “problems” it can cause before diving headlong into implementing it in your production applications.