.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

 

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