AUTO1 Group

Database Transaction Isolation

By Hayk Jhangiryan

Hayk is Senior Java Engineer at AUTO1 Group.

< Back to list
Coding Jun 20 2018

Database Transaction Isolation

What is transaction isolation level?

Transaction isolation level defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. 

Let's have a look at phenomena which can occur during the execution of concurrent transactions:

  • Dirty read - A transaction may read data written but not yet committed by other transactions.            

    example: Transaction T1 modifies a row. Transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 have read a row that was never committed and that may be considered to have never existed.

  • Nonrepeatable read - A transaction re-reads data it had previously read and finds that data has been modified by another transaction (that committed since the initial read).            

    example: Transaction T1 reads a row. Transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

  • Phantom read - A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

    example: Transaction T1 reads the set of rows N that satisfy some search condition. Transaction T2 then executes SQL-statements that generate one or more rows that satisfy the search condition used by transaction T1. If transaction T1 then repeats the initial read with the same search condition, it obtains a different set of rows.

Isolation is the I in the acronym ACID. In ACID, atomicity and durability are strict requirements. Whereas consistency and isolation are more of configuration (kind of). Besides, they are closely related, so much so that the SQL standard defines four levels of transaction isolation based on the consistency they provide (from least to the most consistency):

ISOLATION LEVEL DIRTY READ NON-REPEATABLE READ PHANTOM READ
READ UNCOMMITTED possible possible possible
READ COMMITTED -- possible possible
REPEATABLE READ -- -- possible
SERIALIZABLE -- -- --

Different isolation levels are defined based on whether they allow (or prevent) the above phenomena (as described by the SQL standard). Note that transaction isolation does not affect the changes made by the same transaction. It means a transaction always sees all the changes made by itself.

Now what?

Inside Java services, the transaction isolation level can be controlled by Spring @Transactional annotation which supports isolation attribute. If you do not specify the isolation level in the code explicitly, the default isolation level will be applied, which means that the default isolation level of the underlying datastore will be used.

It is worth to mention that default isolation levels for each system might be different. For example, the default isolation level for MySQL 5.7 (InnoDB) and PostgreSQL 9.5 is respectively, REPEATABLE READ and READ COMMITTED. To check the default isolation level, query:

  • SHOW VARIABLES LIKE 'tx_isolation'; -- on MySQL
  • SHOW default_transaction_isolation'; -- on PostgreSQL

References

  1. MySQL Transaction Isolation Levels
  2. Transaction Isolation in PostgreSQL
  3. Useful article
Stories you might like:
CodingJul 2
By Nicholas Peretti

Create forms at scale with Formik and Yup

CodingJun 24
By Wojciech Oroński

Yet another case study of developing serverless apps with PHP.

CodingApr 4
By Chirag Swadia

How we use ES6 generators instead of thunk to simplify our React Redux application code and...