От экспертов «1С-Рарус»: Блокировки в 1С при работе с внешними источниками данных и блокировки на схемах в MS SQL

Почему вопрос блокировок в 1С актуален?

Последние годы компания «1С» и разработчики тех СУБД, что обеспечивают работу пользователей 1С сделали по-настоящему много для того, чтобы снизить градус накала в этой части работы с системой. Появление управляемых блокировок в платформе, реализация версионирования в PostgreSQL, задействование уровня изоляции транзакций RCSI в MS SQL, реализация стека прикладного кода 1С так, чтобы сократить время блокирования данных: все это без сомнения во многом привело к тому, что систему 1С стало возможным эксплуатировать для крупных предприятий.

Но давайте зададимся двумя вопросами в связи с вышесказанным:

• Означает ли это, что проблематика блокировок ушла полностью?
• И если эта проблематика ушла совсем либо так мала, что ее почти не заметно, то знания эксперта по производительности в этой части приобретают исключительно академический характер и прикладной ценности более не имеют?

Данная статья опишет два кейса, случившиеся буквально не далее, чем пару месяцев от момента публикации. И она призвана показать, что ответами на заданные вопросы являются: «Нет» и «Нет».

Блокировки все еще случаются, и знания в этой части весьма нужны. Более того, мы делаем парадоксальный вывод: нужны еще более глубокие знания и более тонкое понимание происходящего. Если вдуматься, то это действительно так: все простые случаи решили за нас разработчики платформы 1С, типовых решений 1С, разработчики движков СУБД. Когда же вдруг наступает случай столкновения с блокировкой, то теперь это как правило достаточно сложный кейс и вам придется серьезно поразмыслить что перед вами и как с этим быть.

Сначала мы посмотрим случай, когда блокировка возникала при чтении данных из большой таблицы базы MS SQL через механизм внешнего источника данных с одновременным реплицированием в эту таблицу. Таблица лежит не в базе 1С, а в какой-то другой базе.

Дальше перейдем к случаю, когда блокировка возникает на уровне не таблиц, а схем. Да-да, в СУБД тоже есть метауровень данных и в нем происходит интересное и важное.

Итак, в путь!

Блокировка при чтении через внешний источник данных из большой таблицы-подписчика репликации MS SQL

Случай произошел при автоматизации крупного промышленного предприятия с использованием конфигурации на платформе «1С:Предприятие 8». У клиента используется система управления базами данных Microsoft SQL Server 2017, а также настроена физическая репликация на уровне СУБД. Для оптимизации нагрузки на сервер конфигурация на платформе «1С:Предприятие 8» подключается не к основной базе данных, а к её реплике с помощью механизма внешних источников данных.

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

Платформа «1С:Предприятие 8» действительно предоставляет возможность указания уровня изоляции транзакций, но чтобы убедиться в верности утверждения администратора проведем серию экспериментов с базой данных в Microsoft SQL Server, а также сравним поведение с базой в PostgreSQL для академического интереса.

Внешние источники данных для «1С:Предприятие 8»

Начиная с версии 8.2.14 платформа «1С:Предприятие 8» предоставляет новый объект метаданных «Внешние источники данных». После этого релиза прошло много времени. Однако многие разработчики до сих пор либо не знают этот объект или знакомы понаслышке, не сталкивались с ним в работе. Вместе с тем, это достаточно мощный инструмент для работы из 1С с разнообразными базами данных.

Итак, основная идея данного объекта заключается в возможности взаимодействия с внешними базами данных, в том числе разработанными не на платформе «1С:Предприятие 8», как с информационными объектами 1С.

Взаимодействие с источниками данных осуществляется с помощью драйверов ODBC (Open Database Connectivity). Соответственно поддерживается работа с различными СУБД Microsoft SQL Server, PostgreSQL, Oracle Database и IBM DB2, с таблицами Microsoft Excel, с многомерными источниками данных и многими другими ODBC-источниками. При взаимодействии с СУБД сохраняется поддержка использования языка запросов, но при этом некоторые конструкции могут не поддерживаться соответствующим драйвером ODBC.

К основным задачам использования внешних источников данных относятся:

• Чтение данных из таблиц или запись в них.
• Выполнение хранимых процедур и функций.
• Формирование отчетов с использованием схемы компоновки данных.
• Хранение в информационной базе 1С ссылки на поля внешнего источника.

Репликация баз данных

Есть старая шутка — «существует два типа администраторов: те, кто делает бэкапы и те, кто уже делает бэкапы». Значение смысла копии базы увеличивается в геометрической прогрессии с ростом и значением проекта. И тут стоит отметить, что реплика базы далеко не всегда делается в тех же целях что и бэкапы.

Благодаря такому механизму как репликация баз данных можно снижать нагрузку на базу за счет переносов запросов на копии баз, а также переносить данные в аналитические и архивные системы. Вспомните как мы рассказывали про репликацию из MS SQL в Yandex ClickHouse в статье «Как и зачем интегрировать Yandex ClickHouse с 1С».

Если кратко, то репликация данных — способ масштабирования баз данных, при котором данные с одного сервера базы данных условно-постоянно копируются (реплицируются) на другой или несколько других серверов.

Существует несколько видов репликации:

• блочная на уровне системы хранения данных;
• физическая на уровне СУБД;
• логическая на уровне СУБД.

При использовании блочной репликации, каждая операция записи выполняется на основном и каждом резервном диске. К преимуществам можно отнести простоту настройки и надежность, т. к. в результате потери базы данных, можно быстро переключиться на работу с зеркальным диском, как основным. К недостаткам: работать с зеркальным диском напрямую нельзя и не поддерживается распределение нагрузки между репликами.

При использовании физической репликации все зафиксированные изменения базы данных в журналах (redo log или write-ahead log) повторно выполняются в реплике или реплика полностью замещается моментальным снимком базы (в зависимости от типа публикации).

К преимуществам перед блочной можно отнести:

• Переключение на резервную базу происходит быстрее, т. к. реплика уже работает и достаточно откатить незавершенные транзакции.
• На реплике можно выполнять запросы, что позволяет снять нагрузку с основной базы.
• Если используется не репликация моментальными снимками, то объем передаваемых данных меньше, т. к. передаются только журналы.

Однако прямая запись в реплику невозможна, т. к. изменения приходят из основной базы. Также поддерживается синхронная и асинхронная репликация. При синхронной завершение транзакции (commit) означает, что все данные переданы и в реплику, что при асинхронной — необязательно. Не смотря на явные плюсы, многие администраторы до сих пор не отказываются от блочной репликации по причине недоверия к технологии.

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

Из явных преимуществ использования:

• Поддерживается репликация между разными версиями СУБД или даже СУБД разных производителей.
• Уменьшение объема передаваемых команд до точечных.
• Настройка набора данных на уровне таблиц. При физической — файлов и табличных пространств; при блочной — на уровне томов.
• Построение сложных топологий.

К недостаткам относятся:

• На практике не бывает полностью синхронной.
• Создает большую нагрузку на реплику.
• Все данные обязаны иметь первичные ключи.
• Приложение должно уметь убедиться, что все изменения с основной базы, применены на реплике.

В рамках рассматриваемого кейса мы обратимся к видам репликации, которые предоставляет в качестве штатной возможности MS SQL Server. Microsoft SQL Server поддерживает следующие виды репликации:

• Snapshot,
• Transactional,
• Peer-to-Peer,
• Merge.

При использовании репликации снимком (Snapshot) основная база формирует снимок опубликованных данных и передает их репликам через запланированное время. Такой вариант лучше всего использовать когда данных немного, они меняются не очень часто или нужны данные за определенный интервал.

Так как использование репликации снимком подходит далеко не всегда, то были разработаны дополнительные виды репликаций, которым достаточно лишь первоначального снимка.

Например, для репликации транзакций (Transactional) далее уже будут использоваться только зарегистрированные данные по транзакциям из журналов.

В последствии на основании репликации транзакций была разработана одноранговая репликация (Peer-to-Peer), которая сохраняет копии данных на нескольких экземплярах сервера и позволяет распространять согласованные на уровне транзакций изменения.

Наиболее сложным видом репликации является репликация слиянием (Merge). Для нее аналогично требуется первоначальный снимок. Однако, последующие изменения произведенные на сторонах основной базы и репликаций, отслеживаются с помощью специальных триггеров, которые позволяют передавать только измененные данные со времени последней синхронизации основной базы и реплики.

Основные проблемы транзакций

Что ж, когда мы разобрались с тем, что такое внешние источники данных (ВИД) и что такое репликация с их видами, давайте разберемся с тем какие виды неприятностей возникают при конкуренции двух транзакций за один и тот же ресурс.

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

К основным проблемам относятся:

• потеря обновления,
• грязное чтение,
• неповторяемое чтение,
• фантомное чтение.

Потеря обновления — потеря всех обновлений данных, за исключением обновлений, выполненных последней транзакций. Например, транзакция 1 и транзакция 2 одновременно производят изменение наименования номенклатуры и в результате будет зафиксировано наименование по транзакции 2. Это нормальное разрешение коллизии, ничего страшного в этом нет.

Грязное чтение — чтение данных незавершенных транзакций. Например, транзакция 1 обновляет объект. Транзакция 2 считывает обновленный объект до того, как транзакция 1 зафиксирует обновление. Если транзакция 1 выполняет отмену изменений, то транзакция 2 считает данные, которые никогда не были зафиксированы. Тут могут быть неприятности типа неверного расчета остатков и в ряде случаев это плохо. Например, мы думаем, что деньги есть, а потом их бах и нет.

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

Фантомное чтение — расхождение полученных данных в рамках одной транзакции в случае блокирования точечных ресурсов, а не диапазона целиком. Например, транзакция 1 считывает набор строк по некоторым условиям поиска. Транзакция 2 создает новую строку, которая соответствует условиям поиска для транзакции 1. Если транзакция 1 выполнит повторный запрос, то в результате получит другой набор данных. Аналогично «неповторяемому чтению» проблема не так и страшна, или не критична вовсе, если мы не собираемся читать еще раз данные в рамках транзакции, что чаще всего и бывает.

Какие бывают уровни изоляции транзакций

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

Согласно стандарта SQL-92 существует четыре уровня изоляции:

• Read uncommitted,
• Read committed,
• Repeatable read,
• Serializable.

Стоит отметить, что в стандарте описаны какие особые условия не должны наблюдаться, а не наоборот должны. Потому система ограничений в различных СУБД может изменяться. Например, в PostgreSQL уровень Read Uncommitted действует как Read Committed, а Repeatable Read не допускает фантомное чтение.

Какие же условия не должны наблюдаться на уровнях:

• Read uncommitted — чтение данных незафиксированных транзакций. По сути полное отсутствие изоляции и в результате возможно грязное чтение.
• Read committed — чтение данных только зафиксированных транзакций. При этом уровне решается проблема грязного чтения с помощью механизма блокировок. Однако сохраняется проблема неповторяемого чтения и фантомного чтения.
• Repeatable read — чтение данных на момент начала транзакции. Если в рамках транзакции происходит чтение или запись, то накладывается автоматическая блокировка до конца транзакции. В результате другие транзакции не могут повлиять на данные. Потому при данном уровне обеспечивается решение всех проблем, кроме фантомного чтения.
• Serializable — наивысший уровень изоляции, при котором ограничения накладываются не только на изменяемые и прочитанные данные, но и на весь диапазон полей индекса. Благодаря этому решаются все проблемы, в том числе и фантомного чтения.

Со временем в Microsoft SQL Server был добавлен дополнительный уровень изоляции Snapshot и расширены возможности уровня Read committed дополнительным параметром Snapshot. Не следует путать уровень изоляции Snapshot и режим READ COMMITTED SNAPSHOT (RSCI). Что важно помнить: RSCI изменяет поведение только и исключительно операций чтения с уровнем изоляции READ COMMITTED и никак не влияет на операции записи.

При уровне Snapshot данные считываемые в рамках транзакции, никогда не будут отражать изменения, внесенные другими одновременными транзакциями. В момент начала транзакции (явной или неявной) формируется снимок, а сами данные не блокируются. И снимков таких может быть создано очень много, пока не завершаться все транзакции, которые работают с одними и теми же записями базы. Транзакции, осуществляющие запись данных, не блокируют считывание данных транзакциями со снимков.

Параметр базы данных Is Read Committed Snapshot On говорит, что транзакция будет читать зафиксированные значения в том виде, в каком они существуют на момент начала чтения.

Стоит отметить, что в Microsoft SQL Server ранее снимки версий всегда хранились в базе tempdb. Однако в версии SQL Server 2019 добавлена возможность включения ускоренного восстановления базы данных (ADR) и если она включена, то версии хранятся в постоянном хранилище версий ADR, расположенном в пользовательской базе данных в файловой группе, которую указывает пользователь. Данная возможность позволяет уменьшить нагрузку на tempdb.

Воспроизводим проблему клиента с блокировками

С таким сочетанием «Внешний источник данных» + «Репликация» мы ранее не сталкивались, поэтому перед тем как предложить решение было принято провести честный эксперимент. Одной из причин эксперимента было то, что для ВИД в документации заявлена возможность управления блокировками и уровнем изоляции транзакций.

Для проведения экспериментов подготовили инфраструктуру, состоящую из конфигурации на платформе «1С:Предприятие 8» к которой подключена сторонняя база данных (Microsoft SQL Server) с помощью механизма внешних источников данных. При этом настройки сторонней базы полностью повторяли те, что были у клиента.

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

SELECT

      CASE    transaction_isolation_level

      WHEN 0 THEN 'Unspecified'

      WHEN 1 THEN 'Read Uncommitted'

      WHEN 2 THEN 'Read Committed'

      WHEN 3 THEN 'Repeatable'

      WHEN 4 THEN 'Serializable'

      WHEN 5 THEN 'Snapshot'

      END    AS TRANSACTION_ISOLATION_LEVEL

      FROM

      sys.dm_exec_sessions

      WHERE

    database_id = 190 

Видим, что включен уровень изоляции Read Committed.

Сделали смелое предположение, что отсутствие снимков и является причиной неприятностей. Включили режим «Is Read Committed Snapshot On» и отключили «Allow Snapshot Isolation». Ведь нам главное обеспечить неблокирования операции чтения через ВИД с одновременной репликацией.

Теперь надо проверить утверждение, что конфигурация при чтении через ВИД самостоятельно устанавливает уровень изоляции. Для этого настраиваем Extended Events на события rpc_completed и sql_batch_completed.

Платформа «1С:Предприятие 8» предоставляет возможность управления уровнями изоляции и режимом блокировки данных у внешних источников данных. Однако не все так просто и очевидно, давайте разбираться. Проведем серию экспериментов, при которых в настройках таблицы внешнего источника данных будем изменять данные настройки, а также выполнять операции чтения и записи.

Установим режим блокировки «Автоматический», а уровень изоляции в «Чтение незафиксированных» (Read uncommited). И из конфигурации выполним операцию чтения данных.

ВЫБРАТЬ ПЕРВЫЕ 100

      СписокПродаж.Номенклатура КАК    Номенклатура,

      СписокПродаж.Количество КАК    Количество,

      СписокПродаж.Сумма КАК Сумма

      ИЗ

    ВнешнийИсточникДанных.УОП_ТочкаПродаж.Таблица.СписокПродаж КАК СписокПродаж 

Если заглянуть в отчет Extended Events, то можно увидеть, что текст запроса не модифицировался и команда SET TRANSACTION ISOLATION LEVEL для установки уровня изоляции — отсутствуют.

Проведем аналогичный эксперимент, но выполним операцию записи данных:

// Выполним чтение любого объекта из базы, чтобы скопировать его    реквизиты.

      Запрос = Новый Запрос;

      Запрос.Текст = "ВЫБРАТЬ ПЕРВЫЕ 1

      |       СписокПродаж.*

      |ИЗ

      |       ВнешнийИсточникДанных.УОП_ТочкаПродаж.Таблица.СписокПродаж КАК СписокПродаж";

      РезультатЗапроса = Запрос.Выполнить();

      НачатьТранзакцию();

      Выборка = РезультатЗапроса.Выбрать();

      Пока Выборка.Следующий() Цикл

      // Подготовим новый объект таблицы.    

      ТекОбъект = ВнешниеИсточникиДанных.УОП_ТочкаПродаж.Таблицы.СписокПродаж.СоздатьОбъект();

      ЗаполнитьЗначенияСвойств(ТекОбъект, Выборка);

      ТекОбъект.Записать();

      КонецЦикла;

    ЗафиксироватьТранзакцию(); 

Если повторно заглянем в отчет Extended Events, то можно увидеть явное выполнение команды установки уровня транзакции READ UNCOMMITED.

Повторим данный эксперимент для всех уровней изоляций и режимов блокировки. Результат выполнения экспериментов зафиксируем в таблицу.

Таким образом, можно увидеть, что переключение режима блокировки и управление уровнем изоляции никак не влияет на «прямое» изменение уровня изоляции.

Что же могло повлечь появления блокировки и ожиданий при репликации? (хотя выше мы уже раскрыли тайну и теперь это секрет Секре́т Полишине́ля). Но давайте еще раз и чуть подробнее. Для того, чтобы ответить на данный вопрос приведем инфраструктуру к варианту клиента, т. е. теперь конфигурация будет подключена не на прямую к сторонней базе данных, а к реплике.

Пример создания репликации будет опущен. Однако уточним, что вид публикации базы у клиента стоял Snapshot publication. При использовании данного типа обновление репликации происходит по результатам формирования моментальных снимков таблиц (Snapshot) и массовым перезаполнением реплик по расписанию.

Ранее мы останавливались на уровне изоляции Snapshot, который изменяет поведение традиционных уровней изоляции и активизируют механизм сохранения версий строк данных. За включение данного уровня изоляции отвечает настройка Allow Snapshot Isolation.

Проведем серию экспериментов, при которых будем выполнять следующую действия:

1. Изменять значения настроек Allow Snapshot Isolation и Is Read Committed Snapshot On.
2. В конфигурации 1С выполнять чтение данных из базы репликации.
3. Вручную запускать синхронизацию основной базы и репликации.
4. Запускать Extended Events на события rpc_completed и sql_batch_completed.
5. Выполнять запрос на анализ блокировок.

Действия 2 и 3 будем выполнять одновременно, чтобы проверить возможность влияния операции чтения на синхронизацию баз данных.

Установим настройки Allow Snapshot Isolation и Is Read Committed Snapshot On в значение ложь. В конфигурации 1С выполним будем следующий запрос:

ВЫБРАТЬ ПЕРВЫЕ 10000000

      СписокПродаж.Номенклатура КАК    Номенклатура,

      СписокПродаж.Количество КАК    Количество,

      СписокПродаж.Сумма КАК Сумма,

      СписокПродаж.ДатаДобавления КАК    ДатаДобавления

      ИЗ

    ВнешнийИсточникДанных.УОП_ТочкаПродаж.Таблица.СписокПродаж КАК    СписокПродаж 

Время его выполнения более 15 секунд, что позволяет параллельно запустить синхронизацию.

Для проверки накладываемых блокировок выполним запрос

SELECT

      TableLock.resource_type,

      TableLock.resource_subtype,

      TableLock.resource_description,

      TableLock.request_mode,

      TableLock.request_type,

      TableLock.request_status,

      TableLock.resource_associated_entity_id,

      TableLock.request_session_id,

      TableLock.resource_lock_partition,

      TableRequest.blocking_session_id,

      TableRequest.command,

      TableRequest.status,

      TableRequest.wait_type

      FROM

      sys.dm_tran_locks AS TableLock

      INNER JOIN sys.dm_exec_requests AS TableRequest

      ON TableLock.request_session_id = TableRequest.session_id

      WHERE

    TableLock.resource_database_id =    DB_ID(N'grimut_fastfood_exp_repl') 

В результате получим таблицу, где можно увидеть, что чтение накладывает блокировку S-типа по объектам, что не дает синхронизации баз данных выполнить команду удаления таблицы и появляется ожидание, о котором возможно говорил клиент.

Если посмотреть в отчет Extended Events, то можно явно увидеть, что, как и ранее, команды на установку уровня изоляции нет. Зато была выполнена следующая последовательность команд:

1. Запрос на чтение, вызванный конфигурацией 1С.

2. Запрос на удаление таблицы, вызванный при синхронизации баз данных.

3. Запрос на создание таблицы, вызванный при синхронизации баз данных.

4. Запрос на массовую вставку данных, вызванный при синхронизации баз данных.

В случае если установить настройку Allow Snapshot Isolation в значение истина, то независимо от значения Is Read Committed Snapshot On, результат будет аналогичный первому эксперименту.

Если же установить настройку Allow Snapshot Isolation в ложь и Is Read Committed Snapshot On в истина, то объектные и страничные блокировки пропадут и тем самым уйдет проблема ожидания.

Таким образом, чтение данных действительно может создавать ожидания, если параллельно запускается синхронизация баз данных. Для того, чтобы этого избежать достаточно использовать уровень изоляции транзакций Read committed, настройка Is Read Committed Snapshot On должна быть «Истина», а настройка Allow Snapshot Isolation — «Ложь».

Решение проблемы блокировок в 1С при использовании реплики базы MS SQL в виде внешнего источника данных

Было рассмотрено влияние операций чтения и записи, с помощью механизма внешних источников данных, на появление команд изменения уровня изоляции баз данных. Соответственно только при операции записи формируются команды установки уровня изоляции транзакций, который указывается в свойствах таблицы внешнего источника данных.

Так как клиент использовал только операцию чтения из репликации данных, то были проверены настройки Allow Snapshot Isolation и Is Read Committed Snapshot On самой базы данных, которые и влияют на создание ожиданий и блокировок при синхронизации репликаций. Отключенная настройка Allow Snapshot Isolation и включенная настройка Is Read Committed Snapshot On помогли клиенту за счет отказа от уровня изоляции Snapshot и традиционного поведения Read committed.

Получится ли избежать такую проблему если использовать PostgreSQL? Да, т. к.. стандарт SQL-92 лишь говорит о том, чего не должно быть, а не что должно быть. Потому разработчики PostgreSQL избежали такой проблемы еще на формировании ограничений уровней изоляции. Read committed в PostgreSQL изначально видит только те данные, которые были зафиксированы до начала запроса; он никогда не увидит незафиксированных данных или изменений, внесённых в процессе выполнения запроса параллельными транзакциями.

Соответственно Read committed в PostgreSQL совпадает с Read Committed Microsoft SQL Server уже с включенной настройкой Is Read Committed Snapshot On, а другого варианта в PostgreSQL и нет.

Блокировка на схемах при включенном режиме асинхронного обновления статистики

Массовые ожидания и таймауты на блокировках в 1С:ERP

Случай произошел при автоматизации крупного промышленного предприятия с использованием конфигурации «1С:ERP Управление предприятием 2». Используется система управления базами данных Microsoft SQL Server 2017. В часы пиковой нагрузки в системе работало более 1000 пользователей.

Администратор базы сообщил, что какое-то время система работает стабильно, после чего начинаются массовые ожидания и таймауты на блокировках. Судя по сообщениям в журнале регистрации, как правило, виновниками блокировок являются пользовательские фоновые процессы, такие как формирование отчетов или чтение динамических списков, однако при удалении процессов-виновников практически тут же возникают другие. Далее приведены примеры ошибок, которые возникают в системе.

Таймаут при выполнении запроса при создании документа на основании:

Таймаут при компоновке результата при формировании отчета:

Выясняем причины ожиданий и таймаутов

По текстам сообщений видно, что блокировки возникают на уровне СУБД во время выполнения запросов:

Microsoft SQL Server Native Client 11.0: Lock request time out period exceeded. HRESULT=80040E31, SQLSrvr: SQLSTATE=HYT00, state=37, Severity=10, native=1222, line=1

Причем, на первый взгляд, возникают они в не связанных объектах и в тех местах, где блокировок в принципе быть не должно (формирование отчета). Чтобы в таком случае понять, что происходит с сервером в режиме реального времени можно воспользоваться динамическим представлением sys.dm_tran_locks. Оно покажет нам активные в данный момент транзакционные блокировки, а также идентификаторы заблокированных объектов.

Выполним запрос, который покажет тип блокируемого ресурса, его описание, статус, а также id текущей и заблокированной сессии (получаем соединением с динамическим представлением sys.dm_exec_requests):

SELECT 

      l.resource_type, 

      l.resource_subtype, 

      l.resource_description, 

      l.request_mode, 

      l.request_type, 

      l.request_status, 

      l.resource_associated_entity_id, 

      l.request_session_id, 

      l.resource_lock_partition, 

      r.blocking_session_id,    r.command, r.status, r.wait_type

      FROM 

      sys.dm_tran_locks    AS l

      INNER JOIN sys.dm_exec_requests AS r

      ON l.request_session_id =    r.session_id

      WHERE 

      l.resource_database_id = DB_ID(N'BaseName')
    ; 

Среди прочих результатов в глаза бросается ожидание на блокировке вида Sch-S. Выглядит она следующим образом:

Это так называемые блокировки на схемах, причем ожидает запрос на выборку данных на блокировке вида Sch-S на объект метаданных статистики, а виновником является сессия обновления статистики в этом же объекте с блокировкой вида Sch-M. Выдвигаем гипотезу о том, что именно этот вид блокировок в данный момент приводит к таймаутам.

Когда и почему возникают блокировки на схемах

Важно, что блокировки sch-m и sch-s несовместимы между собой. То есть, если на объект уже наложена одна или несколько блокировок стабильности схемы, а другой процесс хочет наложить блокировку изменения схемы, то возникает ожидание и наоборот.

Для чтения данных при выполнении обычных SELECT запросов в Microsoft SQL Server всегда требуется блокировка стабильности схемы (sch-s) для объекта, из которого мы выбираем данные. В том случае, если для исполняемого запроса нет плана, либо он устарел и его необходимо перестроить — накладывается аналогичная блокировка (sch-s) для объектов статистики, по котором строится этот самый план запроса. Важно, что даже запросы с инструкцией WITH NOLOCK все равно будут накладывать блокировку стабильности схемы.

Объекты статистики Microsoft SQL Server

Microsoft SQL Server создает, хранит и обновляет специальные объекты метаданных для хранения статистики по таблицам и колонкам таблиц базы данных. Статистика используется оптимизатором для построения планов запросов и является важной частью архитектуры Microsoft SQL Server. Объекты статистики можно увидеть на специальной вкладке в Management Studio:

Или же запросом:

SELECT    sp.stats_id, 

      name, 

      filter_definition, 

      last_updated, 

      rows, 

      rows_sampled, 

      steps, 

      unfiltered_rows, 

      modification_counter

      FROM sys.stats AS    stat

      CROSS APPLY    sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS    sp

    WHERE stat.object_id = OBJECT_ID('ИмяОбъекта'); 

Объекты статистики создаются автоматически для каждого индекса в таблице, а также для отдельных столбцов, если они используются в отборах запросов и при этом настройка Auto create statistics установлена в значение True:

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

Microsoft SQL Server обновляет статистику по объектам в зависимости от настройки Auto update statistics. Если параметр включен, то оптимизатор запросов до компиляции плана запроса пытается понять устарела ли статистика и при необходимости автоматически обновляет ее.

До версии Microsoft SQL Server 2016 количество строк, которые должны быть изменены, чтобы произошло обновление статистики считалось по формуле 500 + 0.2 × Количество
СтрокВТаблице. То есть, для таблицы с 1 миллионом строк необходимо было изменить 200500 строк. Начиная с Microsoft SQL Server 2016 пороговое значение считается как минимальное значение из старой формулы и новой √((1000) × Количество
СтрокВТаблице). То есть для той же таблицы с 1 миллионом записей значение будет √(1000 × 1000000) = 31622 строк.

Обновление статистики по умолчанию выполняется синхронно, но есть возможность делать это в фоне при помощи настройки Auto update statistics Asynchronously. Если данный параметр включен, то запуск обновления статистики при выполнении запроса запустится параллельно с компиляцией плана. Запрос, который инициировал обновление будет использовать старую статистику, а все новые запросы уже будут пользоваться обновленной информацией.

Однако у такого подхода есть недостаток. Асинхронное обновление статистики может привести к проблемам при параллельной работе пользователей из-за блокировок на схемах, т. к. такое обновление устанавливает на блокировку изменения схемы (sch-m). Как уже было сказано ранее, этот вид блокировки несовместим с блокировками стабильности схемы (sch-s), которые устанавливаются при выполнении запросов на чтение данных, поэтому возникают ожидания и таймауты.

Пробуем отключить Auto update statistics

Как было сказано ранее, асинхронное обновление статистики может порождать ожидания при блокировках на схемах. Необходимо проверить, включена ли данная настройка в продуктивной базе:

Видно, что настройка включена. Необходимо отключить ее и понаблюдать за поведением системы при помощи журнала ошибок и все того же динамического представления sys.dm_tran_locks. Нормальным поведением системы будет предоставление блокировок стабильности схемы в подавляющем большинстве случаев, а также сокращение количества ошибок в журнале регистрации:

Асинхронное обновление статистики на низком уровне в Microsoft Azure SQL

С недавнего времени, в Microsoft Azure SQL появилась опция для асинхронного обновления статистики с низким приоритетом ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Включив ее, фоновый процесс обновления статистики теперь будет ожидать любые блокировки стабильности схемы (sch-s) и вытесняться ими. Это позволяет избежать ожидания при блокировках на схемах объектов статистики при выполнении запросов на чтение в пиковой нагрузке, когда выполняется большое количество перекомпиляций планов запросов.

Наглядная иллюстрация того, как работает очередь блокировки ресурсов со включенным асинхронным обновлением статистики с нормальным приоритетом:

А вот так будет выглядеть очередь с использованием новой опции асинхронного обновления статистики с низким приоритетом:

Видим, что хоть и фоновый процесс обновления статистики может выполняться дольше, он никак не мешает блокировкам стабильности схемы при выполнении запросов в системе. Как уже было сказано ранее, данная функциональность уже есть в Microsoft Azure SQL и в скором будущем должна быть анонсирована и в Microsoft SQL Server.

Когда включать асинхронное обновление статистики в MS SQL

Было рассмотрено влияние асинхронного обновления статистики на количество ожиданий при блокировках на схемах и методы борьбы с ними. Включение данной опции может уменьшить время выполнения небольших запросов в случае, если в момент их выполнения запускается обновление статистики, т. к. оно может занять больше времени чем компиляция плана и его исполнение.

Однако, при большом количестве одновременно работающих пользователей в системе можно столкнуться с ожиданиями при блокировках на схемах объектов статистики. Поэтому, использование асинхронного обновления рекомендуется включать только если проведены эксперименты по его влиянию на работу пользователей с конкретной базой данных.

Заключение

There are more things in heaven and earth, Horatio,
Than are dreamt of in your philosophy

И в небе и в земле сокрыто больше,
Чем снится вашей мудрости, Горацио

«Гамлет», Уильям Шекспир.
Перевод Михаила Лазинского.

Надеемся, что мы достигли цели статьи: показать, что знания о блокировках, уровнях изоляции, различных механизмов платформы 1С и механизм СУБД нисколько не потеряли в актуальности, а быть может приобрели еще большую ценность, поскольку простые случаи решают разработчики за нас. Сложности наступают там, где о нас не позаботились, там, где мы сталкиваемся с непривычными сочетаниями.

Поэтому завершить статью хочется крылатым латинизмом Scientia potentia est, чаще всего переводимым как «Знание — сила».

Всем удачи и до новых встреч в наших публикациях!