To answer that I have to take a little detour, so bear with me.
If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not "compatible" with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource and X locks are taken to write to a resource. S locks are compatible with each other, X locks are not compatible with anything else. U locks are a hybrid that is used in some cases for deadlock prevention.
Now, SQL Server can take locks on several levels:Table, Partition, Page and Row. So if session one takes a table lock and session two takes a non-compatible lock on one row of the table, those two locks are not on the same resource and SQL Server won't detect the collision. To protect against that, SQL Server always starts to take a lock on the table level and works its way down the hierarchy. Now the point of page and row locks is higher concurrency, so if one session wants to write to one row and another session wants to write to another row, they should not block each other. If a session in addition to taking a lock on a row also has to take the same lock on the table, that advantage is gone. So instead of taking an exclusive lock (X) on the table, the session requests an intend-exclusive lock (IX). This lock is compatible with other intend locks but not with other "real" locks. So another session can take an intend-exclusive lock on the same table as well. The intend-exclusive lock says, that the session intends to take an exclusive lock on a lower level resource. The same happens on the page level, if the intended lock is a row lock, so after all is done, the session has an IX lock on the table and on one of the pages and an X lock on one of the rows in that page. This also means, that you will never find an intend lock on a row as rows are the lowest level in the lock hierarchy.
In some circumstances a session holds an S lock on the table or a page. If the session now (within the same transaction) requests an X lock on a row in that same table, it first has to take an IX lock on the table/page. However, a session can hold only one lock on any given resource. So to take the IX lock, it would have to release the S lock wich is probably not desired, so SQL Server offers a combination: SIX.
The reason why you have a page lock is due to SQL Server sometimes deciding that it would be better to lock the page instead of locking each row. That happens often if there are very many locks taken between al sessions already, but can have many other reasons too.
So far the theory.
Now in your case the SIX lock is held by a three table join select query. A select never takes any type of lock that is not a shared lock unless you explicitly tell it to (e.g. with a XLOCK hint). Such a hint is not visible within the input buffer, so I assume the IX part is a left over from the last batch on this connection. If you are using connection pooling and forget to cleanup all open transactions, such a lock can live potentially forever. But it becomes also very hard to troubleshoot.
You could start by running an XEvent session that pairs OPEN TRANs with COMMITs and see if you can find the culprit that way.
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
It has nothing to do with locks. The previous version of a modified row is copied to the version store in tempdb just before the actual modification is made. Physical integrity is protected by a latch on the page.
The reading query does not have to wait (in the way you suggest), because the version it needs is guaranteed to be in the version store before the data change is made. Since modifications are made a row at a time, the question of waiting for the whole table to be copied to tempdb does not arise.
It may have to wait to acquire a latch on the page containing the row, while the data modification is being made, but this is not specific to using a versioning isolation level.
In a pathological case, where the update and select process the same pages in the same order, at an equal rate, the select would have to wait (on a latch) for each page while the update changed those rows and copied to the version store, but this is exceedingly unlikely to happen in practice. As soon as the update yields even briefly, the select can easily get ahead, and so no longer have to wait.