sqlsql-serversnapshot-isolation

Why check TRANCOUNT before setting TRANSACTION ISOLATION LEVEL SNAPSHOT

When using snapshot isolation, why do I often see IF @@TRANCOUNT = 0 before setting the transaction level?

I.e., in a stored proc:

IF @@TRANCOUNT = 0
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
END
Solution

  • one reason is this from Microsoft Documentations:

    A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.