Транзакции

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

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;

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

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 проверяется, не стал ли уровень запасов отрицательным. Если так, мы откатываем транзакцию и печатаем сообщение об ошибке. Если нет, транзакция фиксируется и печатается сообщение об успешном выполнении.

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

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

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

Блокировка таблиц
Не позволяет нескольким пользователям одновременно изменять данные в одной таблице.
Блокировка страниц
Не позволяет нескольким пользователям изменять данные в одной и той же странице (страница — это сегмент памяти, обычно в диапазоне от 2 до 16 Кбайт) таблицы одновременно.
Блокировка строк
Не позволяет нескольким пользователям одновременно изменять одну и ту же строку в таблице.

Точки сохранения транзакции

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

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

SAVEPOINT my_savepoint;

Для отката к определённой точке сохранения просто вводится команда ROLLBACK, за которой следуют ключевые слова TO SAVEPOINT и имя точки сохранения, например:

START TRANSACTION;

-- Создаем точку сохранения перед изменением баланса первого пользователя
SAVEPOINT before_updating_user_1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- Проверка условия для первого пользователя
-- например, проверяем логику бизнес-правил

-- Здесь мы предполагаем, что условие не выполнилось, и нам нужно отменить изменение баланса
ROLLBACK TO SAVEPOINT before_updating_user_1;

-- Обновляем баланс для второго пользователя
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

-- Завершаем транзакцию
COMMIT;

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

Несмотря на название, при создании точки сохранения ничего не сохраняется. Чтобы ваши изменения в рамках транзакции стали постоянными, необходимо выполнить команду COMMIT.
При выполнении отката транзакции без указания конкретной точки сохранения, все ранее установленные точки сохранения будут проигнорированы, и будет произведён откат всей транзакции.

Изоляции

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

READ COMMITTED: Это установка по умолчанию для большинства запросов SQL Server. Она определяет, что транзакция в текущем сеансе не может читать данные, которые были модифицированы другой транзакцией.

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

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

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

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

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