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

SQL Server 2000 поддерживал четыре уровня изоляции транзакций: «read uncommitted» (nolock), «read committed», «repeatable read» и «serializable». В SQL Server 2005 были добавлены два новых уровня изоляции: «read committed snapshot» и «snapshot». Указание уровня изоляции определяет, какие блокировки будут использованы SQL Server при доступе к данным. Следовательно, выбор уровня изоляции фактически определяют степень параллелизма и согласованности, которые станут возможны для операций с данными и транзакций. Все указанные уровни изоляции описаны в Books Online.

В этой статье я рассмотрю используемый по умолчанию уровень изоляции транзакций: read committed. Когда SQL Server выполняет блок операторов с уровнем изоляции read committed, он последовательно накладывает совместную блокировку на записи, которые затрагивает запрос. Продолжительность действия этих блокировок довольно велика, за это время осуществляется чтение и последующие операции с каждой записью выборки. Как правило, сервер снимает блокировку с записи перед тем, как перейти к следующей. Таким образом, если вы выполняете простой оператор «SELECT» с «read committed» и наблюдаете за блокировками (например, с помощью sys.dm_tran_locks), вы, как правило, видите блокировку одной записи за каждую итерацию выборки. Цель этих блокировок является гарантия того, что данные не изменятся во время их считывания и возвращения оператором выборки. Нужда в подобных блокировках возникает потому, что изменения данных всегда приобретают эксклюзивную блокировку, которая блокирует любые операции чтения, пытающиеся наложить совместную блокировку.

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

Проведём эксперимент, который покажет этот эффект в действии. Для этого эксперимента нам понадобятся два сеанса сервера. Сначала создаём простую таблицу с тремя строками:

use tempdb
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 заблокируем вторую строку:

use tempdb
begin tran
update t set b = 12 where a = 2

Теперь в сеансе 2 выполним простое сканирование таблицы:

select * from t

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

use tempdb
update t set a = 4 where a = 1
update t set a = 0 where a = 3
select * from t
commit tran

Вот новое содержимое таблицы, следующее за этими изменениями:

a            b
----------- -----------
0            3
2            2
4            1

Наконец, вот результат сканирования в сеансе 2:

a           b
----------- -----------
1           1
2           2
4           1

Обратите внимание, что в этой выборке первая строка такая, какой она была до изменений, а третья строка со значением после изменений. Фактически, эти два значения относятся к одной и той же строке до и после изменения. Более того, исходные значения третьей строки, которые были (3, 3), вообще не попали в выборку (тут мы могли бы утверждать, что изменение первичного ключа привело к удалению этой строки и созданию новой, но мы можем получить аналогичный эффект и для некластеризованного индекса).

Наконец, попробуем повторить этот эксперимент, но добавив в таблицу уникальный столбец:

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

Вы получите тот же результат, но в уникальном столбце вы увидите «дубликаты».
Если приведенные выше результаты неприемлемы, вы можете включить для своей базы данных read committed snapshot или вы можете работать на более высоком уровне изоляции (хотя и с меньшим параллелизмом).