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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Допустим, у нас есть таблица счетов 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 студентов

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

Руководство по SQL. Транзакции.

Транзакция является рабочей единицей работы с базой данных (далее – БД). Это последовательность операций, выполняемых в логическом порядке пользователем, либо программой, которая работает с БД.

Мы можем сказать, что транзакция – это распространение изменений в БД. Например, если мы создаём, изменяем или удаляем запись, то мы выполняем транзакцию. Крайне важно контролировать транзакции для гарантирования.

Основные концепции транзакции описываются аббревиатурой ACID – Atomicity, Consistency, Isolation, Durability (Атомарность, Согласованность, Изолированность, Долговечность).

Атомарность

Атомарность гарантирует, что любая транзакция будет зафиксирована только целиком (полностью). Если одна из операций в последовательности не будет выполнена, то вся транзакция будет отменена. Тут вводится понятие “отката” (rollback). Т.е. внутри последовательности будут происходить определённые изменения, но по итогу все они будут отменены (“откачены”) и по итогу пользователь не увидит никаких изменений.

Согласованность

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

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

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

Долговечность

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

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

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

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

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

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

 mysql> SET autocommit=0; 

Предположим, что у нас есть таблица developers, которая содержит следующие записи:

 +----+-------------------+-----------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+-----------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | C++ | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 7 | Ivan Ivanov | C# | 1 | 900 | | 8 | Ludmila Geiko | UI/UX | 2 | 1800 | +----+-------------------+-----------+------------+--------+ 

Удалим всех С++ разработчиков с помощью следующей команды:

 mysql> DELETE FROM developers WHERE SPECIALTY = 'C++'; mysql> COMMIT; 

В результате выполнения данного запроса наша таблица будет содержать следующие записи:

 +----+-------------------+-----------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+-----------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 7 | Ivan Ivanov | C# | 1 | 900 | | 8 | Ludmila Geiko | UI/UX | 2 | 1800 | +----+-------------------+-----------+------------+--------+ 

Теперь попробуем выполнить команду ROLLBACK:

 mysql> ROLLBACK; 

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

 +----+-------------------+-----------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+-----------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | C++ | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 6 | Ludmila Geiko | UI/UX | 2 | 1800 | | 7 | Ivan Ivanov | C# | 1 | 900 | +----+-------------------+-----------+------------+--------+ 

Как мы видим, запись С++ разработчика вновь в таблице.

Теперь постараемся разобраться с SAVEPOINT.
Для начала создадим точку сохранения, используя следующий запрос:

 mysql> SAVEPOINT SP1; 

Теперь выполним следующие запросы:

 mysql> DELETE FROM developers WHERE OK, 1 row affected (0.00 sec) mysql> DELETE FROM developers WHERE OK, 1 row affected (0.02 sec) mysql> DELETE FROM developers WHERE OK, 1 row affected (0.00 sec) 

На данный момент наша таблица содержит следующие записи:

 +----+-------------------+-----------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+-----------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | C++ | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | +----+-------------------+-----------+------------+--------+ 

Теперь мы вернёмся к точке сохранения SP1 с помощью команды:

 mysql> ROLLBACK TO SP1; 

После выполнения данного запроса, наша таблица будет хранить следующие записи:

 +----+-------------------+-----------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+-----------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | C++ | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 6 | Ludmila Geiko | UI/UX | 2 | 1800 | | 7 | Ivan Ivanov | C# | 1 | 900 | +----+-------------------+-----------+------------+--------+ 

Как мы видим, мы откатились к состоянию таблицы на момент создания точки сохранения SP1.

Теперь, когда нам больше не нужна данная точка сохранения, мы можем её освободить:

 mysql> RELEASE SAVEPOINT SP1; 

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

 SET TRANSACTION READ ONLY; 

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

 SET TRANSACTION READ WRITE; 

На этом мы заканчиваем изучение SQL транзакций.
В следующей статье мы рассмотрим функции даты.