Understanding Row Versioning-Based Isolation Levels  

Posted by ReelTym


Useful Links:
The following table summarizes the differences between snapshot isolation and read committed isolation using row versioning.
PropertyRead-committed isolation level using row versioningSnapshot isolation level
The database option that must be set to ON to enable the required support.READ_COMMITTED_SNAPSHOTALLOW_SNAPSHOT_ISOLATION
How a session requests the specific type of row versioning.Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. This can be done after the transaction starts.Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.
The version of data read by statements.All data that was committed before the start of each statement.All data that was committed before the start of each transaction.
How updates are handled.Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Acquires exclusive locks on actual data rows to be modified. No update conflict detection.Uses row versions to select rows to update. Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.
Update conflict detection.None.Integrated support. Cannot be disabled.

This entry was posted on Thursday, February 17, 2011 and is filed under . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments