Что такое транзакция в sql
Перейти к содержимому

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

  • автор:

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

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

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

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

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

Транзакции (Transact-SQL)

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

SQL Server работает в перечисленных ниже режимах транзакций.

Автоматическое принятие транзакций
Каждая отдельная инструкция является транзакцией.

Явные транзакции
Каждая транзакция явно начинается с инструкции BEGIN TRANSACTION и явно заканчивается инструкцией COMMIT или ROLLBACK.

Неявные транзакции
Новая транзакция неявно начинается, когда предыдущая транзакция завершена, но каждая транзакция явно завершается инструкцией COMMIT или ROLLBACK.

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

Особые замечания в отношении продуктов Data Warehouse см. в разделе Транзакции (Azure Synapse Analytics).

в этом разделе

SQL Server предоставляет перечисленные ниже инструкции транзакций.

Транзакционность — Основы реляционных баз данных

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

Запросы внутри транзакции

Допустим, у нас есть таблица счетов accounts, в которой две записи:

id user_id amount
1 10 100
2 30 100

Процесс перевода можно представить так:

    Получаем количество денег пользователя:

SELECT amount FROM accounts WHERE user_id = 10; 
UPDATE accounts SET amount = amount - 50 WHERE user_id = 10; 
UPDATE accounts SET amount = amount + 50 WHERE user_id = 30; 

В результате таблица примет следующий вид:

id user_id amount
1 10 50
2 30 150

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

id user_id amount
1 10 50
2 30 100

Такое может произойти не только с деньгами, но и в большинстве других ситуаций. Приложения по возможности должны находиться в согласованном состоянии. В распределенных системах это невозможно, добиться этого можно с помощью механизма транзакций. Мы не будем подробно разбирать эту тему, но вы можете узнать о ней больше, изучив CAP-теорему и Eventual Consistency .

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

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

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

BEGIN; SELECT amount FROM accounts WHERE user_id = 10; UPDATE accounts SET amount = amount - 50 WHERE user_id = 10; UPDATE accounts SET amount = amount + 50 WHERE user_id = 30; COMMIT; 

Транзакции в PostgreSQL — это блок запросов, который обрамляется запросами:

  • BEGIN — открытие транзакции
  • COMMIT — закрытие транзакции

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

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

BEGIN; UPDATE accounts SET amount = amount - 50 WHERE user_id = 10; ROLLBACK; 

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

Требования к транзакционной системе

В информатике есть набор требований к транзакционной системе, которые гарантируют ее надежность — ACID. К ним относятся:

  • Atomicity (Атомарность)
  • Consistency (Согласованность)
  • Isolation (Изолированность)
  • Durability (Устойчивость)

Разберем каждое требование подробнее

Atomicity (Атомарность)

Любая транзакция не может быть частично завершена — она либо выполнена, либо нет.

Consistency (Согласованность)

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

В примере выше снятие денег с одного счета приводит к тому, что данные рассинхронизированы. Но когда транзакция завершается, этого нет.

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

Isolation (Изолированность)

Когда транзакция выполняется, параллельные транзакции не должны оказывать влияния на ее результат. Ни одна транзакция не может увидеть изменения, которые сделаны другими незавершенными транзакциями. Изолированность — дорогое требование, поэтому в реальных БД существуют режимы, которые изолируют транзакцию не полностью — уровни изолированности Repeatable Read и ниже.

Durability (Устойчивость)

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

Открыть доступ

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

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов

Наши выпускники работают в компаниях:

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

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