How to find current transaction level?

Question

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

score 315 · Accepted Answer · edited Jul 25 '22 at 13:30

315

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.

edited Jul 25 '22 at 13:30

StackzOfZtuff

2,534
1
28
25

answered Jun 24 '09 at 12:54

SQLMenace

132,095
25
206
225

11

This one is not accurate if the isolation level is "read_commited_snapshot". In this case, it will only show "Readcommited". – GaTechThomas May 28 '13 at 17:07
9

@GaTechThomas , `READ_COMMITTED_SNAPSHOT` is not isolation level, it is a database's option permitting to change the behavior of `ReadDCommitted` isolation level database-wide – Gennady Vanin Геннадий Ванин Dec 11 '13 at 07:29
@GaTechThomas, then how to find that READ_COMMITTED_SNAPSHOT or READ_COMMITTED_Locked – Imran Qadir Baksh - Baloch Dec 15 '13 at 08:13
1

@user960567, IIRC, Scott Ivey's answer would give those results. – GaTechThomas Dec 15 '13 at 19:13
this unfortunately does not work for SQL Server 2000. – zzzeek Jan 26 '17 at 20:52
3

@zzzeek - That's what you get for using a database that has been obsolete for twelve years. – Martin Brown Aug 01 '17 at 08:15
This is not correct in some instances. If I have, e.g., "Read Commited Snapshot" isolation level, the above code will only write ReadCommited. – StupidOne Jul 25 '19 at 12:57
1

@StupidOne "Read committed snapshot" is not a distinct isolation level, so showing "read committed" is correct. It is a database option that changes the behavior of READ COMMITTED. To check if READ_COMMITTED_SNAPSHOT is enabled, take a look at this: https://stackoverflow.com/a/51977/208318 – Doug Feb 17 '20 at 10:08
3

I understand the technicality of READ COMMITTED SNAPSHOT not being it's own distinctive isolation level but this will catch people out and did catch me out. Also MS themselves state it as an isolation level in the TABLE shown further down this page before the examples start so this answer could be improved by being more pragmatic and being in line with MS remarks https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15 – Alan Macdonald Aug 25 '21 at 11:32
READ_COMMITED_SNAPSHOT behaves differently than READ_COMMITED, so it is isolation level in its own right, hence the downvote In theory, we can be technically correct, but in practice we need to be realistically correct. – andowero Sep 01 '22 at 06:47

score 71 · Answer 2 · answered Sep 30 '14 at 21:14

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

and it points out "read committed snapshot" when active (see RC snapshot vs locked), at least on SQL Server 2008 — user1075613, Apr 28 '17 at 20:04

score 33 · Answer 3 · answered Feb 23 '14 at 10:39

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();

Please also elaborate on the code to be more educative. – László Papp Feb 23 '14 at 10:59 — László Papp, Feb 23 '14 at 10:59

score 26 · Answer 4 · answered Jun 24 '09 at 13:16

26

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

answered Jun 24 '09 at 13:16

Scott Ivey

40,768
21
80
118

+1 since its also prints 'snapshot' when it being used along w/ read committed (and not the default shared lock mechanism) – Shmil The Cat Feb 26 '14 at 14:48
this is overkill, just do DBCC USEROPTIONS as thiagoh says – user1075613 Apr 28 '17 at 20:01

score 10 · Answer 5 · edited Apr 26 '16 at 17:07

10

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.

edited Apr 26 '16 at 17:07

David Ferenczy Rogožan

23,966
9
79
68

answered Jun 24 '09 at 12:49

Eric Petroelje

59,820
9
127
177

6

Also keep in mind DBCC USEROPTIONS is an awesome option for finding your SESSION'S isolation level, but it can be tricky - if your code changes the isolation level per transaction, those periods of time where the isolation level is different from the session default can be hard to capture. For example, if you open your session with isolation level x, but change the isolation level to y for the duration of a specific transaction within the session, the DBCC USEROPTIONS will not give you visibility into that if called outside of that transaction. – DCaugs Oct 18 '13 at 14:16
1

In SQL Server 2012 "isolation level" of `DBCC USEROPTIONS` is set to "read committed" – Gennady Vanin Геннадий Ванин Dec 12 '13 at 07:16

score 4 · Answer 6 · answered Jun 09 '21 at 16:12

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();

Your answer very barely differs from [this answer](https://stackoverflow.com/a/21967158/2605758), which was posted over 7 years ago. You should [edit] your answer to include more details about your solution and/or an explanation of *how* this code block answers the question. This helps to provide context and makes your answer much more useful for future readers. — Hoppeduppeanut, Jun 10 '21 at 01:59
This answer does not count for the `transaction_isolation_level` value 5 that is set when Snapshot isolation level is set. So a `WHEN transaction_isolation_level = 5 THEN 'Snapshot'` need to be added in the CASE statement before the END — Besto, Jan 05 '23 at 06:53

How to find current transaction level?

6 Answers6

Linked