1

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

Syed
  • 2,471
  • 10
  • 49
  • 89
  • Does this answer your question? [Spring @Transactional - isolation, propagation](https://stackoverflow.com/questions/8490852/spring-transactional-isolation-propagation) – Julien Feb 14 '20 at 10:08
  • I understand how read uncommited works. I want a real time example where I have to implement. – Syed Feb 14 '20 at 10:15
  • 1
    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 Feb 14 '20 at 11:48
  • @NathanHughes, Can you pls give me an example where READ UNCOMMITTED is used with a scenario – Syed Feb 14 '20 at 12:00
  • 1
    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 Feb 14 '20 at 12:19
  • @NathanHughes Ouch. Is it an abomination in the sense that nobody sane would ever implement something like that (I totally agree) or in the sense that there is something technically wrong about it? I would hate to have an accepted answer that is technically wrong... – Filippo Possenti Feb 14 '20 at 13:03
  • 1
    @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 Feb 14 '20 at 13:10
  • Then we totally agree... albeit I would extend the case to poor implementation choices in general rather than just mainframes. In the meantime, let me edit the answer to put a link to a very useful explanation that considers both your mainframe case and the more general case. – Filippo Possenti Feb 14 '20 at 13:16
  • Very nice to have a descriptive comments – Syed Feb 14 '20 at 13:37
  • It's very useful on testing, I already implemented it in a production-ready service (test scope), and it's by far the cleanest approach I found for messing with db through 600 IT tests, with 0 problems. Especially efficient for REST endpoints testing, for instance, a POST side-effects. See my comment in https://stackoverflow.com/questions/33646012/postgresql-transaction-isolation-read-uncommitted#comment127939032_33648607. – Whimusical May 29 '22 at 09:02

1 Answers1

3

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.
Filippo Possenti
  • 1,300
  • 8
  • 18
  • If you use Serializable, you don't have any chance to encounter these problems. It's very confusing, if READ UNCOMMITTED doesn't make any sense, why did Spring add it – Syed Feb 14 '20 at 11:19
  • If you use SERIALIZABLE, you can't implement the above type of programs as they need to span across transactions and sessions and SERIALIZABLE doesn't allow that. That's why there is READ UNCOMMITTED and READ COMMITTED. Some systems can be very complex and they can't necessarily be simplified or the amount of resources to do so may be so high that other solutions are advised. These two isolation levels allow to cater for more scenarios. – Filippo Possenti Feb 14 '20 at 11:24
  • Question here, as you said why are you going to send the generated key to service B, without committing. Any reasons? – Syed Feb 14 '20 at 11:30
  • It really depends on the specific system. Ideally such a system wouldn't exist but in reality there are systems where dirty reads do still provide value. My example is intentionally simplistic as I encountered a similar system only on one occasion. In my case the reason was simply "poor design choices". Unfortunately, sometimes you can't go back and revisit such choices so you have to work with the tools you have at your disposal. Normally the only isolation levels you deal with are SERIALIZABLE and REPEATABLE READ. Still, they are not 100% sufficient... hence the other two options. – Filippo Possenti Feb 14 '20 at 11:37
  • Note that Spring adds them because databases expose them. In fact, some databases expose other levels too. – Filippo Possenti Feb 14 '20 at 11:38