Вопросы по теме «Транзакции в SQL: ACID, COMMIT и ROLLBACK, уровни изоляции и аномалии»
Сначала изучите урок: Транзакции в SQL: ACID, COMMIT и ROLLBACK, уровни изоляции и аномалии
Всего: 9 вопросов
1. Что такое транзакция в SQL и что означают свойства ACID?
Что такое транзакция в 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, 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 и что происходит, если команда внутри транзакции завершилась ошибкой?
Когда 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, какой действует по умолчанию в 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 и согласованное чтение в 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 и когда они нужны?
Чем 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) и как сделать их редкими?
Чем дедлок (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?
Соединение 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.