По материалам статьи Craig Freedman: «Read Committed and Large Objects»

В моей последней статье было показано, что SQL Server при исполнении оператора UPDATE откладывает фиксацию блокировки до конца чтения (вместо снятия блокировок со строк сразу после их освобождения). Как там отмечалось, это делается для того, чтобы между просмотром или поиском строк для предстоящего изменения не допустить другие изменения. В этой статье будет рассмотрен аналогичный алгоритм при работе с большими объектами.

Обычно, когда SQL Server прогоняет данные через блокирующий оператор, например, это может быть сортировка, он делает промежуточную материализацию этих данных. После того, как SQL Server сделает такую копию, нет необходимости в дальнейшем блокировании исходных строк или источника данных. Однако, поскольку большие объекты (например, varchar (max)) могут занимать до 2 Гбайт, делать копии больших объектов как правило непрактично. Взамен, когда это возможно, SQL Server использует указатели на данные вместо того, чтобы материализовать копию данных. Для гарантии, что указатели остаются актуальными, SQL Server не снимает блокировку строк, содержащих большие объекты, до завершения инструкции.

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

create table t (pk int primary key, i int, lob varchar(max))
insert t values (1, 1, 'abc')
insert t values (2, 2, 'def')
insert t values (3, 3, 'ghi')

В первом сеансе заблокируем третью строку:

begin tran
update t set i = i where pk = 3

Теперь, во втором сеансе запомним spid (нам он понадобится позже, когда будем смотреть блокировки) и запустим запрос, который просматривает таблицу и читает все большие объекты (с использованием уровня изоляции по умолчанию - read committed):

select @@spid
select lob from t

В этом запросе используется тривиальный план, состоящий из сканирования кластерного индекса:

|--Clustered Index Scan(OBJECT:([t].[PK__t__2D27B809]))

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

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
PAGE	IS	LOCK	GRANT
KEY	S	LOCK	WAIT
OBJECT	IS	LOCK	GRANT

Мы видим, что сканирование не блокирует ключи и ждёт окончания блокировки одного ключа, которая удерживается первым сеансом.

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

select lob from t order by i

План этого запроса включает сортировку:

|--Sort(ORDER BY:([t].[i] ASC))
....|--Clustered Index Scan(OBJECT:([t].[PK__t__2D27B809]))

Как было показано выше, анализ удерживаемых этим запросом блокировок показывает, что из-за большого объекта и сортировки он удерживает блокировки ключей всех затронутых строк:

resource_type	request_mode	request_type	request_status
DATABASE	S	LOCK	GRANT
KEY	S	LOCK	GRANT
PAGE	IS	LOCK	GRANT
KEY	S	LOCK	WAIT
OBJECT	IS	LOCK	GRANT
OBJECT	IS	LOCK	GRANT

Если такая ситуация кажется неправдоподобной, когда блокировки ключей с большими объектами вызывают дополнительные блокировки, повторите этот эксперимент с другим, почти идентичным запросом, который не возвращает в выборке большой объект:

select i from t order by i

После этого, попробуйте ещё вот такой запрос:

select i from t where lob > 'a'

План этого запроса использует явно оператор Filter для ограничения выборки условием предиката, в котором указан большой объект:

|--Filter(WHERE:([t].[lob]>[@1]))
....|--Clustered Index Scan(OBJECT:([t].[PK__t__2D27B809]))

Фильтр не является блокирующим оператором, и этот запрос включает в выборку большой объект. Тем не менее, если вы его запустите, вы увидите, что SQL Server снова заблокирует все строки, которые затронуты запросом. Как показывают эти примеры, SQL Server слишком консервативен и накладывает блокировки, даже если они технически не нужны.

Комментарий от Пола Уайта от 6 июня 2012 г.

По поводу UPDATE. Не могу точно сказать, когда это изменилось, но общие блокировки (S, IS…) больше не удерживаются (слишком консервативно) для того примера, в котором используется конвейерный фильтр. Чтобы было ясно тем, кто читает этот комментарий, поведение будет таким же, как описано для оператора stop-and-go (блокирующего).

Проверено для следующих версий:

SQL Server 2005, сборка 5296 (SP4 CU3)

SQL Server 2008, сборка 5775 (SP3 CU4)

SQL Server 2008 R2, сборка 3720 (SP2 CTP)

SQL Server 2012, сборка 2316 (RTM CU1)