Kendra Little explains when a transaction really begins when you are in the snapshot isolation level:
-
00.000 – Session A sets its isolation level to snapshot
-
00.001 – Session A explicitly begins a transaction with BEGIN TRAN
-
00.002 – Session A starts a WAITFOR command for 15 seconds
-
10.000 – Before the WAITFOR completes, Session B inserts rows into dbo.Table
-
15.001 – Session A starts a SELECT from dbo.Table, which returns the rows that Session B inserted
This seems wrong, because many of us commonly say things like, “in Snapshot Isolation level, all statements see data consistent with the beginning of the transaction.”
But in this case, Session B inserted the rows after Session A began its transaction using Snapshot Isolation level. So why did Session A see those rows?
Kendra explains the nuance well, so read the whole thing.