vladmihalcea.com: A BEGINNER’S GUIDE TO TRANSACTION ISOLATION LEVELS IN ENTERPRISE JAVA

http://vladmihalcea.com/2014/12/23/a-beginners-guide-to-transaction-isolation-levels-in-enterprise-java/

A relational database strong consistency model is based on ACID transaction properties. In this post we are going to unravel the reasons behind using different transaction isolation levels and various configuration patterns for both resource local and JTA transactions.

In a relational database system, atomicity and durability are strict properties, while consistency and isolation are more or less configurable. We cannot even separate consistency from isolation as these two properties are always related.

The lower the isolation level, the less consistent the system will get. From the least to the most consistent, there are four isolation levels:

• READ UNCOMMITTED
• READ COMMITTED (protecting against dirty reads)
• REPEATABLE READ (protecting against dirty and non-repeatable reads)
• SERIALIZABLE (protecting against dirty, non-repeatable reads and phantom reads)

Although the most consistent SERIALIZABLE isolation level would be the safest choice, most databases default to READ COMMITTED instead. According to Amdahl’s law, to accommodate more concurrent transactions, we have to reduce the serial fraction of our data processing. The shorter the lock acquisition interval, the more requests a database can process.

As we previously demonstrated, application level repeatable reads paired with an optimistic locking mechanism are very convenient for preventing lost updates in long conversations.

In a highly concurrent environment, optimistic locking might lead to a high transaction failure rate. Pessimistic locking, like any other queuing mechanism might accommodate more transactions when giving a sufficient lock acquisition time interval.

Apart from MySQL (which uses REPEATABLE_READ), the default isolation level of most relational database systems is READ_COMMITTED. All databases allow you to set the default transaction isolation level.

Typically, the database is shared among multiple applications and each one has its own specific transaction requirements. For most transactions the READ_COMMITTED isolation level is the best choice and we should only override it for specific business cases.

This strategy proves to be the very efficient, allowing us to have stricter isolation levels for just a subset of all SQL transactions.