Mysql способы избежать взаимных блокировок

Взаимоблокировки — классическая проблема транзакционных баз данных. Они не опасны до тех пор пока не становятся настолько частыми, что вы вообще не можете запустить некоторые транзакции. Обычно вы можете написать свои приложения таким образом, что они всегда будут подготавливать перезапуск транзакции, если произошел откат из-за взаимоблокировок.

InnoDB использует автоматическую блокировку уровня строки. Вы можете создать взаимоблокировку даже в случае транзакций, которые всего лишь добавляют или удаляют единичную строку. Это происходит из-за того, что в действительности эти операции не являются «атомарными»: они автоматически устанавливают блокировку на индексные записи добавляемых/удаляемых строк (или на несколько записей).

Вы можете избежать взаимоблокировок или уменьшить их количество, следуя следующим приемам:

Используйте SHOW INNODB STATUS в MySQL начиная с 3.23.52 и 4.0.3 для определения причины последней взаимоблокировки. Это поможет вам настроить ваше приложение, что бы избежать взаимоблокировок.

Всегда подготавливайте перезапуск транзакции, если произошел откат из-за взаимоблокировки. Взаимоблокировка не опасна: всего лишь попробуйте еще раз.

Чаще фиксируйте свои транзакии. Маленькие транзакции меньше склонны к противоречиям.

Если вы используете чтение с блокировкой SELECT . FOR UPDATE или . LOCK IN SHARE MODE , попробуйте использовать более низкий уровень изоляции READ COMMITTED .

Производите операции с вашими таблицам и строками в фиксированном порядке. Тогда транзакции будут формировать очередь и не будет происходить взаимоблокировка.

Добавьте хорошие индексы на ваши таблицы. Тогда ваши запросы будут сканировать меньше индексных записей и, соответственно, будут устанавливать меньше блокировок. Используйте EXPLAIN SELECT для того, чтобы узнать, выбирает ли MySQL соответствующий индекс для ваших запросов.

Используйте меньше блокировок: если вы можете допустить, чтобы SELECT возвращал данные из старого снимка, не добавляйте к выражению FOR UPDATE или LOCK IN SHARE MODE . Используйте уровень изоляции READ COMMITTED , который больше всего подходит для данной ситуации, так как каждое согласованное чтение внутри одной и той же транзакции читает свой собственный свежий снимок.

Если ничего не помогло, сериализируйте свои транзакции с блокировкой уровня таблиц: LOCK TABLES t1 WRITE, t2 READ, . ; [здесь можете развлекаться с таблицами t1 и t2]; UNLOCK TABLES . Блокировка на уровне таблиц выстраивает ваши транзакции в очередь, и позволяет избежать взаимоблокировки. Заметьте, что LOCK TABLES неявным образом начинает транзакцию наподобие BEGIN , и UNLOCK TABLES неявным образом завершает ее в COMMIT .

Другое решение для сериализации транзакций — это создание вспомагательного «семафора» таблицы, где есть всего лишь одна строка. Каждая транзакция обновляет эту строку перед доступом к другой таблице. В этом случае все транзакции выполняются в виде очереди. Отметим что таким же образом в настоящий момент работает и алгоритм определения взаимоблокировок в InnoDB, так как блокировка сериализации — это блокировка уровня строки. При блокировке на уровне таблицы в MySQL мы используем метод таймаута для разрешения взаимоблокировки.

Источник

Блокировки и уровни изоляции транзакций InnoDB в MySQL

Здравствуй, Хабр!
Предлагаю всем желающим вспомнить или познать суть блокировок движка InnoDB в MySQL.

КДПВ: deadlock в исполнении тропической фауны

Базовые понятия

Все, думаю, уже знают, что 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 (значение по умолчанию)

READ COMMITED

READ UNCOMMITED (самый слабый уровень)

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. Таким образом для сценария считать → изменить → записать обратно блокировка будет ставиться только один раз в момент считывания. Такой вариант снизит вероятность возникновения взаимных блокировок.

Источник

[Конспект] Про блокировки и транзакции в MySQL

Oct 4, 2018 • zinvapel

В этом посте я хотел бы собрать воедино информацию о блокировках и транзакциях в MySQL.

Блокировки

Типы блокировок

Разделяемые (shared). Блокировки чтения, возможность читать записи есть у всех клиентов, а возможности записи нет. Каждый клиент может поставить такую блокировку одновременно.
Монопольные (exclusive). Никто не сможет повесить другую блокировку на данные, а также выполнить изменения.

Стратегии блокировок

Табличные блокировки. Блокируется вся таблица, причём блокировка записи двигает блокировки чтения в очереди.
Блокировка строк. Более накладной вариант блокировок, поддерживается в InnoDB и Falcon.

Блокировки в InnoDB:

InnoDB использует блокировки на уровне строк. В зависимости от уровня изоляции транзакции могут блокироваться как строки, попавшие в результирующую выборку, так и все строки, что были просмотрены при поиске. Например, в REPEATABLE READ блокирующий запрос без использования индекса потребует перебора всей таблицы, а следовательно и блокировки всех записей.

Есть два базовых типа блокировок:

Shared lock — совместная блокировка, позволяет другим транзакциям читать строку и ставить на нее такую же совместную блокировку, но не позволяет изменять строку или ставить исключительную блокировку.
Exclusive lock — исключительная блокировка, запрещает другим транзакциям блокировать строку, а также может блокировать строку как на запись, так и на чтение в зависимости от текущего уровня изоляции (о коих ниже).

Если копнуть глубже, то выяснится, что есть еще 2 типа блокировок, назовем их блокировками «о намерениях». Нельзя просто так взять и заблокировать запись в InnoDB. Блокировки intention shared и intention exclusive являются блокировками на уровне таблицы и блокируют только создание других блокировок и операции на всей таблице типа LOCK TABLE. Наложение такой блокировки транзакцией лишь сообщает о намерении данной транзакции получить соответствующую совместную или исключительную блокировку строки.

Record lock — блокировка записи индекса.
Gap lock — блокировка промежутка между, до или после индексной записи.
Next-key lock — блокировка записи индекса и промежутка перед ней.

SELECT… LOCK IN SHARE MODE — блокирует считываемые строки на запись.

Другие сессии могут читать, но ждут окончания транзакции для изменения затронутых строк. Если же в момент такого SELECT’а строка уже изменена другой транзакцией, но еще не зафиксирована, то запрос ждет окончания транзакции и затем читает свежие данные. Данная конструкция нужна, как правило, для того чтобы получить свежайшие данные (независимо от времени жизни транзакции) и заодно убедиться в том, что их никто не изменит.

SELECT… FOR UPDATE — блокирует считываемые строки на чтение. Точно такую же блокировку ставит обычный UPDATE, когда считывает данные для обновления.

Взаимоблокировки (deadlock) возникают тогда, когда две и более транзакции взаимно удерживают и запрашивают блокировку одних и тех же ресурсов, создавая циклическую зависимость. InnoDB обрабатывает взаимоблокировки откатом той транзакции, которая захватила меньше всего монопольных блокировок строк (приблизительный показатель легкости отката). Нельзя справиться с взаимоблокировками без отката одной из транзакций, частичного либо полного.

Транзакции в MySQL

MySQL предоставляет пользователям две транзакционные подсистемы хранения данных: InnoDB и NDB Cluster. MySQL позволяет устанавливать уровень изолированности с помощью команды SЕТ TRANSACТION ISOLAТION LEVEL , которая начинает действовать со следующей транзакции. Можете настроить уровень изолированности для всего сервера в конфигурационном файле или только для своей сессии.

По умолчанию MySQL работает в режиме АUТОСОММIT . Это означает, что, пока вы не начали транзакцию явно, каждый запрос автоматически выполняется в отдельной транзакции. Некоторые команды, будучи запущенными во время начатой транзакции, заставляют MySQL подтвердить транзакцию до их выполнения. Обычно это команды языка определения данных (Data Definition Language, DDL), которые вносят изменения в структуру таблиц, например ALТЕR TABLE, но LOCK TABLES и другие директивы также обладают этим свойством.

Если вы используете транзакционные и нетранзакционные таблицы (например, таблицы InnoDB и MyISAM) в одной транзакции, то все будет работать хорошо, пока не произойдет что-то неожиданное, откатить данные из нетранзакционных таблиц невозможно.

Транзакция должна функционировать как единая неделимая единица работы таким образом, чтобы вся транзакция была либо выполнена, либо отменена.

База данных должна всегда переходить из одного непротиворечивого состояния в последующее.

Результаты транзакции обычно невидимы другим транзакциям, пока она не закончена.

Будучи зафиксированы, внесенные в ходе транзакции изменения становятся постоянными. Это означает, что изменения должны быть записаны так, чтобы данные не могли быть потеряны в случае сбоя системы.

Уровни изоляции транзакций

Проблемы параллельного доступа с использованием транзакций:

Проблема	Описание
Lost update — потерянное обновление	При одновременном изменении одного блока данных разными транзакциями одно из изменений теряется.
Dirty read — грязное чтение	Чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится.
Non-repeatable read — неповторяемое чтение	При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.
Phantom read — фантомное чтение	При повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк.

Уровни изоляции:

Название	Описание	Реализация
READ UNCOMMITED	Гарантирует только отсутствие потерянных обновлений. Если несколько параллельных транзакций пытаются изменять одну и ту же строку таблицы, то в окончательном варианте строка будет иметь значение, определенное всем набором успешно выполненных транзакций. Защита от Lost Update.	В рамках транзакции Т1 накладывается разделяемая блокировка на изменяемые данные, все остальные транзакции, желающие изменить эти данные, ждут завершения Т1.
READ COMMITED	Обеспечивается защита от грязного чтения, тем не менее, в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных.	В рамках транзакции Т1 создается снимок изменяемых строк, с которым будут работать все остальные клиенты до завершения Т1. Если будут несколько изменений одних и тех же строк, то зафиксированы будут только изменения Т1.
REPEATABLE READ	Читающая транзакция «не видит» изменения данных, которые были ею ранее прочитаны. При этом никакая другая транзакция не может изменять данные, читаемые текущей транзакцией, пока та не окончена.	В рамках транзакции Т1 накладывается монопольная блокировка на считываемые данные, все остальные транзакции, желающие изменить эти данные, ждут завершения Т1.
SERIALIZABLE	Транзакции полностью изолируются друг от друга, каждая выполняется так, как будто параллельных транзакций не существует.	Параллельным транзакциям данные блокируются даже для чтения.

Конкретная реализация каждого уровня изоляции определяется подсистемой хранения данных MySQL.

Уровни изоляции InnoDB

Согласованное чтение (SELECT) ничего не блокирует, читает строки из снимка, который создается при первом чтении в транзакции. Одинаковые запросы всегда вернут одинаковый результат.
Для блокирующего чтения (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокировка будет зависит от типа условия. Если условие уникально (WHERE то блокируется только найденная индексная запись (record lock). Если условие с диапазоном (WHERE id > 42), то блокируются весь диапазон (gap lock или next-key lock).

Согласованное чтение ничего не блокирует, но каждый раз происходит из свежего снимка.
Блокирующее чтение (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокирует только искомые индексные записи (record lock). Таким образом возможна вставка параллельным потоком записей в промежутки между индексами. Промежутки блокируются (gap lock) только при проверках внешних ключей и дублирующихся ключей. Также блокировки просканированных строк (record lock), не удовлетворяющих WHERE, снимаются сразу же после обработки WHERE.

Все запросы SELECT читают в неблокирующей манере. Изменения незавершенной транзакции могут быть прочитаны в других транзакциях, а изменения эти могут быть еще и впоследствии откачены. Это так называемое «грязное чтение» (несогласованное). В остальном все так же, как и при READ COMMITED.

Аналогичен REPEATABLE READ, за исключением одного момента. Если autocommit выключен (а при явном старте транзакции он выключен), то все простые запросы SELECT неявно превращаются в SELECT… LOCK IN SHARE MODE, если включен — каждый SELECT идет в отдельной транзакции. Используется, как правило, для того чтобы превратить все запросы чтения в SELECT… LOCK IN SHARE MODE, если этого нельзя сделать в коде приложения.

Журнал транзакций

Ведение журнала помогает сделать транзакции более эффективными. Вместо обновления таблиц на диске после каждого изменения подсистема хранения данных может изменить находящуюся в памяти копию данных. Это происходит очень быстро. Затем подсистема хранения запишет сведения об изменениях в журнал транзакции, который хранится на диске и поэтому долговечен. Это тоже довольно быстрая операция, поскольку добавление событий в журнал сводится к операции последовательного ввода/вывода в пределах ограниченной области диска вместо случайного ввода/вывода в разных местах. Позже процесс обновит таблицу на диске.

MVCC — multiversion concurrency control

MVCC сохраняет мгновенный снимок состояния данных в определенный момент времени.

InnoDB реализует MVCC, сохраняя вместе с каждой строкой два скрытых значения, которые показывают, когда строка была создана и когда истек срок ее хранения (или она была удалена). Вместо хранения фактического момента времени, когда произошли данные события, строка хранит номер версии системы для этого момента.

Применение для Repeatable Read:

SELECT. InnoDB должна проверить каждую строку на соответствие двум критериям.
- Найти версию строки, по крайней мере такой же старой, как версия транзакции (то есть ее номер версии должен быть меньше номера версии транзакции или равен ему). Это гарантирует, что либо строка существовала до начала транзакции, либо транзакция создала или изменила эту строку.
- Версия удаления строки должна быть не определена, или ее значение должно быть больше, чем версия транзакции. Это гарантирует, что строка не была удалена до начала транзакции.
INSERT. InnoDB записывает текущий номер версии системы вместе с новой строкой.
DELEТE. InnoDB записывает текущий номер версии системы как ID удаления строки.
UPDATE. InnoDB записывает новую копию строки, используя номер версии системы в качестве версии новой строки. Она также записывает номер версии системы как версию удаления старой строки.

Источник