И вот однажды RCSI сделал результаты запросов более точными

Автор: Brent Ozar, And Then There Was The Time RCSI Actually Made Query Results More Accurate

Обычно, когда я рассказываю людям об оптимистичных уровнях изоляции SQL Server — Read Committed Snapshot Isolation (RCSI) и Snapshot Isolation (SI) — мне приходится произносить небольшую речь о том, что им нужно тестировать свои запросы, потому что результаты могут измениться.

Однако недавно я работал с клиентом, который получал неверные результаты запросов при использовании пессимистичного уровня изоляции по умолчанию — и мы переключились на RCSI, чтобы это исправить! Я не буду объяснять здесь RCSI или SI — используйте ссылку выше для ознакомления с основами — вместо этого я сосредоточусь на демонстрационном скрипте, который я написал, чтобы показать проблему, с которой они столкнулись, и то, как RCSI её решил.

Допустим, нам нужно отслеживать позиции гонщиков в гонке, чтобы знать, кто на первом месте, кто на втором и так далее. Мы создали таблицу лидеров (leaderboard) с одной строкой на каждого гонщика, показывающей его позицию. Я буду использовать:

DROP DATABASE IF EXISTS [IsolationLevelDemo];
GO
CREATE DATABASE [IsolationLevelDemo]
GO
ALTER DATABASE [IsolationLevelDemo] SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT
GO
USE [IsolationLevelDemo];
GO
 
DROP TABLE IF EXISTS dbo.F1Standings;
GO
CREATE TABLE dbo.F1Standings
(
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    DriverName varchar(50) NOT NULL,
    Position int NOT NULL
);
 
INSERT dbo.F1Standings (DriverName, Position)
VALUES
('Kimi Antonelli', 1),
('George Russell', 2),
('Charles Leclerc', 3),
('Lewis Hamilton', 4);
GO

Чтобы проверить положение гонщиков в нашей таблице лидеров, мы выполняем запрос:

SELECT DriverName, Position
FROM [IsolationLevelDemo].dbo.F1Standings
ORDER BY Position;

Результаты имеют смысл: Кими Антонелли — 1, Джордж Рассел — 2, Шарль Леклер — 3, Льюис Хэмилтон — 4.

Когда один гонщик обгоняет другого, нам нужно внести два изменения: переместить более быстрого гонщика НА ОДНУ позицию вверх, а более медленного — НА ОДНУ позицию вниз. В нашей гонке нет понятия ничьей. Таким образом, наша транзакция обновления выглядит так:

BEGIN TRAN;
 
UPDATE dbo.F1Standings
SET Position = 3
WHERE DriverName = 'Lewis Hamilton';
 
UPDATE dbo.F1Standings
SET Position = 4
WHERE DriverName = 'Charles Leclerc';
 
COMMIT;

(Да, вы могли бы сделать это с помощью +1-минус-1 и с именами гонщиков через параметры, но я упрощаю.)

Наши пользователи сообщают, что иногда во время гонки они видят то, чего не должны: ничью (два гонщика на третьем месте). 

Бизнес-пользователи говорят: «Подождите секунду, мы делаем всё в транзакции. Всё должно переключаться вместе или откатываться вместе, верно? Мы не используем NOLOCK. Как мы можем видеть неточные результаты?!?»

Как воспроизвести проблему:

1. Запустите транзакцию и выполните первый оператор обновления, переместив Льюиса на 3-е место.
2. В ДРУГОМ окне/сеансе запустите выборку из таблицы лидеров. Она, по-видимому, будет заблокирована.
3. Вернитесь в окно/сеанс транзакции и выполните второй оператор обновления и фиксацию, переместив Шарля на 4-е место.
4. Окно с выборкой лидеров покажет и Шарля, и Льюиса на третьем месте.

Вот почему SQL Server по умолчанию показывает «неправильные» результаты.

Когда наша таблица только заполнена, её кластерный индекс выглядит так, отсортированный по Id: 1: Кими (поз.1), 2: Джордж (2), 3: Шарль (3), 4: Льюис (4).

После выполнения первого запроса обновления таблица меняется: строка Id 4 (Льюис) теперь имеет позицию 3. 

Вот в чём сложность: строка Id 4 теперь заблокирована, потому что мы обновили Льюиса до 3-го места. Его строка не перемещается в таблице — его строка всё ещё последняя, — но прямо сейчас она заблокирована.

Затем, если выборка выполняется в нашем пессимистичном уровне изоляции Read Committed по умолчанию:

SELECT DriverName, Position
FROM [IsolationLevelDemo].dbo.F1Standings
ORDER BY Position;

SQL Server начинает чтение с начала таблицы. Он читает строки 1, 2 и 3, потому что эти строки ещё не заблокированы. Это означает, что его чтение ВКЛЮЧАЕТ в себя в данный момент НЕЗАБЛОКИРОВАННОГО Шарля, и выборка видит его на третьем месте. Однако запрос теперь останавливается на строке 4, не в состоянии прочитать её (заблокирован), потому что мы обновили строку 4.

Второй запрос в нашей транзакции обновления теперь нуждается в блокировке строки 3 (Шарль) и может её получить. Выборка не удерживает блокировку на этой строке — она уже закончила её чтение, и выборка уже увидела Шарля на 3-м месте. Обновление блокирует Id 3 (Шарль), устанавливает его позицию на 4-е, фиксирует транзакцию и снимает блокировки со всех строк — позволяя нашей выборке наконец прочитать строку 4 (Льюис), но теперь он на 3-м месте!

Чтобы исправить это, мы включили RCSI.

Read Committed Snapshot Isolation включает хранилище версий (version store), которое позволяет запросам чтения видеть предыдущую версию заблокированных строк до того, как изменения были внесены. Это меняет то, что происходит при пошаговом выполнении процесса:

1. Запустите транзакцию и выполните первый оператор обновления, установив Льюису 3-е место.
2. В ДРУГОМ окне/сеансе запустите выборку лидеров. На этот раз она не будет заблокирована и сможет прочитать предыдущую версию строки Льюиса, показывая его на 4-м месте. Запрос лидеров завершается, показывая результат, который технически истинен, потому что обновление ещё не было зафиксировано.

Вы можете возразить: «Брент, это не правильная таблица лидеров, потому что Льюис только что обогнал Шарля!» Я слышу вас, но в такой быстро меняющейся ситуации я предпочёл бы показать набор результатов лидеров, который был технически истинен в какой-то момент времени, а не показывать невозможный набор результатов, который никогда не был истинен ни в один момент времени.

Если бы вы действительно хотели сделать это правильно, вы бы заблокировали обе строки в одном операторе обновления:

UPDATE dbo.F1Standings
SET Position = CASE 
    WHEN DriverName = 'Lewis Hamilton' THEN 3
    WHEN DriverName = 'Charles Leclerc' THEN 4
    END
WHERE DriverName IN ('Charles LeClerc', 'Lewis Hamilton');

Таким образом, вы блокируете обе строки в одном операторе, не требуется отдельная транзакция, и запросы чтения будут видеть правильные результаты независимо от того, какой уровень изоляции мы используем (если только вы не используете NOLOCK, но это для другой статьи).