Your understanding is correct. It does get a little confusing.
Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.
If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.
If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. MS SQL Server automatically applies snapshot isolation for that table.
I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.
A quick look at performance overhead using Snapshot Isolation.
Good article on how snapshot isolation can change the expected behavior of your app. It shows examples of how a update statement and a select statement might return totally different and unexpected results.
How does the output illustrate implicit elevation of isolation level?
Sunil is technically correct, but it does sound a little confusing, I agree.
The output shows the session is blocked waiting to acquire a U lock. The definition of the READ COMMITTED isolation level is that the session will only encounter committed data. SQL Server honours this logical requirement under the default pessimistic (locking) implementation of read committed by holding shared locks just long enough to avoid seeing uncommitted data. These shared locks are normally quickly released (usually just before reading the next row).
Under optimistic (row-versioning) read committed (RCSI) SQL Server avoids reading uncommitted data by reading the last-committed version of the row at the time the statement started instead.
The sense Sunil is trying to convey is that taking U locks (instead of brief shared locks or reading versions) represents a (technical) escalation of isolation level (though not to any explicitly named level).
The effective isolation level in this case is not quite REPEATABLE READ because any U locks taken (and not converted to X locks) are released at the end of the statement. This is different from the behaviour of the UPDLOCK hint, which acquires and holds U locks (at least) until the end of the transaction. In addition, REPEATABLE READ generally acquires S locks (though this is strictly just an implementation detail).
Confusingly, the engine also takes U locks on the access method when identifying rows to update under default (locking) read-committed. This is a convenience to avoid a common deadlocking scenario without having to specify UPDLOCK explicitly. I apologise that this is so complicated, but there we are.
How to check for real isolation level "jumpings" in context of some statements?
There is nothing explicitly exposed in query plans to identify cases where the engine temporarily increases the effective isolation level. This might change in a future version of SQL Server. There may be indirect evidence in terms of locks taken, but this is rarely a convenient approach.
When to expect them and why do they occur?
Some of the occasions when internal escalation occurs are (somewhat) documented in Books Online. For example, Understanding Row Versioning-Based Isolation Levels says (among other things worth noting):
In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read.
The general reason for temporary changes in effective isolation level changes is to avoid data corruption. A list of posts identifying some common cases follows:
Blocking Operators
Large Objects
Lookup with Prefetching
Cascading Referential Integrity
Other common cases (not a complete list):
- Shared locks taken when the query processor verifies foreign key relationships.
- Range locks taken when maintaining an indexed view referencing more than one table.
- Range locks taken when maintaining an index with
IGNORE_DUP_KEY.
Some of these behaviours may be documented in Books Online, somewhere, but there's no convenient single list that I am aware of.
Best Answer
Adding this section based on @AppleBook89's comment.
Read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data.
The selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. This is the same as a read-committed transaction that does not use row versioning. If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.
For read same answer as above.
Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.
I do not know :)
I would rephrase your question to Can someone define:
Even though the optimistic concurrency control mechanism is sometimes called optimistic locking, it is not a true locking scheme—the system does not place any locks when optimistic concurrency control is used. The term locking is used because optimistic concurrency control serves the same purpose as pessimistic locking by preventing overlapping updates.
When you use optimistic locking, you do not find out that there is a conflict until just before you write the updated data. In pessimistic locking, you find out there is a conflict as soon as you try to read the data.
I find this explanation by Kendra Little very easy to follow.
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Common questions and misconceptions are explained by Robert Sheldon.
Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask
Reference:
PESSIMISTIC vs. OPTIMISTIC concurrency control
Understanding Row Versioning-Based Isolation Levels