5 minutes with – Spring Transaction Isolation Level

Understanding the isolation level of the database transaction is not so obvious as you might think. Let me lead you through the different isolation type in the way to find out the correct one to use in your next Dao application.

First, let me clear the concept that there is not a “correct” and “incorrect” isolation level to use in an application.  Every level has a different features and functions, so, the choice depends by the type of behaviour you would like to get .

Now, you’ve made the choice to use the transaction in your dao application. Ok, you’ve already done the correct choice, so it’s time to decide which transaction level you need to achieve your goal. Let’s go understand their different behaviour from 4 different types:

  • READ_COMMITTED
  • READ_UNCOMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE

READ_COMMITTED

It’s the common transaction level used. Locks the table in writing and release the reading locks immediately after the Select operation is concluded.

To explain the different levels, the example includes two users (Warehouse and Seller) that use the same database.

read_commitedThe “Warehouse” user selects the item from the catalogue with Id=5. The result is null. Afterwards, the user “Seller” inserts the record. Until the method is not completed the database Commit is not called. After that,  the user “Warehouse” can get the record making the same read.

 

 

 

 

 

 

 

 

 

 

The code is the follow:

@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
public void wareHouseReadComitted(Catalog catalog) throws Exception
{
	/*
	 * ReadRow
	 */
	System.out.println("Read Record");
	List<Catalog> catalogDB = selectCatalog(catalog.getIdCatalog());

	/*
	 * Read catalog
	 */
	if (catalogDB.size()>0)
		System.out.println(catalogDB.get(0) .toString());

	/*
	 * Wait the other transaction read
	 */
	Thread.currentThread().sleep(4000);

	/*
	 * ReadRow
	 */
	System.out.println("Read Record");
	List<Catalog> catalogDBSecondTime = selectCatalog(catalog.getIdCatalog());

	/*
	 * Read catalog
	 */
	if (catalogDB.size()>0)
		System.out.println(catalogDB.get(0) .toString());

}

For the Seller the code is:

@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
public void sellerReadComitted(Catalog catalog) throws Exception
{
	/*
	 * Wait the other transaction read
	 */
	System.out.println("Waiting for inserting record");
	Thread.currentThread().sleep(3000);
	/*
	 * Insert new item
	 */
	int row = insertCatalog(catalog);
	System.out.println("Record inserted");

}

READ_UNCOMMITTED

This level lets the others transactions to read data which are not still committed in the database. This lazy constraint avoid table lock but you might get “unfair” data.

read_uncommitedThe “Warehouse” user inserts the item catalogue with id=5. The “Seller” get the item although it hasn’t been committed by the “Warehouse” transaction.

The “Warehouse” transaction is rolled back by the Exception in the method. The result is to get a dirty reading for the “Seller” user.

 

 

 

 

 

 

 

 

 

The “Warehouse” code is:

@Transactional(isolation = Isolation.READ_UNCOMMITTED, rollbackFor = Exception.class)
public void wareHouseReadUncomitted(Catalog catalog) throws Exception
{
/*
* Insert new item
*/
int row = insertCatalog(catalog);
System.out.println("Row Inserted, Waiting for reading");
/*
* Wait the other transaction read
*/
Thread.currentThread().sleep(5000);
/*
* Rollback transaction
*/
throw new Exception("Insert Rollback");
}

And the code for “Seller” is:

@Transactional(isolation = Isolation.READ_UNCOMMITTED, rollbackFor = Exception.class)
public void sellerReadUncomitted(Catalog catalog) throws Exception
{
/*
* Wait the other transaction insert
*/
System.out.println("Waiting for insert record");
Thread.currentThread().sleep(2000);
/*
* ReadRow just inserted
*/
System.out.println("Read Record");
List<Catalog> catalogDB = selectCatalog(catalog.getIdCatalog());
/*
* Loop catalog
*/
for (Catalog item : catalogDB)
System.out.println(item.toString());
}

REPEATABLE_READ

This level avoid the non-repeatable reading issue generates when a method makes two reading action in the same transaction. On the other hand, the query isn’t able to get new and updated record that might be happens in other transaction.

repeatable_read

The select items for the “Warehouse” user gets the same value although a new item (Id=5) has been inserted by the user “Seller” in other transaction and, that transaction, has been committed.

 

 

 

 

 

 

 

 

 

 

The relative code for the user “Warehouse” is:

@Transactional(isolation = Isolation.REPEATABLE_READ, rollbackFor = Exception.class)
public void wareHouseRepeatableRead(int idcatalogStart, int idcatalogStop) throws Exception
{
/*
* ReadRow
*/
System.out.println("Read Record");
List<Catalog> catalogDB = selectBetweenCatalog(idcatalogStart, idcatalogStop);
/*
* Loop catalog
*/
for (Catalog item : catalogDB)
System.out.println(item.toString());
/*
* Wait the other transaction read
*/
Thread.currentThread().sleep(4000);
/*
* ReadRow
*/
System.out.println("Read Record");
List<Catalog> catalogDBSecondTime = selectBetweenCatalog(idcatalogStart, idcatalogStop);
/*
* Loop catalog
*/
for (Catalog item : catalogDBSecondTime)
System.out.println(item.toString());
}

And the corresponding for the “Seller” is

@Transactional(isolation = Isolation.REPEATABLE_READ, rollbackFor = Exception.class) 
public void sellerRepeatableRead(Catalog catalog) throws Exception
{
 /*
  * Wait the other transaction read
  */
 System.out.println("Waiting for inserting record");
 Thread.currentThread().sleep(2000);
 /*
  * Insert new item
  */
 int row = insertCatalog(catalog);
 System.out.println("Record inserted");
 
}

SERIALIZABLE

The last (but not less important) level lets you execute the query locking all the others queries (even in read). Concurrent transactions are not allowed due to the lock of the table.

serializable

The “Warehouse” user inserts a new record (item id=5). The “Seller” user reads the table and has been locked by the transaction until it has committed.

Once the transaction has completed the lock is released and the reading action is completed.

 

 

 

 

 

 

 

 

 

“Warehouse” code:

@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class)
public void wareHouseSerializable(Catalog catalog) throws Exception
{
/*
* Insert new item
*/
int row = insertCatalog(catalog);
System.out.println("Record inserted");
System.out.println("Transaction is blocked for 20 seconds");
/*
* Wait this transaction
*/
Thread.currentThread().sleep(20000);
}

“Seller” code:

@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class) 
public void sellerSerializable(int idcatalog) throws Exception
{
 /*
  * ReadRow
  */
 System.out.println("Read Record");
 List<Catalog> catalogDB = selectCatalog(idcatalog);
 /*
  * Loop catalog
  */
 for (Catalog item : catalogDB)
  System.out.println(item.toString());  
}

SUMMARY

As you can see,  every single level has own characteristics. To simplify the differences we can summarize them in 3 main types.

  • dirty reads
  • non-repeatable reads
  • phantom reads

Putting them in a Matrix we can easily find out the different transaction level behaviour.

dirty reads non-repeatable reads phantom reads
READ_UNCOMMITTED yes yes yes
READ_COMMITTED no yes yes
REPEATABLE_READ no no yes
SERIALIZABLE no no no

I’ve made a little example to run the different transaction level in one application. I’ve used multi thread to simulate the different users in the same context. The source is available here (Eclipse Project).

REFERENCE

http://en.wikipedia.org/wiki/Isolation_(database_systems)

http://www.byteslounge.com/tutorials/spring-transaction-isolation-tutorial

Advertisements

3 thoughts on “5 minutes with – Spring Transaction Isolation Level

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s