CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005isolation-level
dance2die
dance2die

Reputation: 36915

How to find current transaction level?

How do you find current database's transaction level on SQL Server?

Upvotes: 211

Views: 179818

Answers (6)

SQLMenace
SQLMenace

Reputation: 135011

Run this:

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

learn.microsoft.com reference for the constant values.

Upvotes: 331

Mauro Pasetti
Mauro Pasetti

Reputation: 59

SELECT CASE  
    WHEN transaction_isolation_level = 0 THEN 'Unspecified' 
    WHEN transaction_isolation_level = 1 THEN 'Read Uncommitted' 
    WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'OFF' THEN 'Read Committed' 
    WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'ON' AND d.is_read_committed_snapshot_on = 1 THEN 'Snapshot Read Committed'  
    WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'ON' AND d.is_read_committed_snapshot_on = 0 THEN 'Snapshot'  
    WHEN transaction_isolation_level = 3 THEN 'Repeatable Read' 
    WHEN transaction_isolation_level = 4 THEN 'Serializable' END AS TRANSACTION_ISOLATION_LEVEL,
    d.is_read_committed_snapshot_on,
    d.snapshot_isolation_state_desc
FROM sys.dm_exec_sessions 
       CROSS JOIN sys.databases AS d
where session_id = @@SPID
  AND  d.database_id = DB_ID();

Upvotes: 5

Eric Petroelje
Eric Petroelje

Reputation: 60498

If you are talking about the current transaction nesting level, then you would use @@TRANCOUNT.

If you are talking about transaction isolation level, use DBCC USEROPTIONS and look for an option of isolation level. If it isn't set, it's read committed.

Upvotes: 10

thiagoh
thiagoh

Reputation: 7388

just run DBCC useroptions and you'll get something like this:

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

Upvotes: 79

SQLpro
SQLpro

Reputation: 371

SELECT CASE  
          WHEN transaction_isolation_level = 1 
             THEN 'READ UNCOMMITTED' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 1 
             THEN 'READ COMMITTED SNAPSHOT' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED' 
          WHEN transaction_isolation_level = 3 
             THEN 'REPEATABLE READ' 
          WHEN transaction_isolation_level = 4 
             THEN 'SERIALIZABLE' 
          WHEN transaction_isolation_level = 5 
             THEN 'SNAPSHOT' 
          ELSE NULL
       END AS TRANSACTION_ISOLATION_LEVEL 
FROM   sys.dm_exec_sessions AS s
       CROSS JOIN sys.databases AS d
WHERE  session_id = @@SPID
  AND  d.database_id = DB_ID();

Upvotes: 37

Scott Ivey
Scott Ivey

Reputation: 41558

DECLARE   @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
DECLARE   @IsolationLevel varchar(100)

INSERT    @UserOptions
EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')

SELECT    @IsolationLevel = Value
FROM      @UserOptions
WHERE     SetOption = 'isolation level'

-- Do whatever you want with the variable here...  
PRINT     @IsolationLevel

Upvotes: 26

Related Questions