"Убираем блокировки" в 1С. Заставляем MS SQL работать как Oracle.

27.09.11

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

Включение уровня изоляции Read commited shapshot в MS SQL позволяет практически полностью избежать блокировок на уровне MS SQL при использовании управляемого режима

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

Собственно рецепт:
1) Выгоняем всех из базы
2) Открываем SQL Server Management Studio, New Query
3) Пишем:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

где "MyDatabase" естественно нужно заменить на название вашей базы данных

Для тех кому абсолютно не интересно что дальше произойдёт и как это работает - можно дальше не читать. 
Для тех, кому наоборот - очень интересно могу привести ссылки: http://msdn.microsoft.com/ru-ru/library/ms173763.aspx
и http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx
Собственно окончательно меня убедило в правильности данного подхода, когда нашел в официальном руководстве для разработчиков Dynamics Ax (http://crusevinaq.narod2.ru/public/spravochnik-professionala-microsoft-dynamics-ax-4.0-skachat.html
) упомянание о том что данный уровень изоляции DAX по-умолчанию включает для баз, созданных на SQL Server 2005/2008. 1С, к сожалению на момент написания данной статьи ещё этого не умеет. И мне, если честно, продвигать эту идею туда пока лень. Собственно 2 команды написать намного проще.

Что при этом изменится. Всё очень просто - часть SQL Server станет версионником. Причём в данном случае рекоммендуется всё-таки использовать SQL Server 2008, т.к. в SQL 2005 эта возможность только появилась. А сами знаете как бывает когда возможности только появляются. У тех кто понял слово "Версионник" глаза уже загорелись, вдруг вспомнился "Oracle". Хотел найти статью, которая бы объяснила чем отличается версионник от блокировочника, но как-то не сложилось.

Обясню в 2 словах: мы хотим списать с остатков 1 Ручку, которая на складе всего одна, и сосед наш тоже этого жутко хочет:
Вместе жмём на кнопку "Провести". Мы на тысячную долю секунды быстрее.

Уровень изоляции везде READ COMMITED

Как сработает блокировочник:
 
- читаем остатки мы, и блокируем их
- читает остатки сосед - ждёт пока отпустим
- записываем остатки
- читает остатки сосед , ну и уже понятно, получает отказ

Как сработает версионник:

- Читаем остатки
- Читает остатки сосед (!!!!!!!) 
- Записываем остатки
- Сосед записывает остатки, естественно дождавшись пока мы запишем

В чём разница? А в том что во втором случае сосед прочитает версию данных которая была зафиксирована до их измененеия. Концептуальному уровню READ COMMITED это всё удовлетворяет. Были прочитаны зафиксированные данные, просто прочитаны данные, которые были зафиксированы ранее. Таким образом, незафиксированных в транзакции данных мы не прочитаем, что немловажно. НО ведь в данном случае мы спишем 2-ю ручку в "-". Да - спишем, конечно если не наложим управляемую блокировку
речь о которых пойдёт в следующем посте. Потому что уровень изоляции READ COMMITED 1С использует только при наличии управляемых блокировок. При этом поддерживается на уровне СУБД только логическая целостность данных, а о своих "заморочках" программисты должны позаботиться сами, с чем я лично не могу не согласиться.

Теперь, я думаю, должно быть понятно какой простор для творчества открывает версионирование данных в случае с уровнем изоляции READ COMMITED. В этом случае можно вообще обойтись без блокировок на уровне СУБД. У версионирования, конечно, есть и проблемы - в частности ресурсов под него нужно несколько больше чем просто для поддержания инфраструктуры блокировок. Но потому как версионирование в MS SQL производится не для всех уровней изоляции, то существенной разницы в потреблении ресурсов быть не должно. Для более высоких уровней изоляции нельзя с уверенностью утверждать что версионность лучше чем блокировки. Да и в принципе это не цель данной статьи.

Как было отмечено выше, включение версионирования возможно только для уровня изоляции READ COMMITED. А этот уровень изоляции используется при включенных управляемых блокировках. При автоматических блокировках чаще всего уровень изоляции SERIALIZABLE - поэтому включение данной опции для автоматического режима несущественно повысит производительность, поэтому настоятельно рекоммендуется их включить, если этого ещё не сделано. Если не вдаваться в подробности включение управляемых блокировок в любой конфигурации не займёт более 3-4 часов работы квалифицированного программиста. Конечно в случае когда они включаются осмысленно и осознанно, а не простым слодованием методике, описанной на ИТС. В следующей статье я опишу как это проще всего сделать.

См. также

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

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

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

13.03.2024    2999    spyke    27    

42

Быстродействие типовой 1С

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

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

13.03.2024    5124    vasilev2015    19    

37

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

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

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

1 стартмани

15.02.2024    7662    159    ZAOSTG    68    

96

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

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

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

09.01.2024    5993    doom2good    48    

63

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

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

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

20.11.2023    8882    ivanov660    6    

76

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

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

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

15.11.2023    5115    a.doroshkevich    20    

72

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

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

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

11.10.2023    16197    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
99. Алексей777 92 05.02.14 09:44 Сейчас в теме
Хочется спросить у ребят теоретиков, а на практике Вы ускоряли что-либо? Запускаю в работу 5-ть пользователей на проведение каждым из них всех документов за месяц по разным организациям в совершенно типовой 1С, платформа 8.2.16.362, УПП, у меня нет самописных управляемых блокировок - все типовое, Правда, база весит уже 20 гб, это mdf, включен режим Full - почти каждый документ при проведении у каждого пользователя выдает dead lock. И это нормально? Нет. Включаем предложенные автором режимы и все становится хорошо - люди работат могут.
Slikolia; timurhv; ZLENKO; +3 Ответить
101. ZLENKO 398 17.07.14 18:33 Сейчас в теме
(99) Алексей777, "Включаем предложенные автором режимы и все становится хорошо - люди работат могут."

В том то и фишка что в режиме блокировщика на MS SQL управляемые блокировки не решают проблему избыточных блокировок :-) Видел много специалистов с умным видом рассуждающих про управляемые блокировки, но не понимающих как оно все в целом работает. Версионник - наше все ! :-)
102. ZLENKO 398 17.07.14 18:35 Сейчас в теме
(99) Алексей777, "платформа 8.2.16.362, УПП, у меня нет самописных управляемых блокировок - все типовое"

Для достижения максимального эффекта по повышению производительности надо бы внести изменения в конфигурацию.
100. Алексей777 92 05.02.14 12:55 Сейчас в теме
спасибо автору, ставлю +.
103. kvic2705 8 20.07.14 23:47 Сейчас в теме
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 195, Level 15, State 5, Line 2
'ALLOW_SNAPSHOT_ISOLATION' is not a recognized SET option.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '-'.
Msg 195, Level 15, State 5, Line 5
'READ_COMMITTED_SNAPSHOT' is not a recognized SET option.
104. comol 5051 21.07.14 00:07 Сейчас в теме
105. timurhv 27.08.14 16:44 Сейчас в теме
Статья помогла для ЗУП/ЗиК 8, где все расчеты выполняются в транзакции - в это время другие учреждения не могут читать данные по регистрам расчета.
После выставления соответствующих настроек: остальные учреждения нормально работают, а по учреждению, где начата транзакция - ждут ее завершения.
106. vde69 925 10.09.14 09:33 Сейчас в теме
не понимаю зачем это нужно вообще?

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

например я в самописке 95% всех движений формирую в одном пакетном запросе и сразу их записываю, единственное чего у меня вне запроса - это ФИФО списание партий, но и для него все данные я получаю этим-же запросом....

Отгрузка на 1000 позиций при формировании движений накладвает блокировку примерно на 0.1 секунду. Зачем сабж - не понимаю....

Ах да, я забыл про монстров типа УПП, но это проблемма не блокировок а избыточного говнокода...
107. ZLENKO 398 10.09.14 12:58 Сейчас в теме
(106) vde69, "Ах да, я забыл про монстров типа УПП, но это проблемма не блокировок а избыточного говнокода..."

Предлагаете всем перейти на "самописки" ?
108. ZLENKO 398 10.09.14 13:00 Сейчас в теме
(106) vde69, "Отгрузка на 1000 позиций при формировании движений накладвает блокировку примерно на 0.1 секунду."

Сделайте замер времени записи 1000 движений по плану счетов по счету с двумя уровнями аналитики (типа Товары на складах)...
109. comol 5051 19.09.14 21:36 Сейчас в теме
(106) vde69,
например я в самописке 95% всех движений формирую в одном пакетном запросе и сразу их записываю
Да вы открыли глаза всем на правильность кода! Если бы все новые типовые конфигурации 1С не были бы так написаны ни зачто бы не догадался так делать :)))))
110. ZLENKO 398 21.06.16 13:25 Сейчас в теме
В платформе 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

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

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

P.S.: Он включается при реструктуризации БД при отключенном режиме совместимости с 8.2
112. shved 13.11.17 19:33 Сейчас в теме
коллеги подскажите кто победил? ставить READ_COMMITED или купить оракл? на 13,11,17 ничо не имзенилось?
ПС 70 юзеров. начались жалобы на блокировки
114. comol 5051 14.11.17 01:37 Сейчас в теме
(112) (113) в 8.3 READ_COMMITED_SNAPSHOT ставится автоматически... ну может не с первой версии с 8.3.3 или 8.3.5. Ну и управляемые блокировки конечно должны быть включены. Нет, Oracle покупать не надо. Грустно с ним всё.
115. shved 14.11.17 06:43 Сейчас в теме
(114)
в 8.3 READ_COMMITED_SNAPSHOT ставится автоматически...


это должно быть видно в скуле?

платформу последняя, блокировки управляемые, совместимость отключена но в скуле snapshot_isolation_state_desc = ОФФ:


Ядро 8.3.10.2650
snapshot_isolation_state_desc = OFF
is_read_committed_snapshot_on = 1

наверно единичка говорит о том что READ_COMMITED включен. а первый параметр не надо насильно включать? щас он включен только у двух системных баз master и msdb.

если READ_COMMITED включен и база на ссд, остается только искать и переписывать код?
116. alex_sh2008 4 14.11.17 10:05 Сейчас в теме
(115)Блокировки 1С и блокировки SQL не связанные между собой, что бы избавится от блокировок нужно грамотно писать код в 1С, манипуляции на уровне SQL сервера могут привести к непредсказуемым ситуациям, вплоть до потери данных. Насколько я знаю 1С поддерживает на уровне SQL, только READ_COMMITED блокировки.
117. ZLENKO 398 14.11.17 14:29 Сейчас в теме
(116)
Блокировки 1С и блокировки SQL не связанные между собой, что бы избавится от блокировок нужно грамотно писать код в 1С

Ну не совсем точное высказывание... При автоматических и при управляемых в 1С блокировки в SQL тоже по разному работают.
А в остальном согласен, надо сначала код в 1С переписать, может и хватить только is_read_committed_snapshot_on.
118. alex_sh2008 4 14.11.17 20:00 Сейчас в теме
(117)В SQL блокировки всегда одинаково работают, если не указано при начале транзакции иной уровень изоляции от используемого по умолчанию. 1С свои блокировки использует над блокировками SQL, думаю когда происходит фиксация транзакции в 1С, в SQL начинается транзакция в SQL. Как то писал рекурсивный алгоритм зачистки организаций в базе, максимум удалось вывести на 18 потоков удаления, больше уже шел конфликт блокировок, при удалении использовались управляемые блокировки, и блокировал все подряд, не помогло.
119. ZLENKO 398 23.11.17 19:16 Сейчас в теме
(118)
больше уже шел конфликт блокировок, при удалении использовались управляемые блокировки, и блокировал все подряд, не помогло.

Потому что на уровне MS SQL происходила эскалация блокировок, вот поэтому мне нравятся версионники :-)
120. alex_sh2008 4 23.11.17 20:42 Сейчас в теме
(119)Самое интересное что в 1С когда блокировал в разных потоках, 1 и тот же объект, но со смещением во времени, ни каких исключение не возникало, говорила все ОК. И узнавал о проблеме только когда SQL сервер привет присылал. Короче плюнул на эти все эксперименты с блокировками в 1С, получается конфликт ну и фиг с ним.
121. ZLENKO 398 24.11.17 10:59 Сейчас в теме
(120)
И узнавал о проблеме только когда SQL сервер привет присылал. Короче плюнул на эти все эксперименты с блокировками в 1С, получается конфликт ну и фиг с ним.

Зря плюнули... переход на версионный режим решает проблему блокировок.
122. alex_sh2008 4 24.11.17 11:19 Сейчас в теме
(121)Но и накладывает другие проблемы, в 1С я бы не стал использовать этот режим, да и к тому же мне нужны были эти блокировки, правда я ожидал это на уровне 1С, что бы управлять потоками, так нет штатных механизмов синхронизации, приостановки и т.д.
113. babys 90 13.11.17 21:42 Сейчас в теме
Если у все базы на уф, и не нужен ора в перспективе, то комитьте.
123. maxpiter 147 06.01.18 04:52 Сейчас в теме
А на сколько это применимо к 7.7?
124. comol 5051 09.01.18 19:23 Сейчас в теме
(123) Уже ведь 2018 год... 7.7 это не поможет. 7.7 в принципе ничто уже не поможет :)))
125. maxpiter 147 09.01.18 20:54 Сейчас в теме
(124) все кроме одной бд уже на 8.3, но одну, увы, перетащить очень затратно. Связываться с переездом и переобучением 100+ человек то ещё веселье. Люди с 2004 года на этой платформе сидят и все бы ничего, но не хватает скорости в "документах в секунду". 8ка тоже не факт что при тех же разрезах (заявки, резервы, партии, взаиморасчеты, продажи, проводки) даст улучшения. В общем в поиске светлых голов и решений :)
127. skyfencer 12.08.19 15:01 Сейчас в теме
Вот вам ситуация:

Есть расчет ЗП, построенный на регистрах накопления. Установлен режим управляемых блокировок и т.п. При попытке одновременно запустить расчет по 2 и более организациям (в параллельных сеансах, естественно), MS SQL выдает deadlock на таблице регистра накопления. Попытка раскурить этот deadlock привела к определению, что он возникает на "Движения.РегистрНак.Записать()" при пустом наборе записей. И никакие ухищрения не помогли, кроме как включения индексирования по измерению (для начала) "Организация". Для меня и товарищей, неискушёных погромистов, это было тяжелым испытанием - решение этой проблемы. И вот прочитав тут пост и дискуссию под ним, хочу задать вопрос: "Почему никто и нигде практически не рассказывает о таких вещах, как индексируемые реквизиты, измерения и т.п., и не рассказывает как ими пользоваться и для чего они?" Возможно, это будет гораздо полезнее для увеличения параллельности работы, чем все эти кульбиты с "версионированием"?
128. ZLENKO 398 04.09.20 10:33 Сейчас в теме
(127)
Возможно, это будет гораздо полезнее для увеличения параллельности работы, чем все эти кульбиты с "версионированием"?


Вы не поняли основные проблемы, которые здесь обсуждалась - избыточные блокировки на уровне MS SQL Server и длительность наложения блокировки при проведении документа. Только индексированием Вы, вероятно, какие то проблемы решите, но далеко не все. Но конечно правильное индексирование может улучшить ситуацию и с блокировками тоже.
Оставьте свое сообщение