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.
The default READ COMMITTED transaction isolation level guarantees that your transaction will not read uncommitted data. It does not guarantee that any data you read will remain the same if you read it again (repeatable reads) or that new data will not appear (phantoms).
These same considerations apply to multiple data accesses within the same statement.
Your UPDATE statement produces a plan that accesses the Transactions table more than once, so it is susceptible to effects caused by non-repeatable reads and phantoms.
There are multiple ways for this plan to produce results you do not expect under READ COMMITTED isolation.
An example
The first Transactions table access finds rows that have a status of WaitingList. The second access counts the number of entries (for the same job) that have a status of Booked. The first access may return only the later transaction (the earlier one is Booked at this point). When the second (counting) access occurs, the earlier transaction has been changed to WaitingList. The later row therefore qualifies for the update to Booked status.
Solutions
There are several ways to set the isolation semantics to get the results you are after. One option is to enable READ_COMMITTED_SNAPSHOT for the database. This provides statement-level read consistency for statements running at the default isolation level. Non-repeatable reads and phantoms are not possible under read committed snapshot isolation.
Other remarks
I have to say though that I would not have designed the schema or query this way. There is rather more work involved than should be necessary to meet the stated business requirement. Perhaps this is partly the result of the simplifications in the question, in any case that is a separate question.
The behaviour you are seeing does not represent a bug of any kind. The scripts produce correct results given the requested isolation semantics. Concurrency effects like this are also not limited to plans which access data multiple times.
The read committed isolation level provides many fewer guarantees than are commonly assumed. For example, skipping rows and/or reading the same row more than once is perfectly possible.
Best Answer
You could switch to an optimistic concurrency control (OCC). Typically writers do not block readers. The throughput will definitely improve in the scenario you describe. Depending how you define "liveness" it may be worse than present.
OCC gives each transaction a snapshot of the data. Typically this represents committed values at the point the transaction started though details vary. So a transaction may not read the most recently committed value since that value was written after the transaction started. It is a trade-off between latency and correctness. Choose which is preferable for this scenario.