Каталог решений - Боремся с Dirty read / Read uncommitted / NOLOCK при формировании отчетов в 1С:8.1, 8.2 под MSSQL

Цель

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

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

** В версии 1С 8.3 версионирование включено «из коробки», и хинт NOLOCK вне транзакций уже не испоользуется, т.е. грязного чтения не происходит.

Методика

1. Включаем версионирование для MSSQL. (Обратите внимание на TempDB, т.к. в таком режиме MSSQL незавершенные транзакции будет хранить в ней, поэтому обеспечьте ей достаточно свободного пространства). Выгоняем всех из базы 1С, (возможно даже отключаем службу сервера 1С) открываем SQL Server Management Studio, останавливаем зеркалирование MSSQL, отключаем все соединения к базе, открываем New Query, выполним команду (вместо MyDatabase подставьте имя вашей sql базы):

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON 

— Операция должна завершиться за пару секунд, если этого не произошло, открываем SQL Server Management Studio -> Managment -> Activity Monitor и смотрим кто остался подключен к базе, и всех кроме запроса «ALTER DTATABASE…» — отключаем.

2. В конфигураторе -> Свойства конфигурации -> закладка Совместимость -> Режим управления блокировками, если стоит Автоматический, ставим Автоматический и управляемый, в противном случае не меняем.

3. В конфигураторе -> Правка -> Глобальный поиск (Ctrl+Shift+F) -> Ищем: .Выполнить() -> таким образом находим все вызовы Выполнить() для объекта Запроc. Нас интересуют только те, что исполняются не из модулей Документов, точнее те которые НЕ заключены в неявные транзакции в процедурах ОбработкаПроведения, ОбработкаОтменыПроведения и т.п.

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

Если глФлагЗапросыВТранзакциях Тогда НачатьТранзакцию(РежимУправленияБлокировкойДанных.Управляемый); КонецЕсли;

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

Если глФлагЗапросыВТранзакциях Тогда ОтменитьТранзакцию(); КонецЕсли;

На случай если нам не понравится как после этого работает 1С (внезапно увеличивается количество блокировок), добавляем в глобальный модуль глобальную переменную:

Перем глФлагЗапросыВТранзакциях Экспорт; 

А в процедуру ПередНачаломРаботыСисемы() устанавливаем его: глФлагЗапросыВТранзакциях = Истина;

Наслаждаемся результатом…

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

Преамбула

1. В общем случае при выполнении запросов 1С ставит хинт для выборки данных NOLOCK, что означает команду для MSSQL прочитать данные даже из незавершенных транзакций и при этом не блокировать таблицы, т.е. в момент выполнения запроса в эти таблицы параллельно могут осуществляться операции модификации, удаления и добавления данных. 

Возможно, если мы просто выбираем данные из справочника такое поведение допустимо, поскольку в справочниках данные меняются достаточно редко, по сравнению с учетными данными в регистрах, табличных частях документов. Но если мы, к примеру, захотим получить данные об остатках, и в этот момент кто-то параллельно с нами будет перепроводить какой либо документ, влияющий на эти остатки, то в результате такого поведения мы рискуем получить некорректные данные, особенно если у документа стоит флаг «Удалять движения автоматически».

2. При помощи ключевого слова «Для изменения» в запросе 1С позволяет заблокировать таблицы от изменений на время выполнения запроса. При этом запрос необходимо выполнять в транзакции.

3. Покопавшись в профайлере мы видим, что даже без ключевого слова «Для изменения» при выполнении запроса в транзакции 1С ставит хинт Serialisable и Repeatable Read — что означает исключительную блокировку таблиц на время выполнения транзакции.

4. http://v8.1c.ru/overview/datalockcontrol.htm Покопавшись в мануалах видим, что в управляемом режиме блокировок 1С использует уровень изоляции транзакций Read Committed, что нам собственно и нужно — прочитать данные из завершенных транзакций.

В свою очередь при начале транзакции мы можем указать режим управления блокировками — автоматический либо управляемый. По умолчанию действует Автоматический режим с хинтом Serialisable и Repeatable Read. При этом для работы транзакции в управляемом режиме блокировок, необходимо чтобы конфигурация поддерживала такой режим, т.е. для кофигурации режим управления блокировками должен быть Автоматический и управляемый или Управляемый. Если у вас не стоял ранее Управляемый режим, не ставьте его сейчас, если стоял Автоматический можете без боязни поставить Автоматический и управляемый режим, на работу системы это не повлияет.

5. Итак, мы поставили для конфигурации режим Автоматический и управляемый (или оставили Управляемый), запрос в отчете заключили в транзакцию с управляемым режимом управления блокировками, в итоге получили желаемое — 1С не ставит хинт для этого запроса, т.е. действует режим изоляции транзакций Read Committed. 

6. Тестируем. В модуле проведения документа, например «Приходная накладная», в процедуре «Обработка проведения», перед созданием проводок выводим модальное окно требующее реакции пользователя для продолжения проведения — проще говоря пишем код: Предупреждение(«Продолжить?..»);

Запускаем два сеанса 1С — в одном будем выводить остатки товаров, в другом перепроводить «Приходную накладную».
1) Когда запрос выполняется вне транзакции, то остатки на момент до начала перепровдения ПН отличаются от остатков на момент вывода предупреждения «Продолжить?..» 
2) Когда запрос выполняется в транзакции в управляемом режиме управления блокировками, то при выведенном предупреждении «Продолжить?..» при попытке выполнить запрос на получение остатков получаем 20 секундный таймаут, видимо запрос ожидает завершения открытой транзакции с исключительной блокировкой…

7. Но зачем нам ждать? Нам достаточно получить данные, которые были актуальны до начала перепроведения «Приходной накладной», а чтобы получить такие данные, необходимо перевести MSSQL в версионный режим. Для этого необходимо выгнать всех из базы, и закрыть все соединения с базой в том числе остановить зеркалирование MSSQL, Открыть SQL Server Management Studio, New Query и выполнить команду:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

8. Тестируем повторно — вуаля, все работает как надо, когда запрос выполняется в транзакции в управляемом режиме управления блокировками, то при выведенном предупреждении «Продолжить?..» при попытке выполнить запрос на получение остатков получаем данные, актуальные на момент начала перепроведения «Приходной накладной», что и требовалось получить.