Quick Scan Report – SERIALIZABLE ISOLATION LEVEL

What this check looks for

A count of sessions in sys.dm_exec_sessions whose transaction_isolation_level is 4, which is SERIALIZABLE. The message reports how many.

The levels are:

Why it matters

Serializable is the strictest isolation level, and it achieves that strictness with locks that are both wider and longer-lived than anything else.

What it does differently:

• Shared locks are held until the transaction commits, not released when the statement finishes. So a SELECT inside a transaction keeps blocking writers for as long as the transaction runs.
• It takes range locks. To guarantee that a repeated query returns the same rows, it locks not just the rows that exist but the gaps between them, preventing inserts that would fall into the range. A query with a wide predicate can lock a range far larger than the rows it actually returned.
• Deadlock probability rises sharply, because range locks on gaps create conflicts that simply do not exist at lower levels.

For the workloads it is designed for, that is correct behaviour and worth the cost. The problem is that it is very rarely chosen deliberately.

The single most common cause is .NET’s TransactionScope. Its default isolation level is Serializable, and has been since it was introduced. A developer wraps some work in a TransactionScope to get a transaction, gets serializable as well, and nothing anywhere indicates it. The application works fine in testing with one user, and blocks heavily in production.

Other sources:

• An explicit SET TRANSACTION ISOLATION LEVEL SERIALIZABLE left in a procedure from debugging.
• A connection string or ORM configuration that sets it globally.
• A linked server or distributed transaction, which may escalate the level.

The tell is that nobody can explain why it is set. When you find it and ask, the answer is almost always that nobody chose it.

How to confirm it yourself

Which sessions, and what they belong to:

SELECT s.[session_id],
       CASE s.[transaction_isolation_level]
            WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read uncommitted'
            WHEN 2 THEN 'Read committed' WHEN 3 THEN 'Repeatable read'
            WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot'
            ELSE 'Unknown' END                          AS [isolation_level],
       s.[login_name],
       s.[host_name],
       s.[program_name],
       s.[status],
       s.[last_request_start_time]
  FROM sys.dm_exec_sessions AS s WITH (NOLOCK)
 WHERE s.[is_user_process] = 1
   AND s.[transaction_isolation_level] = 4
 ORDER BY s.[last_request_start_time] DESC;

program_name and host_name are the answer. They identify the application, and the application is where the setting is.

A breakdown of everything connected, which puts it in proportion:

SELECT CASE [transaction_isolation_level]
            WHEN 1 THEN 'Read uncommitted' WHEN 2 THEN 'Read committed'
            WHEN 3 THEN 'Repeatable read'  WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot' ELSE 'Other' END AS [isolation_level],
       [program_name],
       COUNT(*) AS [sessions]
  FROM sys.dm_exec_sessions WITH (NOLOCK)
 WHERE [is_user_process] = 1
 GROUP BY [transaction_isolation_level], [program_name]
 ORDER BY [sessions] DESC;

Whether it is causing blocking, which is what makes it urgent or not:

SELECT r.[session_id], r.[blocking_session_id], r.[wait_type], r.[wait_time],
       s.[program_name], t.
  FROM sys.dm_exec_requests AS r WITH (NOLOCK)
 INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK) ON s.[session_id] = r.[session_id]
 OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE r.[blocking_session_id] <> 0
 ORDER BY r.[wait_time] DESC;

And find it in code:

SELECT OBJECT_SCHEMA_NAME([object_id]) AS [schema_name],
       OBJECT_NAME([object_id])        AS [object_name]
  FROM sys.sql_modules WITH (NOLOCK)
 WHERE [definition] LIKE '%SERIALIZABLE%';

How to fix it

Find where it is set, and decide whether it was meant.

If it is TransactionScope, which it usually is, the fix is in the application and it is small:

var options = new TransactionOptions {
    IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
};
using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
{
    // work
    scope.Complete();
}

That is the whole change, and it is worth checking every TransactionScope in the codebase rather than just the one you found.

If it is in a stored procedure, ask what it was protecting. Sometimes a genuine requirement: a check-then-insert where a phantom row would break correctness. Far more often it was added during debugging and never removed.

If it is genuinely required, the alternatives are usually better:

• SNAPSHOT isolation gives a consistent view without taking locks at all. It uses tempdb for row versions, which is a real cost with its own checks, and it removes the blocking entirely.
• UPDLOCK on the specific statement that needs the protection, rather than the whole transaction at serializable. That has its own check, because it can be over-applied too, and applied narrowly it is the right tool for a read-then-update race.

And keep the transaction short. Whatever the level, a serializable transaction that runs for a second blocks far less than one that runs for a minute.

How long it takes

About an hour to identify the source. Changing the application is a code change with its own release cycle.

Related reports

Related checks

Frequently asked questions

Is serializable ever right? Yes, for a genuine phantom read problem where correctness depends on the range not changing. It is rare, and when it is right it should be on the specific transaction rather than the connection.

Why does .NET default to it? TransactionScope‘s default has been Serializable since it was introduced, and changing it would break existing behaviour. Every project has to override it.

Does read committed snapshot help? For reader-writer blocking, considerably. It does not change what a session that has explicitly asked for serializable does.

The sessions are idle. Are they still holding locks? If they have an open transaction, yes. A sleeping session at serializable with an open transaction holds every lock it has taken, which has its own check.