Боремся с 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 оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

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

13.03.2024    2380    spyke    25    

38

Анализируем SQL сервер глазами 1С-ника

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

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

1 стартмани

15.02.2024    7353    149    ZAOSTG    66    

95

Удаление строк из таблицы значений различными способами с замером производительности

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

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

09.01.2024    5765    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8586    ivanov660    6    

75

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    4999    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15958    skovpin_sa    14    

98

Как эффективно настроить autovacuum в Postgres для 1С

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

Кто не любит убирать мусор? Думаю, практически все, а вот в Postgres это обязательный ритуал для эффективной работы. Как эффективно настроить уборку за 1С в Postgres, можно прочитать в этой статье и еще раз задуматься о бесплатности Postgres.

05.08.2023    4974    1CUnlimited    5    

51
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. kapustinag 19.10.12 21:05 Сейчас в теме
На какой версии MSSQL проверяли?
А если заключить в транзакцию с управляемым режимом блокировок выполнение запроса, который расположен не в основной конфигурации, а во внешней обработке/отчете - для этого тоже придется переводить конфигурацию в "Автоматический и управляемый режим"?
2. tarasenkov 340 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 215 24.10.12 14:21 Сейчас в теме
Как то очень стремно. Не повредить бы базу.
5. klel 24.10.12 22:47 Сейчас в теме
Спасибо за статью =) очень полезна
6. marat_n 112 25.10.12 12:26 Сейчас в теме
сразу в глаза бросается, что как минимум нужно смотреть, чтобы контроль остатков при проведении был во всех документах написан корректно (с корректными блокировками на изменение). а то начитаем по похожей схеме в каждом одновременно проводимом документе одинаковые остатки.
7. tarasenkov 340 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 340 27.10.12 12:52 Сейчас в теме
(10) kilokilo,
По поводу Postgre задумывался, полагаю тормоза имеются по сравнению с MSSQL из-за:
1) До версии 1с 8.3 в Postgre не используются кластерные индексы.
2) MSSQL даже для полей без индексов ведет некую статистику и по сути доступ к ним получается индексирован, в Postgre такого нет.

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

Дисковую подсистему можно собрать в RAID, tempDB вывести в RAM диск, пересчитать/реорганизовать индексы для снижения тормозов.
12. V0fka 28 13.08.13 23:34 Сейчас в теме
А зачем думать о согласованности данных в отчетах? Есть в отчете, например 1000 строк. Так где гарантии того, что после выполнения запроса и вывода результата, пока пользователь долистает до 500 строки - ничего не изменится в базе данных? Т.е. за промежуток времени между выполнением запроса и обработкой результата пользователем - ситуация может всеравно поменяться.
13. hogik 443 14.08.13 00:46 Сейчас в теме
(12)
Владимир (V0fka).
Допустим (образно), пользователь набил в расходной накладной - продать миллион автомашин разом одному клиенту. ;-) Проводит накладную. Еще до завершения транзакции в базе данных (под управлением MS SQL в "обычном" режиме) появится расход на это количество. Наш отчет отобразит это количество в отчете. Естественно, потом такая расходная накладная не проведется из-за нехватки на складе, т.к. у нас на складе всего два автомобиля. ;-) Транзакция откатится и строка расхода на миллион автомобилей удалится из базы данных. Но в отчете будет строка с продажей миллиона автомобилей...
14. V0fka 28 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 28 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 340 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 19 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 7133 07.02.15 12:01 Сейчас в теме
В 8.3 при включенном READ COMMITED SNAPSHOT не нужно везде открывать транзакции. Этот режим и без включения транзакции будет избавлять от грязного чтения. Грязное чтение даже при желании из 1С нельзя будет выполнить.
26. helgi 23.11.16 23:18 Сейчас в теме

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


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