.Net / SQL Server–Transaction Isolation Level–Tracking thru Profiler

Background

As said in the past one of the areas to familiarize oneself with when experiencing deadlocks and timeouts is the Application’s default Isolation Level.

Developers are also able to change and revert Isolation Level during the course of an Application.

Lineage

Here are previous posts on Isolation Levels

  1. Entity Framework & Isolation Levels
    Link

Lab

In the code that we will be profiling, we requested an isolation Level of Serializable.

TroubleShooting

DBA

The SQL Server DBA has various tools to track Transaction Isolation level.

Inclusive are:

  1. Querying the Dynamic Management Views ( DMV )
  2. SQL Server Profiler

Dynamic Management Views

Supporting View

constant.vw_IsolationLevel
Overview

We use the Constant.vw_IsolationLevel view to convert the numeric IsolationLevel to its String equivalence.

Btw, at a session level the Isolation Level is exposed via the sys.dm_exec_sessions DMV; specifically the transaction_isolation_level column.

Code

use master
go


if schema_id('constant') is null
begin

	exec('create schema [constant] authorization [dbo]')
end

go
if object_id('[constant].[vw_IsolationLevel]') is null
begin

	exec('create view [constant].[vw_IsolationLevel] as select [shell] = 1/0');

end
go


 alter view [constant].[vw_IsolationLevel]
 as

	select 
			  [id] = 0
			, [literal] = 'Unspecified'

	union all

    select
		     [id] = 1
		   , [literal] = 'ReadUncommitted'

	union all

    select
		     [id] = 2
		   , [literal] = 'ReadCommitted'

	union all

    select
		     [id] = 3
		   , [literal] = 'RepeatableRead'

	union all

    select
		     [id] = 4
		   , [literal] = 'Serializable'

	union all

    select
		     [id] = 5
		   , [literal] = 'Snapshot'

go

grant select on [constant].[vw_IsolationLevel] to [public]
go


Query

The SQL code below queries the Dynamic Management Views and identifies the data listed below:

  1. sys.dm_exec_sessions
    • login_name
  2. [master].[constant].[vw_IsolationLevel]
    • isolation level literal
  3. sys.dm_exec_requests
  4. sys.dm_tran_session_transactions
    • transaction_descriptor
    • transaction_id
    • is_user_transaction
  5. sys.dm_tran_active_transactions
    • Transaction Name
      • user_transaction
      • <Name> if named transaction
Code

select 

		  tblDES.[session_id]

		--, tblDES.[is_user_process]
		, tblDES.[login_name]

		, tblDES.[host_name]

		, tblDES.[program_name]

		, tblDES.[transaction_isolation_level]

		, [transactionIDRequest]
			= tblDER.[transaction_id]

		, [transactionDescriptor]
			= tblDMTST.transaction_descriptor

		, [transactionIDTransaction]
			= tblDMTST.[transaction_id]		

		--, tblDMTST.is_enlisted
		--, tblDMTST.is_bound
		, [isUserTransaction]
			= tblDMTST.is_user_transaction

		, vwIL.[literal]

		, [transactionName]
			= tblSMTAT.[name]

		, tblDES.[open_transaction_count]

		, tblDER.[command]

		, [sessionState]
			= tblDES.[status]

from   sys.dm_exec_sessions tblDES

left outer join [master].[constant].[vw_IsolationLevel] vwIL
		on tblDES.transaction_isolation_level = vwIL.[id]

left outer join sys.dm_exec_requests tblDER
		on tblDES.[session_id] = tblDER.[session_id]

left outer join sys.dm_tran_session_transactions  tblDMTST
		on tblDES.[session_id] = tblDMTST.[session_id]

left outer join sys.dm_tran_active_transactions tblSMTAT
		on tblDMTST.[transaction_id] = tblSMTAT.transaction_id

where  (
	
			( tblDES.session_id >= 50)
	   )

and   (
			( tblDES.[login_name] != SYSTEM_USER )
	  )

order by
		  vwIL.[literal] desc
		, tblDES.[session_id] asc

Query Result

 

SQL Server Profiler

Trace File Properties

Image

Here are the events that we are Tracing On.

And, the properties that we watching.

Events Captured

Tabulate
Event Class Payload
 Audit Login set transaction isolation level read committed
 TM: Begin Tran starting Begin Transaction
 SQLTransaction
 TM: Begin Tran completed  Begin Transaction
 RPC:Completed  exec sp_executesql N’SELECT
[Limit1].[C1] AS [C1]
FROM ( SELECT TOP (1)
[Extent1].[sequenceNbr] AS [C1]
FROM [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax](@traceID) AS [Extent1]
) AS [Limit1]’,N’@traceID int’,@traceID=2551
 Audit Logout
 RPC: Completed  exec sp_reset_connection
 Audit Login set transaction isolation level read committed
 RPC:Completed  exec sp_executesql N’INSERT [dbo].[traceFlagSource]([id], [sequenceNbr], [status],

, [title], [url], [addedBy], [dateAdded])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
‘,N’@0 int,@1 tinyint,@2 bit,@3 varchar(100),@4 varchar(100),@5 varchar(200),@6 nvarchar(128),@7 datetime2(7)’,@0=2551,@1=9,@2=1,@3=’msft’,@4=’caps’,@5=’http://&#8217;,@6=N’LAB\dadeniji’,@7=’2017-06-08 19:58:00.3960092′

 Audit Logout
 RPC: Completed  exec sp_reset_connection
 TM: Commit Tran starting COMMIT TRANSACTION
 SQLTransaction
 TM: Commit Tran completed COMMIT TRANSACTION

 

 

Screen Capture

Screen Capture :- 001 – TraceStart

Screen Capture :- 002 – SQLTransaction

 

Screen Capture :- 003 — SQLTransaction

Screen Capture :- 004 – Audit Login

Image

Explanation

The Audit Login event captures the following:

  1. The session’s event such as
    • set transaction isolation level read committed

Screen Capture :- 005 — “TM: Begin Tran starting”

Image

 

Explanation

The “TM: Begin Tran starting” event

  1. Contains the “Begin Transaction” payload

 

Screen Capture :- 006 — “SQLTransaction”

Image

Explanation

The “SQLTransaction” event

  1. Requests for Sql Server to start a new transaction

 

Screen Capture :- 007 — “TM: Begin Tran completed”

Image

 

Explanation

The “TM: Begin Tran completed” event

  1. The Transaction Manager confirmed that the transaction has began

 

Screen Capture :- 008 — “RPC: Completed”

Image

Explanation

The “RPC:Completed” event

  1. The SQL to get the current max sequenceNbr is completed

 

Screen Capture :- 010 — “Audit Logout”

Image

 

Explanation

The “Audit Logout” event

  1. Connection Logged out

 

Screen Capture :- 010 — “RPC: Completed”

Image

Explanation

The “RPC:Completed” event

  1. The SQL to reset the connection is completed
    • The payload is “exec sp_reset_connection

 

Screen Capture :- 011 –Audit Login

Image

 

Explanation

The “Audit Login” event

  1. Connection request processed
  2. Set statements processed
    • set transaction isolation level serializable

Screen Capture :- 012 — TransactionLog

Image

 

Screen Capture :- 013 — TransactionLog

Image

 

 

Screen Capture :- 014 — “RPC: Completed”

Image

 

 

Explanation

The “RPC:Completed” event

  1. Insert statement completed as a “Remote Procedure Call” (RPC)

 

Screen Capture :- 015 — “Audit Logout”

Image

 

Screen Capture :- 016 — “RPC: Completed”

Image

Explanation

The “RPC:Completed” event

  1. The SQL to reset the connection is completed
    • The payload is “exec sp_reset_connection

 

Screen Capture :- 017 — “Audit Login”

Image

 

Explanation

The “Audit Login” event

  1. Connection request processed
  2. Set statements processed
    • set transaction isolation level serializable

 

 

Screen Capture :- 018 — “TM: Commit Tran starting”

Image

 

Explanation

The “TM: Commit Tran starting” event

  1. The Transaction Manager started the commit process
    • The payload is “COMMIT TRANSACTION

Screen Capture :- 021 — “TM: Commit Tran completed”

Image

The “TM: Commit Tran completed” event

  1. The Transaction Manager completed the commit process
    • The payload is “COMMIT TRANSACTION
    • Unfortunately, the “Transaction ID” is not registered

Conclusion

Here is what we are able to track:

  1. SQLTransaction
  2. Batch — 01
    • Audit Login
      • set transaction isolation level committed
    • TM: (Transaction Management)
      • TM: Begin Tran starting
        • BEGIN TRANSACTION
      • SQLTransaction
      • TM: Begin Tran completed
        • BEGIN TRANSACTION
    • RPC:Completed
  3. Batch — 02
    • RPC:Completed
      • Query for Max Sequence Number
    • Audit Logout
    • RPC:Completed
      • exec sp_reset_connection
  4. Batch — 03
    • Audit Login
      • set transaction isolation level committed
    • RPC:Completed
      • Insert SQL Statement
    • Audit Logout
    • RPC:Completed
      • exec sp_reset_connection
  5. Batch — 04
    • Audit Login
      • set transaction isolation level committed
    • TM: (Transaction Management)
      • TM: Commit Tran starting
        • COMMIT TRANSACTION
      • SQLTransaction
      • TM: Commit Tran completed
        • COMMIT TRANSACTION

Summary

Based on our quick study, we can see that SQL Server Profiler is a very capable tool for monitoring Transaction Isolation Levels.

To do so, please include the following events in the list of Events you are monitoring:

  1. Audit Login
    • Text Data
      • Syntax
        • set transaction isolation level [isolation level]
      • Sample
        • set transaction isolation level read committed

 

Entity Framework – Change Database Column – Error – “could not be set to a ‘System.Boolean’ value. You must set this property to a non-null value of type ‘System.String'”

 

Background

Upon changing one of our database columns from varchar to bit, we experienced an invalid “System.InvalidOperationException” issue.

 

Error

Error – “Unhandled Exception: System.InvalidOperationException: The ‘status’ property on ‘usp_TraceFlagSourceFetch_Result1’ could not be set to a ‘System.Boolean’ value.
You must set this property to a non-null value of type ‘System.String'”

Here is the error we are experiencing.

Error Text


Unhandled Exception: System.InvalidOperationException: The 'status' property on 'usp_TraceFlagSourceFetch_Result1' could not be set to a 'System.Boolean' value.
You must set this property to a non-null value of type 'System.String'.
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
at lambda_method(Closure , Shaper )
at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at lab001VS.UITraceFlag.listTraceFlagSourceUseSPImplicit(Int32 iID) in

TroubleShooting

We need to find “usp_TraceFlagSourceFetch_Result1

 

Model Browser

We found usp_TraceFlagSourceFetch_Result1 via the Model Browser.

Model Browser – Original

We looked in the Model Browser and this is what it looks like:

 

Model Browser – Original – Column – Status

Let us review the identified Property, Status.

 

Model Browser – Original – Column – Status – Type – “String”

Model Browser – Original – Column – Status – Type – “Boolean”

Please change the Type of Status from String to Boolean.

 

 

Summary

Really impressed with how quickly Entity Framework makes it to align .Net code with database structural changes.

This expediency especially when change can be localized to the Model and made via the Model Browser is intriguing.

 

 

Entity Framework – Using Database Functions

Background

We are going to add functionality to add records to our referenced table.

The table that we will be adding data has a primary key consisting of two columns.

The two columns are id and sequenceNbr.

The id column binds us to the parent\referenced table.

And, the sequenceNbr is a progressive column that we increment for each new record belonging to the same group.

 

Why Scaler Function?

The Scaler function will be employed on the Client.

It will connect to the backend and retrieve the last used Sequence Number for the group.

 

Utilize – Scaler Function – Get Current In-use SequenceNbr

SQL Server – Management Studio ( SSMS )

Database – Diagram

 

Function

dbo.udfn_TraceFlagSource_GetSequenceNbrMax
Outline
  1. Query [dbo].[traceFlagSource]
    • Get Max(sequenceNbr)
      • Filter
        • Filter on [id] = traceID

Code

if object_id('[dbo].[udfn_TraceFlagSource_GetSequenceNbrMax]') is null
begin

	exec('create function [dbo].[udfn_TraceFlagSource_GetSequenceNbrMax]
		   ()
		   returns int
		   as
		   begin
				return 1/0
		   end
			')

end
go

alter function [dbo].[udfn_TraceFlagSource_GetSequenceNbrMax]
(
	@traceID int
)
returns int
as
begin

	return
	(
		ISNULL
		(
			(
				select max
						(
							tblTFS.[sequenceNbr]
						)

				from   [dbo].[traceFlagSource] tblTFS

				where  tblTFS.[id] = @traceID

			)
			, 0
		)
	)


end
go


 

Visual Studio

Outline

Here are the steps:

  1. Launch Visual Studio
  2. Update Data Model

 

Data Model

 

Model Browser

Model Browser – Before Import Database Function

Here is the Model Browser before importing the Database Function

 

Model Browser – After Importing Database Function – dbo.udfn_TraceFlagSource_GetSequenceNbrMax

Here is the screen once we import the  dbo.udfn_TraceFlagSource_GetSequenceNbrMax  scalar function.

 

Add Function Import

Tried to Add Function Import…

But, none of the functions that we have defined in the Database shows up.

 

Utilize – Table Function – Get Current In-use SequenceNbr

SQL Server – Management Studio ( SSMS )

 

Function

dbo.TVF_TraceFlagSource_GetSequenceNbrMax
Outline
  1. Query [dbo].[traceFlagSource]
    • Get Max(sequenceNbr)
      • Filter
        • Filter on [id] = traceID
Code

if object_id('[dbo].[TVF_TraceFlagSource_GetSequenceNbrMax]') is null
begin

	exec('create function [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax]
		   ()
		   returns TABLE
		   as
		   return
		   (

				select
					[shell] = 1/0
		   )
			')

end
go

ALTER FUNCTION [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax]
(
	@traceID int
)
RETURNS TABLE 
AS
RETURN 
(
	select
		[sequenceNbr]
			= ISNULL
			(
				(
					select max([sequenceNbr])
					from   [dbo].[traceFlagSource] tblTFS
					where  tblTFS.[id] = @traceID
				)
				, 0
			)

)
GO

grant select on [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax] to [edmgen]
go


Visual Studio

Outline

Here are the steps:

  1. Launch Visual Studio
  2. Update Data Model

 

Data Model

Model Browser – After Importing Database Function – Table Value Function – dbo.TVF_TraceFlagSource_GetSequenceNbrMax

Here is the screen once we import the  dbo.udfn_TraceFlagSource_GetSequenceNbrMax  scalar function.

 

Add Function Import

Tried to Add Function Import…

Still Functions, Scaler or Table Functions, show up.

Model Browser

Thankfully, our Table Value Function is automatically created.

It is shown under “Function Imports” \ “TVF_TraceFlagSource_GetSequenceNbrMax“.

 

Code

Outline

  1. Methods
    • traceFlagSourceAdd
      • Display prompt asking user to enter data
      • Accept data
      • Start Transaction
        • Serializable
          • Ensure that getting current Max Sequence Number and usage of the data are handled in a single transaction
      • Call Entity Framework asking for Max Sequence Number
        • Pass along variables
          • Trace ID
        • Process Result Set
          • Get First Record
        • Increment Max Value as we prepare for Insert
      • Instantiate traceFlagSource
      • Populate traceFlagSource
      • Save Changes
        • If error, invoke  handleDBException method
    • handleDBException
      • Depending on the type of Exception access different Exception Type
    • getDBExceptionMessage
      • Argument Type :- System.Data.Entity.Validation.DbEntityValidationException
        • Access EntityValidationErrors
        • Access ValidationErrors
      • Argument Type :- System.Data.Entity.Infrastructure.DbUpdateException
        • System.Data.SqlClient.SqlException = ex.InnerException
        • Iterate sqlException.Errors, accessing each System.Data.SqlClient.SqlError in the collection

 

traceFlagSourceAdd


public void traceFlagSourceAdd()
{

	Console.WriteLine("Trace Flag ID");
	strID = Console.ReadLine();

	if (strID == "")
	{
		return;
	}

	//Parse Trace ID as Trace ID (int?)
	iTraceID = Int32.Parse(strID);

	Console.WriteLine("Trace Flag Source");
	strSource = Console.ReadLine();

	Console.WriteLine("Trace Flag Source Title");
	strTitle = Console.ReadLine();

	Console.WriteLine("Trace Flag Source URL");
	strURL = Console.ReadLine();


	//Instanciate Transaction Object to Serializable
	objTO = new TransactionOptions
	{
		IsolationLevel = System.Transactions.IsolationLevel.Serializable
	};

	using
	(

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

	)
	{

                IQueryable<byte?> iQueryableSequenceNbrMax = dbDE.TVF_TraceFlagSource_GetSequenceNbrMax
                    (
                        traceID: iTraceID
                    )
                    ;

		byte? byteSequenceNbrMax = iQueryableSequenceNbrMax.FirstOrDefault();


		byteSequenceNbr = (byte)(byteSequenceNbrMax + 1);

		try
		{
			objTFS = new traceFlagSource();

			objTFS.id = (int)(iTraceID);
			objTFS.sequenceNbr = byteSequenceNbr;
			objTFS.status = true;
			objTFS.source = strSource;
			objTFS.title = strTitle;
			objTFS.url = strURL;

			objTFS.addedBy = getUser();
			objTFS.dateAdded = DateTime.Now;


			dbDE.traceFlagSources.Add(objTFS);

			dbDE.SaveChanges();

			//complete the transaction
			objTS.Complete();

		}
		catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
		{
			handleDBException(objTS, dbEx);
		}
		catch (System.Data.Entity.Infrastructure.DbUpdateException dbEx)
		{
			handleDBException(objTS, dbEx);
		}

		if (objTFS != null)
		{
			objTFS = null;
		}

	}

} //traceFlagSourceAdd



 

handleDBException

        private void handleDBException
        (
              TransactionScope objTS
            , System.Data.DataException dbEx
        )
        {
            //dispose the transaction
            objTS.Dispose();

            Exception raise = dbEx;

            messageBuffer = "";

            if (dbEx is System.Data.Entity.Validation.DbEntityValidationException)
            {

                messageBuffer = getDBExceptionMessage
                                (
                                    (System.Data.Entity.Validation.DbEntityValidationException)
                                    dbEx
                                );
            }
            else if (dbEx is System.Data.Entity.Infrastructure.DbUpdateException)
            {

                messageBuffer = getDBExceptionMessage
                                (
                                    (System.Data.Entity.Infrastructure.DbUpdateException)
                                    dbEx
                                );
            }


            colorForegroundSaved = Console.ForegroundColor;

            Console.ForegroundColor = ConsoleColor.Red;

            Console.WriteLine(messageBuffer);

            Console.ForegroundColor = colorForegroundSaved;

            // raise a new exception nesting
            // the current instance as InnerException
            //raise = new InvalidOperationException(messageBuffer, raise);

            //throw raise;

        }

 

getDBExceptionMessage // System.Data.Entity.Infrastructure.DbUpdateException


        private String getDBExceptionMessage
        (
            System.Data.Entity.Infrastructure.DbUpdateException dbEx
        )
        {

            strMessageBuilder.Clear();

            strMessage =
                String.Format
                    (
                          FORMAT_EXCEPTION_DB_INSERT_TRACE_SOURCE
                        , dbEx.Message
                        , dbEx.HResult
                    );

            strMessageBuilder.Append(strMessage);

            Exception updateException
                = (Exception) dbEx.InnerException;

            System.Data.SqlClient.SqlException sqlException
                = (System.Data.SqlClient.SqlException)updateException.InnerException;

            int iErrorNumber = 0;

            foreach (System.Data.SqlClient.SqlError error in sqlException.Errors)
            {

                iErrorNumber = iErrorNumber + 1;

                strMessageBuilder.AppendFormat
                        (
                              "{0} Inner Exception {1}\n"
                            , iErrorNumber
                            , error.Message
                        );
            }

            foreach (var result in dbEx.Entries)
            {
                strMessageBuilder.AppendFormat
                    (
                          "Type: {0} was part of the problem. \n"
                        , result.Entity.GetType().Name
                    );
            }

            return (strMessageBuilder.ToString());

        }


getDBExceptionMessage // System.Data.Entity.Validation.DbEntityValidationException

        private String getDBExceptionMessage
        (
            System.Data.Entity.Validation.DbEntityValidationException dbEx
        )
        {

            strMessageBuilder.Clear();

            foreach (var validationErrors in dbEx.EntityValidationErrors)
            {
                foreach (var validationError in validationErrors.ValidationErrors)
                {

                    string message = 
                            string.Format
                            (
                                "{0}:{1}"
                                , validationErrors.Entry.Entity.ToString()
                                , validationError.ErrorMessage
                            );

                    strMessageBuilder.Append(message + Environment.NewLine);


                }

            }

            return (strMessageBuilder.ToString());

        }


References

  1. StackOverflow
    • How to import a function with scalar return value in EF 5
      Link
    • InnerException in Entity Framework when I try add an entity
      Link
    • What is the best way to rollback a .net transaction?
      Link
  2. Microsoft
    • Microsoft – Developer
        • .NET Framework Class Library > System.Data Namespaces >  System.Data.SqlClient
          • SqlError Class
            Link
        • System.Data.SqlClient > SqlException Class > SqlException Properties
          • SqlException.Errors Property
            Link

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

Entity Framework – Query – “SELECT 1 AS [C1]” – Workfile & WorkTable

Background

Sent a couple of goodwill posts towards Entity Framework.

Ever the curmudgeon, it is time to start taking some shots.

 

Sample Generated Query

Query Snippet – Original

Here is a very small portion of one of the queries generated by Entity Framework.


select 1

where 
(
	NOT EXISTS
         (

		SELECT 1 AS [C1]
            
		FROM
                (

		    SELECT
 
		        [Extent10].[End_Id] AS [End_Id],
                        [Extent10].[FootnoteParent_Id] AS [FootnoteParent_Id],
                        [Extent11].[Id] AS [Id2],
                        [Extent12].[Id] AS [Id3],
                        [Extent12].[BeginDate] AS [BeginDate]
             
		   FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
				
		   INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
			ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
			AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
             
		  LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12] 
			ON [Extent10].[Begin_Id] = [Extent12].[Id]

            
		  WHERE [Extent10].[Container_Id] IS NOT NULL 

			
	      ) AS [Filter2]
          
	     LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
		   
		ON [Filter2].[End_Id] = [Extent13].[Id]

	) -- NOT EXISTS

) -- SELECT 1


Query Snippet – Revised

Here is a rewrite



select 1

where not exists 
(

	SELECT 
			1

	FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
	
	INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
		ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
	

) -- SELECT 1


Explanation

  1. We can discard is not null, due to the same columns being referenced in the “Inner Join
    • The Inner Join “ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
    • And, the where clause we can likely jettison are
      • AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
      • WHERE  ( [Extent10].[Container_Id] IS NOT NULL )
  2. We can also likely discard the Left Outer Join, as they are quite not needed in a Not Exists Clause
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12]
      • ON [Extent10].[Begin_Id] = [Extent12].[Id]
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
      • ON [Extent10].[End_Id] = [Extent13].[Id]

 

Obviously, we will have to test things out.

 

 

Query Plan

Query Snippet – Original

Query Snippet – Revised

Explanation

  1. Query Comparison
    • The Original Query is at 17%
    • And, the revised is at 83%
  2. Join Type
    • Original Query uses “Hash Join
      • Cost is 79%
    • And, the revised is “Nested Loops
      • Cost is 2%

 

Statistics I/O

Query Snippet – Original

 

Query Snippet – Revised

Tabulate

Table Original Query Revised Query
 UcNoteContainerFootnotes Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 CourseUCTCA  Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Workfile  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 WorkTable  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 

Explanation

  • The reworked query appears to be less taxing in terms of IO
    • CourseUCTCA
      • Original Query :- Logical Reads is at 8
      • Revised Query :- Logical Reads is at 4
    • Workfile
      • Original Query because it is an Hash Join
    • WorkTable
      • Original Query, also because it is an Hash Join

 

Summary

In later posts, will talk more about WorkTable and Workfiles.

What they are and how to measure their cost.

And, very importantly, how to make sure code re-write is accurate.

Unfortunately, Entity Framework generated SQL Code can suffer from a little intemperance

 

Entity Framework – SQL Server/ Stored Procedure

Background

Let us look further into Entity Frameworks.

As Entity Framework supports the full repertoire of programmable objects in SQL Server, we can use Stored Procedures and Functions, in addition to Tables and Views.

 

Database

SQL Server

Diagram

Stored Procedure

Stored Procedure – dbo.usp_TraceFlagSourceFetch

if object_id('[dbo].[usp_TraceFlagSourceFetch]') is null
begin
	exec('create procedure [dbo].[usp_TraceFlagSourceFetch] as ');
end
go

alter procedure [dbo].[usp_TraceFlagSourceFetch] 
(
	@id int
)
as
begin

	select 
			  tblTFS.id
			, tblTFS.[sequenceNbr]
			, tblTFS.[status]
			, tblTFS.
			, tblTFS.[title]
			, tblTFS.[url]
			, tblTFS.[addedBy]
			, tblTFS.[dateAdded]
	from   [dbo].[traceFlagSource] tblTFS

	where tblTFS.id = @id

end
go

grant execute on [dbo].[usp_TraceFlagSourceFetch]   to [edmgen]
go


 

Visual Studio

Launch Visual Studio and open the Project.

Solution Explorer

Access the Solution Explorer and double click on the edmx file.

EDMX

Here is what our EDMX Diagram looks like.

 

Model Browser

Let us review the Model Browser.

Right click in an empty spot on the diagram panel and from the drop-down menu choose, Model Browser.

Diagram Panel – Menu

Model Browser Panel – What Is?

Access the “Model Browser“.

An edmx contains the following section, Diagrams, Conceptual model, and Data Store.

We have already covered the Diagram.

The Conceptual Model reflects the Object Components ( Entity Types, Complex Types, Enum Types, Association, and Function Imports ).

And, the last section is the Data Store.

 

Model Browser Panel – Original

 

Update Model

Let us update the model so that we can have access to the recently created or exposed Stored Procedure.

Update Model – Choose Your Database Objects and Settings

Before Creating \ Exposing

Here is what the “Update Wizard” looks like before exposing the Stored Procedure.

 

After Creating \ Exposing

Upon creating the SP, usp_TraceFlagSourceFetch, it shows up in the Update Wizard’s Add Tab.

 

Add the Database Stored Procedure

 

Model Browser Panel – Post Adding Database Object

Once we update our Database Model and choose to Add the Stored Procedure, we will revisit the Model Browser.

The pertinent areas are :

  1. Model
    • Function Imports
      • usp_TraceFlagSourceFetch
  2. Store
    • Stored procedures / Functions
      • usp_TraceFlagSourceFetch

 

Function Import – default

During the process of updating the model with the Stored Procedure, the Wizard also creates a default Function Import.

Model Browser – Model – Function Imports – usp_TraceFlagSourceFetch

Properties

Let us quickly review the properties of our auto-created Function Import

Menu

To access the property right click on the Function and select Properties from the dropdown menu.

Properties

Explanation
  1. Stored Procedure Name
    • We see the name of the  underlying Stored Procedure, usp.TraceFlagSourceFetch,
  2. Return Type
    • And, also notice that the system has auto created a Return Type
      • The Return Type is usp_TraceFlagSourceFetch_Result1

 

Properties – Return Type

We are able to access the “Return Type” and review the structure of hat is actually Returned

Edit Function Import

By default the Return is an auto-created type.

It’s naming syntax is <SP>_Result#.

 

Edit Function Import .. #2

We should click on the “Get Column Information” button to get the actual columns and their metadata.

 

Mapping Details

If we access the Model Browser and review the Mapping Details for our selected SP, here is what we see:

 

 

Add New – Function Import

When defining our Stored Procedure we made sure to include the same columns as the table that we are exposing.

And, in so doing, we can explicitly map the Return Type to our table.

Add New – Function Import – Menu

Right click in an empty area on the diagram model and choose the “Add New” \ “Function Import” menu entry.

 

Add New – Function Import – TraceFlagSourceFetch

Explanation

  1. Function Import Name
    • We offered a name and that name is TraceFlagSourceFetch
  2. Return a collection of
    • Entities
      • traceFlagSource

Add New – Function Import – TraceFlagSourceFetch – Get Column Information

Upon clicking on the “Get Column Information” button, we are able to review the list of columns.

 

Model Browser Panel – Post Adding a “Function Import”

Explanation

Quick review, we have two Function Imports:

  1. TraceFlagSourceFetch
    • The one we defined ourselves
  2. usp_TraceFlagSourceFetch
    • The one auto-created for us

 

 

Visual Studio

Launch Visual Studio and open the Project.

Solution Explorer

Access the Solution Explorer and double click on the edmx file.

EDMX

Here is what our EDMX Diagram looks like.

 

Model Browser

Let us review the Model Browser.

Right click in an empty spot on the diagram panel and from the drop-down menu choose, Model Browser.

Diagram Panel – Menu

Model Browser Panel – What Is?

Access the “Model Browser“.

An edmx contains the following section, Diagrams, Conceptual model, and Data Store.

We have already covered the Diagram.

The Conceptual Model reflects the Object Components ( Entity Types, Complex Types, Enum Types, Association, and Function Imports ).

And, the last section is the Data Store.

 

Model Browser Panel – Original

 

Update Model

Let us update the model so that we can have access to the recently created or exposed Stored Procedure.

Update Model – Choose Your Database Objects and Settings

Before Creating \ Exposing

Here is what the “Update Wizard” looks like before exposing the Stored Procedure.

 

After Creating \ Exposing

Upon creating the SP, usp_TraceFlagSourceFetch, it shows up in the Update Wizard’s Add Tab.

 

Add the Database Stored Procedure

 

Model Browser Panel – Post Adding Database Object

Once we update our Database Model and choose to Add the Stored Procedure, we will revisit the Model Browser.

The pertinent areas are :

  1. Model
    • Function Imports
      • usp_TraceFlagSourceFetch
  2. Store
    • Stored procedures / Functions
      • usp_TraceFlagSourceFetch

 

Function Import – default

During the process of updating the model with the Stored Procedure, the Wizard also creates a default Function Import.

Model Browser – Model – Function Imports – usp_TraceFlagSourceFetch

Properties

Let us quickly review the properties of our auto-created Function Import

Menu

To access the property right click on the Function and select Properties from the dropdown menu.

Properties

Explanation
  1. Stored Procedure Name
    • We see the name of the underlying Stored Procedure, usp.TraceFlagSourceFetch,
  2. Return Type
    • And, also notice that the system has auto created a Return Type
      • The Return Type is usp_TraceFlagSourceFetch_Result1

 

Properties – Return Type

We are able to access the “Return Type” and review the structure of hat is actually Returned

Edit Function Import

By default the Return is an auto-created type.

It’s naming syntax is <SP>_Result#.

 

Edit Function Import .. #2

We should click on the “Get Column Information” button to get the actual columns and their metadata.

 

Mapping Details

If we access the Model Browser and review the Mapping Details for our selected SP, here is what we see:

 

 

Add New – Function Import

When defining our Stored Procedure we made sure to include the same columns as the table that we are exposing.

And, in so doing, we can explicitly map the Return Type to our table.

Add New – Function Import – Menu

Right click in an empty area on the diagram model and choose the “Add New” \ “Function Import” menu entry.

 

Add New – Function Import – TraceFlagSourceFetch

Explanation

  1. Function Import Name
    • We offered a name and that name is TraceFlagSourceFetch
  2. Return a collection of
    • Entities
      • traceFlagSource

Add New – Function Import – TraceFlagSourceFetch – Get Column Information

Upon clicking on the “Get Column Information” button, we are able to review the list of columns.

 

Model Browser Panel – Post Adding a “Function Import”

Explanation

Quick review, we have two Function Imports:

  1. TraceFlagSourceFetch
    • The one we defined ourselves
  2. usp_TraceFlagSourceFetch
    • The one auto-created for us

 

Code

We have to stub codes to share:

The first one calls the Function created by default.

And, the other one calls the function we created and casted to our Table.

Code – Default Function


private void listTraceFlagSourceUseSPImplicit(int iID)
{ 

	//invoke the default Function
	var varTFS = dbDE.usp_TraceFlagSourceFetch(iID);

	//Iterate list
	//Remember that it Returns data as a usp_TraceFlagSourceFetch_Result1
	foreach (usp_TraceFlagSourceFetch_Result1 objTFS in varTFS)
	{

		// prepare UI output
		strLog
			= String.Format
				(
					  FORMAT_TRACESOURCE_LIST
					, objTFS.source
					, objTFS.title
					, objTFS.url
				);

		Console.WriteLine(strLog);

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

   } //foreach


} //listTraceFlagSourceUseSPImplicit

Code – Explicit Function


public void listTraceFlagSourceUseSPExplicit(int iID)
{

	//invoke the explicit Function
	//Remember that the data return is strongly type
	//and it is a table
	var varTFS = dbDE.TraceFlagSourceFetch(iID);

	//Iterate list
	//Using foreach expose each list element in varTF
	foreach (traceFlagSource objTFS in varTFS)
	{

		// prepare UI output
		strLog
			= String.Format
				(
					  FORMAT_TRACESOURCE_LIST
					, objTFS.source
					, objTFS.title
					, objTFS.url
				);

		Console.WriteLine(strLog);

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

	} //foreach
	


} //listTraceFlagSourceUseSPExplict


Sql Server Profiler

Let us target SQL Server Profiler and see what our payload looks like:

Explanation:

  1. Event Class
    • RPC:Completed
    • TextData
      • Sample
        • exec [dbo].[usp_TraceFlagSourceFetch] @id=2559
    • CPU
      • 0
    • Reads
      • 2
    • Writes
      • 0

 

Listening

Behind every post there is a story.

But like Rick, this is how I will have to end this Story.

Slick Rick – Bedtime Story
Link

He dropped the gun, so went the glory
And this is the way I have end this story

Just another case ’bout the wrong path,
Straight ‘n narrow or yo’ soul gets cast(?)

 

 

Entity Framework – Application – Version # – Console & ASP.Net Applications

Background

Let us go a tiny bit further into our Entity Framework application.

But, before we go too far let us review the Version # of the Entity Framework library that is being used.

 

Diagnostic

File System

Let us review the File System, specifically the folder where the application is installed.

Entity Framework Libraries

Entity Framework libraries are bundled in dlls.

There is the baseline EntityFramework.dll and each targeted database platform will have its own dll.

Screen Shots

Console App
Image

EntityFramework.dll

EntityFramework.SqlServer.dll

 

Tabulate
Type File Product Version
 Core
 EntityFramework.dll  6.1.3-40302
 SQL Server
 EntityFramework.SqlServer.dll  6.1.3-40302

 

 

Web App
Image

 

 

Running Application

Resource Monitor

Start the Application and start the OS built-in Resource Monitor.

Image

Console App

Tabulate

Associated Modules
Type Module Name Version# Full Path
 Associated Modules
 EntityFramework.ni.dll 6.1.40302.0 C:\Windows\Assembly\NativeImages_v4.0.30319_32\EntityFramework\…\EntityFramework.ni.dll

 

 

 

Web Site App
w3wp.exe

 

Tabulate

Associated Handles
Type File Product Version
 Core
EntityFramework.DLL  4.0.30319
 SQL Server
EntityFramework.SqlServer.Dll  4.0.30319.33440

 

 

Associated Modules
Type File Product Version
 Core
System.Data.Entity.ni.dll  4.6.1087.0
System.Data.Linq.ni.ll  4.6.108.07
 SQL Server
 System.Data.OracleClient.dll  4.0.30319.33440
 System.Data.OracleClient.ni.dll  4.0.30319.33440

 

 

Summary

Both the libraries\dll files bundled with the Application and the modules loaded into memory reveal that the EntityFramework Library is in-use by the Application.

For our console application, the specific version of EntityFramework Library is Version 6.1.40302.0

And, for the Web App, the version is 4.6.1087.0

BTW, the CLR Version is 4.0.30319 32 bit.