По материалам статьи Craig Freedman: Repeatable Read Isolation Level

В двух предыдущих статьях (1, 2) было продемонстрировано как запросы с уровнем изоляции «read committed» могли порождать неожиданные результаты. Это становилось возможным из-за выполняющихся в одно и то же время изменений затронутых запросом строк. Чтобы недопустить подобных неожиданностей (но не всех), следует использовать для выборки уровень изоляции «repeatable read». В этой статье мы как раз и рассмотрим как одновременные изменения ведут себя с уровнем изоляции «repeatable read» (повторяемое чтение).

В отличие от просмотра с «read committed», просмотр с «repeatable read» удерживает блокировки каждой затронутой строки до окончания транзакции. На всём протяжении транзакции заблокированными могут оказаться даже некоторые строки, которые не соответствуют выборке в результате запроса. Такое блокирование гарантирует, что затронутые запросом строки не будут изменены или удалены в параллельном сеансе, пока текущая транзакция не будет завершена (независимо от того, будет ли она зафиксирована или произойдёт её откат). Эти блокировки не защищают от изменения или удаления те строки, которые еще не были охвачены просмотром, и не препятствуют вставке новых строк межу уже заблокированными строками. На рисунке ниже такое поведение проиллюстрировано графически:

Обратите внимание, что возможность вставлять новые «фантомные» строки между заблокированными строками, которые уже были отсканированы, является принципиальной разницей между уровнями изоляции с повторяемым чтением и сериализуемыми уровнями изоляции. Сериализуемое сканирование получает блокировку диапазона ключа, которая предотвращает вставку любых новых строк в любом месте этого диапазона (а также изменение или удаление любых имеющихся строк в этом диапазоне).Далее в статье будет несколько примеров того, как мы можем получить неожиданные результаты даже при выполнении запросов с уровнем изоляции «repeatable read». Эти примеры аналогичны приведенным в предыдущих двух статьях.

Перемещение строк

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

create table t (a int primary key, b int)
insert t values (1, 1)
insert t values (2, 2)
insert t values (3, 3)

Далее в сеансе 1 блокируем вторую строку:

begin tran
update t set b = 2 where a = 2

После этого во втором сеансе сделаем просмотр таблицы с «repeatable read»:

select * from t with (repeatableread)

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

update t set a = 0 where a = 3
commit tran

Как мы и ожидали, сессия 2 потеряла третью строку и возвращает только две строки:

a    b    c
———– ———– ———–
1    1    1
2    2    2

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

Фантомные строки

Теперь давайте рассмотрим создание фантомных строк, и как они могут привести к неожиданным результатам. Этот пример похож на эксперимент с вложенными циклами из предыдущей статьи. Начните с создания двух таблиц:

create table t1 (a1 int primary key, b1 int)
insert t1 values (1, 9)
insert t1 values (2, 9)
create table t2 (a2 int primary key, b2 int)

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

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

Затем в сеансе 2 сделаем выборку с внешним соединением и с уровнем изоляции «repeatable read»:

set transaction isolation level repeatable read
select * from t1 left outer join t2 on b1 = a2

План запроса для этого соединения использует оператор соединения с вложенными циклами:

^{|–Nested Loops(Left Outer Join, WHERE:([t1].[b1]=[t2].[a2]))
... |–Clustered Index Scan(OBJECT:([t1].[PK__t1]))
... |–Clustered Index Scan(OBJECT:([t2].[PK__t2]))}

Этот план просматривает первую строку из t1, пытается соединить её с t2, обнаруживает, что совпадающих строк нет, и выводит вместо значений NULL. Затем он блокируется сеансом 1, ожидая снятия блокировку со второй строки t1. В это время, в сеансе 1 нужно вставить новую строку в t2 и снять блокировку:

insert t2 values (9, 0)
commit tran

Вот результат вывода для этого внешнего соединения:

a1   b1   a2   b2
———– ———– ———– ———–
1    9    NULL NULL
2    9    9    0

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

Резюме

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

ПОЯСНЕНИЕ 26.08.2008: Приведенные выше примеры работают так, как описано в этой статье, если они выполняются в контексте базы данных tempdb. Однако операторы SELECT в сеансе 2 могут не блокироваться, как тут описано, если примеры выполняются в контексте других базах данных. Это возможно из-за сделанной в новых версиях оптимизации, при которой SQL Server избегает получения блокировок с «read committed», когда он знает, что данные на странице не изменились. Если вы столкнулись с этой проблемой, запустите примеры в контексте базы данных tempdb, либо измените оператор UPDATE в сеансе 1 так, чтобы он изменял данные в изменённой строке. Например, для первого примера попробуйте «update t set b = 12 where a = 2».