Understanding Database Isolation Levels

Transaction Management is an essential mechanism of relational Database Management Systems. They allow for concurrency whilst maintaining the ACID properties: Atomicity, Consistency, Isolation and Durability. They are used to control and maintain the integrity of each action in a transaction, despite errors that may occur in the system.

Understanding the various isolation levels and choosing the one that fits your project and allows to achieve better performance or/and guarantee data consistency. Let’s take a look at them and how to define them on a Play 2 with PostgreSQL environment.

Isolation Levels

A Transaction consists of a series of read and write operations of database objects that are meant to be atomic: either all actions are carried out or none are. [Raghu p.523]

For performance reasons a DBMS might interleave transactions, sacrificing the isolation of a transaction from other concurrently executing transactions. They can run of different levels of isolation, each one having a bigger toll on performance although offering a more isolated execution context. DBMS manage transaction concurrency by applying Locks to the required objects. The more elevated isolation level the more locks the transaction will have to acquire in other to execute, thus preventing other concurrent transactions from using the same resources.

Let’s go over the major JDBC levels:

• TRANSACTION_NONEIn this mode, all read and write operations can execute concurrently.
• TRANSACTION_READ_COMMITTEDAn operation will only be able to read values that were committed, i.e written by a terminated transaction, before it started. This prevents the problem know as ‘Dirty Read’ (or Write-Read Conflict) which happens when a transaction T1, reads a value written by a another transaction T2, that is not yet committed. If T2 aborts, the value that T1 read will be wrong, thus causing an inconsistency.
• TRANSACTION_REPEATABLE_READAll operations within the transaction will only be able to read values that were commit previous to the start of the transaction. Adding this limitation solves the ‘Non-repeatable Read’ problem (or Read-Write Conflict). Assume that a transaction T1 reads a given value. If another transaction T2 overwrites that value, and T1 tries to read it again, T1 will get a different value without having changed it.
• TRANSACTION_SERIALIZABLEThis is the stricter mode. Transactions execute as they would in a serial execution mode. They still execute concurrently but if a transaction T1 tries to access any object that was used by another transaction T2, this will cause T1 to abort. In this mode, as well as preventing the previous issues, also the ‘Phantom Read’ is prevented. A ‘Phantom Read’ occurs when, during the course of a transaction T1, when another transaction T2 inserts or deletes rows. The same operation running on T1 context may yield a different result if executed before and after T2.

Isolation level on Play 2

To set the isolation level of the transactions on a Play 2 project use the following config:

db.default.isolation="VALUE"

The allowed values are NONE, READCOMMITTED, READUNCOMMITTED, REPEATABLE_READ and SERIALIZABLE. If you get a Configuration Error for an “Unknown isolation level” try adding a space after the value. This is due to a bug in the Play DB API.

Optionally, you can set the level per transaction, if you’re writing plain SQL, by defining the isolation level in the BEGIN TRANSACTION statement, like for example:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Or if you using the Slick framework you can set the level in a wrapper for the withTransaction method:

CodacyDB.database.withTransaction { session =>
        session.conn.setTransactionIsolation(
            java.sql.Connection.TRANSACTION_READ_COMMITTED
        )
  ...
}

We’ve seen and understood different database isolation levels applied to transactions. We’ve specified how this can be achieved in Play using Slick. We’ve used this internally in the process of scaling Codacy to thousands of projects under analysis. Would love to hear what you’re currently using for your application!

Follow the discussion at hacker news.

Edit: We just published an ebook: “The Ultimate Guide to Code Review” based on a survey of 680+ developers. Enjoy!

Note

This is a blog post of our Code Reading Wednesdays from Codacy (http://www.codacy.com): we make code reviews easier and automatic.

About Codacy

Codacy is used by thousands of developers to analyze billions of lines of code every day!

Getting started is easy – and free! Just use your  GitHub, Bitbucket or Google account to sign up.

GET STARTED