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

21.12.12

База данных - HighLoad оптимизация

Заставляем 1С в запросах читать данные только из завершенных транзакций (Read Committed), не блокируя при этом работу остальных (Read Committed Snapshot ON).
*Для конфигураций на поддержке может не подойти, т.к. придется вносить изменения в модули.

Цель

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

* В общем случае 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. Тестируем повторно - вуаля, все работает как надо, когда запрос выполняется в транзакции в управляемом режиме управления блокировками, то при выведенном предупреждении "Продолжить?.." при попытке выполнить запрос на получение остатков получаем данные, актуальные на момент начала перепроведения "Приходной накладной", что и требовалось получить.

См. также

HighLoad оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

Обсудим поиск и разбор причин длительных серверных вызовов CALL, SCALL.

24.06.2024    5295    ivanov660    12    

56

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024    9455    Evg-Lylyk    61    

44

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    5175    spyke    28    

49

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    7698    vasilev2015    20    

42

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

2 стартмани

15.02.2024    12581    249    ZAOSTG    82    

115

HighLoad оптимизация Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    5776    glassman    18    

40

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    14520    doom2good    49    

71
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. kapustinag 19.10.12 21:05 Сейчас в теме
На какой версии MSSQL проверяли?
А если заключить в транзакцию с управляемым режимом блокировок выполнение запроса, который расположен не в основной конфигурации, а во внешней обработке/отчете - для этого тоже придется переводить конфигурацию в "Автоматический и управляемый режим"?
2. tarasenkov 341 19.10.12 22:33 Сейчас в теме
(1) kapustinag, Версия MSSQL с которой действует данный метод SQL Server 2005. Должно также работать в SQL Server 2008 и SQL Server 2008R2. Проверял на 2005 SP3.

>> Придется переводить конфигурацию в "Автоматический и управляемый режим"?
Да
3. khaoos 239 22.10.12 07:38 Сейчас в теме
Вот еще статья по использованию Read Commited Snapshot в 1С: http://infostart.ru/public/91879. Не лишним будет упомянуть, что в платформе 8.3 этот режим уже включен по умолчанию, начиная с MSSQL 2005
demon2004; tarasenkov; +2 Ответить
24. ZLENKO 398 21.06.16 13:13 Сейчас в теме
(3) khaoos, "в платформе 8.3 этот режим уже включен по умолчанию, начиная с MSSQL 2005"

Установил 8.3.8.1747, СУБД MSSQL 2008, создал базу средствами сервера приложений.

Проверяю: select name,snapshot_isolation_state_desc,is_read_committed_snapshot_on from sys.databases

name snapshot_isolation_state_desc is_read_committed_snapshot_on
workdb OFF 0

Т.е. не включен... Что я сделал не так?
25. ZLENKO 398 21.06.16 17:22 Сейчас в теме
(24) ZLENKO, "Т.е. не включен... Что я сделал не так?"

Нашел ответ: "Единственное изменение по отношению к версии 8.2 - это переход на уровень изоляции READ_COMMITED_SNAPSHOT в режиме управляемых блокировок"

P.S.: Он включается при реструктуризации БД при отключенном режиме совместимости с 8.2
4. bulpi 217 24.10.12 14:21 Сейчас в теме
Как то очень стремно. Не повредить бы базу.
5. klel 24.10.12 22:47 Сейчас в теме
Спасибо за статью =) очень полезна
6. marat_n 112 25.10.12 12:26 Сейчас в теме
сразу в глаза бросается, что как минимум нужно смотреть, чтобы контроль остатков при проведении был во всех документах написан корректно (с корректными блокировками на изменение). а то начитаем по похожей схеме в каждом одновременно проводимом документе одинаковые остатки.
7. tarasenkov 341 25.10.12 12:56 Сейчас в теме
(6) marat_n,
В данной схеме не меняются правила проведения - если у документов/регистров стоял автоматический режим управления блокировками - он таким и остается, мы его не меняем. Это значит, что при проведении документов блокировки ставятся в исключительном режиме как и раньше.

Если же у вас имеются документы/регистры в управляемом режиме блокировок, то для них уже должны быть прописаны в коде "Исключительные" блокировки или прописано ключевое слово "Для изменения". Т.е. они также как и до перевода SQL в версионный режим будут ставить исключительные блокировки и логика их поведения не изменится.
hogik; Новенький_2209; +2 Ответить
8. Новенький_2209 26.10.12 15:51 Сейчас в теме
9. hogik 443 26.10.12 17:58 Сейчас в теме
(7)
Маленькое уточнение:
"В режиме управляемых блокировок, за счет использования другого уровня изоляции транзакций СУБД, конструкция ДЛЯ ИЗМЕНЕНИЯ языка запросов не работает."(с)
http://1cexpo.ru/informacziya/27-blokirovki-dannyx-v-1spredpriyatii-8.html
tarasenkov; +1 Ответить
10. kilokilo 18 27.10.12 02:11 Сейчас в теме
Это все прекрасно.. но вот включать "версионный" режим базы - лучше на надо для баз начиная с 15 - 20 Гб. Нагрузка на диск растет в 2 и более раз.. В итоге можно словить очереди к диску и тормоза в работе.

В общем, хотя бы замеряйте трафик диска до и после включения "версионного режима".. а так - а Вы не задумывались, почему postgreSQL частенько в разы медленнее MSSQL работает?
11. tarasenkov 341 27.10.12 12:52 Сейчас в теме
(10) kilokilo,
По поводу Postgre задумывался, полагаю тормоза имеются по сравнению с MSSQL из-за:
1) До версии 1с 8.3 в Postgre не используются кластерные индексы.
2) MSSQL даже для полей без индексов ведет некую статистику и по сути доступ к ним получается индексирован, в Postgre такого нет.

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

Дисковую подсистему можно собрать в RAID, tempDB вывести в RAM диск, пересчитать/реорганизовать индексы для снижения тормозов.
12. V0fka 29 13.08.13 23:34 Сейчас в теме
А зачем думать о согласованности данных в отчетах? Есть в отчете, например 1000 строк. Так где гарантии того, что после выполнения запроса и вывода результата, пока пользователь долистает до 500 строки - ничего не изменится в базе данных? Т.е. за промежуток времени между выполнением запроса и обработкой результата пользователем - ситуация может всеравно поменяться.
13. hogik 443 14.08.13 00:46 Сейчас в теме
(12)
Владимир (V0fka).
Допустим (образно), пользователь набил в расходной накладной - продать миллион автомашин разом одному клиенту. ;-) Проводит накладную. Еще до завершения транзакции в базе данных (под управлением MS SQL в "обычном" режиме) появится расход на это количество. Наш отчет отобразит это количество в отчете. Естественно, потом такая расходная накладная не проведется из-за нехватки на складе, т.к. у нас на складе всего два автомобиля. ;-) Транзакция откатится и строка расхода на миллион автомобилей удалится из базы данных. Но в отчете будет строка с продажей миллиона автомобилей...
14. V0fka 29 15.08.13 10:11 Сейчас в теме
(13) Владимир, хорошо, допустим мы считаем, что это действительно проблема и мы знаем, как её решить. Но как решить такое: продали 2 автомобиля. Формируем отчет, который показывает, что продали 2 автомобиля. И тут, после выполнения запроса в отчете, на следующую секунду мы либо продаем ещё 1 автомобиль, либо отменяем продажу какого-то. В итоге в отчете мы тоже имеем неактуальную цифру. Как же быть? :)
15. hogik 443 15.08.13 17:14 Сейчас в теме
(14)
Владимир (V0fka).
В отчете мы всегда имеем неактуальные данные на момент его изучения глазами. ;-)
Но, мы не должны получать в отчете состояние информации, которого никогда не было в реальной жизни (мой пример из (13) сообщения). Для этого и используется средства описанные в данной статье.
Vladimir Litvinenko; +1 Ответить
17. V0fka 29 16.08.13 17:48 Сейчас в теме
(15) hogik, Иван Иванов случайно провел документ, который проводить не надо было. На следующую секунду Петя Петров сформировал отчет, который учел данные Иванова. На следующую секунду Иванов понял, что он промахнулся мышкой, после чего отменил проведение старого документа и провел совсем другой. Разве в таком случае нельзя сказать, что в отчет попали данные, которых никогда в реальной жизни не было (не на уровне данных в базе, а на уровне процесса)?

(16) а если этот заказ поставщику провели на 1 секунду позже формирования отчета? Получаем данные в отчете опять таки не те, что так же приведет к неправильному заказу.

Я просто считаю, что данная проблема высосана из пальца. Нет, я не говорю, что везде, я говорю, что это высосано из пальца в контексте отчетов. Т.к. никто не принимает решения сейчас на сейчас за доли секунд. И, например, в случае заказа товара, лицо, которое принимает решение о закупке сначала проверит количество на складе, потом проверит существующие заказы (вдруг уже заказали), потом посмотрит у какого поставщика лучше заказать и т.п. И на это все уйдет какое-то время, а не доли секунды и вероятность того, что за это время что-то изменится - на порядки больше, чем в случае с "несогласованными данными". И подобные проблемы, я считаю, может решить только запрет неоперативного проведения документов. И то, даже это в полной мере не решит эту проблему (сегодня сделали заказ, завтра его закрыли, т.к. сроки поставки не устроили). Поэтому, на мой взгляд, овчинка выделенки не стоит, в данном контексте.

ПС. Все вышесказанное ИМХО и может не совпадать с чьим-либо мнением. И я с удовольствием приму участие в дальнейшем обсуждении (если оно будет) =) .
18. hogik 443 16.08.13 18:13 Сейчас в теме
(17)
"Разве в таком случае нельзя сказать, что в отчет попали данные, которых никогда в реальной жизни не было (не на уровне данных в базе, а на уровне процесса)?"(с)
Владимир (V0fka).
Для 1С-продукта "реальная жизни"="учет бумажных документов". ;-)
И если "Иван Иванов"(с) ошибся в одной строке документа, а потом его исправил путем перепроведения, то это не должно отражаться на состоянии ВСЕХ товарах данного документа в момент проведения. Да и по проблемному товару не должно происходить изменение: "Ошибка -> 0 -> Верно". В реальности это так: "Ошибка -> Верно". И это в любой реальности, даже "на уровне процесса"(с) учета бумажных документов. :-) :-) :-)
19. hogik 443 16.08.13 18:39 Сейчас в теме
(17)
"лицо, которое принимает решение о закупке сначала проверит количество на складе, потом проверит существующие заказы"(с)
Владимир (V0fka).
Если система не обеспечивает достоверного отражения реальной жизни (хозяйственных операций, а не бумажных документов), то так и происходит.
Каменный век АвтоМехАнизации на перфокартах... :-)
16. tarasenkov 341 16.08.13 14:23 Сейчас в теме
(14) V0fka, Пример из опыта - делается заказ поставщику на основании отчета, который показывает остатки по регистру Товары. В момент формирования отчета перепровели некий документ прихода (по этому же поставщику) содержащий значительное количество товара. В отчете это количество не отразилось (считались несогласованные данные именно в тот момент когда движения документа уже были удалены, а новые движения еще не были записаны). Это могло привести/привело/ к заказу значительного количества лишнего/возможно скоропортящегося/ товара.
20. ZLENKO 398 30.12.14 18:50 Сейчас в теме
"** В версии 1С 8.3 версионирование включено "из коробки", "

Не факт что оно включается при конвертации существующей базы на 8.3
Я так думаю что только при создании новой базы средствами 1С из 8.3
21. maddy 20 31.12.14 14:03 Сейчас в теме
(20) Недавно столкнулись с этой проблемой.

8.3 в режиме совместимости в 8.2 ВЫКЛЮЧАЕТ этот режим при реструктуризации даже если в конфигурации включен управляемый режим блокировок. И включает если режим совместимости выключен.

8.2 на этот параметр не влияла.

22. ZLENKO 398 12.01.15 11:23 Сейчас в теме
(21) maddy, "8.3 в режиме совместимости в 8.2 ВЫКЛЮЧАЕТ этот режим при реструктуризации даже если в конфигурации включен управляемый режим блокировок. И включает если режим совместимости выключен."

Спасибо за интересную информацию.
23. tormozit 7238 07.02.15 12:01 Сейчас в теме
В 8.3 при включенном READ COMMITED SNAPSHOT не нужно везде открывать транзакции. Этот режим и без включения транзакции будет избавлять от грязного чтения. Грязное чтение даже при желании из 1С нельзя будет выполнить.
26. helgi 23.11.16 23:18 Сейчас в теме

(23)
Этот режим и без включения транзакции будет избавлять от грязного чтения. Грязное чтение даже при желании из 1С нельзя будет выполнить.


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