May 2017 | SansSQL

Monday, May 29, 2017

Find Isolation Level of transaction and Database

Find Isolation level of a transaction
SELECT  session_id AS SessionID, 
  program_name AS ProgramName,
  DB_NAME(database_id) AS DatabaseName, 
  CASE transaction_isolation_level 
   WHEN 0 THEN 'Unspecified' 
   WHEN 1 THEN 'ReadUncommitted' 
   WHEN 2 THEN 'ReadCommitted' 
   WHEN 3 THEN 'Repeatable' 
   WHEN 4 THEN 'Serializable' 
   WHEN 5 THEN 'Snapshot' 
  END AS Transaction_Isolation_Level
FROM sys.dm_exec_sessions

Changing the Transaction Isolation level
Using the below statement the isolation level of a transaction can be changed.

Isolation Level
  • Read uncommitted
  • Read committed
  • Repeatable read
  • Serializable
  • Read committed snapshot
  • Snapshot
SET TRANSACTION ISOLATION LEVEL <<Isolation Level>>

Find Isolation level of a Database
USE <<Database Name>>
GO
DECLARE @UserOptions TABLE ([Set Option] NVARCHAR(50), [Value] NVARCHAR(50))
INSERT INTO @UserOptions Execute ('DBCC USEROPTIONS')
SELECT * FROM @UserOptions WHERE [Set Option] = 'isolation level'

You can download the script from here.