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

В данной статье мы рассмотрим механизм транзакций. Для начала приведем один простой пример.

Это перевод денег с лицевого счета клиента с номером 2 на лицевой счет клиента с номером 1. А теперь представим что первый запрос удачно выполнился, а вот второй по каким либо причинам (ошибка базы данных, ошибка на сервере и т.д.) нет. Таким образом мы получаем ситуацию которая грозит реальными денежными потерями.

Для того что избежать этого нужно чтобы оба запроса выполнялись как одно целое. И если возникла ошибка в одном запросе не выполнились бы остальные. Для этого и был придуман механизм транзакций.

Оператор открывающий транзакцию в MySQL: "START TRANSACTION;". После правильного выполнения всех запросов транзакцию можно либо завершить внеся все изменения в базу данных - "COMMIT;", либо откатить вернув все в начальное состояние - "ROLLBACK".

Если конкретно рассматривать случай с базой MySQL то тут есть несколько подводных камней.
1. В MySQL существует несколько типов таблиц. Это ISAM, HEAP, MyISAM, InnoDB, BDB и т.д. Транзакционный механизм поддерживают только InnoDB и BDB. Поэтому все таблицы с которыми вы хотите работать через транзакции следует переконвертировать в соответствующий тип.
2. По умолчанию MySQL работает в режиме autocommit. Это означает, что результаты выполнения любого SQL-оператора, изменяющего данные, будут сразу сохраняться.

Режим autocommit можно отключить командой SET AUTOCOMMIT=0. При отключенном режиме autocommit каждую транзакцию надо явно завершать операторами COMMIT / ROLLBACK.

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

START TRANSACTION;
UPDATE user_account SET allsum=allsum + 1000 WHERE id='1';
UPDATE user_account SET allsum=allsum - 1000 WHERE id='2';
COMMIT;