Транзакции 1С и SQL. Уровни изоляции и проблемы параллельного доступа.

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

Производительность напрямую связана с ТРАНЗАКЦИЯМИ 1С

«Конфликт блокировок при выполнении транзакции:
Microsoft OLE DB Provider for SQL Server: Lock request time out period exceeded.
HRESULT=80040E31, SQLSrvr: SQLSTATE=HYT00, state=34, Severity=10, native=1222, line=1»

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

Транзакции 1С

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

К механизму транзакций выдвигается ряд требований (известный под аббревиатурой ACID): Атомарность (Atomicity), Согласованность (Consistency), Изолированность (Isolation), Устойчивость (Durability)

Атомарность (Atomicity). Это требование заключается в том, что все данные, с которыми работает транзакция, должны быть либо подтверждены (commit), либо отменены (rollback). Не должно быть ситуации, когда часть изменений подтверждается, а часть – отменяется.

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

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

Журнал транзакций (SQL Server)

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

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

В зависимости от настроек базы данных (recovery model) журнал транзакций транзакции может обрезаться (удаляются данные старых транзакции) либо автоматически, либо вручную(recovery model=FULL). Иногда системный администратор забывает обрезать журнал и может возникнуть ошибка: «The transaction log for database is full»

Физически журнал транзакций СУБД MS SQL Server находится в файле .LDF (а файл данных — .MDF).

Транзакции в системе «1С:Предприятие»

Система «1С:Предприятие» осуществляет неявный вызов транзакций при выполнении любых действий, связанных с модификацией информации, хранящейся в базе данных. Например, все обработчики событий, расположенные в модулях объектов и наборов записей, связанные с модификацией данных базы данных, вызываются в транзакции.

Пример неявной транзакции: последовательность событий при проведении документа из формы

Транзакции 1С

Пример неявной транзакции: последовательность событий при проведении документа из формы

Практически определить что запись объекта 1С (например документа) происходи тв транзакции можно проведя следующий опыт: Попробовать провести и закрыть (нажать «ОК» в форме документа) новый документ заранее зная что он не проведется (например указав большое количество товара для списания). Так как остатки проверяются на этапе проведения документа, в обработчике  «ОбработкаПроведения()», то к этому моменту сам документ уже должен быть записан в базу, так как запись документа происходит ранее между событиями «ПередЗаписью()» и «ПриЗаписи()». Но после появления сообщения об ошибке (отсутствие необходимого количества), Мы обнаружим что документ в базу не записан (останется флаг модифицированности «*» и в списке документ не появиться). Это происходит потому что транзакция после возникновения ошибки отменяется (откатывается «rollback»).

Использование явного вызова транзакций

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

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

Проблемы параллельного доступа

При параллельном выполнении транзакций возможны следующие проблемы:
потерянное обновление (англ. lost update) — при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется;
«грязное» чтение (англ. dirty read) — чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);
неповторяющееся чтение (англ. non-repeatable read) — при повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными;
фантомное чтение (англ. phantom reads) — одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет или удаляет строки, попадающие в критерии выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк.

Рассмотрим ситуации, в которых возможно возникновение данных проблем:

Потерянное обновление

Предположим, имеются две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1; SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=20 WHERE f1=1;
UPDATE tbl1 SET f2=25 WHERE f1=1;

В Транзакции1 изменяется значение поля f1, а затем в Транзакции2 также изменяется значение этого поля в той же таблице. В результате изменение, выполненное первой транзакцией, будет потеряно.

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

«Грязное» чтение

Если предыдущая проблема возникает при записи данных, то «грязное» чтение возможно, когда одна транзакция пытается прочитать данные, с которыми работает другая параллельная транзакция.
Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;

В Транзакции1 изменяется значение поля f1, а затем в Транзакции2 выбирается значение поля f2. После этого происходит откат Транзакции 1. В результате значение, полученное второй транзакцией, будет отличаться от значения, хранимого в базе данных.

Строго говоря, здесь тоже проблема именно в непредсказуемости результата. Т. е. вторая транзакция может «успеть» считать необновленное значение, а может не успеть и получить обновленное.

Неповторяющееся чтение

Предположим, имеются две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1; SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
SELECT f2 FROM tbl1 WHERE f1=1;

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

Фантомное чтение

Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1 Транзакция 2
SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
SELECT SUM(f2) FROM tbl1;

В Транзакции2 выполняется SQL-оператор, использующий все значения поля f2. Затем в Транзакции1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомной вставкой и является частным случаем неповторяющегося чтения.

Уровни изоляции транзакций

Уровень изоляции – это свойство транзакции, определяющее независимость работы транзакции от других, параллельно выполняющихся, транзакций.

Стандартом Введены следующие четыре уровня изоляции, применение которых предотвращает определенные проблемы параллельного доступа:
READ_UNCOMMITTED — нефиксированное чтение. Этот уровень изоляции решает проблему «потерянного обновления», но при этом возможно получение разных результатов для одинаковых запросов без учета фиксации транзакции (возможна проблема «грязного чтения»). Это самый низкий уровень изоляции, используемый в СУБД, который обеспечивает максимальную параллельность работы.
READ_COMMITTED — фиксированное чтение. Это уровень изоляции предотвращает проблему «грязного чтения», но позволяет получать разные результаты для одинаковых запросов в транзакции (сохраняется возможность «неповторяющегося чтения»);
REPEATABLE_READ — повторяющееся чтение. Это уровень изоляции решает проблему «неповторяющегося чтения». На этом уровне сохраняется возможно выполнение операторов INSERT, приводящих к конфликтной ситуации «фантомная вставка». Этот уровень целесообразно использовать, если на выполняющиеся SQL-операторы не влияет добавление новых строк;
SERIALIZABLE — последовательное выполнение. Третий уровень. Этот уровень гарантирует предотвращение всех описанных выше проблем параллельного доступа, но соответственно, наблюдается самая низкая степень параллелизма, так как обработка транзакций (с доступом к одним и тем же ресурсам) проводится только последовательно.

В MS SQL Server 2005 введен уровень изоляции SNAPSHOT – «снимок». Его суть заключается в том, что он не устанавливает блокировки (см. далее) на ресурсы, используемые транзакцией, а создает рабочие дубликаты («снимки») данных и работает с ними в ходе всей транзакции, тогда как исходные данные остаются неизменными до ее завершения. Параллельные транзакции, таким образом, работают как бы в «параллельной реальности». Такой механизм заимствован из СУБД Oracle. где в режиме READ_COMMITTED не устанавливается исключительную блокировку, а выдает другому сеансу копию (версию) данных до начала транзакции.
В «1С» данный уровень изоляции используется по умолчанию в версии 8.3.

Решение проблемы параллельного доступа транзакций и уровни изоляции в виде таблицы можно изобразить так («+» — проблема исключена):

Проблемы параллельного доступа и уровни изоляции Фантомное чтение Неповторяющееся чтение «Грязное» чтение Потерянное обновление
SERIALIZABLE + + + +
REPEATABLE_READ + + +
READ_COMMITTED + +
READ_UNCOMMITTED +

На уровне SQL сервера можно устанавливать уровень изоляции самостоятельно:
для всего сеанса, например директивой
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

для конкретного запроса с помощью конструкции WITH
SELECT Name FROM Contracts WITH SERIALIZABLE

Узнать уровень изоляции, установленный в текущей сессии
select transaction_isolation_level from sys.dm_exec_sessions
where session_id = @@spid

В автоматическом режиме (старый режим, который применялся в 8.0) управления блокировками данных используются уровни изоляции транзакций REPEATABLE_READ и SERIALIZABLE, обеспечиваемые системой управления базами данных. Эти уровни изоляции транзакций обеспечивают согласованное и целостное чтение данных, и от разработчика не требуется каких-либо дополнительных действий по управлению блокировками.

Управляемый режим блокировок (начиная с версии 8.1) позволяет повысить параллельность работы пользователей в клиент-серверном варианте работы за счет использования более низкого уровня изоляции транзакций базы данных (READ_COMMITTED); этот же уровень изоляции установлен по умолчанию
и в MS SQL сервере. При записи данных в транзакции объекты встроенного языка автоматически блокируют необходимые данные. Но при чтении разработчику требуется управлять блокировками данных в тех случаях, когда бизнес-логика требует согласованного и целостного чтения данных в транзакции.

Для версии 8.3 в управляемом режиме используется уровень изоляции READ_COMMITTED_SNAPSHOT.

Выводы

Транзакции — необходимый механизм СУБД, который активно используется в 1С. Для решения проблем параллельного доступа транзакции в СУБД могут выполняться с различными уровнями изоляции.

Используемый «1С» уровень изоляции READ_COMMITTED решает проблемы «Потерянное обновление» и «Грязное чтение»: измененные данные блокируются до конца транзакции и для чтения и для изменения (накладывается исключительная блокировка).

Уровень изоляции READ_COMMITTED не решает проблемы «Неповторяющееся чтение» и «Фантомное чтение». Чтобы решить эти проблемы необходимо использовать управляемые блокировки «1С», установленные программно.

В 8.3 используется более гибкий уровень изоляции READ_COMMITTED_SNAPSHOT.

Следующая статья: Блокировки 1С и MS SQL

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

One Comment

  1. Спасибо за статью!!! Очень познавательно, в особенности для обычного 1С-ника как я.

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

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