Записки оптимизатора 1С (часть 6). Логические блокировки MS SQL Server в 1С: Предприятие

Поговорим о блокировках в 1С:Предприятие. Идея написать эту статью появилась «по просьбам слушателей». Постараюсь максимально простым языком, без зауми рассказать о природе блокировок и что с ними делать. В один пост весь материал помещать не буду – громоздко, поэтому сегодня речь пойдет о логических блокировках сервера СУБД.

С точки зрения конечного пользователя проблема избыточных блокировок выглядит почти одинаково – замедление при выполнении операций и/или ошибка. Но природа блокировок бывает разной и решения тоже разные.

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

Среди причин конфликтов блокировок, пожалуй, стоит выделить две укрупненные:

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

Логические блокировки классифицируются по типам, уровням изоляции и объектам, на которые накладываются блокировки.

1. Табличные блокировки (TAB)

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

Появление табличных блокировок

Табличные блокировки обычно возникают:

1) При массовом изменении данных в таблице.

На скриншоте выше как раз видно, что запрос (SPID 212) выполнял INSERT в таблицу, а сессии 421 и 437 ждали окончания вставки. При этом абсолютно не обязательно, что сессии работают с одним и тем же массивом данных – это свойство как раз табличных блокировок. В жизни выглядит примерно так – один пользователь закрывает месяц, а другие не могут работать. Или пользователь проводит документы по одной организации, а у другого пользователя блокировки на документе по второй организации (разделителю).

2) При регламентном обслуживании базы данных (пересчет индексов).

На дереве блокировок (см. рисунок ниже) сессии SPID 208 и SPID 212 висят на табличных блокировках, вызванной запросом сессии SPID 209. Полный текст запроса сессии 209 виден в трассе Duration и как раз касается перестроения индекса.

Что делать с табличными блокировками

Здесь, как в прочем и с любыми другими блокировками, смотрим на количество, частоту и длительность блокировок. Чаще всего табличные блокировки всё же связаны с операциями перестроения индексов, поэтому обслуживание индексов стоит проводить в период минимальной пользовательской активности и фоновых заданий. Обычно – это ночной период. Поэтому обязательно проведите ревизию регламентов обслуживания БД, настройте правильное расписание, а также рекомендуем пользоваться многопоточными скриптами перестроения. Их можно написать либо самостоятельно, либо обращайтесь к нам.

Кроме того, стоит рассмотреть возможность отключения эскалации блокировок для некоторых таблиц. Список таблиц, на которых возникают табличные (и другие) блокировки можно посмотреть в статистике по трассе Locks:

На рисунке статистика приведена только для табличных блокировок за пять рабочих дней. Итого за рабочую неделю только на табличных блокировках пользователи теряют четыре часа.

Имеет смысл взять, например, ТОП10 таблиц (по количеству запросов) из этого списка и отключить для них эскалацию.

Из минусов данного подхода – дополнительные накладные расходы на обслуживание блокировок (память, диск), но они не критичные.

2. Ключевые блокировки (KEY)

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

Появление ключевых блокировок

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

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

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

Что делать с ключевыми блокировками

В примере на скрине выше конфликты связаны с таблицами регистрации изменений (характерный суффикс ChngR). Это таблицы, хранящие список измененных объектов для ПлановОбмена. Эти объекты, с одной стороны, часто изменяются пользователями, а с другой — постоянно выгружаются в сообщения обмена. Никакими покрывающими индексами эту проблему не решить. Из стандартных решений рекомендуем запускать план обмена в нерабочее время, либо настолько часто, чтобы за прошедшее с момента выгрузки время в системе не успевал накопиться объем измененных данных, выгрузка которого могла бы занимать сколь-нибудь существенное время. Это позволит снизить вероятность появления блокировок.

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

В общем, процесс требует моделирования запроса, тестовой среды и соответствующей квалификации специалиста. Если читателям интересен процесс анализа тяжелых запросов в части поиска и создания нужного индекса, то пишите в комментариях и в одной из следующих статей обязательно разберем это подробно. Почему я пишу про индекс, а не про «переписать запрос в коде 1С», потому что добавление правильного индекса – это почти всегда самое простое и безболезненное решение ускорения запроса. А переписать код – это всегда функциональные риски плюс время. Естественно, бывают запросы, которые должны быть переписаны в любом случае, и никакой индексный тюнинг их не спасёт, но таких запросов (в коде 1С) не так уж и много, как многим кажется.

3. Страничные блокировки (PAG)

По умолчанию SQL Server использует именно механизм блокировки страниц данных индекса. Для MS SQL – это страница оперативной памяти размером 8кБ.

Появление страничных блокировок

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

Пользователь 1 изменяет данные (insert, update, delete) в таблице, и SQL блокирует индексную страницу, соответствующую изменяемым данным. Остальные пользователи, даже если изменяют другие данные, попадают на страничную блокировку, т.к. их индекс находится на той же странице, что и у первого пользователя.

Что делать со страничными блокировками

Страничные блокировки индекса можно отключать (вручную или скриптом), и тогда они фактически станут строчными. Если делать запрет вручную, то важно случайно не запретить и блокировку строк, иначе SQL Server каждый раз будет блокировать весь индекс (таблица):

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

Например, предположим, что индекс состоит из двух колонок размерностью 16 байт каждая. Итого 32 байта на одну строку. Значит, всего строк индекса на одной странице памяти получается 8 кб / 32 = 256. Т.е. заместо одной блокировки на страницу SQL сервер может наложить 256 страничных блокировок. Это, естественно, в пределе.

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

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

При отключении страничных блокировок есть нюанс, о котором нужно помнить – обслуживание индексов. Если в ваших регламентных заданиях по обслуживанию индексов используется команда REORGANIZE, то она работать не будет и SQL вернет ошибку типа:

… the index on table cannot be reorganized because page level locking is disabled…

Поэтому, либо перед выполнением реорганизации необходимо временно возвращать страничные блокировки, либо использовать только REBUILD.

Про другие, распространенные в 1С:Предприятие, блокировки на SQL сервере поговорим в следующей статье. На скриншотах приведены реальные данные из программы мониторинга PERFEXPERT.

Продолжение следует…

Ссылки на остальные части Записок оптимизатора1С: