Resources
Data Access has been discontinued. Please refer to this page for more information.

Managing Isolation Level

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

This article will introduce you to the means provided by Telerik Data Access for changing the isolation level applied for each transaction:

What is Isolation Level?

The default isolation level of the transactions employed by the DBMSs is provided through the SQL-92 standard and specifies that only committed data is read and uncommitted changes are not visible. It guarantees that the rows that are read at a given moment will not be modified by another operation and that the read will not include data that currently are subjects of modification. With this isolation level each row that is read is locked for editing but is visible to other operations that may need to do so, and each row that is edited is blocked, meaning that neither read nor other CUD operations are aware of it.

In many situation this behavior provides correct sets of data and the performance of the application is not an issue. It may lead, however, to data phenomena like Nonrepeatable reads and Phantom reads (a detailed description is provided by this MSDN article). Besides that, there are situations where a transaction contains a long-running operation and using the default isolation level can lead to deadlocks between concurrent transactions and therefore a slow retrieval and manipulation of the data. A solution in the latter cases could be the utilization of another isolation level.

Isolation Levels Set Through Telerik Data Access

Telerik Data Access allows you to customize the isolation level with each instance of the context you create. The possible options are:

  • ReadCommitted - the default isolation level.
  • ReadUncommitted - allows the reading operations to view the uncommitted data.
  • Snapshot - it guarantees that the reads performed within a given context instance will see a consistent snapshot of the data, the last committed version. Once a transaction with isolation level Snapshot begins, and concurrent transactions start changing (or change) the data, the one with Snapshot will not be able to commit its changes, if any.

The isolation level of a context instance could be set before the execution of any data modifying or read operations.

When an isolation level is specified on a context instance, the read operations will always be executed within a transaction on the database server. This instance of the context will disregard the value of the backend property ReadWithoutTransactions.

The customization is performed through the IsolationLevel property exposed through the Telerik.OpenAccess.IContextOptions interface. The interface is implemented by the OpenAccessContextBase class which is inherited by the OpenAccessContext class. The OpenAccessContext class is the foundation for the implementation of the specific context in your model.

The IsolationLevel property is of type System.Data.IsolationLevel. It is a good practice to add reference to the System.Data namespace in your code files.

You can set the isolation level as demonstrated below:

using (EntitiesModel dbContext = new EntitiesModel())
{
    dbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    //dbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadUncommitted;
    //dbContext.ContextOptions.IsolationLevel = IsolationLevel.Snapshot;
    //...
    //Setting null for the isolation level on the context instance
    //will cause the transaction to use the default value of the given database server
    //dbContext.ContextOptions.IsolationLevel = null;
    //...
    //The CRUD operations to be executed through this instance of the context
    //...
}

Using dbContext As New EntitiesModel()
    dbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadCommitted
    'dbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadUncommitted
    'dbContext.ContextOptions.IsolationLevel = IsolationLevel.Snapshot
    '...
    'Setting null for the isolation level on the context instance
    'will cause the transaction to use the default value of the given database server
    'dbContext.ContextOptions.IsolationLevel = Nothing
    '...
    'The CRUD operations to be executed through this instance of the context
    '...
End Using

Telerik Data Access will throw a System.ArgumentOutOfRangeException exception, if the values Chaos, RepeatableRead, Serializable, and Unspecified are provided.

Due to the risks of possible data phenomena, Second Level Cache is disabled when the value is different than null.

The next example demonstrates the usage of the isolation levels that can be set through Telerik Data Access. In it through an instance of the context an object is inserted in the Categories table (the SofiaCarRental sample database) together with a read operation that counts the number of Category objects in this table. The performed changes are flushed to the database and the transaction started with the context is not committed yet. After that a concurrent transaction is started by another instance of the context with customized isolation level and the objects in the Categories table are counted again.

ReadUncommitted

The next example demonstrates the usage of the ReadUncommitted isolation level.

In it through an instance of the context an object is inserted in the Categories table (the SofiaCarRental sample database) together with a read operation that counts the number of Category objects in this table. The performed changes are flushed to the database and the transaction started with the context is not committed yet. After that a concurrent transaction is started by another instance of the context with isolation level ReadUncommitted, and the objects in the Categories table are counted again.

By providing this non-restrictive isolation level, the second count of the objects is not blocked by the uncommitted insert operation from the first context and the result includes the new object:

The risks related with the usage of the ReadUncommitted isolation level are the presence of the Dirty reads, Nonrepeatable reads, and Phantom reads phenomena.

//Open a trasaction with an instance of the context
using (EntitiesModel firstDbContext = new EntitiesModel())
{
    //Create an object that will be persisted to the database
    Category newCategory = new Category()
    {
        CategoryName = "New Category"
    };
    //Add the new object to the first context
    firstDbContext.Add(newCategory);
    //Flush the changes so that the first transaction remains opened
    firstDbContext.FlushChanges();
    //Obtain the number of the objects in the Categories table through the first context
    //The expected result includes the newly flushed object
    int insertedCategoriesCount = firstDbContext.Categories.Count();
    //Open a concurrent transaction with a second instance of the context
    using (EntitiesModel secondDbContext = new EntitiesModel())
    {
        //Set the isolatition level for the second transaction
        secondDbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadUncommitted;
        //Obtain the number of the objects in the Categories table through the second context
        //The expected result includes the uncommitted object
        int retrievedCategoriesCount = secondDbContext.Categories.Count();
        //If you compare the two counters you will notice that the numbers match
        Console.WriteLine("Inserted Categories Count: {0}", insertedCategoriesCount);
        Console.WriteLine("Retrieved Categories Count: {0}", retrievedCategoriesCount);
    }
}

'Open a trasaction with an instance of the context
Using firstDbContext As New EntitiesModel()
    'Create an object that will be persisted to the database
    Dim newCategory As New Category() With 
    {
        .CategoryName = "New Category"
    }
    'Add the new object to the first context
    firstDbContext.Add(newCategory)
    'Flush the changes so that the first transaction remains opened
    firstDbContext.FlushChanges()
    'Obtain the number of the objects in the Categories table through the first context
    'The expected result includes the newly flushed object
    Dim insertedCategoriesCount As Integer = firstDbContext.Categories.Count()
    'Open a concurrent transaction with a second instance of the context
    Using secondDbContext As New EntitiesModel()
        'Set the isolatition level for the second transaction
        secondDbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadUncommitted
        'Obtain the number of the objects in the Categories table through the second context
        'The expected result includes the uncommitted object
        Dim retrievedCategoriesCount As Integer = secondDbContext.Categories.Count()
        'If you compare the two counters you will notice that the numbers match
        Console.WriteLine("Inserted Categories Count: {0}", insertedCategoriesCount)
        Console.WriteLine("Retrieved Categories Count: {0}", retrievedCategoriesCount)
    End Using
End Using

ReadCommitted

The next example demonstrates the usage of the ReadCommitted isolation level.

In it through an instance of the context an object is inserted in the Categories table (the SofiaCarRental sample database) together with a read operation that counts the number of Category objects in this table. The performed changes are flushed to the database and the transaction started with the context is not committed yet. After that a concurrent transaction is started by another instance of the context with isolation level ReadCommitted, and the objects in the Categories table are counted again.

Setting the isolation level to ReadCommitted will result in Telerik.OpenAccess.Exceptions.LockNotGrantedException when the read operation inside the second instance of the context attempts to retrieve the number of the objects in the Categories table. In other words, the insert operation will block the reading one:

//Open a trasaction with an instance of the context
using (EntitiesModel firstDbContext = new EntitiesModel())
{
    //Create an object that will be persisted to the database
    Category newCategory = new Category()
    {
        CategoryName = "New Category"
    };
    //Add the new object to the first context
    firstDbContext.Add(newCategory);
    //Flush the changes so that the first transaction remains opened
    firstDbContext.FlushChanges();
    //Obtain the number of the objects in the Categories table through the first context
    //The expected result includes the newly flushed object
    int insertedCategoriesCount = firstDbContext.Categories.Count();
    //Open a concurrent transaction with a second instance of the context
    using (EntitiesModel secondDbContext = new EntitiesModel())
    {
        //Set the isolatition level for the second transaction
        secondDbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadCommitted;
        //Obtain the number of the objects in the Categories table through the second context
        //The expected result includes the uncommitted object
        //A Telerik.OpenAccess.Exceptions.LockNotGrantedException will be thrown
        int retrievedCategoriesCount = secondDbContext.Categories.Count();
    }
}

'Open a trasaction with an instance of the context
Using firstDbContext As New EntitiesModel()
    'Create an object that will be persisted to the database
    Dim newCategory As New Category() With 
    {
        .CategoryName = "New Category"
    }
    'Add the new object to the first context
    firstDbContext.Add(newCategory)
    'Flush the changes so that the first transaction remains opened
    firstDbContext.FlushChanges()
    'Obtain the number of the objects in the Categories table through the first context
    'The expected result includes the newly flushed object
    Dim insertedCategoriesCount As Integer = firstDbContext.Categories.Count()
    'Open a concurrent transaction with a second instance of the context
    Using secondDbContext As New EntitiesModel()
        'Set the isolatition level for the second transaction
        secondDbContext.ContextOptions.IsolationLevel = IsolationLevel.ReadCommitted
        'Obtain the number of the objects in the Categories table through the second context
        'The expected result includes the uncommitted object
        'A Telerik.OpenAccess.Exceptions.LockNotGrantedException will be thrown
        Dim retrievedCategoriesCount As Integer = secondDbContext.Categories.Count()
    End Using
End Using

Snapshot

The next example demonstrates the usage of the Snapshot isolation level.

In it through an instance of the context an object is inserted in the Categories table (the SofiaCarRental sample database) together with a read operation that counts the number of Category objects in this table. The performed changes are flushed to the database and the transaction started with the context is not committed yet. After that a concurrent transaction is started by another instance of the context with isolation level Snapshot, and the objects in the Categories table are counted again.

By setting Snapshot for isolation level of the second context instance, it will be able to retrieve the number of the Category objects that are already committed to the database (the number will be lower than the one retrieved through the first instance):

The attempt to set Snapshot may lead to Telerik.OpenAccess.Exceptions.DataStoreException: Snapshot isolation transaction failed accessing database ... because snapshot isolation is not allowed in this database, if this isolation level is not enabled on database level. In MS SQL Server, you can use the command described in this article to modify you database.

The Snapshot isolation level does not impose any of the possible data phenomena.

//Open a trasaction with an instance of the context
using (EntitiesModel firstDbContext = new EntitiesModel())
{
    //Create an object that will be persisted to the database
    Category newCategory = new Category()
    {
        CategoryName = "New Category"
    };
    //Add the new object to the first context
    firstDbContext.Add(newCategory);
    //Flush the changes so that the first transaction remains opened
    firstDbContext.FlushChanges();
    //Obtain the number of the objects in the Categories table through the first context
    //The expected result includes the newly flushed object
    int insertedCategoriesCount = firstDbContext.Categories.Count();
    //Open a concurrent transaction with a second instance of the context
    using (EntitiesModel secondDbContext = new EntitiesModel())
    {
        //Set the isolatition level for the second transaction
        secondDbContext.ContextOptions.IsolationLevel = IsolationLevel.Snapshot;
        //Obtain the number of the objects in the Categories table through the second context
        //The expected result does not include the uncommitted object
        int retrievedCategoriesCount = secondDbContext.Categories.Count();
        //If you compare the two counters you will notice that the number
        //of the retrieved categories is lower than the number of the inserted categories
        Console.WriteLine("Inserted Categories Count: {0}", insertedCategoriesCount);
        Console.WriteLine("Retrieved Categories Count: {0}", retrievedCategoriesCount);
    }
}

'Open a trasaction with an instance of the context
Using firstDbContext As New EntitiesModel()
    'Create an object that will be persisted to the database
    Dim newCategory As New Category() With 
    {
        .CategoryName = "New Category"
    }
    'Add the new object to the first context
    firstDbContext.Add(newCategory)
    'Flush the changes so that the first transaction remains opened
    firstDbContext.FlushChanges()
    'Obtain the number of the objects in the Categories table through the first context
    'The expected result includes the newly flushed object
    Dim insertedCategoriesCount As Integer = firstDbContext.Categories.Count()
    'Open a concurrent transaction with a second instance of the context
    Using secondDbContext As New EntitiesModel()
        'Set the isolatition level for the second transaction
        secondDbContext.ContextOptions.IsolationLevel = IsolationLevel.Snapshot
        'Obtain the number of the objects in the Categories table through the second context
        'The expected result does not include the uncommitted object
        Dim retrievedCategoriesCount As Integer = secondDbContext.Categories.Count()
        'If you compare the two counters you will notice that the number
        'of the retrieved categories is lower than the number of the inserted categories
        Console.WriteLine("Inserted Categories Count: {0}", insertedCategoriesCount)
        Console.WriteLine("Retrieved Categories Count: {0}", retrievedCategoriesCount)
    End Using
End Using