Различия между MyISAM и InnoDB
Собственно, чем отличаются MyISAM и InnoDB?

  • MyISAM поддерживает сжатие таблиц в отличии от InnoDB

  • MyISAM имеет встроенные полнотекстный поиск в отличии от InnoDB

  • InnoDB поддерживает транзакции в отличии от MyISAM

  • InnoDB более надежна при больших объемах данных

  • InnoDB в теории немного быстрее



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

Транзакции, конкурирующие запросы

Под транзакцией понимается некоторая группа DML команд. Все изменения сделанные ими, сохраняются в отдельной области памяти до окончательного подтверждения изменений (успешное завершение транзакции), либо до их отмены. Если во время транзакции делается запрос на выборку данных, то создается отдельное представление. Для чего нужны транзакции? В англоязычной литературе концепция транзакций описывается абривиатурой ACID:


  • атомарность (atomicity) - выполнение или не выполнение всех DML команд входящих в тразакцию;

  • целостность БД (consistency) - завершение транзакции не должно нарушать целостность БД;

  • изоляция (isolation) - можно отображать либо исходные данные, которые были до начала транзакции, либо новые данные после выполнения транзакции;

  • сохранность данных (durability) - если пользователю пришло подтверждение выполнения транзакции, то его изменения не будут отменены по каким-либо причинам.



В стандарте предусмотрены следующие команды управления транзакциями:

  • START TRANSACTION - явное начало транзакции. Команда не поддерживается в Oracle. В MySQL и PostgreSQL можно использовать синоним begin (не путать с блоковым оператором begin, после которого нет разделителя). Если начало транзакции явно не указано, то PostgreSQL считает каждую DML команду отдельной транзакцией. В Oracle транзакции следуют одна за другой. То есть первая DML команда открывает транзакцию, следующие команды становятся частью этой транзакции, пока не будет вызвана команда commit или rollback. Если режим автоподтверждения (autocommit) включен, то MySQL работает также как PostgreSQL, иначе как Oracle;

  • COMMIT - завершить транзакцию, применяя все сделанные изменения;

  • ROLLBACK - завершить транзакцию, отменяя все сделанные изменения. Если точка отката не указана, то отменяется вся текущая транзакция;

  • SAVEPOINT - сохранить точку отката;

  • RELEASE SAVEPOINT - уничтожить точку отката, что позволяет освободить часть ресурсов до завершения транзакции;

  • SET TRANSACTION - устанавливает характеристики текущей транзакции. Если транзакция не начата явно, то эта команда игнорируется в PostgreSQL.



Ниже приведен пример использования некоторых команд внутри выполняемого блока в Oracle.
BEGIN
   ...
   SAVEPOINT pt1; -- сохранили точку отката
   ...
EXCEPTION
   WHEN VALUE_ERROR  THEN  -- неправильное значение
      -- SAVEPOINT можно опустить
      ROLLBACK TO SAVEPOINT pt1;
   WHEN OTHERS THEN  -- другие исключения
   ...
END;


Команда select ... for update, блокирует записи таблицы от изменений другими пользователями. При этом нельзя использовать другие конструкции как distinct, group by.
-- блокируем запись для обновления
SELECT * FROM tbl1
    WHERE id=2
    FOR UPDATE;    

-- обновляем записи через UPDATE
...

-- завершаем транзакцию, блокировка снимается    
COMMIT;


Для блокировки нескольких таблиц в различных режимах используется команда lock table. Блокировка снимается при завершении текущей транзакции.
-- блокируем таблицу в режиме SHARE
-- запросы данных разрешены
-- изменение таблицы запрещено
-- блокировать таблицу в режиме EXCLUSIVE запрещено
LOCK TABLE tbl2
   IN SHARE MODE;

-- блокируем таблицу в режиме EXCLUSIVE
-- и не ждем, если другой пользователь уже заблокировал таблицу
LOCK TABLE tbl1
   IN EXCLUSIVE MODE
   NOWAIT;


В MySQL поддержка транзакций зависит от используемого способа хранения таблиц. Так для таблиц на движке InnoDB возможны транзакции, а для MyISAM нет. В последнем случае отсутствие одновременного обновления одних и тех же данных разными пользователями гарантируется самой СУБД. Невозможность отката, означает, необходимость самостоятельной предварительной проверки на ошибки перед внесением изменений. Такой подход увеличивает скорость работы от трех до пяти раз за счет уменьшения использования памяти, дискового пространства и процессора.