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

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

Сортировка на внешней стороне Nested Loops Join.
Nested Loops Join с «OPTIMIZED».
Nested Loops Join с «WITH PREFETCH».

В следующем ниже примере демонстрируется «Nested Loops Join» для «Bookmark Lookup» и предварительной выборки:

create table t (a int, b int, c char(1000))
create unique clustered index ta on t(a)
set nocount on
declare @i int
set @i = 0
while @i < 1000
  begin
    insert t values (@i, @i, @i)
    set @i = @i + 1
  end
set nocount off
create index tb on t(b)
select c from t where b < 25

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

^{|--Nested Loops(Inner Join, OUTER REFERENCES:([t].[a], [Expr1004]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([t].[tb]), SEEK:([t].[b] < (25)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([t].[ta]), SEEK:([t].[a]=[t].[a]) LOOKUP ORDERED FORWARD)}

Обратите внимание на ключевые слова «WITH UNORDERED PREFETCH», указанных в конце строки плана с оператором Nested Loops Join.Давайте не будем вдаваться в излишние детали и ограничимся только кратким описанием. Когда SQL Server выполняет показанный выше запрос, он удерживает S-блокировки возвращаемых поиском по индексу строк до тех пор, пока этот запрос не завершится. Если хотите увидеть эти блокировки, можно провести эксперимент, аналогичный тому, как это было сделано в предыдущих статьях.Давайте теперь посмотрим, почему SQL Server добавляет эти блокировки. Предположим, что SQL Server при поиске по индексу не удерживал бы S-блокировки до конца исполнения оператора, а тут же их снимал. Необходимость предварительной выборки приводит к задержке между моментом времени, когда поиск по индексу возвращает строки, и моментом, когда соединение получит эти строки в обработку и выполнит соответствующий поиск по кластеризованному индексу. В течение этой задержки никаких возвращаемых поиском (но еще не обработанных соединением) блокировок строк не будет. Вполне допустима ситуация, когда в другом сеансе эти строки будут изменены, что может привести к потере целостности данных, подобной тем, которые были продемонстрированы ранее.Стоит подчеркнуть еще два момента.Во-первых, обновления также могут использовать предварительную выборку (это можно узнать, по наличию в плане запроса ключевых слова: «WITH PREFETCH»). Для таких UPDATE на источнике подлежащих обновлению строк SQL Server будет удерживать блокировки точно так же, как это происходит, когда план включает любой другой блокирующий оператор, например, SORT или SPOOL.Во-вторых, для предотвращения потери целостности данных, при обновлении всегда накладываются блокировки, даже если они выполняются на уровне изоляции «Read Uncommitted», и, если это необходимо, из-за блокирующих операторов в плане запроса. Эти блокировки удерживаются до конца исполнения оператора. Запросы, которые обращаются к большим объектам и запросы с поиском закладок не устанавливают и не удерживают блокировки при выполнении на уровне изоляции «Read Uncommitted».

Read Committed and Bookmark Lookup