on June 12, 2017
They say, “never read the comments” on the internet, but I’m lucky to get lots of interesting points and questions in my comments.
Recently, Jim mentioned that he was doing some testing in a database that allows snapshot isolation level, and he saw the something like the following sequence of events. (These are fake timestamps, just for the purpose of illustration.)
- 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?
Snapshot transactions don’t start with BEGIN TRAN
I hadn’t really thought much about this before Jim’s comment. But this behavior is documented deep within the whitepaper, SQL Server 2005 Row Versioning-Based Transaction Isolation. There’s a paragraph titled ‘Understanding the “Beginning” of a Transaction’ which explains:
…the version that a transaction will use is based on the first statement that accesses data, and not the BEGIN TRAN that creates the transaction.
To make this even clearer, the version the transaction will used is based on the first statement that accesses data using SNAPSHOT ISOLATION. If you hint the first statement in the transaction to lower the isolation level (with a NOLOCK hint, for example), it doesn’t read versioned data or ‘set’ the version for the transaction.
So perhaps I should change my language a bit, and start saying something more like, “Under snapshot isolation, all statements see data consistent with the first time data is accessed within the transaction.”
For most users, this won’t make a difference, as usually we perform data access immediately after starting a transaction.
But oftentimes folks do need to get a bit creative, so making this explicit is worthwhile.
REGISTER