Telerik Data Access: More Control Over the Isolation Level

One of the most popular feature requests you posted on our Ideas and Feedback portal was the ability to control the locking behavior of the transactions with each instance of the context. An investigation on our side showed that the main reason about it was the generally random appearance of locks between concurrent transactions on the server. The problem behind it was the same as the one with the dining philosophers of Dijkstra.

While gathering your feedback, we had the opportunity to take a close look at one particular case in which Telerik Data Access was utilized in the data access layer, and the scenario required some data to be inserted in a database table only if it was empty. As trivial as it sounds, there was a catch: the two operations were executed in different transactions and the one that had to insert the data was always started first (the cause: legacy code). The deadlock occurred when the first transaction was not allowed to commit its changes until the second one did not verify that there were no records in the table. The second one, however, was not able to perform the necessary read operation, because the first one had blocked the uncommitted records.
 
Digging deeper in the code, we discovered that, in general, the application was using the default isolation level of the database server (the one that locks the rows for editing during read and blocks the rows for reading when they are edited). This set up was adequate to the needs the application was serving, except in the described situation. 

It was clear that the solution had to meet three conditions:

1. The second transaction had to be able to perform the read operation.
2. The result from the read operation should not include the uncommitted records.
3. The solution should affect only this particular case.

The first two are satisfied if the isolation level of the second transaction was set to Snapshot, and the third one became possible when we introduces the new option of the Data Access context (IsolationLevel), which allowed the customization of the isolation level per context instance. 

The following code snippet utilizes our sample SofiaCarRental database, and demonstrates the solution in a similar situation:

//Open a transaction 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);
    }
}

If you look at the code, you will notice that the isolation level is set on the second instance of the context right before the definition of the read operation. 

In the particular case with the Snapshot isolation level, during the implementation of the feature we found out that the database had to be configured to use it (how to do it for MS SQL Server is shown here).

The feature itself is described in details in the Managing Isolation Level article in our documentation.

Let us know how it works for you,