План запроса с уровнем изоляции «Read Committed»

По материалам статьи Craig Freedman: Query Plans and Read Committed Isolation Level

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

Соединение вложенных циклов

Давайте начнем с рассмотрения вот такого простого запроса:

create table Customers (CustId int primary key, LastName varchar(30))
insert Customers values (11, ‘Doe’)
create table Orders (OrderId int primary key, CustId int foreign key references Customers, Discount float)
insert Orders values (1, 11, 0)
insert Orders values (2, 11, 0)
select * from Orders O join Customers C on O.CustId = C.CustId

 План этого запроса использует соединение Nested Loops Join:

^{|–Nested Loops(Inner Join, OUTER REFERENCES:([O].[CustId]))
….|–Clustered Index Scan(OBJECT:([Orders].[PK__Orders] AS [O]))
….|–Clustered Index Seek(OBJECT:([Customers].[PK__Customers] AS [C]), SEEK:([C].[CustId]= [O].[CustId]))}

Напомним, что соединение вложенных циклов (Nested Loops Join) обрабатывает входные данные внутренней таблицы по одному разу для каждой строки из внешней таблицы, поставляющей данные для соединения.  В этом примере таблица Orders является внешней таблицей и в ней есть две записи, поэтому будет выполнено два поиска в таблице Customers.  Кроме того, обе записи относятся к одному и тому же клиенту.  Что произойдет если мы изменим данные о клиенте между двумя поисками по индексу?  Чтобы это понять, давайте проведём эксперимент.  Во-первых, в первом сеансе заблокируем второй заказ:

begin tran
update Orders set Discount = 0.1 where OrderId = 2

Теперь во втором сеансе выполним соединение:

select * from Orders O join Customers C on O.CustId = C.CustId

Соединение найдёт первый заказ в таблице Orders и соответствующую строку для этого заказа в таблице Customers.  Затем соединение выберет следующую строку, но дальнейшее продвижение будет остановлено ожиданием завершения блокировки, наложенной в первой сессии.  Наконец, в первой сессии внесём изменение в имени клиента и завершим транзакцию, чтобы освободить блокировку и разрешить продолжение выполнения запроса во второй сессии:

update Customers set LastName = ‘Smith’ where CustId = 11
commit tran

Ниже мы видим результаты работы соединения:

OrderId CustId  Discount   CustId   LastName
———–    ———–   ———————-    ———--–   ——————————

1       11     0           11       Doe

2       11     0.1         11       Smith

Обратите внимание, что данные о клиенте для двух заказов отличаются, хотя идентификатор клиента совпадает!

Полное внешнее соединение

Далее рассмотрим следующий запрос с полным внешним соединением (Full Outer Join):

create table t1 (a1 int, b1 int)

insert t1 values (1, 1)
insert t1 values (2, 2)

create table t2 (a2 int, b2 int)

insert t2 values (1, 1)

select * from t1 full outer loop join t2 on t1.a1 = t2.a2

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

^{|–Concatenation
….|–Nested Loops(Left Outer Join, WHERE:([t1].[a1]=[t2].[a2]))
….|    |–Table Scan(OBJECT:([t1]))
….|    |–Table Scan(OBJECT:([t2]))
….|–Compute Scalar(DEFINE:([t1].[a1]=NULL, [t1].[b1]=NULL))
……..|–Nested Loops(Left Anti Semi Join, WHERE:([t1].[a1]=[t2].[a2]))
…………|–Table Scan(OBJECT:([t2]))
…………|–Table Scan(OBJECT:([t1]))}

Хотя исходный запрос ссылается на каждую таблицу только один раз, этот план запроса делает два просмотра каждой таблицы.  Между двумя просмотрами данные могут изменяться.  Посмотрим, что получится.  Начнём с блокировки второй строки t1 в сеансе 1:

begin tran
update t1 set a1 = 2 where a1 = 2

После этого во втором сеансе выполним соединение:

select * from t1 full outer loop join t2 on t1.a1 = t2.a2

План запроса начинается с выборки первой строки t1 и соединения её с t2 (где есть соответствующая строка).  Затем попадаем на блокировку. В этот момент в сеансе 1 удаляем первую строку из t1:

delete t1 where a1 = 1
commit tran

Когда план запроса c выборкой продолжит свою работу, он считает строку из t2 и выполнит анти-полусоединение (anti-semi join) с t1 для поиска строк, которые существуют в t2, но для них нет соответствия в t1.  Эти строки необходимы для достижения необходимого результата внешнего соединения, но не могут быть получены посредством левого внешнего соединения вложенных циклов.  Заметим, что к этому моменту по плану запроса уже состоялось соединение первой строки t1 с соответствующей строкой в t2.  Однако, поскольку мы удалили строку из t1, анти-полусоединение находит строку в t2, но не может сопоставить ее со строкой в t1 и заменит это дополнительными значениями null.  Вот результат:

a1          b1          a2          b2

———– ———– ———– ———–

1           1           1           1

2           2           NULL     NULL

NULL    NULL     1           1

Проанализировав показанный выше результат, видим, что он включает в себя первую строку, как результат соединения, а также строки, дополненные значениями NULL!

ЗАМЕЧАНИЕ от 26.08.2008г.: приведенный выше пример работает так, как описано выше, если он выполняется в tempdb.  Однако, если пример выполняется в других базах данных, инструкция SELECT в сеансе 2 не может наложить блокировку, как было показано, что происходит из-за оптимизации, которая заставляет SQL Server стараться избегать read committed блокировок, если он знает, что данные на странице не изменялись.  При возникновении этой проблемы запустите этот пример в базе данных tempdb или измените инструкцию UPDATE в сеансе 1 так, чтобы она изменяла значение столбца b1.  Например, попробуйте внести такие изменения в T1: “update t1 set b1 = 12 where a1 = 2″.

Пересечение Индексов

В завершение, рассмотрим следующий запрос:

create table t (a int primary key, b int, c int, check (b = c))
create index tb on t(b)
create index tc on t(c)

insert t values (1, 1, 1)
insert t values (2, 2, 2)

select * from t with (index(tb, tc))

Мы заставили в плане запроса выполнить пересечение (intersection) индексов.  План запроса сканирует и соединяет столбцы из обоих некластеризованных индексов, что формирует конечный результат:

^{|–Hash Match(Inner Join, HASH:([t].[a])=([t].[a]))
….|–Index Scan(OBJECT:([t].[tb]))
….|–Index Scan(OBJECT:([t].[tc]))}

Напомним, что хэш-соединение просматривает всю входную выборку (tb), а затем просматривает всё из второго входного потока (tc).  Что же произойдет, если между двумя этими просмотрами содержимое индексов изменится?  Давайте ещё раз уясним.  Вначале в сессии 1 блокировка налагается на вторую строку:

begin tran
update t set b = 4, c = 4 where a = 2

После этого, в сеансе 2 выполним инструкцию select:

select * from t with (index(tb, tc))

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

update t set b = 3, c = 3 where a = 1
commit tran

Инструкция select возобновит работу, завершит просмотр индекса tb, выполнит просмотр индекса tc (где найдёт уже изменённую первую строку), и вернёт соединённую строку, которая частично состоит из строки до изменения, а частично из строки после изменения:

a   b  c 
——– —— ——– 
1   1  3 
2   4  4

Обратите внимание, что этот результат, кажется, даже нарушает ограничение в первичном ключе!

Резюме

Мы продемонстрировали три неожиданных результата запросов при использовании уровня изоляции Read Committed.  Нужно подчеркнуть, что эти результаты не являются ошибочными.  SQL Server гарантирует, что зафиксированные данные всегда согласованы и не допускают нарушения каких-либо ограничений.  Эти результаты являются просто следствием использования таких странностей в результатах.  Преимущество использования уровня изоляции транзакций Read Committed в более высокой степени возможного параллелизма и в меньшей вероятности блокировок, что уменьшает их количество.  Недостатком является худшая согласованность результатов выборки.