SQL-Ex blog

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

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

Транзакции могут мыслиться как обертка вокруг множества операторов 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”. Мы хотим обновить номер телефона клиента с ID = 12345 и вставить нового клиента в таблицу с ID = 67890.

Чтобы эти две операции рассматривались как единое атомарное действие, мы могли бы использовать такую транзакцию:

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 для начала новой транзакции. Затем мы обновляем номер телефона клиента с ID = 12345 и вставляем нового клента с ID = 67890. Наконец мы используем оператор 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