Entity Framework & Isolation Levels

Background

We have an Entity Framework application that is experiencing database deadlocks and timeouts.

It is time to review our options; especially application isolation levels.

 

Isolation Levels

As Entity Framework is simply an ORM tool that sits on top of ADO.Net it has to use one of the Isolation Level that is offered by ADO.Net.

 

Isolationlevel Enumeration

Let us list the available Isolation Levels:

Developer Network
.NET Framework Class Library > System.Data Namespaces > System.Data
Link

Setting Description
 ReadCommitted Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
 ReadUncommitted  A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
 RepeatableRead  Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
 Serializable  A range lock is placed on the Dataset preventing other users from updating or inserting rows into the dataset until the transaction is complete.
 Snapshot  Reduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.

 

 

Default Isolation Level

Here is the default isolation level for common applications and platforms:

Vendor Product Default Isolation Level
 Microsoft
 SQL Server Management Studio ( SSMS )  Read Committed
 Entity Framework Serializable

 

Setting Isolation Levels

Outline

  1. Configuration  File
    • Save desired Isolation level in configuration file
  2. Source Code
    • Read desired Isolation Level
    • Convert Read String to IsolationLevel  enumeration
    • Review Code and utilize choice in Strategic locations

 

Configuration

Configuration File

As we have a  console application, we will simple edit our App.Config file and add a new entry in the appSettings section.

 

Source Code

Read Configuration File


        static void readConfiguration()
        {

            String ITEM_IsolationLevel = "IsolationLevel";

            string isolationLevelAsString;

            if (ConfigurationManager.AppSettings[ITEM_IsolationLevel] != null)
            {
                isolationLevelAsString
                     = ConfigurationManager.AppSettings[ITEM_IsolationLevel].ToString();
            }
            else
            {
                isolationLevelAsString = null;
            }


        } // readConfiguration()

 

 

Parse Configuration File


        static void parseConfiguration()
        {

            _isolationLevel = dbIsolationLevel.getIsolationLevel(isolationLevelAsString);


        } // parseConfiguration()

Get Isolation Level from String


      static IsolationLevel _isolationLevel = System.Transactions.IsolationLevel.Unspecified;

        static String FORMAT_ISOLATIONLEVEL_INVALID = "Invalid Isolation Level {0}";

        static String strLog;

        static public IsolationLevel getIsolationLevel(String strValue)
        {

            switch (strValue)
            {

                case "ReadUncommitted":
                    _isolationLevel =System.Transactions.IsolationLevel.ReadUncommitted;
                    break;

                case "ReadCommitted":
                    _isolationLevel =System.Transactions.IsolationLevel.ReadCommitted;
                    break;


                case "RepeatableRead":
                    _isolationLevel =System.Transactions.IsolationLevel.RepeatableRead;
                    break;

                case "Serializable":
                    _isolationLevel =System.Transactions.IsolationLevel.Serializable;
                    break;

                case "Snapshot":
                    _isolationLevel =System.Transactions.IsolationLevel.Snapshot;
                    break;

                default:
                    {

                        strLog = String.Format(FORMAT_ISOLATIONLEVEL_INVALID, strValue);
                        System.Console.WriteLine(strLog);

                        _isolationLevel =System.Transactions.IsolationLevel.Unspecified;

                        break;

                    }

            } // switch


            return (_isolationLevel);

        } //getIsolationLevel


Query DB for Current Isolation Level

Using Code stolen from Diederik Krols.

Getting and setting the Transaction Isolation Level on a SQL Entity Connection
Link

Overview

  1. sys.dm_exec_sessions
    • Session level Isolation level are exposed via sys.dm_exec_sessions
    • Pass along current sessions @@SPID and read that session’s transaction_isolation_level
  2. Use Enum.Parse and typeof convert string to enumeration

Code



        public static IsolationLevel getIsolationLevel(System.Data.IDbConnection connection)
        {
            string query =
                @"SELECT CASE transaction_isolation_level
                    WHEN 0 THEN 'Unspecified'
                    WHEN 1 THEN 'ReadUncommitted'
                    WHEN 2 THEN 'ReadCommitted'
                    WHEN 3 THEN 'RepeatableRead'
                    WHEN 4 THEN 'Serializable'
                    WHEN 5 THEN 'Snapshot'
                    END AS [Transaction Isolation Level]
            FROM sys.dm_exec_sessions
            WHERE session_id = @@SPID";

            /*
            if (connection is System.Data.Entity.Core.EntityClient.EntityConnection)
            {

                return (connection as System.Data.Entity.Core.EntityClient.EntityConnection).StoreConnection.GetIsolationLevel();

            }
            */

            if (connection.State == System.Data.ConnectionState.Closed)
            {
                //open connection if currently closed
                connection.Open();
            }

            if (connection is System.Data.SqlClient.SqlConnection)
            {
                System.Data.IDbCommand command = connection.CreateCommand();

                command.CommandText = query;
                string result = command.ExecuteScalar().ToString();

                return (IsolationLevel)Enum.Parse
                    (
                          typeof(IsolationLevel)
                        , result
                    );

            }

            return IsolationLevel.Unspecified;
        }

 

Browse Data

Overview

  1. TransactionOptions
    • Instantiate new TransactionOptions
      • Pass along desired Isolation Level
  2. TransactionScope
    • Instantiate new TransactionScope
      • TransactionScopeOption
        • Make sure that you pass in Required
          • TransactionScopeOption.Required
        • TransactionScopeOption.Suppress
          • Will not work, as it will not send set desired Isolation Level in DB
        • TransactionScopeOption.New
          • Overkill as it is OK to use existing transaction if one is available
      • TransactionOptions
        • Pass along one created earlier
    • Start code block using use
    • Complete Transaction using
      • transactionScope.complete()

Code


        public void browse()
        {

            TransactionOptions objTO;
            TransactionScope objTS;

            //Instantiate Transaction Object from passed in isolation level
            objTO = new TransactionOptions
                         {
                            IsolationLevel = _isolationLevel
                        };

            using
            (

                //Instanciate Transaction Scope from Transaction Object
                objTS = new TransactionScope
                            (
                                   TransactionScopeOption.Required  
                                   ,  objTO
                            )

            )
            {

                //set 
                //dbDE.Database.ExecuteSqlCommand(SQL_TRANSACTION_ISOLATIONLEVEL_READUNCOMMITED);

                //get current DB Transaction Level
                //not required, just record keeping
                _isolationLevelDBCurrent = dbIsolationLevel.getIsolationLevel(dbDE.Database.Connection);

                //get isolation level on DB
                strLog = String.Format
                            (
                                  FORMAT_ISOLATIONLEVEL_ON_DB
                                , _isolationLevelDBCurrent.ToString()
                            );

                Console.WriteLine(strLog);

                //get all entities from entity traceflags
                //result should be captured in a variant variable tfQuery
                //ordering will likely be based on the DB table's primary key ( PK )
                var tfQuery = from tf in dbDE.traceFlags
                              select tf
                           ;


                //copy data into list object
                listTF = tfQuery.ToList();

                //Iterate list
                //Using foreach expose each list element in varTF
                foreach (var varTF in listTF)
                {

                    //cast Var object to traceflag object
                    objTF = (traceFlag)varTF;

                    // prepare UI output
                    strLog
                        = String.Format
                            (
                                  FORMAT_BROWSE_DETAIL
                                , objTF.id
                                , objTF.description
                            );

                    //write data
                    Console.WriteLine(strLog);

                    // Output Line Break
                    Console.WriteLine(CHAR_LINEBREAK);

                } //foreach

                //Transaction Scope Complete
                objTS.Complete();

            } //using

        } //browse



 

Instrumentation

sp_whoIsActive

When we run sp_whoIsActive, here is what we see

Explanation

  1. We see that our Entity Framework client ( login_name = edmgen //user given)
    • wait_info
      • is waiting indicating by wait_info ( LCK_M_S )
    • blocking_session_id
      • Being blocked as we have an entry other than NULL in the blocking_session_id column
    • status
      • suspended

Error

Error Logged

Explanation

  1. Exception
    • System.Data.Entity.Core.EntityCommandExecutionException
      • An error occurred while executing the command definition

Commentary

Googled on LCK_M_S and found the entry pasted below:

SQLSHACK

Wait_Info

Again, the Lock Type is LCK_M_S

Image

Textual

This wait occurs when a request is waiting to acquire a shared lock.

This typically happens when read requests are blocked by write transactions (implicit or explicit) that have been kept open for extended periods of time

Suggested Solutions

  1. Keep transaction durations short.
  2. Ensure transactions isolation levels are appropriate (avoid SERIALIZABLE and REPEATABLE READ if possible).
  3. Investigate enabling the READ_COMMITTED_SNAPSHOT database setting.
  4. Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked.
  5. Indexing, ensure that long running queries and transactions are optimized.
  6. Ensure that row, page and table locks are being escalated correctly. If the table is partitioned consider enabling partition escalation.

Listening

Nothing NEW here.

Like your fave group,  In my case, at this moment, Tears For Fears, I was just way over my head…

Link

 

Dedicated

So many have covered avoiding timeouts and deadlocks via relaxing your Isolation Level.

Just now noticed that I have never shared, via a published post, that inclusive of my teachers is Scott Hanselman.

Thankfully getting to do so now.

Especially this week as I am being nudged along by resident Dormitory Prefect, Sean Paul …

they take and take and don’t credit.

 

References

  1. Developer Network
    • .NET Development > Entity Framework > Introduction to Entity Framework
      Entity Framework Working with Transactions (EF6 Onwards)
      Link
    • IsolationLevel Enumeration
      Link
  2. SCOTT HANSELMAN
    • Getting LINQ to SQL and LINQ to Entities to use NOLOCK
      Link
  3. Diederik Krols
    • Getting and setting the Transaction Isolation Level on a SQL Entity Connection
      Link
  4. Nikola Breznjak
    • How to use App.config in Visual Studio C# .NET
      Link
  5. Stack Overflow
    • No Lock
      • How to force Entity Framework not to lock the database [duplicate]
        Link
      • Entity Framework with NOLOCK
        Link

One thought on “Entity Framework & Isolation Levels

Leave a Reply

Please log in using one of these methods to post your comment:

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