При интеграции систем на платформе 1С с внешними базами данных MS SQL Server через механизм ADO (ActiveX Data Objects) программисты часто сталкиваются с классической задачей: считать порцию необработанных данных, выполнить над ними определенные операции в коде 1С, а затем пометить эти строки как успешно обработанные (например, установить флаг Прочитано = 1). На первый взгляд, задача кажется тривиальной, однако при реализации "в лоб" возникает множество подводных камней: от критического падения производительности из-за построчных запросов к СУБД до блокировок таблиц и взаимной перезаписи данных в многопоточной среде.
В этой статье мы подробно разберем четыре практических способа реализации сценария выборки с последующим обновлением (Read-and-Update). Мы проанализируем настройки курсоров ADO, разберем работу с транзакциями и блокировками MS SQL Server, а также рассмотрим наиболее производительный метод пакетного обновления данных.
Типичная ошибка начинающего разработчика заключается в открытии однонаправленной выборки (курсора типа "только вперед") и выполнении отдельного SQL-запроса UPDATE на каждую итерацию цикла. В индустрии баз данных этот антипаттерн называют RBAR (Row-By-Agonizing-Row), что можно перевести как "построчное мучение".
Если ваша выборка содержит тысячи строк, такое решение приведет к тысячам сетевых запросов туда-обратно между 1С и MS SQL Server. Накладные расходы на сетевой стек, парсинг запросов сервером СУБД и транзакционные логи сделают операцию крайне медленной. Выясним, как сделать эту операцию эффективной и безопасной.
По умолчанию метод Connection.Execute() возвращает самый быстрый, но неизменяемый курсор типа adOpenForwardOnly (только вперед, только для чтения). Попытка изменить значение поля в таком наборе записей вызовет ошибку. Чтобы иметь возможность редактировать данные прямо в процессе обхода выборки, нам необходимо правильно настроить объект Recordset перед его открытием.
Для этого мы должны явно задать три ключевых параметра:
3 (что соответствует константе adUseClient). Это указывает ADO кэшировать данные на стороне клиента, что необходимо для работы механизмов обновления.3 (константа adOpenStatic) или 1 (adOpenKeyset). Статический курсор позволяет свободно перемещаться по записям и вносить изменения.3 (константа adLockOptimistic). Это оптимистическая блокировка, при которой СУБД блокирует записи только в момент вызова метода обновления, а не на все время удержания курсора.Рассмотрим пример реализации этого подхода на встроенном языке 1С:
// Инициализируем соединение ADO
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open("Provider=SQLOLEDB;Data Source=SQL_SERVER;Initial Catalog=MyDatabase;User ID=sa;Password=secret;");
// Создаем объект Recordset
RecordSet = Новый COMОбъект("ADODB.Recordset");
RecordSet.CursorLocation = 3; // adUseClient
RecordSet.CursorType = 3; // adOpenStatic
RecordSet.LockType = 3; // adLockOptimistic
// Открываем Recordset напрямую
ТекстЗапроса = "SELECT ID, Данные, Прочитано FROM ОбменДанными WHERE Прочитано = 0";
RecordSet.Open(ТекстЗапроса, Connection);
Пока Не RecordSet.EOF() Цикл
// Выполняем полезную работу в 1С с данными строки
ТекущиеДанные = RecordSet.Fields("Данные").Value;
// ... Логика обработки данных ...
// Изменяем значение поля напрямую в курсоре
RecordSet.Fields("Прочитано").Value = 1;
// Сохраняем изменения на сервере СУБД
RecordSet.Update();
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
Connection.Close();
Этот метод удобен тем, что код выглядит просто и логично: мы читаем запись, обрабатываем её и тут же "на месте" помечаем как прочитанную. Однако помните, что метод RecordSet.Update() по-прежнему отправляет отдельный микро-апдейт на каждую строку СУБД, что не подходит для огромных объемов данных.
Для оптимизации построчной записи мы можем использовать пакетный режим обновления (Batch Update). В этом режиме изменения накапливаются в локальной оперативной памяти компьютера клиента, а затем отправляются на MS SQL Server одним единственным сетевым пакетом.
Чтобы переключить ADO в этот режим, достаточно изменить тип блокировки LockType на значение 4 (константа adLockBatchOptimistic). Вместо метода Update() в цикле мы просто вызываем переход к следующей строке MoveNext(). Фиксация изменений на сервере происходит после цикла вызовом метода UpdateBatch().
Посмотрим на пример кода с использованием пакетного обновления:
RecordSet = Новый COMОбъект("ADODB.Recordset");
RecordSet.CursorLocation = 3; // adUseClient
RecordSet.CursorType = 3; // adOpenStatic
RecordSet.LockType = 4; // adLockBatchOptimistic
ТекстЗапроса = "SELECT ID, Данные, Прочитано FROM ОбменДанными WHERE Прочитано = 0";
RecordSet.Open(ТекстЗапроса, Connection);
Пока Не RecordSet.EOF() Цикл
// Логика обработки...
// Изменяем локальное значение в кэше
RecordSet.Fields("Прочитано").Value = 1;
// MoveNext() сохранит изменения в локальной памяти, не отправляя запрос на SQL Server
RecordSet.MoveNext();
КонецЦикла;
// Отправляем все накопившиеся изменения на СУБД одним пакетом
Если RecordSet.RecordCount > 0 Тогда
RecordSet.UpdateBatch();
КонецЕсли;
RecordSet.Close();
Использование adLockBatchOptimistic существенно повышает производительность при обработке сотен и тысяч строк, так как СУБД выполняет оптимизированную массовую запись, сокращая накладные расходы на сетевое взаимодействие.
Проанализируем крайне важную проблему конкурентного доступа. В реальных условиях автоматизации выгрузка-загрузка данных может выполняться параллельно в нескольких сессиях 1С (например, регламентными заданиями). Если один поток выбрал строки для обработки, а в это же время второй поток выбрал те же самые строки, мы получим "двойную обработку" данных.
Чтобы предотвратить эту ситуацию, мы должны использовать транзакции на уровне ADO и блокировочные подсказки на уровне MS SQL Server (Table Hints).
Разберем две ключевые конструкции:
WITH (UPDLOCK, ROWLOCK) — специальная подсказка (hint) в SELECT-запросе к MS SQL Server. Подсказка UPDLOCK заставляет СУБД накладывать блокировку обновления на выбранные строки в момент чтения. Ни один другой поток не сможет прочитать эти строки с целью изменения, пока наша транзакция не завершится. Подсказка ROWLOCK принудительно указывает СУБД блокировать данные на уровне строк, а не блокировать всю таблицу целиком, что сохраняет высокую параллельность работы системы.Connection.BeginTrans(), Connection.CommitTrans() и Connection.RollbackTrans(). Блокировки UPDLOCK живут только внутри активной транзакции, поэтому оборачивание кода в транзакцию является обязательным условием.Рассмотрим, как правильно реализовать этот защищенный механизм:
Connection.BeginTrans(); // Открываем транзакцию
Попытка
RecordSet = Новый COMОбъект("ADODB.Recordset");
RecordSet.CursorLocation = 3;
RecordSet.CursorType = 3;
RecordSet.LockType = 3;
// Выбираем данные с наложением блокировки на строки
ТекстЗапроса =
"SELECT ID, Данные, Прочитано
|FROM ОбменДанными WITH (UPDLOCK, ROWLOCK)
|WHERE Прочитано = 0";
RecordSet.Open(ТекстЗапроса, Connection);
Пока Не RecordSet.EOF() Цикл
// Обработка данных в 1С...
RecordSet.Fields("Прочитано").Value = 1;
RecordSet.Update();
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
Connection.CommitTrans(); // Все успешно, фиксируем изменения и снимаем блокировки
Исключение
Connection.RollbackTrans(); // В случае ошибки откатываем изменения
ЗаписьЖурналаРегистрации("Обмен данными", УровеньЖурналаРегистрации.Ошибка, , , ОписаниеОшибки());
ВызватьИсключение "Ошибка обработки данных. Транзакция отменена: " + ОписаниеОшибки();
КонецПопытки;
Благодаря этому подходу мы полностью застрахованы от ситуации, когда параллельный процесс считывает те же самые строки до того, как мы успеем проставить флаг Прочитано = 1.
Если бизнес-логика обработки строк в 1С достаточно сложная и требует обязательного построчного анализа, но при этом вам важна максимальная скорость работы базы данных, мы рекомендуем использовать разделение процессов чтения и записи.
Этот паттерн проектирования заключается в следующем:
adOpenForwardOnly через метод Cmd.Execute()).UPDATE MyTable SET Прочитано = 1 WHERE ID IN (ID_1, ID_2, ...) и отправляем его на выполнение СУБД.Посмотрим на программную реализацию этой схемы:
// Быстрый запрос на чтение без накладных расходов на создание тяжелого Recordset
Cmd = Новый COMОбъект("ADODB.Command");
Cmd.ActiveConnection = Connection;
Cmd.CommandText = "SELECT ID, Данные FROM ОбменДанными WHERE Прочитано = 0";
Выборка = Cmd.Execute();
СписокID = Новый Массив;
Пока Не Выборка.EOF() Цикл
// Читаем данные
ТекущийID = Выборка.Fields("ID").Value;
ТекущиеДанные = Выборка.Fields("Данные").Value;
Попытка
// ... Логика обработки строки в 1С ...
// Если обработка прошла успешно, добавляем ID в список
СписокID.Добавить(ТекущийID);
Исключение
// Обработка конкретной строки завершилась ошибкой, пропускаем её
ЗаписьЖурналаРегистрации("Обмен данными", УровеньЖурналаРегистрации.Предупреждение, , ,
"Не удалось обработать строку ID: " + ТекущийID + ". Ошибка: " + ОписаниеОшибки());
КонецПопытки;
Выборка.MoveNext();
КонецЦикла;
Выборка.Close();
// Выполняем групповое обновление только для успешно обработанных строк
Если СписокID.Количество() > 0 Тогда
// Форматируем массив ID в строку через запятую для SQL-запроса IN
СтрокаID = СтрСоединить(СписокID, ",");
// Один быстрый запрос UPDATE вместо тысяч построчных
ТекстОбновления = "UPDATE ОбменДанными SET Прочитано = 1 WHERE ID IN (" + СтрокаID + ")";
Cmd.CommandText = ТекстОбновления;
Cmd.Execute();
КонецЕсли;
Этот метод сочетает в себе невероятную скорость работы (поскольку СУБД обновляет записи одной пакетной операцией индексированного поиска) и отказоустойчивость (если при обработке одной из строк в 1С произойдет сбой, вся остальная пачка строк все равно успешно пометится как прочитанная).
Выбор конкретного технического решения зависит от ваших требований к надежности и скорости разработки:
WITH (UPDLOCK, ROWLOCK) (Способ 3).UPDATE ... WHERE ID IN (...) (Способ 4). Это золотой стандарт проектирования интеграционных процессов в 1С при работе с MS SQL Server.