- 7.5.8.1. Согласованное чтение
- 7.5.8.2. Чтение с блокировкой
- 7.5.8.3. Блокировка следующего ключа: устранение проблемы с фантомом
- 7.5.8.4. Блокировка, осуществляемая различными операторами SQL в InnoDB
- 7.5.8.5. Обнаружение и откат взаимоблокировки (deadlock)
- 7.5.8.6. Пример работы согласованного чтения в InnoDB
- 7.5.8.7. Каким образом избежать взаимоблокировок (deadlock)?
- 7.5.8.8. Рекомендации по увеличению производительности
- 7.5.8.9. InnoDB Monitor
Назначение транзакционной модели 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
:
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
Пользователь может изменить уровень изоляции для отдельно взятой сессии или нового соединения таким образом:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
Обратите внимания что названия уровней изоляции пишется без дефиса в SQL-выражении.
Если вы указали ключевое слово GLOBAL
в указанном выше выражении,
оно будет определять уровень изоляции для новых соединений,
но не будет иметь эффекта для старых соединений. Любой пользователь может
изменить уровень изоляции для своей сессии, даже внутри самой транзакции. В
версиях старше 3.23.50 SET TRANSACTION
не оказывает эффекта на таблицы
InnoDB. В версиях старше 4.0.5 вы можете использовать только REPEATABLE READ
и SERIALIZABLE
.
Вы можете получить информацию об уровне изоляции, глобальном или для текущего соединения:
SELECT @@global.tx_isolation; SELECT @@tx_isolation;
В блокировке уровня строки 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
.