понедельник, 27 июня 2011 г.

Transactions and Isolation Levels explained once and for all (nHibernate examples)

Transactions and Isolation Levels explained once and for all (nHibernate examples)

A question about transactions and isolation levels is one of my favourites when interviewing software engineers. Some of them heard about it, some of them know what they are, some of them can list them, some of them can describe them in detail. But there are also developers that don't have the slightest idea what are isolation levels.

Today, as a part of my nHibernate series I'll show you a unit test which shows you the behaviour of 4 isolation levels: read uncommitted, read committed, repeatable read, and serialisable.

The project

For this excercise I used nHibernate project I wrote last time. The post is here: Playing around with nHibernate: the basics.

Test structure

OK. To test concurent accesses I had to create 2 independent sessions and 2 independent transactions. I create sessions and transactions in [TestInitialize] and dispose them in [TestCleanup]. Just in case something goes wrong I delete all records from app_country in [ClassCleanup] method.

The structure looks like this:

[TestClass] public class TransactionsTest {      private ISession SessionA { get; set; }     private ISession SessionB { get; set; }     private ITransaction TransactionA { get; set; }     private ITransaction TransactionB { get; set; }      [TestInitialize]     public void Startup()     {         SessionA = OpenSession();         SessionB = OpenSession();         TransactionA = SessionA.BeginTransaction();         TransactionB = SessionB.BeginTransaction();     }      [TestCleanup]     public void Cleanup()     {         TransactionA.Dispose();         TransactionB.Dispose();         SessionA.Dispose();         SessionB.Dispose();     }      [ClassCleanup]     public static void ClassCleanup()     {         using (ISession session = OpenSession())         {             using (ITransaction transaction = session.BeginTransaction())             {                 IQuery query = session.CreateSQLQuery("delete from app_country");                 query.ExecuteUpdate();                 transaction.Commit();             }         }     }      private static ISessionFactory _sessionFactory;     private static ISession OpenSession()     {         if (_sessionFactory == null)         {             Configuration configuration = new Configuration();             configuration.AddAssembly(Assembly.GetCallingAssembly());             _sessionFactory = configuration.BuildSessionFactory();         }         return _sessionFactory.OpenSession();     } }

Rolling back transactions

Before I show you the isolation levels I'd like to show you how to rollback a transaction. The code is very simple and self commenting:

[TestMethod] public void TestRollback() {     Country country = new Country()     {         Description = "Poland",         ContinentId = 1     };      try     {         SessionA.Save(country);         Assert.IsTrue(country.Id > 0);         TransactionA.Rollback();         Assert.IsTrue(TransactionA.WasRolledBack);         IQuery query = SessionA.CreateQuery("FROM Country WHERE Id = :id");         query.SetInt32("id", country.Id);         Country nonExistingRolledBackCountry = query.UniqueResult<Country>();         Assert.IsNull(nonExistingRolledBackCountry);     }     catch (Exception e)     {         Assert.Fail("Test failed " + e.Message);     } }

Default Isolation Level

OK. When you create a transaction using just BeginTransaction() (without the isolation level parameter) it starts the transaction in default isolcation level. If you don't know what is the default isolation level you will know it after reading this post :)

This and all next tests use SessionA and SessionB objects to simulate 2 concurent clients.

public void TestDefaultIsolationLevel() {     Country countryA = new Country()     {         Description = "Poland",         ContinentId = 1     };      try     {         // save an object in session A         SessionA.Save(countryA);         Assert.IsTrue(countryA.Id > 0);          // try to retrieve newly inserted object from session B         IQuery query = SessionB.CreateQuery("FROM Country WHERE Id = :id");         query.SetInt32("id", countryA.Id);         Country countryB = query.UniqueResult<Country>();     }     catch (GenericADOException ex)     {         // this is the expected behaviour!         Assert.AreSame(typeof(GenericADOException), ex.GetType());     }     catch (Exception ex)     {         Assert.Fail("Unexpected exception " + ex.Message);     }     finally     {         TransactionA.Rollback();     } }

Result: Session B client is not able to retrieve the country because it's not yet committed. The root cause is timeout exception.

Read Uncommitted Isolation Level

This is the lowest isolation level. In this isolation level you can retrieve uncommitted records:

[TestMethod] public void TestReadUncommittedIsolationLevel() {     Country countryA = new Country()     {         Description = "Poland",         ContinentId = 1     };      // close session B transaction     TransactionB.Dispose();     // create a new one with ReadUncommitted isolation level     TransactionB = SessionB.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);      try     {         // save an object in session A         SessionA.Save(countryA);         Assert.IsTrue(countryA.Id > 0);          // try to retrieve newly inserted object from session B         IQuery query = SessionB.CreateQuery("FROM Country WHERE Id = :id");         query.SetInt32("id", countryA.Id);         Country countryB = query.UniqueResult<Country>();         Assert.IsNotNull(countryB);          // rollback transaction A         TransactionA.Rollback();         // session B stays with dirty record countryB     }     catch (Exception ex)     {         Assert.Fail("Unexpected exception " + ex.Message);     } }

Result: Session B reads dirty (uncommitted) record, session A rolls back the transaction, Session B stays with dirty record.

Read Committed Isolation Level

This isolation level guarantees no dirty records.

[TestMethod] public void TestReadCommittedIsolationLevel() {     Country countryA = new Country()     {         Description = "Poland",         ContinentId = 1     };      TransactionB.Dispose();     // create new transaction     TransactionB = SessionB.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);      try     {         // save an object in session A         SessionA.Save(countryA);         Assert.IsTrue(countryA.Id > 0);          // try to retrieve newly inserted object from session B         IQuery query = SessionB.CreateQuery("FROM Country WHERE Id = :id");         query.SetInt32("id", countryA.Id);         Country countryB = query.UniqueResult<Country>();     }     catch (GenericADOException ex)     {         // this is expected behaviour!         Assert.AreSame(typeof(GenericADOException), ex.GetType());     }     catch (Exception ex)     {         Assert.Fail("Unexpected exception " + ex.Message);     }     finally     {         TransactionA.Rollback();     } }

Result: Session B cannot read uncommitted record.

Repeatable Read Isolation Level

This isolation level guarantees repeatable reads. If a repeatable read isolation level is used, a read lock is acquired on fetched records.

[TestMethod] public void TestRepeatableReadIsolationLevel() {     Country countryA = new Country()     {         Description = "Poland",         ContinentId = 1     };     Country countryB = null;      // close current session B transaction     TransactionB.Dispose();     // open new transaction with RepeatableRead isolation level     TransactionB = SessionB.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);      try     {         // save an object in session A         SessionA.Save(countryA);         Assert.IsTrue(countryA.Id > 0);          TransactionA.Commit();         // open new transaction         TransactionA.Dispose();         TransactionA = SessionA.BeginTransaction();         // retrieve from session A         IQuery queryA = SessionA.CreateQuery("FROM Country WHERE Id = :id");         queryA.SetInt32("id", countryA.Id);         countryA = queryA.UniqueResult<Country>();          // retrieve from session B         IQuery queryB = SessionB.CreateQuery("FROM Country WHERE Id = :id");         queryB.SetInt32("id", countryA.Id);         countryB = queryB.UniqueResult<Country>();          SessionA.Delete(countryA);         TransactionA.Commit();     }     catch (GenericADOException ex)     {         // this is the expected behaviour!         Assert.AreSame(typeof(GenericADOException), ex.GetType());     }     catch (Exception ex)     {         Assert.Fail("Unexpected exception " + ex.Message);     }     finally     {         SessionB.Delete(countryB);         TransactionB.Commit();     } }

Result: Session A cannot update/delete country, because session B acquired a read lock thanks to using a transaction with a repeatable read isolation level.

Some databases don't acquire read locks (called range-locks) when fetching large data sets like where Id between (0, 1000000). Thus phantom records can occur.

Serialisable Isolation Level

This is the most resource-consuming (and thus slowest) transaction level. But it guarantees a complete transaction isolation. All transactions are serialisable. No phantom records occur.

[TestMethod] public void TestSerialisable() {     Country countryA = new Country()     {         Description = "Poland",         ContinentId = 1     };      // close current session B transaction     TransactionB.Dispose();     // open new transaction with Serializable isolation level     TransactionB = SessionB.BeginTransaction(System.Data.IsolationLevel.Serializable);      try     {         // save an object in session A         SessionA.Save(countryA);         Assert.IsTrue(countryA.Id > 0);          TransactionA.Commit();         // open new transaction         TransactionA.Dispose();         TransactionA = SessionA.BeginTransaction();         // retrieve in 1 session         IQuery queryA = SessionA.CreateQuery("FROM Country WHERE Id = :id");         queryA.SetInt32("id", countryA.Id);         countryA = queryA.UniqueResult<Country>();          // if I would like to delete a record there would be a timeout exception see TestRepeatableReadIsolationLevel()         // alghought I haven't used any INSERT/UPDATE/DELETE statements I have to release read locks         // if I close the transaction B, transaction A will work         TransactionB.Rollback();          SessionA.Delete(countryA);         TransactionA.Commit();     }     catch (GenericADOException ex)     {         // this is expected behaviour!         Assert.AreSame(typeof(GenericADOException), ex.GetType());     }     catch (Exception ex)     {         Assert.Fail("Unexpected exception " + ex.Message);     } }

Summary

So, what is the default transaction isolation level?

cheers,
Łukasz

Комментариев нет:

Отправить комментарий