Транзакция в SQL - это единица работы или последовательность действий, выполненных в логическом порядке пользователем или программой, которая работает с базой данных. Транзакция может включать один или несколько запросов SQL, которые изменяют данные в базе данных. Транзакция должна обладать свойствами ACID: атомарность, согласованность, изолированность и долговечность. Это означает, что транзакция должна быть выполнена целиком или отменена, не нарушая целостности данных, не зависеть от других транзакций и сохранять свои результаты даже при сбоях системы.
Для управления транзакциями в SQL используются специальные команды, такие как BEGIN TRANSACTION, COMMIT, ROLLBACK и SAVEPOINT. Они позволяют начинать, завершать, отменять и частично откатывать транзакции. Транзакции применяются только к операциям с данными (DML), таким как INSERT, UPDATE и DELETE. Они не должны быть использованы для операций с объектами базы данных (DDL), таких как CREATE, ALTER и DROP.
В зависимости от СУБД и типа операции DDL, использование транзакций с операциями DDL в SQL может быть возможным или нет. Например, в PostgreSQL вы можете использовать транзакции с операциями DDL, такими как CREATE, ALTER и DROP. Однако в некоторых случаях это может нарушить изоляцию транзакций, например, операция TRUNCATE будет видна из других транзакций сразу, но при откате ее эффект будет отменен. В SQL Server вы также можете использовать транзакции с операциями DDL, но некоторые из них могут быть минимально протоколированы или не протоколированы вовсе. В MySQL с движком InnoDB вы не можете использовать транзакции с операциями DDL, так как они автоматически фиксируются при выполнении с целью упрощения реализации и повышения производительности.
На каждом собеседовании разработчикам задают вопрос про то что что такое транзакция, что будет если в ней повстречается ошибка и как она откатывается, но мало кто знает что транзакции можно откатывать частично. Этим вопросом работодатели могут заваливать кандидатов и сбивать им корону с головы (то есть зарплату), а кандидатам заваливать собеседующего, но после этого на работу вас не возьмут т.к. вы "слишком умный" и по их мнению хотите подсидеть собеседующего нчальнчика.
Частичный откат транзакций SQL - это процесс, при котором можно отменить только часть изменений, сделанных в рамках транзакции, а не всю транзакцию целиком. Для этого можно использовать точки сохранения (savepoints), которые позволяют установить временные метки внутри транзакции и выполнить откат до определенной точки сохранения при необходимости. Например, если вы хотите выполнить три операции обновления в одной транзакции, но при выполнении второй операции возникла ошибка, вы можете откатить только эту операцию, а не всю транзакцию.
Для создания точки сохранения в SQL Server можно использовать инструкцию SAVE TRANSACTION, указав имя точки сохранения.
SAVEPOINT имя_точки_сохранения;
Для выполнения отката до точки сохранения можно использовать инструкцию ROLLBACK TRANSACTION, указав то же имя.
ROLLBACK TO имя_точки_сохранения;
Для фиксации всех изменений, сделанных в транзакции, можно использовать инструкцию COMMIT TRANSACTION.
COMMIT TRANSACTION;
Пример
Начать транзакцию.
BEGIN TRANSACTION;
Создать таблицу для демонстрации.
CREATE TABLE TestTable (id int, name varchar(50));
Вставить первую строку и создать точку сохранения.
INSERT INTO TestTable VALUES (1, 'Alice');
SAVE TRANSACTION SavePoint1;
Вставить вторую строку и создать точку сохранения.
INSERT INTO TestTable VALUES (2, 'Bob');
SAVE TRANSACTION SavePoint2;
Вставить третью строку и создать точку сохранения.
INSERT INTO TestTable VALUES (3, 'Charlie');
SAVE TRANSACTION SavePoint3;
Попытаться вставить четвертую строку с дублирующимся id.
Это вызовет ошибку и прервет транзакцию.
INSERT INTO TestTable VALUES (1, 'David');
Откатить только до второй точки сохранения.
Это отменит вставку третьей и четвертой строки.
ROLLBACK TRANSACTION SavePoint2;
Фиксировать оставшиеся изменения.
COMMIT TRANSACTION;
Проверить содержимое таблицы.
SELECT * FROM TestTable;
Результат:
id name
1 Alice
2 Bob