There was a lot of feedback on yesterday’s editorial citing confusion or miss-represented concepts.
I think I haven’t been clear in my terms, and there are terms that are nearly the same “RowVersion” = Type, RowVersions = Data Change Tracking.
Perhaps this paragraph from https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx will clarify, or make the concept even more murky. This is how ADO.Net is configured to not block.
SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. Transactions that write data do not block snapshot transactions from reading data. You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.
So for ADO.Net to use snapshot isolation you have to turn it on in each database individually. Then when you use READ COMMITTED in your ADO.Net Transaction, it will behave based on the database setting.
By Contrast this link demonstrates the same concept implemented at that database level for internal transactions, or implicit transactions not provided through the client.
https://msdn.microsoft.com/en-us/library/ms173763.aspx
Here’s a quote to demonstrate how the database setting works with Row Versioning.
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.
Snapshot isolation supports FILESTREAM data. Under snapshot isolation mode, FILESTREAM 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.
As I am reading these documents it appears that this does not require database change tracking, the row versioning appears to be implemented for all tables in tempdb; which does not require data change management, as I understood this technique yesterday. So, there may be some confusion in that.
Thanks guys for helping to clear this up. Be sure to leave a comment if I don’t have it quite right today.
Cheers,
Ben