Transaction isolation levels

What is a Transaction ?

A Database transaction is set or group of operations which can be read or writes and are treated as a single unit of work, so they either all are done (committed) or none of them is done(roll back).

Transactions have ACID properties which is acronym for :

A - Atomicity

C - Consistency

I - Isolation

D - Dependency

In this blog post, we will focus on property 'Isolation'.

What is Transaction Isolation Level ? 

A transaction isolation level defines how much a transaction can see and be affected by the other concurrent transactions' changes.

What are different transaction isolation levels ?

There are four transaction isolation levels:

- Read Uncommitted

- Read Committed

- Repeatable Read

- Serializable

Different databases can support different set of isolation levels and have different default isolation level so when working with databases, we should be aware of what isolation level our database supports.

Here are some of the most commonly used databases and their default isolation level:

MySQL, MariaDB: Repeatable Read

Oracle, PostgreSQL, SQL Server: Read committed

Let us see each of these isolation level with example of MariaDB:

Note: every isolation level example starts with salary= 80,000

Read Uncommitted:

In this transaction isolation level, suppose transaction T1 has read row R1 and has made update but have not committed the updated data yet, then transaction T2 can read these uncommitted changes.

The problem with this is that if T1 fails later and all changes are rolled back, T2 has already read the updated data from T1 and now if it updates database on the basis of that it would cause database in inconsistent state. 

Lets see how it works with an example:

Session A and Transaction T1:

Then update the record in same transaction:

UPDATE employee SET salary = 81000 WHERE employee_id = 100;

Don't commit T1.

Session B and Transaction T2:

Now open another Session and start a new transaction T2 and within this transaction read the employee record updated in transaction T1 and it shows the uncommitted data:

When one transaction can read uncommitted data of another transaction, it is called Dirty read and to prevent Dirty reads we can use 'READ COMMITTED' isolation level which we will discuss next.

Read Committed:

With Read Committed isolation level, transaction T2 can only read the committed changes of T1 so there will not be any dirty read.

Session A, transaction T1:

Change transaction isolation level to READ COMMITTED. Start a new transaction T1 and update employee salary to 81000 without committing.

Session B, transaction T2:

Now open another session Session B and start a new transaction, T2 and read the employee salary by executing select statement and it still shows the old salary as now uncommitted data can not be read.

Session A, transaction T1:

Now I will go back to Session A and within Transaction T1, I will commit the changes by executing following statement:

COMMIT;

Session B, transaction T2:

Now if go back to Session B and from within Transaction T2, I read the salary again by executing select statement, it will show the updated salary, which is what we expect.

However  READ COMMITTED has following problems:

Non Repeatable Reads: 

Multiple reads in one transaction can result in different result if another transaction committed between multiple reads of first transaction.

Phantom Reads:

Multiple reads in one transaction can result in different result if another transaction inserted new records in another transaction which satisfies the criteria of select query in first transaction.

Repeatable Read:

Repeatable Read solves the problem of 'Non Repeatable Reads' as well as 'Phantom Read' at least with maria db(can be different for different DBs), which means with 'Repeatable Read' isolation level, if within transaction T1, same row is read multiple times, it will always return same result. In other words, no other transaction can modify or delete the row(s) read by Transaction T1 until transaction T1 is finished.

so if within transaction T1, row r1 is read and then within transaction T2, row r1 is read and one of its field is updated and even committed, if transaction reads row r1 again, it will still see the old row what it saw before updates of T2.

Let us see example of how Non repeatable reads does not happen with 'Repeatable Read'.

Session A, transaction T1:

Read the row with employee_id 100

Session B, transaction T2:

Updated the row with employee_id 100

Session A, transaction T1:

Let us see example of how Phantom reads does not happen with 'Repeatable Read'

Phantom read is basically when same transaction re-runs the same query and get different set of rows.

Session A, transaction T1:

A query with criteria that returns 2 records

Now lets add additional record in another transaction that fulfills same criteria as in previous query.

Session B, transaction T2:

Now lets again go back to transaction T1 and re-run the same query again and see the result:

As we can see, it still returns 2 records, so no phantom reads. In case of Phantom read, we would have got additional record that we inserted in transaction T2.

Next we will see isolation level Serializable.