The below are the parts of my response in an email thread that involved me – I haven’t heard back from the other party, so I’m not posting his parts of the thread since I don’t have his permission (yet). Now, admittedly, this means that without sufficient context, some of this will be confusing, but hopefully you’ll get some value out of it.
[His part deleted]
Agreed – IMHO, the SQL Server team should be shouting about this from the rooftops – the reason they may not be doing so is because the response from the Oracle camp would (correctly) be: “Hey, we’ve had that for well over a decade – congrats on finally catching up with that feature! Tell us when you can do a real grid!”
I did do a post about the feature. I’m not sure if it properly covers it with the below issues, but give it a read and let me know 🙂
Also, I’ll try to answer things in this email as well – feel free to post it or tell me to if you think there’s enough additional value to have it posted.
http://blog.sublogic.com/2006/02/oracles-multiversioning-now-available-in-sql-server/
Also, there’s a *great* (IMHO) TechNote about it.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp
[His part deleted]
You don’t need A_S_I turned on to get this – once RCS is on, writes to the database go to 2 places – the actual table and a copy to tempdb with the versioning info (see technote for a good description of the linked list that’s created).
The versioning information isn’t actually kept around for the statement/transaction that performs it – it’s kept for the other statements/transactions that are going on in the system. After all, whoever wrote the data wants to see the post-write data. It’s everyone else in the system that doesn’t want to see it, they want to see the pre-write state instead.
When 1) read committed statements start (with RCS on) OR 2) snapshot isolation transactions (with ASI also ON and snapshot requested), they take note of the current “transaction sequence number” (just a global increment of committed transactions). During the statement/transaction, the TSN is kept along with the query.
As queries during the statement/transaction happen, they evaluate based on that point-in-time of the database (TSN is a point in time, much like changeset in a version control system). If the query runs and it needs a particular row that’s been modified/deleted/whatever, it can’t get it from the current contents of the table, but that’s not a problem – it goes over to the tempdb and finds the right version of the row there (following the linked list from the technote).
[His part deleted]
Couple issues here.
How long versions are kept
First, as described above, the writers cause the versioning information to be stored, but it’s all the other things going on in the system that determine how long it’s kept around. For instance, if I kept a snapshot isolation transaction running for 10 minutes, that’s how long all the other writes would have to be kept around in tempdb. This is because at any point in time during my snapshot-isolation transaction, I may query any table I want, and I have to see the table in whatever state it was 10 minutes ago when I started by transaction.
This is why snapshot isolation gets its own A_S_I setting and explicitly has to be requested – it doesn’t change the number of writes to tempdb (those are fixed once row versioning starts getting used), but it does have a significant effect on how long the versioning information is required. When just RCS is on (and no snapshot isolation is in effect), you don’t typically have to keep around the row versions nearly as long since they’re only needed to evaluate currently running statements.
Once snapshot isolation is in effect, the row versions have to be kept around (potentially) much longer (based on how long the transactions run, of course). For instance, if all your transactions were a single statement, they would have equivalent load on the tempdb, but that’s rarely the case, of course 🙂
Implicit transactions
Each statement commits as its own transaction by default, but it’s a bad idea (IMHO) to call this implicit transactions. (I’ve seen it called “autocommit mode” a bit. Admittedly, this is more trivia than anything else, but you can turn on implicit_transactions for a connection and when one of the “state of the data matters to me” commands executes, an implicit transaction starts (and does *not* commit as soon as the statement is done). These implicit transactions span multiple statements, and you need to commit/rollback them like any other transaction – it just saved you from doing BEGIN TRAN.
[His part deleted]
I’m not sure I’m parsing the above sentence correctly, so I’ll just re-state the above – once the snapshot isolation transaction starts, all writes done by *other* transactions have to be kept around in tempdb until the transaction ends. This is necessary since the snapshot transaction has to see the entire database at the point in time when it start, with only the changes applied that it made.
Row Versioning goodness
SQL Server 2005 provides non-locking, non-blocking read consistency to your users via snapshot isolation. Find out when to use it to improve performance and reduce latency in your applications.
Technorati Tags: sql, sqlserver
