Может ли любая операция в SQL содержаться в транзакции и какие есть исключения

Что проверяют

Знаете ли вы, что ответ зависит от СУБД, и не считаете ли транзакцию обёрткой, которая откатывает что угодно. Вопрос с подвохом, правильное начало это «в разных базах по-разному». Следом просят пример: что будет, если внутри транзакции выполнить CREATE TABLE, а потом ROLLBACK.

Как отвечать

Сначала разделите операции по типу:

• DML: INSERT, UPDATE, DELETE, MERGE транзакционны везде и откатываются полностью;
• DDL в PostgreSQL: транзакционный, CREATE TABLE и ALTER TABLE откатываются вместе с остальным, поэтому миграции тут накатываются одним куском. Исключения есть: CREATE DATABASE, CREATE TABLESPACE, VACUUM, ALTER SYSTEM, CREATE INDEX CONCURRENTLY и REINDEX CONCURRENTLY в транзакции не работают;
• DDL в MySQL и Oracle: вызывает неявную фиксацию текущей транзакции. Откатить CREATE TABLE нельзя, TRUNCATE тоже фиксируется неявно. В MySQL 8 отдельная DDL-операция атомарна сама по себе, но в вашу транзакцию она всё равно не входит;
• вне отката в любой базе: значения последовательностей, nextval назад не возвращается, отсюда дыры в идентификаторах. Плюс всё за пределами базы: отправленное письмо, вызов внешнего API, записанный файл.

Полезно добавить про вложенность: вложенных транзакций в SQL нет, есть SAVEPOINT и частичный откат до него. В Spring PROPAGATION_NESTED реализован именно через сейвпоинты, а REQUIRES_NEW приостанавливает текущую транзакцию и открывает физически отдельную.

Где спотыкаются

Переносят опыт одной СУБД на все. «DDL нельзя в транзакции» верно для Oracle и MySQL и неверно для PostgreSQL. Категоричный ответ здесь и проваливает вопрос.

Считают, что @Transactional в Spring откатит всё при любом исключении. По умолчанию откат идёт на RuntimeException и Error, а проверяемое исключение транзакцию зафиксирует. Плюс аннотация не сработает при вызове метода изнутри того же класса, минуя прокси.

Забывают про блокировки. ALTER TABLE внутри длинной транзакции держит эксклюзивную блокировку до самого коммита, и на нагруженной таблице это останавливает приложение целиком, поэтому в миграциях выставляют lock_timeout и режут изменения на короткие шаги.