Welcome to the website of Davis Fiore

A transaction can execute multiple reads, so data integrity became an important issues when transactions are executed in parallel. A locking mechanism is needed to avoid wrong readings.

There are different levels of isolation, depending on the level of integrity required. They may be set on the server (DBMS) or on the client (jdbc, Hibernate etc)

Here are the levels of isolation, in relation to the types of read permitted:

Isolation level                Dirty Reads   Non-Repeatable Reads   Phantom Reads
--------------------------------------------------------------------------------
TRANSACTION_READ_COMMITTED         no               yes                  yes 
TRANSACTION_READ_UNCOMMITTED       yes              yes                  yes
TRANSACTION_REPEATABLE_READ        no               no                   yes
TRANSACTION_SERIALIZABLE           no               no                   no

TRANSACTION_READ_UNCOMMITTED means there are no locks and it's possible to read uncommitted data. This can give DIRTY READS, that is reads of non-permanent values.

TRANSACTION_READ_COMMITTED means only committed data can be read. But it's still possible to have different reads, if in the meantime an other transaction has been committed. That reads are known as NON-REPEATIBLE READS because the same request return different data in the same transaction.

TRANSACTION_REPEATABLE_READ avoids the problem of non-repeatable reads, but phantom reads can still happen. A phantom read happen when an other transaction insert a new row or update a new row in a way that the second read in the initial transaction return one more row (phantom).

TRANSACTION_SERIALIZABLE prevents any data integrity issue, but obviously has a high cost on the performance.