Блокировки 1С и СУБД MS SQL

Блокировки 1С

Для реализации уровней изоляции транзакций (описано в статье Предыдущая статья: Транзакции 1С и MS SQL) используется механизм блокировок.

В составе СУБД MS SQL Server работает компонент «Диспетчер блокировок», который управляет операциями, связанными с блокировками (установка, снятие, проверка совместимости, эскалация и т. д.).

Блокировки СУБД подразделяются на следующие основные типы:
— разделяемые блокировки (S), которые могут одновременно устанавливаться несколькими пользователями. Используется для операций считывания, которые не меняют и не обновляют данные, такие как инструкция SELECT;
— исключительные блокировки (X), которые устанавливаются только одним пользователем, получающим эксклюзивный доступ к данным. Используется для операций модификации данных, таких как инструкции INSERT, UPDATE или DELETE. Гарантирует, что несколько обновлений не будет выполнено одновременно для одного ресурса;

— блокировка обновления (U). Применяется к тем ресурсам, которые могут быть обновлены. Предотвращает возникновение распространенной формы взаимоблокировки, возникающей тогда, когда несколько сеансов считывают, блокируют и затем, возможно, обновляют ресурс. Применялась в 1С 8.0 (в автоматическом режиме блокировок) при использовании «Для изменения» в запросах;

— блокировки намерений (I). Используется для создания иерархии блокировок. Типы намеренной блокировки: с намерением совмещаемого доступа (IS), с намерением монопольного доступа (IX), а также совмещаемая с намерением монопольного доступа (SIX). Оптимизируют работу диспетчера блокировок при определении совместимости блокировок: http://technet.microsoft.com/ru-ru/library/ms175519(v=sql.105).aspx

— блокировки диапазона ключей (Range). Защищают диапазон строк, считываемый запросом при использовании уровня изоляции SERIALIZABLE. Запрещает другим транзакциям вставлять строки, что защищает от фантомов: http://technet.microsoft.com/ru-ru/library/ms191272(v=sql.105).aspx

Совместимость основных типов блокировок СУБД

S X U
S + +
X
U +

«+» — блокировки совместимы, «-» — не совместимы.

Блокировки в пределах одного сеанса СУБД всегда совместимы. Блокировки разных ресурсов всегда совместимы
Обычно установленные блокировки «живут» до конца транзакции.

Когда транзакция каким-то действием (чтение/изменение), в соответствии с уровнем изоляции, пытается заблокировать какой-то ресурс (строку/таблицу), то диспетчер блокировок СУБД проверяет возможность блокировки на совместимость с уже установленными блокировками. Если диспетчер блокировок «признал» блокировку совместимой, то она устанавливается (блокировка получает состояние «GRANT»). Если блокировка не совместима, она находится в ожидания установки (получает состояние «WAIT»). Если по истечении определенного времени (переменная LOCK_TIMEOUT) блокировка не была установлена, то возникает известная ошибка ожидания блокировки «Lock request time out period exceeded«. В базах 1С значение LOCK_TIMEOUT по умолчанию равно 20 секунд и может быть изменено.

Основные уровни Блокировок в СУБД MS SQL Server
— блокировка на уровне таблицы (table-level locking) – ресурс блокировки вся таблица;
— блокировка на уровне строк (row-level locking) – ресурс блокировки одна строка таблицы.

Для серверного варианта в управляемом режиме блокировок «1С» использует блокировки на уровне строк. Но возможна блокировка всей таблицы из-за неправильно написанного запроса или из-за эскалации блокировок.

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

Обращение к данной структуре возвращает сведения о ресурсах диспетчера блокировок. Каждая строка представляет текущий активный запрос диспетчеру блокировок о блокировке, которая была установлена (состояние «GRANT») или находится в состоянии ожидания («WAIT»). Анализ данных позволяет ответить на вопросы: кто блокирует или намерен заблокировать (идентификатор сеанса), что блокирует (идентификатор ресурса).

Пример запроса к sys.dm_tran_locks:
select * from sys.dm_tran_locks
where request_session_id = 50 and resource_database_id = DB_ID();

Описание системной таблицы блокировок sys.dm_tran_locks.
Динамические административные представления MS SQL Server, связанные с транзакциями, начинаются с префикса sys.dm_tran_. Например, sys.dm_tran_session_transactions.
Таблица sys.dm_tran_locks появилась в MS SQL Server 2005. Системная таблица syslockinfo, выполняющая те же функции в предыдущих версиях, и связанная с ней функция sp_lock являются устаревшими и присутствуют в последних версиях MS SQL Server только для обеспечения обратной совместимости. Таким образом, их использование является нежелательным.

Эскалация (укрупнение) блокировок 1С

Эскалация блокировок — механизм расширения области блокировки. Основная задача эскалации состоит в повышение производительность при очень большом количестве блокировок за счет укрупнения области блокировки. Например, вместо блокировок на каждую строку таблицы будет наложена блокировка на всю таблицу. Это упрощает работу СУБД, но уменьшает параллельность конкурентных процессов. То есть результат эскалации — избыточная блокировка с целью сохранить производительность аппаратной части.

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

Отключение эскалации блокировок до уровня таблиц (для MS SQL 2008):
ALTER TABLE _AccRg20434 SET (LOCK_ESCALATION = DISABLE)

При отключении эскалации для таблиц необходимо помнить, что при блокировке большого количества записей, могут закончиться ресурсы диспетчера блокировок. Отследить укрупнение блокировок можно с помощью события Приложение SQL Server Profiler Lock:Escalation.

Использование блокировок при различных уровнях изоляции

READ_UNCOMMITTED
Уровень изоляции READ_UNCOMMITTED предоставляет наипростейшую форму изоляции транзакций. Когда транзакция на этом уровне изоляции отыскивает строку, она не запрашивает блокировки и не признает никаких существующих блокировок. Данные, которые читаются этой транзакцией, могут быть несогласованными. В этом случае транзакция читает данные, которые могут быть изменены какой-нибудь другой активной транзакцией. Если для второй транзакции позже будет выполнен откат, то значит, что первая транзакция прочла данные, которые никогда реально не существовали.

Из четырех проблем одновременного доступа к данным, рассмотренных ранее, READ_UNCOMMITTED допускает грязное чтение, неповторяемое чтение и фантомные записи. Уровень изоляции READ_UNCOMMITTED обычно является весьма нежелательным и должен быть использован, только когда не требуется точности чтения данных или когда данные изменяются редко.
схема read_uncommitted

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

READ_COMMITTED
Транзакция, которая читает строку и использует уровень изоляции READ_COMMITTED, проверяет только лишь, установлена ли исключительная блокировка на эту строку. Если такой блокировки не существует, транзакция читает строку. При этом на читаемые данные накладывается разделяемая «S» блокировка. Это действие не дает транзакции возможности читать неподтвержденные изменения данных других транзакции (на которые установлена «X» блокировка), которые впоследствии могут быть отменены. Разделяемые блокировки, устанавливаемые на читаемые данные транзакцией этого уровня изоляции, немедленно отменяются сразу после того, как данные будут обработаны и эти данные могут быть изменены любой другой транзакцией, что может привести к проблеме неповторяющегося чтения.

 

схема read_committed

При использовании уровня READ COMMITED разделяемая «S» блокировка накладывается на данные во время их чтения (на время исполнения запроса на ВСЕ данные, которые прочитаны в процессе выполнения запроса). После выполнения чтения (после выполнения запроса) «S» блокировка снимается и данные могут быть изменены другой транзакцией (возникает проблема неповторяющегося чтения). Исключительная «X» блокировка при изменении данных устанавливается до конца транзакции, что препятствует чтению этих данных из другой транзакции с таким же уровнем изоляции («S» блокировка несовместима с «X»).

REPEATABLE_READ
В отличие от уровня изоляции READ_COMMITTED уровень изоляции REPEATABLE_READ устанавливает разделяемую блокировку на все данные, которые читаются, и сохраняет эту блокировку до конца транзакции (пока транзакция не будет подтверждена, или отменена). Следовательно, в этом случае выполнение запроса несколько раз внутри транзакции всегда вернет один и тот же результат (решена проблема повторяющегося чтения). Недостатком такого уровня изоляции является то, что одновременный доступ ухудшается, потому что временной интервал, в течение которого другие транзакции не могут обновлять те же данные, значительно больше, чем в случае READ_COMMITTED.
Этот уровень изоляции не запрещает другим транзакциям добавлять новые строки, которые появляются в последующих чтениях, следовательно, могут появиться фантомные записи.

схема repeatable_read

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

Блокировки 1С и СУБД MS SQL
Уровень изоляции SERIALIZABLE реализован с использованием метода блокировки диапазона ключей. Этот метод блокирует индивидуальные строки и упорядочивает их. Метод блокировки диапазона ключей устанавливает блокировки на записи индексов, а не на отдельные страницы или на всю таблицу. В этом случае никакая операция модификации данных в другой транзакции не может быть выполнена, потому что невозможны необходимые изменения индексных записей.

Чтение данных вне транзакции

При выполнении запросов вне транзакции MS SQL по умолчанию запрещает доступ к данным, измененным незавершенными транзакциями. Это позволяет избежать проблем чтения «грязных» данных, так как незавершенные транзакции могут отмениться. Что бы все таки прочитать данные, измененные незавершенными транзакциями, к каждому запросу необходимо добавлять указание (hint) «nolock». Именно в таком режиме (с указанием «nolock») платформа 1С выполняет запросы вне транзакции, в отчетах, при обновлении списков. В версии 8.3 указание «nolock» не применяется, так как при использовании версионирования, чтение данных, измененных незавершенными транзакциями, исключается.

Избыточные блокировки 1С

Из вышеизложенного следует что блокировки необходимы для реализации уровней изоляции, что в свою очередь решает проблемы параллельного доступа и обеспечивает нормальную работу механизма транзакций. Понятно что в этом случае блокировки полезны и необходимы. Но часто из-за неправильных действий программистов или администраторов СУБД возникают избыточные (лишние) блокировки, которые могут мешать выполнению транзакций других пользователей. Причин избыточных блокировок несколько. Самые:

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

Неправильное (не по назначению) использование объектов конфигурации — Платформа 1С предлагает большое количество объектов конфигурации: константы, справочники, документы, регистры и т. д. Разработчик 1С должен четко представлять для каких целей используется тот или иной объект и использовать его по назначению. Также разработчик должен знать и применять все возможности платформы по оптимизации работы системы. Большинство разработчиков всецело занято решением прикладной задачи и вопрос оптимизации остается в стороне. Проходит время, база вырастает и получаем проблему. Еще раз отметим что ответственность за правильное использование объектов конфигурации и их свойств по оптимизации несет разработчик, а не системный администратор.

 

Вывод

Блокировки — это необходимый механизм СУБД. Блокировки используются для реализации уровней изоляции транзакций.

Избыточные блокировки 1С — это лишние (не нужные) блокировки, которые препятствуют параллельной работе других пользователей. Избыточные блокировки — причина появления в 1С ошибки  «Lock request time out period exceeded».

Основные причины избыточных блокировок 1С: Неоптимальные запросы и неправильное применение объектов конфигурации.

Следующая статья: Управляемые блокировки 1С

Posted in Блог Оптимизации 1С.

3 Comments

  1. Вы пишите:
    «В базах 1С значение LOCK_TIMEOUT по умолчанию равно 20 секунд и может быть изменено.»
    подскажите как это сделать.

    • В конфигураторе Администрирование — Параметры информационной базы — Время ожидания блокировки данных
      Извините что долго не отвеччали…

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *