The SET Succeeds and the Catalog Read Succeeds, Then Msg 3951
Snapshot isolation is the answer to most of the problems WITH (NOLOCK) gets used for, which I covered in NOLOCK Returned a Balance That Never Existed. Readers stop waiting for writers, and unlike read uncommitted, everything they read was actually committed.
Adopting it has two prerequisites that are easy to get wrong, and both fail in ways that point somewhere other than the cause.
You cannot switch mid-transaction
The isolation level has to be set before the transaction starts. What makes this awkward is where the error appears.
Everything below runs in a scratch database with one small table in it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE DATABASE [snapshot_demo]; GO USE [snapshot_demo]; GO CREATE TABLE [dbo].[snapshot_probe] ( [probe_id] int NOT NULL IDENTITY(1,1) , [payload] varchar(20) NOT NULL , CONSTRAINT [pk_snapshot_probe] PRIMARY KEY CLUSTERED ([probe_id]) ); INSERT [dbo].[snapshot_probe] ([payload]) VALUES ('row one'), ('row two'); |
Now open a transaction and try to switch:
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SNAPSHOT; PRINT N'the SET itself raised nothing'; SELECT [catalog_read] = COUNT_BIG(1) FROM [sys].[all_objects]; PRINT N'catalog view read succeeded'; SELECT [user_table_read] = COUNT_BIG(1) FROM [dbo].[snapshot_probe]; |
|
1 2 3 4 5 6 7 |
the SET itself raised nothing 2417 catalog view read succeeded Msg 3951, Level 16, State 1 Transaction failed in database 'snapshot_demo' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. |
Identical on SQL Server 2019 (15.0.4480.2) and SQL Server 2025 (17.0.1125.2).
Three things happen in order there, and the order is the problem.
The SET succeeds. It raises nothing at all, so the statement that is wrong reports no error.
The read of sys.all_objects succeeds.
The read of a user table fails, with Msg 3951 naming a statement several lines below the one that caused it.
Why the third statement is the one that fails
A snapshot transaction reads every row as of a single point in time, and it identifies that point with a transaction sequence number. sys.dm_tran_active_snapshot_database_transactions returns one row per snapshot transaction, so you can watch for the moment a transaction acquires one.[4]
Here is the same sequence, with a count from that DMV added after each step:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SELECT [after_set] = COUNT_BIG(1) FROM [sys].[dm_tran_active_snapshot_database_transactions] WHERE [session_id] = @@SPID; SELECT [catalog_read] = COUNT_BIG(1) FROM [sys].[all_objects]; SELECT [after_catalog_read] = COUNT_BIG(1) FROM [sys].[dm_tran_active_snapshot_database_transactions] WHERE [session_id] = @@SPID; SELECT [user_table_read] = COUNT_BIG(1) FROM [dbo].[snapshot_probe]; |
|
1 2 3 |
after BEGIN TRANSACTION and SET : 0 snapshot transactions after reading sys.all_objects : 0 snapshot transactions reading dbo.snapshot_probe : Msg 3951 |
The transaction never acquires a sequence number. SET TRANSACTION ISOLATION LEVEL changes a session setting, and it does not give an already-open transaction a point in time to read from. The read of dbo.snapshot_probe is the first statement that needs a row version, which makes it the first statement to go looking for a sequence number, and there is not one to find.
Run the same steps in the correct order, with the SET before BEGIN TRANSACTION, against the same database with ALLOW_SNAPSHOT_ISOLATION turned on:
|
1 2 3 |
after BEGIN TRANSACTION : 0 snapshot transactions after reading sys.all_objects : 0 snapshot transactions after reading dbo.snapshot_probe : 1, sequence number 31371 |
The sequence number appears at the read of user data. Not at BEGIN TRANSACTION, and not at the catalog read.
That is also the answer for sys.all_objects. It does not register a snapshot transaction even when the transaction is correctly formed, because it does not read row versions. Having nothing to version, it has nothing to check, and it cannot fail this way.
That middle step is what makes this expensive to debug. A smoke test that switches isolation level and then queries sys.databases or sys.tables to confirm it worked will pass, and the code ships. The failure then appears the first time the procedure touches real data.
One control, because the two prerequisites are easy to conflate. With ALLOW_SNAPSHOT_ISOLATION ON for the database, and the SET still issued after BEGIN TRANSACTION, the read raises Msg 3951 again. Enabling the database setting does not repair the ordering.
It has to be enabled on the database
The second prerequisite is that ALLOW_SNAPSHOT_ISOLATION must be ON for the database being read.[1] It is off by default, including on a database created moments ago, which is what snapshot_demo was above.
Get the ordering right and leave the database setting alone, and the read fails for the other reason:
|
1 2 3 4 |
Msg 3952, Level 16, State 1 Snapshot isolation transaction failed accessing database 'snapshot_demo' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation. |
Two prerequisites, two error numbers, and they are worth telling apart. Msg 3951 means the transaction started under the wrong isolation level. Msg 3952 means the database does not permit snapshot isolation at all.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [name] , [snapshot_isolation_state_desc] , [is_read_committed_snapshot_on] FROM [sys].[databases] WHERE [snapshot_isolation_state] <> 0 ORDER BY [name]; |
On my 2019 instance that returns four databases out of everything on the server. Not a single user application database among them.
This is worth running before you write a line of code that depends on snapshot isolation, because the alternative is discovering it in whichever environment you deploy to first. Enabling it is an ALTER DATABASE, and on a busy database that statement has to wait for existing transactions to finish before it can take effect.
Two settings, not one
ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are separate options and they do different things.
ALLOW_SNAPSHOT_ISOLATION permits transactions that explicitly ask for SET TRANSACTION ISOLATION LEVEL SNAPSHOT. Nothing changes for code that does not ask.
READ_COMMITTED_SNAPSHOT changes what the default read committed level does, so every existing read committed transaction starts using row versioning instead of shared locks, without any code change at all.[2]
The second is the bigger hammer and the bigger commitment. It removes reader blocking across the whole database at once, and it changes the behaviour of code nobody has looked at in years. The query above returns both columns for that reason.
Both put row versions in tempdb, so tempdb needs the space and the write throughput to carry them.[3]
The guard
Because the SET has to happen outside a transaction, the pattern needs a check:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @isolation_changed bit = 0; IF @@TRANCOUNT = 0 BEGIN SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SET @isolation_changed = 1; END; /* ... queries here ... */ IF @isolation_changed = 1 BEGIN SET TRANSACTION ISOLATION LEVEL READ COMMITTED; END; |
If a caller already has a transaction open, the procedure leaves the isolation level alone and runs under whatever the caller chose. It does not attempt a switch that would fail on the first user table read.
Restoring the level afterwards matters because the setting persists for the connection, not the batch. Leave it set and every subsequent query on that connection inherits it, which in a pooled application means arbitrary later work runs under an isolation level it did not ask for.
The same @@TRANCOUNT check appears in the transaction ownership pattern for the same underlying reason: a procedure cannot know whether it is the outermost caller unless it looks.
Where I stop short
Snapshot isolation is a reader-side improvement, and I apply it to read paths.
I do not impose it on procedures that update key tables, counter tables, or sequence tables, particularly ones written around ROWLOCK and UPDLOCK semantics. Under snapshot isolation those writers can hit update conflicts, error 3960, where read committed would have blocked and then proceeded. That converts a wait into a failed transaction, which is a change in behaviour rather than a change in performance, and it deserves its own testing rather than a blanket rollout.
The rule
Prefer snapshot isolation to NOLOCK on read paths. Guard the SET with @@TRANCOUNT = 0, restore the previous level afterwards, and confirm ALLOW_SNAPSHOT_ISOLATION is ON for the database first.
When testing that it works, read a user table. A test that only queries sys. objects reports success where a user table would fail.
This is one of a series on the T-SQL conventions I actually use and why.
Have you rolled out READ_COMMITTED_SNAPSHOT on an existing database, and did anything surprise you? Bluesky or LinkedIn.
References
- ALTER DATABASE SET Options (Transact-SQL) - Microsoft Learn. ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT, their defaults, and what taking effect requires. ↩
- SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - Microsoft Learn. The snapshot level, the restriction on switching once a transaction has started, and the resulting error. ↩
- Transaction Locking and Row Versioning Guide - Microsoft Learn. How the version store works, where it lives, and the update conflict behaviour writers can meet. ↩
- sys.dm_tran_active_snapshot_database_transactions - Microsoft Learn. One row per active snapshot transaction per database, including the transaction sequence number used here to show when it is acquired. ↩