Вопросы по теме «Транзакции в SQL: ACID, COMMIT и ROLLBACK, уровни изоляции и аномалии»

Всего: 9 вопросов

1. 

Что такое транзакция в SQL и что означают свойства ACID?

Транзакция — группа команд, которую база выполняет как одно целое: либо все изменения фиксируются COMMIT, либо все отменяются ROLLBACK, и промежуточное состояние не видно другим. В MySQL транзакции поддерживает движок InnoDB.

  • Атомарность — всё или ничего; InnoDB хранит прежние версии строк в журнале отмены (undo log).
  • Согласованность — ограничения PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK не нарушены; проверяются при каждой команде.
  • Изоляция — параллельные транзакции видят друг друга в пределах уровня изоляции; MVCC для обычного SELECT и блокировки строк для записи.
  • Долговечность — зафиксированное переживёт сбой; журнал повтора (redo log) пишется на диск при COMMIT.

Согласованность СУБД обеспечивает не полностью: правило «сколько списали у Васи, столько пришло Семёну» соблюдает приложение.

2. 

Как управлять транзакцией в MySQL: autocommit, START TRANSACTION, COMMIT, ROLLBACK и SAVEPOINT?

По умолчанию MySQL работает в режиме автофиксации (autocommit = 1, проверка — SELECT @@autocommit;): каждая команда — отдельная транзакция, которая фиксируется сразу.

  • START TRANSACTION или BEGIN открывает транзакцию, автофиксация отключается до её конца.
  • COMMIT фиксирует изменения, и они становятся видны другим сессиям; ROLLBACK отменяет все изменения транзакции.
  • SAVEPOINT имя ставит точку сохранения, ROLLBACK TO SAVEPOINT имя отменяет изменения после неё, но не закрывает транзакцию, RELEASE SAVEPOINT имя удаляет точку.
  • SET autocommit = 0 — каждая следующая команда начинает транзакцию, которую нужно закрыть COMMIT или ROLLBACK.

Внутри транзакции сессия видит свои незафиксированные изменения, другие сессии — прежние данные до COMMIT. Транзакции работают только с InnoDB: изменения в таблице MyISAM ROLLBACK не вернёт.

3. 

Когда MySQL фиксирует транзакцию без COMMIT и что происходит, если команда внутри транзакции завершилась ошибкой?

Неявный COMMIT выполняют команды DDL (CREATE TABLE, ALTER TABLE, DROP, TRUNCATE), повторный START TRANSACTION (вложенных транзакций в MySQL нет) и SET autocommit = 1 при открытой транзакции. После них ROLLBACK уже не вернёт сделанное.

Ошибка не откатывает транзакцию. MySQL отменяет только упавшую команду, всё сделанное до неё остаётся, транзакция открыта. Пример: зачислили Семёну 100 баллов, списание у Васи (30 баллов) дало ERROR 3819: Check constraint 'chk_points_non_negative' is violated, а у Семёна в транзакции уже 170. COMMIT зафиксировал бы баллы ниоткуда, поэтому после ошибки выполняют ROLLBACK, а в Java вызывают rollback() в catch.

Всю транзакцию MySQL сам откатывает в частом случае дедлока (1213); при нарушении ограничения, синтаксической ошибке и таймауте блокировки (1205) отменяется одна команда.

4. 

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

  • Потерянное обновление (lost update) — две транзакции прочитали views = 10, каждая записала 11, второе изменение затёрло первое. В InnoDB возможно даже на REPEATABLE READ; лечится атомарным UPDATE ... SET views = views + 1 или SELECT ... FOR UPDATE.
  • Грязное чтение (dirty read) — чтение чужих незафиксированных изменений, которые могут быть откачены. Только на READ UNCOMMITTED.
  • Неповторяемое чтение (non-repeatable read) — повторное чтение той же строки даёт другое значение: между чтениями её изменили и зафиксировали.
  • Фантомное чтение (phantom read) — повторный запрос с тем же условием возвращает другой набор строк: другая транзакция вставила или удалила подходящую строку.

Какие аномалии возможны, определяет уровень изоляции транзакции.

5. 

Какие уровни изоляции транзакций есть в SQL, какой действует по умолчанию в MySQL и как его изменить?

Стандарт SQL задаёт четыре уровня от слабого к строгому:

  • READ UNCOMMITTED — возможны все аномалии, включая грязное чтение;
  • READ COMMITTED — нет грязного чтения, возможны неповторяемое и фантомное;
  • REPEATABLE READ — нет грязного и неповторяемого чтения; фантомы по стандарту возможны, но в InnoDB обычный SELECT их не видит;
  • SERIALIZABLE — аномалий нет.

В MySQL с InnoDB по умолчанию REPEATABLE READ, в PostgreSQL, Oracle и SQL Server — READ COMMITTED. Чем строже уровень, тем больше ожиданий блокировок.

Текущий уровень: SELECT @@transaction_isolation; (старая tx_isolation удалена в MySQL 8.0). Изменить: SET TRANSACTION ISOLATION LEVEL ... — для следующей транзакции, SET SESSION ... — для сессии, SET GLOBAL ... — для новых сессий. Внутри открытой транзакции команда даёт ERROR 1568.

6. 

Что такое MVCC и согласованное чтение в InnoDB и чем REPEATABLE READ отличается от READ COMMITTED?

MVCC (multi-version concurrency control) — механизм, при котором обычный SELECT не ставит блокировок и не ждёт чужих транзакций, а читает снимок (consistent read): версии строк, зафиксированные к моменту его создания. Старые версии берутся из журнала отмены.

  • READ COMMITTED создаёт снимок заново для каждого SELECT, поэтому видны свежие изменения.
  • REPEATABLE READ создаёт снимок при первом чтении в транзакции, а не при START TRANSACTION, и все следующие SELECT читают его: чужие изменённые и новые строки не видны.

UPDATE, DELETE и блокирующие чтения снимок не используют и работают с последней зафиксированной версией. Поэтому в REPEATABLE READ SELECT не видит тему, вставленную другой сессией, а UPDATE той же транзакции её меняет (Rows matched: 3), после чего тема появляется и в SELECT: свои изменения видны в снимке.

7. 

Чем SELECT ... FOR UPDATE отличается от SELECT ... FOR SHARE и когда они нужны?

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

  • FOR UPDATE — исключающая блокировка (X): другие транзакции не могут изменить строки или заблокировать их. Нужна, чтобы прочитать значение и затем изменить его.
  • FOR SHARE (с MySQL 8.0, раньше LOCK IN SHARE MODE) — разделяемая блокировка (S): читать с FOR SHARE могут все, менять — никто. Нужна, чтобы строка не изменилась, пока вы её используете.

Обычный SELECT эти блокировки не останавливают. В режиме автофиксации FOR UPDATE бесполезен: блокировка снимается сразу после чтения, поэтому сначала START TRANSACTION. Для простого счётчика блокировка избыточна: UPDATE topics SET views = views + 1 решает задачу одной командой. Не ждать помогают NOWAIT (сразу ошибка 3572) и SKIP LOCKED.

8. 

Чем дедлок (ERROR 1213) отличается от таймаута ожидания блокировки (ERROR 1205) и как сделать их редкими?

Дедлок — две транзакции ждут друг друга: каждая держит блокировку, нужную другой. InnoDB обнаруживает его сразу и откатывает одну транзакцию целиком с ERROR 1213 (40001): Deadlock found when trying to get lock; её нужно повторить с начала. Подробности — в SHOW ENGINE INNODB STATUS, секция LATEST DETECTED DEADLOCK.

Таймаут — обычное ожидание без цикла дольше innodb_lock_wait_timeout (по умолчанию 50 секунд): ERROR 1205 (HY000): Lock wait timeout exceeded. Откатывается только команда (если не включён innodb_rollback_on_timeout), поэтому нужно сделать ROLLBACK, повторить и найти долгую транзакцию.

  • Короткие транзакции: никаких HTTP-запросов и ожидания пользователя между START TRANSACTION и COMMIT.
  • Блокировки в одном порядке: при переводе баллов сначала строка с меньшим user_id.
  • Повтор транзакции 2–3 раза при SQLState 40001.
9. 

Как работать с транзакциями в JDBC?

Соединение JDBC по умолчанию работает в режиме автофиксации. Для нескольких команд в одной транзакции:

  • conn.setAutoCommit(false) перед командами;
  • conn.commit() после всех команд;
  • conn.rollback() в блоке catch — иначе открытая транзакция останется на соединении и зафиксируется следующим commit() вместе с чужими изменениями;
  • conn.setAutoCommit(true) в finally — важно, если соединение вернётся в пул.

Уровень изоляции задаёт conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED), точку сохранения — conn.setSavepoint("имя") и conn.rollback(savepoint). При дедлоке Connector/J выбрасывает SQLTransactionRollbackException с getSQLState() = "40001" — по нему решают, повторить ли транзакцию. В Spring те же вызовы скрывает @Transactional.

Страница 1 из 1