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?
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.