August 23, 2005 at 9:41 am
I have read that
1. snaphosts are great for applications generating reports because they represent a "static" (point in time) picture of the database, and
2) The Snapshot actually holds only data that has changed since the original snapshot. Doing a SELECT against a Snapshot pulls data from the snapshot (if it has changed) or from the original database (if the data has not changed).
Which one is correct? If #2 is correct, then a SELECT always returns the current realtime state of the data, which contradicts #1
TIA,
Bill
August 24, 2005 at 10:49 am
No, they're both correct statements.
The current realtime state of the data that hasn't changed is, by definition, the same as it was when the snapshot was created.
A query against a snapshot returns static data because you will get the same data every time (until the snapshot is overwritten), regardless of changes in the original database.
August 24, 2005 at 1:26 pm
Hello Bill,
It is not a contradiction. It is only a "new" paradigm.
In the past (before snapshot), SQL server uses locks to ensure you cannot read the modified row until the transaction which modifies that row commits or rollback.
Now with "snapshot", you can read the row when you want. If the transaction that modify it has not commited or has rollbacked, you will see the "current" value (not modified), if the transaction that modify it has commited, you will see the new value (modified value).
In transactions, everything happens at the commit or nothing happens (rollback). Row versioning with "snapshot" or "Read commited" isolation levels reflect that perfectly and involve no contention between readers and writers.
Regards,
Carl
August 25, 2005 at 12:56 am
At the risk of restating what the other guys said (but I do not think it came out 100% clear).
There is no contradiction. What #2 actually says is that if the data has changed a select will read the original value (as it was when you started your transaction) from the snapshot (stored in tempdb). If it has not changed then it will be read from the original database (naturally).
In detail, what happens is that your select of course first goes to the original database. If a row has changed after your transaction was started there will be a pointer there that points to the previous version of the row, stored in tempdb. Following that pointer you might find that that version is also newer than your transaction, so you need to follow the pointer from there to the previous version. Sooner or later you will get to the version of the row as it was when your transaction was started.
August 25, 2005 at 1:25 am
Thanks to everyone for their responses but Chris's response really hit home with me. I don't think I understood the subtlety involved.
Bill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply