Что такое транзакция в sql

Транзакции в MySQL

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

Транзакции не могут быть вложенными, потому что любой оператор, начинающий транзакцию, приводит к завершению предыдущей.

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

По шагам это будет выглядеть так:

1. Убедиться, что остаток на счете клиента больше 3000 рублей.
2. Вычесть 3000 рублей со счета клиента.
3. Добавить 3000 к счету интернет-магазина.

Команды входящие в транзакцию:

Команда START TRANSACTION начинает транзакцию.

Убеждаемся, что на счету пользователя достаточно средств.

Снимаем средства со счета пользователя.

Перемещаем денежные средства на счет интернет-магазина.

Чтобы изменения вступили в силу, мы должны выполнить команду COMMIT.

Основные концепции транзакции ACID

Atomicity, Consistency, Isolation, Durability (Атомарность, Согласованность, Изолированность, Стойкость, или долговечность).

Atomicy — атомарност

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

Consistency — согласованность

Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. При выполнении принципа согласованности база данных должна всегда переходить из одного непротиворечивого состояния в другое непротиворечивое состояние. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. Это условие является необходимым для поддержки четвёртого свойства.

Isolation — изолированность

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

Durability — сохраняемость

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

Управление транзакциями

Для управления транзакциями используются следующие команды:

• COMMIT — сохраняет изменения;
• ROLLBACK — откатывает (отменяет) изменения;
• SAVEPOINT — создаёт точку к которой группа транзакций может откатиться;
• SET TRANSACTION — размещает имя транзакции.

Команды управление транзакциями используются только для DML команд: INSERT , UPDATE , DELETE .

Rollback

ROLLBACK (от roll back — англ. откатывать, возвращаться) — оператор языка SQL, который применяется для того, чтобы:

• отменить все изменения, внесённые начиная с момента начала транзакции или с какой-то точки сохранения ( SAVEPOINT );
• очистить все точки сохранения данной транзакции;
• завершить транзакцию;
• освободить все блокировки данной транзакции.

При выполнении транзакции мы выясняем, что не можем завершить транзакцию, например, пользователь ее отменяет или происходит еще что-то. Чтобы ее отметить мы воспользовались командой ROLLBACK .

Для некоторых операторов нельзя выполнить откат при помощи оператора ROLLBACK . К их числу относят следующие команды:

• CREATE INDEX
• DROP INDEX
• CREATE TABLE
• DROP TABLE
• TRUNCATE TABLE
• ALTER TABLE
• RENAME TABLE
• CREATE DATABASE
• DROP DATABASE
• ALTER DATABASE

Не помещайте их в транзакции с другими операторами.

Кроме того, существует ряд операторов, которые неявно завершают транзакцию, как если бы был вызван оператор COMMIT :

• ALTER TABLE
• BEGIN
• CREATE INDEX
• CREATE TABLE
• CREATE DATABASE
• DROP DATABASE
• DROP INDEX
• DROP TABLE
• DROP DATABASE
• LOAD MASTER DATA
• LOCK TABLES
• RENAME
• SET AUTOCOMMIT=1
• START TRANSACTION
• TRUNCATE TABLE

SAVEPOINT и ROLLBACK TO SAVEPOINT

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

• SAVEPOINT — создает точку сохранения;
• ROLLBACK TO SAVEPOINT — позволяет откатиться к одной из точек сохранения.

Несколько точек сохранения

Допускается создание нескольких точек сохранения. Если текущая транзакция имеет точку сохранения с таким же именем, старая точка удаляется и устанавливается новая. Все точки сохранения транзакций удаляются, если выполняется оператор COMMIT или ROLLBACK без указания имени точки сохранения.

Уровни изоляции

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

• READ UNCOMMITTED — используется редко, поскольку его производительность не намного выше, чем у других. На этом уровне вы видите промежуточные результаты чужих транзакций, т.е. осуществляете грязное чтение.
• READ COMMITTED — подразумевает, что транзакция увидит только те изменения, которые были уже зафиксированы другими транзакциями к моменту ее начала. Произведенные ею изменения останутся невидимыми для других транзакций, пока она не будет зафиксирована. На этом уровне возможен феномен невоспроизводимого чтения. Это означает, что вы можете выполнить одну и ту же команду дважды и получить различный результат.
• REPEATABLE READ — этот уровень изоляции установлен по умолчанию. Он гарантирует, что любые строки, которые считываются в контексте транзакции, будут выглядеть такими же при последовательных операциях чтения в пределах одной и той же транзакции, однако теоретически на этом уровне возможен феномен фантомного чтения (phantom reads). Он возникает в случае, если вы выбираете некоторый диапазон строк, затем другая транзакция вставляет новую строку в этот диапазон, после чего вы выбираете тот же диапазон снова. В результате вы увидите новую фантомную строку.
• SERIALIZABLE — самый высокий уровень изоляции, решает проблему фантомного чтения, заставляя транзакции выполняться в таком порядке, чтобы исключить возможность конфликта. Уровень SERIALIZABLE блокирует каждую строку, которую транзакция читает. На этом уровне может возникать множество задержек и конфликтов при блокировках. На практике данный уровень изоляции применяется достаточно редко.

Изменить уровень изоляции можно при помощи команды SET TRANSACTION :

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

Запросить параметры журнала транзакций можно при помощи следующего запроса:

Где располагается журнал транзакций

ib_logfile0 и ib_logfile1 — файлы журнала транзакций, все транзакции сначала помещаются сюда, затем перегоняются в файлы единого табличного пространства: если сервер MySQL останавливается штатно, все транзакции из журнала сохраняются в таблицу. Если происходит сбой и сервер останавливается, например из-за отсутствия питания, перед стартом MySQL проверяет журнал транзакций и перегоняет в единое табличное пространство все транзакции которые не были сохранены в таблицах. Таким образом, потерять сохраненные транзакции невозможно.

Получить путь к каталогу, в котором размещены файлы транзакций, можно при помощи следующего запроса:

Управление режимом сохранения транзакций

За режим управления сохранения транзакций отвечает переменная innodb_flush_log_at_trx_commit, которая может принимать следующие значения:

• 0 — сохранение журнала раз в секунду,
• 1 — сохранение после каждой транзакции,
• 2 — сохранение журнала раз в секунду и после каждой транзакции.

Узнать текущий режим сохранения транзакций:

Установить новое значение:

SQL-Ex blog

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

Что такое транзакции?

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

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

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

Для использования транзакции в SQL нам нужно иметь несколько операторов SQL. Общая схема такова:

1. BEGIN TRANSACTION: этот оператор начинает новую транзакцию. Любые операторы SQL, которые следуют за этим оператором, рассматриваются как часть транзакции до тех пор, пока транзакция не будет зафиксирована или выполнен откат.
2. COMMIT TRANSACTION: этот оператор сохраняет сделанные во время транзакции изменения в базе данных. Если транзакция завершается успешно, эти изменения становятся постоянными (фиксируются).
3. ROLLBACK TRANSACTION: этот оператор отменяет изменения, сделанные во время транзакции, и восстанавливает базу данных в ее предшествующем состоянии (откат).

Пример 1

В этом примере мы используем транзакцию для обновления статуса заказа и одновременного уменьшения количества товара в таблице inventory.

BEGIN TRANSACTION; 
-- обновление таблицы orders 
UPDATE orders 
SET status = 'shipped' 
WHERE order_id = 123; 
--обновление таблицы inventory 
UPDATE inventory 
SET quantity = quantity - 1 
WHERE product_id = 456; 
COMMIT TRANSACTION;

Если оба оператора завершаются успешно, транзакция фиксируется, и изменения в базе данных становятся постоянными.

Пример 2

Предположим, что у нас есть таблица “employees” со столбцами “employee_id”, “first_name” и “last_name”. Мы хотим обновить фамилию (last name) сотрудника с ID 123 на “Doe” и сотрудника с ID 456 на “Smith”. Вот как мы могли бы это сделать, используя транзакцию:

BEGIN TRANSACTION; 
UPDATE employees 
SET last_name = 'Doe' 
WHERE employee_id = 123; 
UPDATE employees 
SET last_name = 'Smith' 
WHERE employee_id = 456; 
COMMIT TRANSACTION;

В этом примере мы начинаем новую транзакцию, используя BEGIN TRANSACTION. Затем мы обновляем фамилию сотрудника с ID 123 на “Doe” и сотрудника с ID 456 на “Smith”. После завершения обновлений мы фиксируем транзакцию, чтобы сделать изменения постоянными.

Пример 3

Пусть у нас есть база данных с таблицей “customers”. Мы хотим обновить номер телефона клиента с и вставить нового клиента в таблицу с эти две операции рассматривались как единое атомарное действие, мы могли бы использовать такую транзакцию:

BEGIN TRANSACTION; 
UPDATE customers 
SET phone_number = '555-1234' 
WHERE customer_id = 12345; 
INSERT INTO customers (customer_id, name, phone_number) 
VALUES (67890, 'John Doe', '555-6789'); 
COMMIT TRANSACTION;

В этом примере мы используем оператор BEGIN TRANSACTION для начала новой транзакции. Затем мы обновляем номер телефона клиента с и вставляем нового клента с Наконец мы используем оператор COMMIT TRANSACTION для фиксации изменений, сделанных в процессе выполнения транзакции в базе данных.

Теперь давайте рассмотрим более сложный пример для иллюстрации работы транзакции.

Пример 4

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

Вот пример того, как мы можем выполнить это с использованием транзакций в SQL:

BEGIN TRANSACTION; 
-- вставить новый заказ в таблицу orders 
INSERT INTO orders (customer_id, product_id, quantity, status) 
VALUES (1, 2, 3, 'Pending'); 
-- обновить уровень запасов для соответствующего товара 
UPDATE inventory 
SET quantity = quantity - 3 
WHERE product_id = 2; 
-- проверим, не стал ли уровень запасов отрицательным 
IF EXISTS (SELECT * FROM inventory WHERE product_id = 2 AND quantity < 0)
BEGIN 
-- если уровень запасов отрицательный, откатываем транзакцию 
ROLLBACK TRANSACTION; 
PRINT 'Error: inventory level is negative'; 
END 
ELSE 
BEGIN 
-- если уровень запасов не отрицательный, фиксируем транзакцию 
COMMIT TRANSACTION; 
PRINT 'Order successfully placed'; 
END

В этом примере оператор BEGIN TRANSACTION начинает транзакцию. Затем в таблицу orders вставляется новый заказ, с помощью оператора UPDATE обновляется уровень запасов соответствующего товара. Затем с помощью оператора IF проверяется, не стал ли уровень запасов отрицательным. Если так, мы откатываем транзакцию и печатаем сообщение об ошибке. Если нет, транзакция фиксируется и печатается сообщение об успешном выполнении.

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

Пример 5

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

Ниже пример, как это может быть сделано с использованием транзакций SQL:

BEGIN TRANSACTION; 
-- обновление зарплаты сотрудника в таблице salaries 
UPDATE salaries 
SET salary = 75000 
WHERE employee_id = 123; 
-- обновление информации о сотруднике в таблице employees 
UPDATE employees 
SET last_name = 'Doe' 
WHERE employee_id = 123; 
-- проверка, что оба обновления были успешны 
IF @@ROWCOUNT > 0 
BEGIN 
COMMIT TRANSACTION; 
PRINT 'Employee salary and information updated successfully'; 
END 
ELSE 
BEGIN 
ROLLBACK TRANSACTION; 
PRINT 'Error: failed to update employee salary and information'; 
END

В этом примере мы начали транзакцию оператором BEGIN TRANSACTION. Затем мы обновили зарплату сотрудника в таблице salaries и его фамилию в таблице employees. Если оба обновления были успешны, то @@ROWCOUNT, которое возвращает число обработанных строк последним оператором, должно вернуть 1. В этом случае мы фиксируем транзакцию и печатаем сообщение об успешном выполнении. В противном случае транзакция откатывается и печатается сообщение об ошибке.

Ссылки по теме

1. Уровни изоляции транзакций
2. Основы журнала транзакций в SQL Server
3. Управление параллельным выполнением транзакций с помощью блокировок в SQL Server

Транзакции (Azure Synapse Analytics и Microsoft Fabric)

Транзакция — это группа инструкций одной или нескольких баз данных, которые либо полностью фиксируются, либо полностью откатываются. Транзакции атомарны, согласованы, изолированы и устойчивы (atomic, consistent, isolated, durable — ACID). Если транзакция выполнена успешно, все инструкции в ней фиксируются. Если транзакция завершается ошибкой, то если хотя бы одна инструкция в группе завершается ошибкой, выполняется откат всей группы.

Начало и конец транзакции зависят от параметра AUTOCOMMIT и инструкций BEGIN TRANSACTION, COMMIT и ROLLBACK.

Поддерживаются следующие типы транзакций:

• Явные транзакции начинаются с инструкции BEGIN TRANSACTION и заканчиваются инструкцией COMMIT или ROLLBACK.
• Транзакции с автофиксацией автоматически запускаются в рамках сеанса и не начинаются с инструкции BEGIN TRANSACTION. Если для параметра AUTOCOMMIT установлено значение ON, каждая инструкция выполняется в транзакции, и явные инструкции COMMIT или ROLLBACK не требуются. Если для параметра AUTOCOMMIT установлено значение OFF, для определения результата транзакции требуется инструкция COMMIT или ROLLBACK. Операции автоматической фиксации начинаются сразу после инструкции COMMIT или ROLLBACK или после инструкции SET AUTOCOMMIT OFF.

Дополнительные сведения о транзакциях в Microsoft Fabric см. в разделе «Транзакции» в Microsoft Fabric.

Синтаксис

BEGIN TRANSACTION [;] COMMIT [ TRAN | TRANSACTION | WORK ] [;] ROLLBACK [ TRAN | TRANSACTION | WORK ] [;] SET AUTOCOMMIT < ON | OFF >[;] SET IMPLICIT_TRANSACTIONS < ON | OFF >[;] 

Аргументы

BEGIN TRANSACTION
Отмечает начальную точку явной транзакции.

COMMIT [ WORK ]
Отмечает завершение явной транзакции или транзакции с автофиксацией. Эта инструкция вызывает изменения в транзакции, чтобы всегда быть зафиксированной в базе данных. Инструкция COMMIT идентична инструкциям COMMIT WORK, COMMIT TRAN и COMMIT TRANSACTION.

ROLLBACK [ WORK ]
Выполняет откат транзакции на начало транзакции. Никакие изменения транзакции не фиксируются в базе данных. Инструкция ROLLBACK идентична инструкциям ROLLBACK WORK, ROLLBACK TRAN и ROLLBACK TRANSACTION.

SET AUTOCOMMIT < ON | OFF >
Определяет метод запуска и завершения транзакций.

DNS
Каждая инструкция выполняется в своей транзакции, явные инструкции COMMIT или ROLLBACK не требуются. Явные транзакции разрешены, когда для параметра AUTOCOMMIT установлено значение ON.

ВЫКЛ.
Azure Synapse Analytics и Microsoft Fabric автоматически инициирует транзакцию, если транзакция еще не выполняется. Все последующие инструкции выполняются в рамках транзакции, и инструкции COMMIT или ROLLBACK необходимы для определения результата транзакции. Как только транзакция фиксирует или откатывается в этом режиме, режим остается OFF, запускается новая транзакция. Явные транзакции не разрешены, если AUTOCOMMIT имеет значение OFF.

Если изменить параметр AUTOCOMMIT в активной транзакции, этот параметр не повлияет на текущую транзакцию и вступит в силу только после завершения транзакции.

Если для параметра AUTOCOMMIT установлено значение ON, выполнение другой инструкции SET AUTOCOMMIT ON не будет иметь результата. Подобным образом, если для параметра AUTOCOMMIT установлено значение OFF, выполнение другой инструкции SET AUTOCOMMIT OFF не будет иметь результата.

SET IMPLICIT_TRANSACTIONS < ON | OFF >
Включает те же режимы, что и SET AUTOCOMMIT. Присвоение параметру SET IMPLICIT_TRANSACTIONS значения ON устанавливает для соединения режим неявных транзакций. Значение OFF возвращает подключение в режим автофиксации. Дополнительные сведения см. в статье SET IMPLICIT_TRANSACTIONS (Transact-SQL).

Разрешения

Для выполнения инструкций, связанных с транзакциями, не нужны конкретные разрешения. Разрешения необходимы для запуска инструкций внутри транзакции.

Обработка ошибок

Если выполнить инструкции COMMIT или ROLLBACK без активной транзакции, возникает ошибка.

Если выполнить инструкцию BEGIN TRANSACTION во время выполнения транзакции, возникает ошибка. Это может произойти, если инструкция BEGIN TRANSACTION выполняется после успешного запуска инструкции BEGIN TRANSACTION или для сеанса установлено SET AUTOCOMMIT OFF.

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

Если ошибка инструкции во время выполнения возникает в пакете, Azure Synapse Analytics и Microsoft Fabric работают в соответствии с sql ServerXACT_ABORT задано значение ON , а вся транзакция откатывается. Дополнительные сведения о параметре XACT_ABORT см. в разделе SET XACT_ABORT (Transact-SQL).

Общие замечания

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

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

Если сеанс закрывается до завершения транзакции, транзакция откатывается.

Управление режимами транзакций выполняется на уровне сеанса. Например, если один сеанс запускает явную транзакцию или устанавливает для параметра AUTOCOMMIT значение OFF или для параметра IMPLICIT_TRANSACTIONS значение ON, это не влияет на режимы транзакции в других сеансах.

Ограничения

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

Azure Synapse Analytics и Microsoft Fabric не имеют механизма совместного использования транзакций. Это означает, что в любой момент времени только один сеанс может работать с транзакцией в системе.

Режим блокировки

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

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

Примеры:

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

BEGIN TRANSACTION; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; COMMIT; 

B. Откат транзакции

В приведенном ниже примере демонстрируется результат отката транзакции. В этом примере инструкция ROLLBACK приведет к откату инструкции INSERT, но созданная таблица будет по-прежнему существовать.

CREATE TABLE ValueTable (id INT); BEGIN TRANSACTION; INSERT INTO ValueTable VALUES(1); INSERT INTO ValueTable VALUES(2); ROLLBACK; 

C. Настройка параметра AUTOCOMMIT

В следующем примере для параметра AUTOCOMMIT устанавливается значение ON .

SET AUTOCOMMIT ON; 

В следующем примере для параметра AUTOCOMMIT устанавливается значение OFF .

SET AUTOCOMMIT OFF; 

D. Использование неявных транзакций из нескольких инструкций

SET AUTOCOMMIT OFF; CREATE TABLE ValueTable (id INT); INSERT INTO ValueTable VALUES(1); INSERT INTO ValueTable VALUES(2); COMMIT; 

Следующие шаги

• SET IMPLICIT_TRANSACTIONS (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• @@TRANCOUNT (Transact-SQL)
• Транзакции, вставка и изменение данных в хранилище Microsoft Fabric

Обратная связь

Были ли сведения на этой странице полезными?

Обратная связь

Отправить и просмотреть отзыв по

Транзакции

Как уже знаете, данные в базе данных обычно используются совместно многими прикладными пользовательскими программами (приложениями). Ситуация, когда несколько прикладных пользовательских программ одновременно выполняют операции чтения и записи одних и тех же данных, называется одновременным конкурентным (параллельным) доступом (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 WHERE 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 запускает транзакцию. Синтаксис этой инструкции выглядит следующим образом:

В параметре 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). Все записи журнала, являющиеся частью определенной транзакции, связаны друг с другом, чтобы можно было найти все части этой транзакции для операции отмены или повтора.