По материалам статьи Craig Freedman: Read Committed and Updates

Проведём эксперимент. Начнем с создания следующей простой схемы:

create table t1 (a int, b int)
create clustered index t1a on t1(a)
insert t1 values (1, 1)
insert t1 values (2, 2)
insert t1 values (3, 3)
create table t2 (a int)
insert t2 values (9)

В сеансе 1 заблокируем третью строку таблицы t1:

begin tran
update t1 set b = b where a = 3

Далее в сеансе 2 посмотрим spid сессии (он позже понадобится), и выполним представленное ниже изменение на уровне изоляции по умолчанию read committed:

select @@spid
update t1 set t1.b = t1.b
where exists (select * from t2 where t2.a = t1.b)

Для это изменения оптимизатор выберет следующий план запроса:

^{|–Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[b] = [t1].[b]))
……|–Top(ROWCOUNT est 0)
…………|–Nested Loops(Left Semi Join, WHERE:([t2].[a]=[t1].[b]))
………………|–Clustered Index Scan(OBJECT:([t1].[t1a]))
………………|–Table Scan(OBJECT:([t2]))}

Чтобы узнать, нужно ли изменять строку, в этом плане выполняется просмотр таблицы t1 и каждой строки в таблице t2. Просмотр получает U блокировку на каждую строку t1. Если строка изменяется, уровень блокирования поднимается до X-блокировки. Если строка не изменяется, просмотр снимает со строки блокировку, поскольку у этой сессии используется режим изоляции read committed.

У нас в первом сеансе удерживается блокировка на третьей строке таблицы t1, из-за этого изменение её будет заблокировано, когда просмотр таблицы t1 дойдёт до третьей строки. В этот момент можно посмотреть, какие блокировки удерживает второй сеанс, выполнив следующий запрос в первом или любом другом сеансе:

select resource_type, request_mode, request_type, request_status
from sys.dm_tran_locks
where request_session_id =<session_2_spid>

resource_type   request_mode   request_type   request_status
-------------   ------------   ------------   --------------
DATABASE        S              LOCK           GRANT
OBJECT          IS             LOCK           GRANT
KEY             U              LOCK           WAIT
PAGE            IU             LOCK           GRANT
OBJECT          IX             LOCK           GRANT

Как и ожидалось, мы видим только одну находящуюся в ожидании U-блокировку.
Далее во втором сеансе проверим, что изменения невозможны, выполнив следующую инструкцию:

update t1 set t1.a = t1.a
where exists (select * from t2 where t2.a = t1.b)

Обратите внимание, что в этот раз мы вносим изменения в ключ кластеризованного индекса. Изменение ключа кластеризованного индекса может привести к перемещению строк в дереве индекса. Для предотвращения повторного изменения строки во время того же самого просмотра (что было бы неправильно), SQL Server должен добавить блокировку между просмотром и обновлением таблицы t1. Это требование принято называть: «Halloween protection». И действительно, новый план выглядит теперь так:

^{|–Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[a] = [t1].[a]))
……|–Top(ROWCOUNT est 0)
…………|–Sort(DISTINCT ORDER BY:([t1].[a] ASC, [Uniq1002] ASC))
………………|–Nested Loops(Inner Join, WHERE:([t2].[a]=[t1].[b]))
……………………|–Clustered Index Scan(OBJECT:([t1].[t1a]), ORDERED FORWARD)
……………………|–Table Scan(OBJECT:([t2]))}

Изменение накладывает блокировку повторно. Давайте теперь проверим, какие блокировки удерживаются, для чего взглянем на показанный выше запрос в динамическом административном представлении sys.dm_tran_locks:

resource_type  request_mode  request_type  request_status
-------------  ------------  ------------  --------------
DATABASE       S             LOCK          GRANT
OBJECT         IS            LOCK          GRANT
KEY            U             LOCK          WAIT
KEY            U             LOCK          GRANT
KEY            U             LOCK          GRANT
PAGE           IU            LOCK          GRANT
OBJECT         IX            LOCK          GRANT

На этот раз мы видим, что предоставлены две U-блокировки. Как же это происходит? Разве эти блокировки не должны были быть сняты после того, как мы запускаем просмотр с read committed? Не так быстро! Если в плане используется оператор сортировки, он накладывает блокировку строки до завершения просмотра, не допуская её изменения. Если бы SQL Server просто снимал каждую U-блокировку со строк, когда просмотр t1 их освобождал, ни одна из этих строк не была бы заблокирована при начале выполнения изменений. Без каких-либо дополнительных блокировок другой сеанс сможет «проскользнуть» и изменить строки, которые уже были просмотрены и которые были изменены по плану запроса. Подобное допущение другому сеансу изменять эти строки может привести к неверным результатам и порче данных. Поэтому SQL Server удерживает эти блокировки до тех пор, пока не завершится выполнение инструкции (но не транзакции).