How to: Check Transaction Isolation Levels

Setting the right transaction isolation level for SQL Server is essential to maintain consistency with optimized concurrency. Below are two quick tips on how to check transaction isolation levels.

How to check optimistic concurrency control level?

Since SQL Server 2005,  optimistic concurrency controls achieve no blocking by row-versioning techniques. There are two kinds of optimistic lockings: SNAPSHOT and READ COMMITTED SNAPSHOT isolations. They can be only set at database level by using "ALTER DATABASE" command. Below shows the query to find out whether you have either of them on or not.

---- To check SNAPSHOT_ISOLATION_STATE and READ_COMMITTED_SNAPSHOT for the database 
SELECT name as database_name
,snapshot_isolation_state
,snapshot_isolation_state_desc
,is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'DATABASE_NAME';
GO

How to check transaction isolation levels for sessions?

There are two ways to check transaction isolation levels for sessions.

1) Run the DBCC UserOptions command get the transaction isolation level for current sessions. You must make sure that you have the right database selected for the session first.
    The possible isolation values for this command are:

• read uncommitted 
• read committed 
• repeatable read 
• serializable
• read committed snapshot
• snapshot 

2) Use sys.dm_exec_sessions to get transaction isolation levels for not only current sessions but also other sessions by session IDs.

--- To check isolation levels for other connections
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 = 
---- where session_id = @@SPID 

Here @@SPID returns the session ID of the current user process.

Look out for READ_COMMITTED_SNAPSHOT!

The above two methods give the same results for all transaction isolation levels EXCEPT for READ_COMMITTED_SNAPSHOT. Using sys.dm_exec_sessions will give you "ReadCommitted" while DBCC UserOptions command will have "read committed snapshot" as the isolation level. Here is the sreenshot:

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 snapshot

At the bottom you can see the isolation level is "read committed snapshot".