Транзакции

Транзакция - операция, которая содержит в себе один или несколько запросов к БД. Цель транзакций - выполнить все запросы к базе, а если что-то не выполняется, то не засчитывать предыдущие запросы. Также в транзакции входит механизм изоляции, который позволяет решать проблемы при совместном доступе к данным. Запомните основное правило транзакций: "Либо она выполняется полностью, либо не выполняется совсем". Существует 2 фундаментальных понятия: ROLLBACK и COMMIT. 

COMMIT - используется, чтобы зафиксировать все изменения и принять выполнение всех запросов.
ROLLBACK - откат всех запросов назад. Отмена предыдущих результатов в транзакции.

Когда транзакция стартует, все происходящие процессы хранятся во временном хранилище. А после выполнения COMMIT, результат сохраняется уже в физической памяти БД. В MYSQL транзакции работают только в таблицах innoDB (кстати по умолчанию у них включен режим autocommit, один запрос == одной транзакции). Таблицы MyISAM не работают с транзакциями. Чтобы начать данную операцию нужно написать "START TRANSACTION", либо можно просто набрать "BEGIN". Для фиксации изменений - COMMIT, либо - ROLLBACK для отката к началу.

Не каждый оператор может работать с транзакциями. К ним относятся операторы DDL (язык определения данных). В эту группу входят запросы CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.

Если в SQL запросе появляется ошибка, то транзакция не сработает. Для этих целей служат sql wrapper'ы, например, PHP PDO. 

Давайте разберем пример на практике: у вас есть 2 таблицы:

• user
• cart

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

Start transaction; 
	INSERT INTO user (id, name) VALUES (1, 'Володя Пупкин'); 
	INSERT INTO cart (id, user, item) VALUES (1, 1, 'Сковородка'); 
	INSERT INTO cart (id, user, item) VALUES (2, 1, 'Пылесос'); 
commit;

Есть 4 уровня чтения данных при параллельных транзакциях (их называют уровнями изоляции):

• 0 — Чтение неподтверждённых данных (грязное чтение) (Read Uncommitted, Dirty Read) 
  Этот уровень изоляции является самым низким. Здесь возможно чтение данных, которые еще не зафиксированы (COMMIT). В нашем примере второй пользователь увидит данные. При этом никто не дает гарантий, что эта запись не будет отменена (ROLLBACK). Из-за чего иногда может возникнуть ситуация непонимания среди пользователей.
• 1 — Чтение подтверждённых данных (Read Committed) 
  При таком виде чтения, возможно получить только данные с зафиксированной транзакцией. Однако, возможны 2 проблемы: 
  Неповторяемое чтение - когда в рамке одной транзакции мы делаем несколько SELECT выборок, и в этот период другая параллельная транзакция, делает обновление данных (предположим изменяет имя пользователя), при данном виде чтения выборка всех SELECT'ов отработает, и, в результате изменения данных, мы получим другую, измененную SELECT выборку. 
  Фантомное чтение - ситуация, когда блокируется не вся таблица, а только строки. Значит возможно возникнет ситуация при двух параллельных транзакциях: когда одна добавляет данные, а другая читает (например SELECT * FROM user). Результат может отличаться количеством строк.
• 2 — Повторяемое чтение (Repeatable Read, Snapshot)
  Этот тип чтения работает также только с зафиксированными транзакциями. Отличие от предыдущего типа чтения в том, что отсутствует проблема "неповторяемое чтение".
• 3 — Сериализуемый (Serializable)
  Данный тип изоляции самый надежный из всех, но при этом он является самым медленным. Какие-либо проблемы с параллельными транзакциями отсутствуют. При этом придется заплатить быстродействием системы.

Уровень изоляции #2 (Repeated Read) используется по умолчанию. Но нужно учитывать особенности вашего приложения и исходить уже из особенностей архитектуры. Чтобы установить уровень изоляции:

SET TRANSACTION - с помощью этого оператора можно установить уровень изоляции.

также можно установить опцию "глобально", либо только для "сеанса":

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Обязательно учтите, что оператор можно выполнить только под привилегией SUPER. Также можно запустить mysqld с глобальным уровнем. Для этого нужно использовать опцию —transaction-isolation