DaJet Script

Команда CONSUME, нюанс SQL Server

Прежде, чем читать эту статью, следует ознакомиться со статьёй Команда CONSUME, нюанс № 1.

Данная статья освещает использование команды CONSUME без опции WITH STRICT ORDER исключительно в среде Microsoft SQL Server. Дело в том, что у хинта READPAST есть занятная особенность в поведении, которая очень трудна для воспроизведения в условиях промышленной эксплуатации обменов данными. Более того её сложно воспроизвести даже в лабораторных условиях.

Для демонстрации примера будет использована Microsoft SQL Server Management Studio. Работа с базой данных 1С:Предприятие 8 будет выполняться в режиме управляемых блокировок, то есть у базы данных будет включена опция read committed snapshot. Структура регистра взята из примера “Нюанс № 1”.

Все ниже следующие скрипты выполняются по таймеру, взведённому на определённое время - команда WAITFOR TIME. Для воспроизведения следует подобрать соответствующее значение времени согласно текущему времени на своём компьютере. Если у вас ничего не получится, то не расстраивайтесь - это трудно уловимое поведение. Попробуйте повторить сценарий несколько раз.

Шаг 1. Транзакция A: откроем новую панель запросов и выполним следующий код SQL. Этот код добавляет 10 000 сообщений в таблицу-очередь, а затем ждёт установленного времени, чтобы зафиксировать транзакцию. Итогом выполнения скрипта будет “подвисание” панели запросов до указанного времени.

DECLARE @counter numeric(15,0) = 0;

BEGIN TRANSACTION;

WHILE @counter < 10000 BEGIN

   SET @counter = @counter + 1;

   INSERT _InfoRg123 SELECT @counter, '', '';

END;

WAITFOR TIME '12:00:00.001';

COMMIT TRANSACTION;

Шаг 2. Транзакция B: откроем новую панель запросов и выполним следующий код SQL. Этот код эмулирует выполнение команды CONSUME без опции WITH STRICT ORDER. Скрипт начнёт своё выполнение в установленное время.

Ожидаемый итог выполнения скрипта:

Успешная обработка 10 000 записей, добавленных в очередь транзакцией A, в том случае если транзакция A успеет зафиксироваться.
Обработка нуля записей из-за того, что сообщения заблокированы всё той же транзакцией A и она ещё не успела зафиксироваться.

WAITFOR TIME '12:00:00.000';

WITH queue AS 
(SELECT TOP (10000)
        _Fld124 AS НомерСообщения,
        _Fld125 AS ТипСообщения,
        _Fld126 AS ТелоСообщения
   FROM _InfoRg123 WITH (ROWLOCK, READPAST)
  ORDER BY _Fld124 ASC)
DELETE queue
OUTPUT deleted.НомерСообщения,
       deleted.ТипСообщения,
       deleted.ТелоСообщения;

Шаг 3. Транзакция C: откроем новую панель запросов и выполним следующий код SQL. Просто убеждаемся, что все сообщения помещены в очередь. Использование хинта NOLOCK в данном случае обязательно, иначе ничего не увидим, так как транзакция A ещё не зафиксировалась.

SELECT _Fld124 AS НомерСообщения,
       _Fld125 AS ТипСообщения,
       _Fld126 AS ТелоСообщения
  FROM _InfoRg123 WITH (NOLOCK);

Шаг 4. Дожидаемся наступления установленного времени и окончательного выполнения скриптов.

Результат выполнения скрипта шага № 2

Результат обработки очереди

Что мы видим ? Из двух ожидаемых результатов мы получили третий! Обработано всего 3952 записи … да ещё и с конца очереди … Нарушена последовательсность обработки записей, которые фиксировались в одной и той же транзакции !!!

Объяснение

Всё дело в том, что, как мы уже знаем из статьи “Нюанс № 1”, хинт READPAST позволяет пропускать заблокированные записи и обрабатывать следующие за ними по порядку незаблокированные. SQL Server обрабатывает по запросу транзакции B записи, по сути своей сканируя кластерный индекс. Таким образом, на начало сканирования, транзакция A всё ещё не зафиксировала все свои изменения, но уже начала это делать. Пока транзакция A фиксируется, транзакция B пытается обработать те же самые записи и успевает пропустить первые 6048, и именно в этот момент транзакция A завершается полностью. Теперь транзакции B становятся доступны следующие 3952 записи в хвосте очереди… Занавес.

Что делать ?

Первое и самое главное: поменьше регистрировать сообщений в таблице-очереди в рамках одной транзакции. Данный “нюанс” не удалось воспроизвести на 1000 записей - слишком всё быстро происходит. Вероятно за наносекунды. Не поймать.

Практичное второе: на практике это очень-очень-очень редко имеет значение и последствия.

Тем не менее (в порядке наиболее рационального и практичного):

Рассмотреть использование опции WITH STRICT ORDER.
Перейти с Microsoft SQL Server на PostgreSQL.
Использовать SQL Server CDC (change data capture).
Использовать подсистему SQL Server Service Broker.

Наверх