Для обеспечения атомарности любая последовательность операций выполняется в рамках своей транзакции, т.е. все изменения выполняемые транзакцией либо применяются - COMMIT, либо откатываются - ROLLBACK. По-умолчанию каждая новая сессия работает в режиме автоматического коммита. В этом режиме любое SQL-выражение либо будет автоматически закоммичено (при отсутсвии ошибок), либо будет откатано назад (при возникновении ошибки поведение коммита и роллбека зависит от типа ошибки, см. Error Handling). Чтобы выполнять последовательность операций без автокоммита, можно либо воспользоваться выражениями START TRANSACTION / BEGIN, либо выключить автокоммит с помощью выражения SET autocommit = 0. При выключенном автокоммите транзакция будет открыта до тех пор пока явно не выполнится COMMIT или ROLLBACK, которые завершат текущую тразакцию и начнут новую. При выполнении COMMIT или ROLLBACK все блокировки установленные внутри транзакции снимаются.
Изоляция транзакций в InnoDB реализована в соответсвии со стандартом SQL-92 в виде четырех уровней: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. По-умолчанию используется REPEATABLE READ, т.к. он обычно является более подходящим под требования, чем остальные. Реже используется READ COMMITED, в ситуациях когда нужно повысить concurrency и можно немного забить на изоляцию. READ UNCOMMITED и SERIALIZABLE используются редко, т.к. это крайности: либо никакой изоляции, либо полная изоляция. Пользователь может изменить уровень изоляции для текущей сессии или для всех новых сессий с помощью выражения SET TRANSACTION. Перед тем как рассмотреть что из себя представляют эти уровни изоляции, стоит кратко пояснить что чтение бывает двух типов: консистентное неблокирующее (Consistent Nonlocking Reads - обычные SELECT) и блокирущее (Locking Reads - SELECT ... FOR UPDATE и SELECT ... LOCK IN SHARE MODE). О типах чтения и блокировках поговорим подробнее чуть позже. Итак, рассмотрим что же из себя представляют уровни изоляции транзакций:
- READ UNCOMMITTED. Самый низкий уровень изоляции. При этом уровне изоляции для неблокирующих чтений возможны ситуации когда будут использованы ранние версии данных. Такие чтения будут неконсистентны, их еще называют "грязным чтением". В остальном этот уровень работает так же как READ COMMITED.
- READ COMMITED. При этом уровне изоляции каждое неблокирующее чтение работает со своим собственным свежим снимком данных, в том числе и в рамках одной транзакции. При выполнении блокирующих чтений, UPDATE и DELETE будут блокироваться только индексные записи (record lock) и не будут блокираться интервалы (gaps). Поэтому данный уровень изоляции позволяет вставлять новые строки в интервалы перед индексными записями. Такие строки называют фантомными. Уровень REPEATABLE READ решает данную проблему.
- REPEATABLE READ. При этом уровне изоляции каждое неблокирующие чтение (так же как и при READ COMMITED) работает со своим снимком данных, но снимок этот не обновляется в рамках одной транзакции, а используется тот что был получен при первом чтении. Т.е. каждое неблокирующее чтение в рамках одной транзакции является консистентным по отношению к остальным. Поведение блокирующего чтения, UPDATE и DELETE зависит от уникальности индекса и уникальности условия поиска. Когда и индекс и условие поиска уникально - блокируется только найденная индексная запись и не блокируется интервал перед ней. В остальных случаях InnoDB будет блокировать некоторый индексный интервал, используя так называемую интервальную блокировку gap lock или блокировку следующего ключа next-key lock. Блокировки интервалов и следующего ключа позволяют избежать фантомных чтений (Phantom Problem).
- SERIALIZABLE. Этот уровень аналогичен REPEATABLE READ, но InnoDB будет неявно преобразовывать все неблокирующие чтения (
SELECT), в блокирующие (SELECT ... LOCK IN SHARE MODE) если автокоммит выключен. В случае если автокоммит включен, неблокирующее чтение будет единственным оператором в транзакции, т.е. понятно что транзакция будет read only, соответственно не имеет смысла блокировать записи такой транзакцией.