Полезные фишки в БД

Сегодня рассмотрим транзакции

Покажу вам простой пример транзакий, которые нужны чтобы небыло непоняток и коллизий. Для начала один простой пример.
UPDATE prices SET current=current + 10000 WHERE id='1';
UPDATE prices SET current=current — 10000 WHERE id='2';


Это перевод денег с лицевого счета клиента с номером 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;

Примерно такая технология используется тут.