Как стать автором
Обновить

Транзакции InnoDB

Время на прочтение 4 мин
Количество просмотров 3.9K
InnoDB это транзакционный, реляционный движок работающий на основе MySQL сервера. Начиная с 2001 года он поставляется в стандартной сборке, а с версии 5.1 может устанавливаться в качестве плагина (без необходимости перекомпилировать ядро сервера). Синтаксис очень простой.
START TRANSACTION;
...
COMMIT; -- или же ROLLBACK; если что-то пошло в логике не так

Про определение


Определение транзакционности и реляционности значат во-первых значат полноценную связанность таблиц через FK и как следствие — целостность данных при удалении рядов. С MyIsam как известно приходилось вручную удалять связанные данные в нескольких таблицах, в InnoDB — каскадное удаление одним запросом. Во-вторых поскольку для БД немыслимы параллельные версии данных как в SVN и некому эти версии объединять в одну ветку, но при этом необходима параллельная работа нескольких процессов (пользователей) с одними данными, то в качестве решения становится транзакции.
Очередь из запросов-автомобилей теперь пополняется атомарной транзакцией-автобусом. Естественно это плохо, поскольку чем длиней и дольше выполняется транзакция тем больше параллельных процессов будут ждать его. Для ускорения работы создаются остановки — типы и уровни блокировки данных. Для InnoDB по умолчанию это блокирование на уровне строки (по PK), тогда как в MyIsam атомарная операция блокирует всю таблицу.


Тразнактивность = блокировка


Два движка поэтому нельзя сравнивать — InnoDB из-за транзактивности приходится спускаться на уровень строк, поскольку вероятность очереди к одной и той же строке у двух процессов ниже, следовательно быстрей будет работа. Но как результат — на каждую строку приходится делать флаги блокировки, значит чуть больше памяти. Из-за разницы в уровнях блокировки данных, сравнивать InnoDB с MyIsam по производительности в зависимости от числа процессов достаточно трудно.
Есть несколько типов блокировок
  • READ (пока я читаю — никто не запишет) — по умолчанию на SELECT ставится
  • WRITE (пока я пишу — никто не прочтёт и не запиет) — по умолчанию на UPDATE ставится
  • LOW_PRIORITY WRITE (дам быстро прочитать если кто-то ждёт)

В качестве ликбеза — блокировать можно вручную целую таблицу (но не нужно ибо для InnoDB это убого тормозит все процессы). Повторное блокирование снимает предыдущие блокировки. Блокировать можно и виртуальные таблицы (view)
LOCK TABLES user WRITE, company READ;
UNLOCK TABLES;


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


В случаях когда два процесса одновременно и частично затрагивают общие данные то не обязательно все данные будут полностью блокированы. Существуют послабления, когда параллельные транзакции получают доступ к незавершённым транзакциям.
Текущий уровень можно получить из настроек, можно прописать в настройки или исполнить запросом — как на время транзакции так и на время всего соединения.
SELECT @@global.tx_isolation;
SET TRANSACTION ISOLATION LEVEL READ COMMITED;

По степени точности (строгости блокировки) по убыванию согласно стандарту SQL92 выделяют:
  • SERIALIZEABLE — полная независимость транзакций, в т.ч. своё чтение
  • REPEATABLE READ (повторяющееся чтение) — значение для InnoDB по умолчанию. Чтение общих строк в транзакциях разрешается, но не их изменение.
  • READ COMMITED (чтение фиксированного) — блокировка записи, но общее чтение. Есть проблема повторяющегося чтения, т.е. в первой транзакции несколь раз по разному читаются общие данные, потому что вторая транзакция их меняет.
  • READ UNCOMMITED («грязное» чтение незафиксированного) — никакой блокировки на чтение и запись. При двух одновременных UPDATE поле получит значение последнего изменения в обоих транзакциях. Возможны множество проблем, особенно если до ROLLBACK одной транзакции изменения читает другая.

В REPEATABLE READ существует проблема фантомной вставки. Поскольку блокируются только ряды на UPDATE, но не на INSERT, то параллельно с транзакцией повторяющегося чтения можно сделать вставку, из-за чего возникнет фантомный ряд. Что-бы этого избежать InnoDB использует три способа блокировки — строка, диапазон и следующая строка на случай вставки (глубже я вчитываться не стал)
Вся эта теория конечно полезна, но по настоящему они используются реальными запросами.
  1. Чтение с уровенем REPEATABLE READ (блокировка на запись). Ждёт если над данными кто-то работает.
    SELECT... LOCK IN SHARE MODE
  2. Чтение в режиме SERIALIZEABLE (блокировка на чтение и запись)
    SELECT... FOR UPDATE

При этих запросах на время исполнении транзакции она переходит в новый режим.

Травматизм deadlock'ов


Deadlock'и, т.е. тупиковая ситуация одновременных процессов (потоков) которые нуждаются в одних и тех же или зависимых друг от друга данных часто возникают в программировании. InnoDB не исключение. Например если идут две транзакции и каждая хочет изменить ресурсы (строки/диапазон строк) которые сейчас заблокированы. Получается что ни одна транзакция не может закончится.
В таких ситуациях InnoDB вынуждена откатить одну из транзакций и выдать ошибку
ERROR 1213 (40001): Deadlock found when trying to get lock; try
restarting transaction

Такие проблемы возникают при большой параллельной вставке/изменении/удалении рядов несколькими процессами. MySQL советуют все транзакции снабжать повторным запуском транзакций.
По теме..


Оригинал статьи
Теги:
Хабы:
+11
Комментарии 2
Комментарии Комментарии 2

Публикации

Истории

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн
PG Bootcamp 2024
Дата 16 апреля
Время 09:30 – 21:00
Место
Минск Онлайн
EvaConf 2024
Дата 16 апреля
Время 11:00 – 16:00
Место
Москва Онлайн