RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server.
This is, as I understand, the context of the sripts in MSDN blog article "Concurrency Series: Minimizing blocking between updaters" telling that:
-
"The second trick of using RCSI does not work either and the update gets blocked behind X lock by session-1 on row with (C1 = 1). The reason is that the following UPDATE statement executes at a higher isolation level than read committed.
update basic_locking set c2 = 1000 where c1 = 2
Here is the output showing the blocking
Solutions: Here are two choices to address this issue:"
How does "the output" illustrate the implicit elevation of implicit default isolation level (of Read Committed)?
How to check for real isolation level "jumpings" in context of some statements?
When to expect them and why do they occur?
Best Answer
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 theREAD 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 anyU
locks taken (and not converted toX
locks) are released at the end of the statement. This is different from the behaviour of theUPDLOCK
hint, which acquires and holdsU
locks (at least) until the end of the transaction. In addition,REPEATABLE READ
generally acquiresS
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 specifyUPDLOCK
explicitly. I apologise that this is so complicated, but there we are.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.
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):
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):
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.