Isolation Level in Rails

we always use transaction in our rails application explicitly or implicitly. when we useActiveRecord#save, we actually use transaction implicitly. But when we want to ensure several database operations only occur when all operations succeed (atomic)we use transaction explicitly by calling ActiveRecord::Base::Transaction. But we seldom know or use options for transaction. There are several option we have, but we will focus on isolation.

Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

There are 4isolation level in Rails

1. read_uncommitted
2. read_committed
3. repeatable_read
4. serializable

But before we jump into the isolation level we need to understand first what violation in transactions.

Dirty Read

Dirty read happens when transaction reads other uncommited transaction

Non Repeatable Read

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

Phantom Read

A phantom read occurs when, in the course of a transaction, new rows are added by another transaction to the records being read.

Now let us start the isolation levels:

read_uncommitted

This is the lowest isolation level, because it allows dirty read to happen. But it has an advantage of performance, because it avoids acquire and release lock.

read_committed

In this isolation level, it ensure that we only read committed data.

repeatable_read

In this isolation level, it will prevent non repeatable read. It keeps read and write locks (acquired on selected data) until the end of the transaction. But range lock is not managed, so this isolation level can not prevent phantom read.

serializable

In this isolation level, write and read are locked, and also range locked must be acquired ( when we use select and where in transaction)

If we put this on table:

isolation levels vs read phenomena

So the question is ‘what is default isolation in rails’ ? The answer is ‘it depends’. It depends what database you are using. Read committed is the default isolation level in PostgreSQL and Oracle, MySQL defaults to read repeatable.

sources:

A Gentle Introduction to Isolation Levels

MySQL :: MySQL 8.0 Reference Manual :: 15.5.2.1 Transaction Isolation Levels

PostgreSQL: Documentation: 10: 13.2. Transaction Isolation

The Read Uncommitted Isolation Level

Isolation (database systems) - Wikipedia