What does "start of the transaction" mean?

Question

According to BOL, "SNAPSHOT

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction"

However, that is not exactly what I am observing. Consider this:

1. In the first tab, run this:

   CREATE TABLE dbo.TestTable(i INT); GO

   INSERT INTO dbo.TestTable(i) VALUES(1);

In another (the second) tab, run this:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION ;

Note that at this time there is only one row in TestTable. Go to the first tab again, and add one more row:

INSERT INTO dbo.TestTable(i) VALUES(2);

Return to the second tab and run a select:

SELECT i FROM dbo.TestTable;
i
-----------
1
2

(2 row(s) affected)

To me this looks like BOL might be wrong. What do you think?

Answer 1

You are seeing the second row, because when you begin the transaction you don't have any locks placed on the table.

If SQL were to make a consistent copy of all data every time a transaction was started there would be a huge amount of IO going on. Try querying the table within the transaction, then adding a new row, then querying the table again.

(I'd test this, but I don't have a box handy to test on at the moment.)

Answer 2

Your transaction "starts" when you attempt to perform some sort of access against a transactional resource. That could be a table, a database user, or even a resource on another system which is participating in a distributed transaction.

So that makes it pretty wide open. If you select from the table right after your BEGIN TRANSACTION statement, I doubt you will see the same behavior. If you access some resource in a distributed transaction, I also doubt you will see the same behavior you are now. If you do something like, "SELECT 1", I would bet (but nothing more than $1) that you would see what you're seeing now, since that doesn't affect any transactional resource.

Answer 3

Also from BOL:

Transactions start at the time a BEGIN TRANSACTION statement is executed. However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement.

So its documented (kind of) that your version store 'consistent view' is consistent with the state of the transactions at the moment you issued your first read or write, since the version store access criteria is based on your transaction sequence number:

Each version is marked with the transaction sequence number of the transaction that made the change. ... When the snapshot transaction reads a row that has a version chain, the Database Engine follows the chain and retrieves the row where the transaction sequence number is:

• Closest to but lower than the sequence number of the snapshot transaction reading the row.
• Not in the list of the transactions active when the snapshot transaction started.

...

...the read-committed transaction reads the latest transaction sequence number issued for that instance of the Database Engine. This is the transaction sequence number used to select the correct row versions for that statement.

So you see, nowhere in the version store access is the transaction time used, is always about the sequence number. And the sequence number starts at first read/write, as documented. You should probably file a bug on the SET TRANSACTION ISOLATION BOL topic and other places that suggest the transaction time is used in the version store.