How Snapshot Isolation and Row Versioning Work
How Snapshot Isolation and Row Versioning Travail
When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the initial row in tempdb, and adds a transaction sequence number to the row. The following is the sequence of events that occurs:
A new transaction is initiated, and it is assigned a bargain proceedings sequence number.
The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence legions is closest to, and lower than, the transaction sequence number.
The Database Engine checks to see if the transaction sequence few is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.
The arrangement reads the version of the row from tempdb that was current as of the start of the transaction. It will not see new rows inserted after the transaction was started because those arrangement number values will be higher than the value of the transaction sequence number.
The current transaction will see rows that were deleted after the arrangement began, because there will be a row version in tempdb with a lower sequence number value.
The net effect of snapshot isolation is that the transaction sees all of the figures as it existed at the start of the transaction, without honoring or placing any locks on the underlying tables. This can result in performance improvements in situations where there is contention.
A snapshot agreement always uses optimistic concurrency control, withholding any locks that would prevent other transactions from updating rows. If a snapshot arrangement attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised
Play to change Isolation Level.
ALTER DATABASE DB_NAME
SET READ_COMMITTED_SNAPSHOT...
