Check the isolation level of a session
T-SQL query that returns the isolation level of the current session from sys.dm_exec_sessions.
This query returns the isolation level of the current session. It reads sys.dm_exec_sessions for the session identified by @@SPID and translates the numeric transaction_isolation_level value into a name.
SELECT 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
WHERE session_id = @@SPID;
To check another session, replace @@SPID with its session ID.