Snapshot isolation behaviour. "Triggered" at first query?

Question

I am doing some tests to try to understand how snapshot isolation works...and I do not. I have SET ALLOW_SNAPSHOT_ISOLATION ON in my db (not interested in READ_COMMITTED_SNAPSHOT atm). Then I do the following tests. I will mark different sessions (practically different tabs in my ssms) by [s1] and [s2] markup,[s2] being the isolated session, and [s1] simulating another, non-isolated session.

First, make a table, and let's give it a row. @[s1]:

create table _g1 (v int)
insert _g1 select 1
select * from _g1
(Output: 1)

Now let's begin an isolated transaction. @[s2]:

set transaction isolation level snapshot
begin tran

Insert another row, @[s1]:

insert _g1 select 2

Now let's see what the isolated transaction "sees", @[s2]:

select * from _g1
(Output: 1,2)

Strange. Shouldn't the isolation "start counting" from the moment of the "Begin tran"? Here, it should not have returned the 2....Let's do this another time. @[s1]:

insert _g1 select 3

@[s2]:

select * from _g1
(Output: 1,2)

So, this time it worked as I expected and did not account the latest insert.

How is this behaviour explained? Does the isolation start working after the first access of each table?

Answer 1

Snapshot isolation works with row versioning. For each modification on a row, the database engine maintains the previous and the current version of the row, along with the serial number (XSN) of the transaction that made the modification.

When snapshot isolation is used for a transaction in [s2]:

The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

(see "How Snapshot Isolation and Row Versioning Work", in https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server). The transaction sequence number XSN2 for the transaction in [s2] is not assigned until a DML statement is issued.

sys.dm_tran_active_snapshot_database_transactions is a DMV which returns a virtual table for all active transactions that generate or potentially access row versions. You can query this view to get information about active transactions that access row versions.

To verify all the above, you could try:

@[s1]

create table _g1 (v int)

@[s2]

set transaction isolation level snapshot
begin tran

select * from sys.dm_tran_active_snapshot_database_transactions  -- < No XSN has been assigned, yet. Zero rows are returned.

select * from _g1 --< XSN2 is now assigned.
(Output: zero rows)

select * from sys.dm_tran_active_snapshot_database_transactions  -- < XSN2 has been assigned and the corresponding record is returned.

@[s1]

insert _g1 select 1
select * from _g1
(Output: 1)

@[s2]

select * from _g1
(Output: zero rows)

Please, see the remarks in https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-active-snapshot-database-transactions-transact-sql?view=sql-server-ver15 about when an XSN is issued:

sys.dm_tran_active_snapshot_database_transactions reports transactions that are assigned a transaction sequence number (XSN). The XSN is assigned when the transaction first accesses the version store. In a database that is enabled for snapshot isolation or read committed isolation using row versioning, the examples show when an XSN is assigned to a transaction:

• If a transaction is running under serializable isolation level, an XSN is assigned when the transaction first executes a statement, such as an UPDATE operation, that causes a row version to be created.

• If a transaction is running under snapshot isolation, an XSN is assigned when any data manipulation language (DML) statement, including a SELECT operation, is executed.

Therefore, to answer your question, snapshot isolation "starts counting" after the first 'SELECT' or other DML statement issued within the transaction and not immediately after the 'begin trasaction' statement.

Answer 2

You can Set Transaction Isolation Level Snapshot either on Database level or Session level.

In our example,we have set on Session level. So Isolation Level Snapshot will work only in that session on which it was declare. Secondly, you must issue a T-Sql statement.

In @s2 ,

Set Transaction Isolation Level Snapshot
Begin Tran

Here Transaction is open but there is no T-Sql.

So Snapshot Version of which which table will be maintain ?

Set Transaction Isolation Level Snapshot
Begin Tran
select * from _g1

Here isolation level will work on table _g1. or what ever tables are mention in T-Sql within Transaction .

In other word it will maintain Own version of records for all tables in TempDB mention in T-Sql this TRANSACTION.

It will read data from TempDB untill that Transaction is not Commit or Rollback. After this,it will read data from Table .

In @s2, Begin Tran is without RollBack or Commit.

Though all record are committed in @s1, it do not fetch 3. it fetch 1,2 which were committed prior to issue T Sql on same table.

If Rollback or Commit is done in @S2 then output will be (1,2,3). Since all Insert statement in @s1 is committed.

After Transaction is Commit or Rollback, it will read data from .

In other example,

Truncate table _g1.

We first start @s2,

Set Transaction Isolation Level Snapshot
Begin Tran
select * from _g1

Output : no record.

Here database engine has maintain own version for table _g1. Since there is no record in _g1,so TempDB is empty.

In @s1,

insert _g1 select 1
select * from _g1
(Output: 1)

In @s2,

If you simply only run

select * from _g1

or you run all script

Output is still nothing.Because we have not committed or rollback so it continue reading from TempDB.

After Commit or Rollback, it will again refresh Record of TempDB.

So output in @s2 will be 1