Все, думаю, уже знают, что InnoDB использует блокировки на уровне строк. В зависимости от уровня изоляции транзакции могут блокироваться как строки, попавшие в результирующую выборку, так и все строки, что были просмотрены при поиске. Например, в REPEATABLE READ блокирующий запрос без использования индекса потребует перебора всей таблицы, а следовательно и блокировки всех записей. Посему помни, %username%, правильный выбор индексов напрямую влияет на скорость работы блокировок.
Есть два базовых типа блокировок:
- shared lock — совместная блокировка, позволяет другим транзакциям читать строку и ставить на нее такую же совместную блокировку, но не позволяет изменять строку или ставить исключительную блокировку.
- exclusive lock — исключительная блокировка, запрещает другим транзакциям блокировать строку, а также может блокировать строку как на запись, так и на чтение в зависимости от текущего уровня изоляции (о коих ниже).
Если копнуть глубже, то выяснится, что есть еще 2 типа блокировок, назовем их блокировками «о намерениях». Нельзя просто так взять и заблокировать запись в InnoDB. Блокировки intention shared и intention exclusive являются блокировками на уровне таблицы и блокируют только создание других блокировок и операции на всей таблице типа LOCK TABLE. Наложение такой блокировки транзакцией лишь сообщает о намерении данной транзакции получить соответствующую совместную или исключительную блокировку строки.
Если наложенная на строку блокировка не позволяет выполнить операцию, то транзакция просто ждет снятия мешающей блокировки. В случае перекрестных блокировок ждать можно долго, это deadlock. В документации MySQL есть пара советов о том, как избежать взаимных блокировок.
InnoDB накладывает блокировки не на сами строки с данными, а на записи индексов. Та или иная блокировка может накладываться на:
- record lock — блокировка записи индекса
- gap lock — блокировка промежутка между, до или после индексной записи
- next-key lock — блокировка записи индекса и промежутка перед ней
Блокировка промежутков нужна для того, чтобы избежать появления фантомных записей, когда, например, между двумя одинаковыми чтениями диапазона соседняя транзакция успевает вставить запись в этот диапазон.
Все описанное выше определяется InnoDB неявно, вам нужно лишь представлять, что происходит «под капотом».
О том какие именно запросы накладывают блокировки, можно посмотреть опять же в документации.
Ну и перед тем как перейти к уровням изоляции, рассмотрим понятие согласованного чтения (consistent read).
В момент первого запроса в транзакции создается снэпшот данных БД (т.н. read view), на который не влияют изменения в параллельных транзакциях, но влияют изменения в текущей. Чтение из такого снэпшота называют неблокирующим согласованным чтением. Неблокирующим — потому что для создания такого снэпшота не требуется навешивание блокировок, согласованным — потому что никакие катаклизмы во внешним мире (кроме DROP TABLE и ALTER TABLE) не повлияют на уютный мирок снэпшота. InnoDB можно попросить сделать снэпшот и до первого запроса в транзакции, для этого нужно упомянуть об этом во время старта транзакции — START TRANSACTION WITH CONSISTENT SNAPSHOT.
Уровни изоляции транзакций InnoDB
Уровень изоляции можно изменить запросом SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL.
REPEATABLE READ (значение по умолчанию)
- Согласованное чтение (SELECT) ничего не блокирует, читает строки из снэпшота, который создается при первом чтении в транзакции. Одинаковые запросы всегда вернут одинаковый результат.
- Для блокирующего чтения (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокировка будет зависит от типа условия. Если условие уникально (WHERE id=42), то блокируется только найденная индексная запись (record lock). Если условие с диапазоном (WHERE id > 42), то блокируются весь диапазон (gap lock или next-key lock).
READ COMMITED
- Согласованное чтение ничего не блокирует, но каждый раз происходит из свежего снэпшота.
- Блокирующее чтение (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокирует только искомые индексные записи (record lock). Таким образом возможна вставка параллельным потоком записей в промежутки между индексами. Промежутки блокируются (gap lock) только при проверках внешних ключей и дублирующихся ключей. Также блокировки просканированных строк (record lock), не удовлетворяющих WHERE, снимаются сразу же после обработки WHERE.
READ UNCOMMITED (самый слабый уровень)
- Все запросы SELECT читают в неблокирующей манере. Изменения незавершенной транзакции могут быть прочитаны в других транзакциях, а изменения эти могут быть еще и впоследствии откачены. Это так называемое «грязное чтение» (несогласованное).
- В остальном все так же, как и при READ COMMITED.
SERIALIZABLE (самый строгий уровень)
- Аналогичен REPEATABLE READ, за исключением одного момента. Если autocommit выключен (а при явном старте транзакции он выключен), то все простые запросы SELECT неявно превращаются в SELECT… LOCK IN SHARE MODE, если включен — каждый SELECT идет в отдельной транзакции. Используется, как правило, для того чтобы превратить все запросы чтения в SELECT… LOCK IN SHARE MODE, если этого нельзя сделать в коде приложения.
One more thing…
И напоследок пара упомянутых в тексте штук, про которые следует знать.
SELECT… LOCK IN SHARE MODE — блокирует считываемые строки на запись.
Другие сессии могут читать, но ждут окончания транзакции для изменения затронутых строк. Если же в момент такого SELECT’а строка уже изменена другой транзакцией, но еще не зафиксирована, то запрос ждет окончания транзакции и затем читает свежие данные. Данная конструкция нужна, как правило, для того чтобы получить свежайшие данные (независимо от времени жизни транзакции) и заодно убедиться в том, что их никто не изменит.
SELECT… FOR UPDATE — блокирует считываемые строки на чтение. Точно такую же блокировку ставит обычный UPDATE, когда считывает данные для обновления.
Обратите внимание, что такие запросы читают уже не из снэпшота, как простой SELECT, т.е. они увидят изменения зафиксированные другой транзакцией после начала текущей. Так происходит потому, что InnoDB может заблокировать только последнюю версию строки, а в снэпшоте она не обязательно будет последней.
Например, в сценарии считать → изменить → записать обратно между считать и записать параллельная транзакция может изменить данные, но это изменение будет тут же затерто текущей транзакцией при записи обратно. LOCK IN SHARE MODE в данном примере не даст вклиниться соседней транзакции, ей придется подождать. Заметьте, что в данном случае блокировка будет ставиться дважды, сначала совместная блокировка при чтении, затем исключительная при записи. Так как блокировок две, то есть теоретический шанс проскочить третьей между ними и вызвать deadlock.
Отличие FOR UPDATE в том, что он сразу ставит исключительную блокировку, такую же, как и обычный UPDATE. Таким образом для сценария считать → изменить → записать обратно блокировка будет ставиться только один раз в момент считывания. Такой вариант снизит вероятность возникновения взаимных блокировок.