Назначение транзакционной модели InnoDB заключается в том, чтобы совместить лучшие свойства многовариантной базы данных и традиционной двухфазной блокировки. Для таблиц InnoDB осуществляется блокировка на уровне строки и запросы по умолчанию запускаются как целостное считывание без блокировок, подобно тому, как это реализовано в Oracle. Хранение таблицы блокировок InnoDB организовано настолько экономично, что нет необходимости в расширении блокировки: обычно несколько пользователей могут блокировать любую строку или любой набор строк в базе данных, не занимая всю память, доступную для InnoDB.
В таблицах InnoDB все действия пользователей осуществляются при помощи транзакций. Если в MySQL используется режим автоматической фиксации, то для каждого оператора SQL будет создаваться отдельная транзакция. MySQL всегда открывает новое соединение с включенным режимом автоматической фиксации.
Если режим автоматической фиксации отключен при помощи SET AUTOCOMMIT = 0, то мы предполагаем, что у пользователя постоянно имеется открытая транзакция. Если он выполняет оператор SQL COMMIT или ROLLBACK, которые завершают текущую транзакцию, сразу же запускается новая транзакция. Оба упомянутых оператора снимают все блокировки InnoDB, которые были установлены во время выполнения текущей транзакции. Оператор COMMIT означает, что изменения, внесенные во время выполнения текущей транзакции, принимаются и становятся видимыми для других пользователей. Оператор ROLLBACK отменяет все изменения, внесенные текущей транзакцией.
Если в соединении установлено AUTOCOMMIT = 1, то пользователь, тем не менее, может использовать транзакции, начиная их с BEGIN и заканчивая при помощи COMMIT или ROLLBACK.
В терминах описания уровней изоляции транзакций (SQL-1992), InnoDB по умолчанию использует REPEATABLE READ. Начиная с версии 4.0.5, InnoDB предлагает все 4 уровня изоляции описанные в стандарте SQL-1992. Вы можете установить уровень изоляции по умолчанию для всех соединений в секции [mysqld] файла my.cnf:
Обратите внимания что названия уровней изоляции пишется без дефиса в SQL-выражении. Если вы указали ключевое слово GLOBAL в указанном выше выражении, оно будет определять уровень изоляции для новых соединений, но не будет иметь эффекта для старых соединений. Любой пользователь может изменить уровень изоляции для своей сессии, даже внутри самой транзакции. В версиях старше 3.23.50 SET TRANSACTION не оказывает эффекта на таблицы InnoDB. В версиях старше 4.0.5 вы можете использовать только REPEATABLE READ и SERIALIZABLE.
Вы можете получить информацию об уровне изоляции, глобальном или для текущего соединения:
В блокировке уровня строки InnoDB использует так называемую блокировку следующего ключа. Это означает, что кроме индексных записей InnoDB может также блокирует "интервал" перед индексной записью для блокировки вставок другими пользователями непосредственно перед индексной записью. Блокировка следующего ключа означает блокировку, которая ставится на индексную запись и интервал перед ней. Блокировка интервала означает только блокировку интервала перед некоторыми индексными записями.
Подробное описание каждого уровня изоляции в InnoDB:
READ UNCOMMITTED Также называется "грязным чтением": неблокирующиеся выборки (SELECT) выполняются таким образом, что мы не видим возможные ранние версии записи; таким образом они "несогласованно" читаются в этом уровне изоляции; в остальных случаях этот уровень работает также как и READ COMMITTED.
READ COMMITTED Нечто похожее на уровень изоляции Oracle. Все выражения SELECT ... FOR UPDATE и SELECT ... LOCK IN SHARE MODE блокируют только индексные записи и не блокируют интервал перед ними. Поэтому они позволяют свободно добавлять новые записи после заблокированных. UPDATE и DELETE, которые используют уникальный индекс и уникальные условия поиска, блокируют только найденную индексную запись, и не блокируют интервал перед ней. Но в UPDATE и DELETE диапазонного типа в InnoDB должны установить блокировку следующего ключа или интервальную блокировку и блокировать добавления другими пользователями в интервал, покрытый диапазоном. Это необходимо, т.к. "фантомные строки" должны быть блокированы для успешной работы репликации и восстановления в MySQL. Согласованное чтение работает как и в Oracle: каждое согласованное чтение, даже внутри одной транзакции, устанавливает и читает свой собственный снимок.
REPEATABLE READ Этот уровень изоляции используется в InnoDB по умолчанию. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, и DELETE, которые используют уникальные индексы и уникальное условие поиска блокируют только найденную индексную запись и не блокируют интервал перед ней. В остальных случаях эта операция использует блокировку следующего ключа, блокирует диапазон индексов, просканированных блокировкой следующего ключа или интервальной, и блокирует новые добавления другими пользователями.
В согласованном чтении есть важное отличие от предыдущего уровня изоляции: на этом уровне все согласованные чтения внутри той же самой транзакции читают снимок, сделанный для первого чтения. Это соглашение означает, что если вы выполните несколько простых выборок (SELECT) внутри той же самой транзакции, эти выборки будут целостными по отношению к друг другу.
SERIALIZABLE Этот уровень похож на предыдущий, но простые SELECT преобразовываются в SELECT ... LOCK IN SHARE MODE.