A few times I’ve wondered how to detect the ISOLATION level and have discovered a few.
DBCC USEROPTIONS
DBCC USEROPTIONS Set Option Value ------------------------------------------------------------------------------- textsize 2147483647 language us_english dateformat mdy datefirst 7 lock_timeout -1 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation level read committed
sys.dm_exec_sessions
select
session_id,
case (transaction_isolation_level)
when 0 then 'Unspecified'
when 1 then 'Read Uncommitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end as transaction_isolation_level_desc
from
sys.dm\_exec\_sessions
where
session_id = @@spid
/*
session_id transaction_isolation_level_desc
---------- --------------------------------
55 Read Committed
*/
sys.dm_exec_requests
select
session_id,
case (transaction_isolation_level)
when 0 then 'Unspecified'
when 1 then 'Read Uncommitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end as transaction_isolation_level_desc
from
sys.dm_exec_requests
where
session_id = @@spid
/*
session_id transaction_isolation_level_desc
---------- --------------------------------
55 Read Committed
*/
Remember that the isolation level can be modified for query / statement through the use of table hints to be different to the level in place when the transaction started. This is supported by the sys.dm_exec_requests view as it has a many to one relationship with a transaction and represents a substring of the sys.dm_exec_sql_text.
Post a Comment