--- title: Транзакции InnoDB date: 2009-01-06T10:00 tags: [backend, mysql, php, tech, вера] --- 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;` ### Уровни изоляции В случаях когда два процесса одновременно и частично затрагивают общие данные то не обязательно все данные будут полностью блокированы. Существуют послабления, когда параллельные транзакции получают доступ к незавершённым транзакциям. Текущий уровень можно получить из настроек, можно [прописать в настройки](http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html) или исполнить запросом - как на время транзакции так и на время всего соединения. `SELECT @@global.tx_isolation; SET TRANSACTION ISOLATION LEVEL READ COMMITED;` По степени точности (строгости блокировки) по убыванию согласно стандарту SQL92 выделяют: - SERIALIZEABLE - полная независимость транзакций, в т.ч. своё чтение - REPEATABLE READ (повторяющееся чтение) - значение для InnoDB по умолчанию. Чтение общих строк в транзакциях разрешается, но не их изменение. - READ COMMITED (чтение фиксированного) - блокировка записи, но общее чтение. Есть проблема повторяющегося чтения, т.е. в первой транзакции несколь раз по разному читаются общие данные, потому что вторая транзакция их меняет. - READ UNCOMMITED ("грязное" чтение незафиксированного) - никакой блокировки на чтение и запись. При двух одновременных UPDATE поле получит значение последнего изменения в обоих транзакциях. Возможны множество проблем, особенно если до ROLLBACK одной транзакции изменения читает другая. В REPEATABLE READ существует [проблема фантомной вставки](http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html). Поскольку блокируются только ряды на 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 [советуют](http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html) все транзакции снабжать повторным запуском транзакций. По теме.. - [Сравнение нагрузок на MyIsam и InnoDB](http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/) в 2007 году - [Ссылочная целостность](http://ru.wikipedia.org/wiki/%D0%A1%D1%81%D1%8B%D0%BB%D0%BE%D1%87%D0%BD%D0%B0%D1%8F_%D1%86%D0%B5%D0%BB%D0%BE%D1%81%D1%82%D0%BD%D0%BE%D1%81%D1%82%D1%8C) - [XtraBackup](http://highload.com.ua/index.php/2009/09/16/xtrabackup-%D1%80%D0%B5%D0%B7%D0%B5%D1%80%D0%B2%D0%BD%D0%BE%D0%B5-%D0%BA%D0%BE%D0%BF%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%B4%D0%BB%D1%8F-innodb/) для создания резервных копий InnoDB ## Related - [Mysql import - export](/ru/blog/tech/backend/DB/mysql/mysql-import-export/) - [Mysql prepared statements](/ru/blog/tech/backend/DB/mysql/mysql-prepared-statements/) - [Mysql rownum](/ru/blog/tech/backend/DB/mysql/mysql-rownum/)