DBA Talk – What is Isolation Level

This week I came across a topic in a group of DBAs where the following statement was made:

Oracle is the only DB where writing does not block reading and reading does not block writing

Before we discuss whether Oracle is, in fact, the only RDBMS doing the above, what does the phrase mean?

Well, to simplify as this content would suffice for an entire article, read and write locks are part of every RDBMS that complies with ACID properties (atomicity, consistency, isolation, and durability). In this article we will focus on the letter I which means:

Isolation. An ongoing, unconfirmed transaction must remain isolated from any other transaction.

In the real world, as the transactions happen concurrently, there are some phenomenons that can happen during the reading, they are:

Dirty reads

A dirty read occurs when a transaction can read data from a row that has been modified by another transaction that has not yet been committed.

Non-repeatable reads

A non-repeatable read occurs when, during the course of a transaction, a SELECT is executed twice and the values within the SELECT differ between the readings (The difference for the dirty read is that in the case there is a commit).

Phantom reads

A phantom read occurs when, in the course of a transaction, new lines are added by another transaction to the records being read (again in this case there is a commit of the transaction modifying the data). In this case, the problem is that there is no range lock guaranteeing the consistency of the data.

Explained the possible phenomenons that may occur, let us return the original statement:

Oracle is the only DB where writing does not block reading and reading does not block writing

This statement is incorrect. All banks that are part of ISO/IEC 10026 comply with ACID have Isolation. Moreover, modern RDBMS such as Oracle, MySQL, Postgres, and SQL Server can handle transactions in parallel.

MySQL itself describes in its documentation:

InnoDB offers all four transaction isolation levels described by the SQL: 1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.

Let’s practice:

We have the table with the data:

Let’s start a transaction in a session:

And open a second transaction and initiate a change:

Note that in session2 there is no commit! Let’s look at session1:

We realize that using the REPEATABLE READ level we do not have dirty reads and SELECT can be executed in parallel. The same can be observed in Oracle, Postgres, and SQL Server.

Now let’s assume that for some application demand I need to read the data before commit (ie, dirty read). For this, we will use the READ UNCOMMITTED isolation level.

Let’s start the session1:

And let’s open a session2 and execute an UPDATE:

Checking session1:

Again, the sessions were run in parallel. These changes were made directly to the database and no changes are needed to be made to the application.

To summarize, it is necessary to define the purpose of the application when reading the data. There are many ways to extract the data, however, you must keep in mind the objective so that the data is not misinterpreted.

Until the next time!

Leave a Reply