Транзакции

161

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Транзакции

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

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

В этой статье показано, как проблемы, связанные с одновременным конкурентным доступом, можно решить посредством транзакций. Здесь дается вводное представление о свойствах транзакций, называемых свойствами ACID (Atomicity, Consistency, Isolation, Durability - атомарность, согласованность, изолированность, долговечность), обзор инструкций языка Transact-SQL, применяемых для работы с транзакциями, и введение в журналы транзакций.

Модели одновременного конкурентного доступа

Компонент Database Engine поддерживает две разные модели одновременного конкурентного доступа:

пессимистический одновременный конкурентный доступ;
оптимистический одновременный конкурентный доступ.

В модели пессимистического одновременного конкурентного доступа для предотвращения одновременного доступа к данным, которые используются другим процессом, применяются блокировки. Иными словами, система баз данных, использующая модель пессимистического одновременного конкурентного доступа, предполагает, что между двумя или большим количеством процессов в любое время может возникнуть конфликт и поэтому блокирует ресурсы (строку, страницу, таблицу), как только они потребуются в течение периода транзакции. Модель пессимистического одновременного конкурентного доступа устанавливает блокировку с обеспечением разделяемого доступа, иначе немонопольную блокировку (shared lock) на считываемые данные, чтобы никакой другой процесс не мог изменить эти данные. Кроме этого, механизм пессимистического одновременного конкурентного доступа устанавливает монопольную блокировку (exclusive lock) на изменяемые данные, чтобы никакой другой процесс не мог их считывать или модифицировать.

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

Понятие оптимистического одновременного конкурентного доступа обычно определяется в более широком смысле. Работа управления оптимистического одновременного конкурентного доступа основана на предположении маловероятности конфликтов между несколькими пользователями, поэтому разрешается исполнение транзакций без установки блокировок. Только когда пользователь пытается изменить данные, выполняется проверка ресурсов, чтобы определить наличие конфликтов. Если таковые возникли, то приложение требуется перезапустить.

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

Транзакция задает последовательность инструкций языка Transact-SQL, применяемую программистами базы данных для объединения в один пакет операций чтения и записи для того, чтобы система базы данных могла обеспечить согласованность данных. Существует два типа транзакций:

Неявная транзакция - задает любую отдельную инструкцию INSERT, UPDATE или DELETE как единицу транзакции.
Явная транзакция - обычно это группа инструкций языка Transact-SQL, начало и конец которой обозначаются такими инструкциями, как BEGIN TRANSACTION, COMMIT и ROLLBACK.

Понятие транзакции лучше всего объяснить на примере. Допустим, в базе данных SampleDb сотруднику "Василий Фролов" требуется присвоить новый табельный номер. Этот номер нужно одновременно изменить в двух разных таблицах. В частности, требуется одновременно изменить строку в таблице Employee и соответствующие строки в таблице Works_on. Если обновить данные только в одной из этих таблиц, данные базы данных SampleDb будут несогласованны, поскольку значения первичного ключа в таблице Employee и соответствующие значения внешнего ключа в таблице Works_on не будут совпадать. Реализация этой транзакции посредством инструкций языка Transact-SQL показана в примере ниже:

USE SampleDb;

-- Начало транзакции
BEGIN TRANSACTION
    UPDATE Employee
        SET Id = 14568
        WHERE Id = 10102

    IF (@@error <> 0)
        -- Отменить транзакцию, если есть ошибки
        ROLLBACK
    
    UPDATE Works_on
        SET EmpId = 14568
        WHERE EmpId = 10102
    
    IF (@@error <> 0)
        ROLLBACK
-- Завершение транзакции
COMMIT

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

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

Свойства транзакций

Транзакции обладают следующими свойствами, которые все вместе обозначаются сокращением ACID (Atomicity, Consistency, Isolation, Durability):

атомарность (Atomicity);
согласованность (Consistency);
изолированность (Isolation);
долговечность (Durability).

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

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

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

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

Инструкции Transact-SQL и транзакции

Для работы с транзакциями язык Transact-SQL предоставляет некоторые инструкции. Инструкция BEGIN TRANSACTION запускает транзакцию. Синтаксис этой инструкции выглядит следующим образом:

BEGIN TRANSACTION [ {transaction_name | @trans_var }
    [WITH MARK ['description']]]


Соглашения по синтаксису

В параметре transaction_name указывается имя транзакции, которое можно использовать только в самой внешней паре вложенных инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK. В параметре @trans_var указывается имя определяемой пользователем переменной, содержащей действительное имя транзакции. Параметр WITH MARK указывает, что транзакция должна быть отмечена в журнале. Аргумент description - это строка, описывающая эту отметку. В случае использования параметра WITH MARK требуется указать имя транзакции.

Инструкция BEGIN DISTRIBUTED TRANSACTION запускает распределенную транзакцию, которая управляется Microsoft Distributed Transaction Coordinator (MS DTC - координатором распределенных транзакций Microsoft). Распределенная транзакция - это транзакция, которая используется на нескольких базах данных и на нескольких серверах. Поэтому для таких транзакций требуется координатор для согласования выполнения инструкций на всех вовлеченных серверах. Координатором распределенной транзакции является сервер, запустивший инструкцию BEGIN DISTRIBUTED TRANSACTION, и поэтому он и управляет выполнением распределенной транзакции.

Инструкция COMMIT WORK успешно завершает транзакцию, запущенную инструкцией BEGIN TRANSACTION. Это означает, что все выполненные транзакцией изменения фиксируются и сохраняются на диск. Инструкция COMMIT WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно.

Язык Transact-SQL также поддерживает инструкцию COMMIT TRANSACTION, которая функционально равнозначна инструкции COMMIT WORK, с той разницей, что она принимает определяемое пользователем имя транзакции. Инструкция COMMIT TRANSACTION является расширением языка Transact-SQL, соответствующим стандарту SQL.

В противоположность инструкции COMMIT WORK, инструкция ROOLBACK WORK сообщает о неуспешном выполнении транзакции. Программисты используют эту инструкцию, когда они полагают, что база данных может оказаться в несогласованном состоянии. В таком случае выполняется откат всех произведенных инструкциями транзакции изменений. Инструкция ROOLBACK WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно. Язык Transact-SQL также поддерживает инструкцию ROLLBACK TRANSACTION, которая функционально равнозначна инструкции ROOLBACK WORK, с той разницей, что она принимает определяемое пользователем имя транзакции.

Инструкция SAVE TRANSACTION устанавливает точку сохранения внутри транзакции. Точка сохранения (savepoint) определяет заданную точку в транзакции, так что все последующие изменения данных могут быть отменены без отмены всей транзакции. (Для отмены всей транзакции применяется инструкция ROLLBACK.) Инструкция SAVE TRANSACTION в действительности не фиксирует никаких выполненных изменений данных. Она только создает метку для последующей инструкции ROLLBACK, имеющей такую же метку, как и данная инструкция SAVE TRANSACTION.

Использование инструкции SAVE TRANSACTION показано в примере ниже:

USE SampleDb;

BEGIN TRANSACTION;
    INSERT INTO Department (Number, DepartmentName)
        VALUES ('d4', 'Скидки');
    SAVE TRANSACTION a;

    INSERT INTO Department (Number, DepartmentName)
        VALUES ('d5', 'Исследование');
    SAVE TRANSACTION b;

    INSERT INTO Department (Number, DepartmentName)
        VALUES ('d6', 'Менеджмент');
    
    ROLLBACK TRANSACTION b;

    INSERT INTO Department (Number, DepartmentName)
        VALUES ('d7', 'Поддержка');

    ROLLBACK TRANSACTION a;

COMMIT TRANSACTION;

Единственной инструкцией, которая выполняется в этом примере, является первая инструкция INSERT. Для третьей инструкции INSERT выполняется откат с помощью инструкции ROLLBACK TRANSACTION b, а для двух других инструкций INSERT будет выполнен откат инструкцией ROLLBACK TRANSACTION a.

Инструкция SAVE TRANSACTION в сочетании с инструкцией IF или WHILE является полезной возможностью, позволяющей выполнять отдельные части всей транзакции. С другой стороны, использование этой инструкции противоречит принципу работы с базами данных, гласящему, что транзакция должна быть минимальной длины, поскольку длинные транзакции обычно уменьшают уровень доступности данных.

Как вы уже знаете, каждая инструкция Transact-SQL всегда явно или неявно принадлежит к транзакции. Для удовлетворения требований стандарта SQL компонент Database Engine предоставляет поддержку неявных транзакций. Когда сеанс работает в режиме неявных транзакций, выполняемые инструкции неявно выдают инструкции BEGIN TRANSACTION. Это означает, что для того чтобы начать неявную транзакцию, пользователю или разработчику не требуется ничего делать. Но каждую неявную транзакцию нужно или явно зафиксировать или явно отменить, используя инструкции COMMIT или ROLLBACK соответственно. Если транзакцию явно не зафиксировать, то все изменения, выполненные в ней, откатываются при отключении пользователя.

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

ALTER TABLE;
FETCH;
REVOKE;
CREATE TABLE;
GRANT;
SELECT;
DELETE;
INSERT;
TRUNCATE TABLE;
DROPTABLE;
OPEN;
UPDATE.

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

Начало явной транзакции помечается инструкцией BEGIN TRANSACTION, а окончание - инструкцией COMMIT или ROLLBACK. Явные транзакции можно вкладывать друг в друга. В таком случае, каждая пара инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK используется внутри каждой такой пары или большего количества вложенных транзакций. (Вложенные транзакции обычно используются в хранимых процедурах, которые сами содержат транзакции и вызываются внутри другой транзакции.) Глобальная переменная @@trancount содержит число активных транзакций для текущего пользователя.

Инструкции BEGIN TRANSACTION, COMMIT и ROLLBACK могут использоваться с именем заданной транзакции. (Именованная инструкция ROLLBACK соответствует или именованной транзакции, или инструкции SAVE TRANSACTION с таким же именем.) Именованную транзакцию можно применять только в самой внешней паре вложенных инструкций BEGIN TRANSACTON/COMMIT или BEGIN TRANSACTION/ROLLBACK.

Журнал транзакций

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

Компонент Database Engine сохраняет все эти записи, в особенности значения до и после транзакции, в одном или более файлов, которые называются журналами транзакций (transaction log). Для каждой базы данных ведется ее собственный журнал транзакций. Таким образом, если возникает необходимость отмены одной или нескольких операций изменения данных в таблицах текущей базы данных, компонент Database Engine использует записи в журнале транзакций, чтобы восстановить значения столбцов таблиц, которые существовали до начала транзакции.

Журнал транзакций применяется для отката или восстановления транзакции. Если в процессе выполнения транзакции еще до ее завершения возникает ошибка, то система использует все существующие в журнале транзакций исходные значения записей (которые называются исходными образами записей (before image)), чтобы выполнить откат всех изменений, выполненных после начала транзакции. Процесс, в котором исходные образы записей из журнала транзакций используются для отката всех изменений, называется операцией отмены записей (undo activity).

В журналах транзакций также сохраняются преобразованные образы записей (after image). Преобразованные образы - это модифицированные значения, которые применяются для отмены отката всех изменений, выполненных после старта транзакции. Этот процесс называется операцией повторного выполнения действий (redo activity) и применяется при восстановлении базы данных.

Каждой записи в журнале транзакций присваивается однозначный идентификатор, называемый порядковым номером журнала транзакции (log sequence number - LSN). Все записи журнала, являющиеся частью определенной транзакции, связаны друг с другом, чтобы можно было найти все части этой транзакции для операции отмены или повтора.