Isolation concept for transaction

Preface:

When two or more transactions are going concurrently, then how to manage this situation so that one transaction won't impact to another, because transactions are a crucial part of any application.

Solution: the above problem can be resolved using isolation concept.

Let us discuss about the problems that comes during concurrency:

Dirty Reads:

This is an uncommitted dependency problem. This problem comes when writing/updating process updates the database and simultaneously reading process reads the same database, so reading program will get dirty data which writing/updating process has not committed yet.

Solution: The solution to this problem is, until one process is not completed with the updates till then other process can't read the incomplete data.

Non repeatable reads:

This problem is also known as inconsistent analysis problem. When reading process reads the data from the database twice then in between reading it got changed as updater/writer process updating the database. Suppose process A read the data from database and after that process B update the database, after that process A again reads the data, now the data read by the process A will be different what it read earlier.

Solution: The reading process will read the document once writer/updater process completely updates the database.

Phantom Reads:

This problem occurs when reading and inserting/deleting processes work concurrently. When reading process perform read operation with same query twice and get different row count in each case. Reading process reads the data from database and after that inserting/deleting process perform insertion or deletion on same database and then again reading process is reading the same database. So the data will be same but the no. of rows returned will be impacted.

Note: Here the impacted rows whether it is deleted ones or inserted one are called as phantom rows. This is a special case of non repeatable reads as that involves only in the modification of data.

Solution: Don't allow insertion / deletion until reader finishes the read operation.

Difference between Dirty and Non Repeatable reads and phantom reads

In dirty read, reader can see the updated data if writer/updater process don't commit data and roll it back. In non repeatable reads, reader reads the updated data which is committed by the writer/updater process. In phantom read, only row count will be different, which is the result of insertion and deletion.

Solution for above mentioned problem is isolation levels:

Isolation levels are provided by JDBC API in java and these can be applied to Connection interface. Let us discuss on each of isolation levels

TRANSACTION_READ_UNCOMMITTED: this provides lower level isolation as it doesn’t prevents dirty, on repeatable and phantom read problem. It can ensure while reading it will not have any corrupted data.

JDBC_TRANSACTION_READ_COMMITTED: this isolation level only prevents the dirty read. It doesn’t allow the uncommitted data to be visible but it can’t prevent the non repeatable and phantom read.

JDBC_TRANSACTION_REPEATABLE_READ: this provides a better isolation level and able to avoid both dirty and non repeatable reads. It holds the read lock on all the rows which are being read until the read operation is finished, so that other transaction can update the data of these rows. But still phantom read problem will be there.

JDBC_TRANSACTION_SERIALIZABLE: this holds a lock on WHERE clause and will not allow any insertion or deletion. This is the high level of isolation which can prevent the dirty, non repeatable and phantom read problem.

How to implement isolation levels:

Using Connection interface isolation can be used. Please refer below some points for getting and setting isolation levels.

Connection con // this will create a reference for connection.
con.setTransactionIsolation(Connection.JDBC_TRANSACTION_SERIALIZABLE)

Also for fetching the isolation level:

con.getTransactionIsolation() this will return an int value.

Thanks for sparing you precious time and reading this conversation. Please feel free to add you comment to this post.