Интернет и базы данных. Часть 06. Транзакции

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

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

UPDATE accounts SET AccSum = AccSum - 1000 WHERE AccNumber = 12345;
UPDATE accounts SET AccSum = AccSum + 1000 WHERE AccNumber = 67890;

Что будет, если первый оператор выполнится, а второй по какой-то причине - нет (сбой сервера, неправильный номер счета, переполнение... - мало ли какая может быть ошибка)? Деньги с одного счета списаны, а на другой не поступили...

Механизм транзакций как раз и позволяет корректно выходить из подобных ситуаций. Объединив эти два оператора UPDATE в одну транзакцию, мы обеспечим выполнение (или невыполнение) их обоих как одного целого.

Стандарт SQL-92 предусматривает, что транзакция в текущей сессии начинается неявно при выполнении первого из SQL-операторов, изменяющих данные, а завершена может быть либо явно - операторами COMMIT [WORK] или ROLLBACK (COMMIT [WORK] подтверждает транзакцию и фиксирует внесенные изменения, ROLLBACK транзакцию откатывает и возвращает данные в базе к тому состоянию, в каком они были до начала транзакции), либо неявно - при завершении текущей сессии или при системном сбое. К сожалению, стандарт SQL-92 не определяет, что должно делаться по умолчанию при завершении текущей сессии - подтверждение транзакции или откат, и в разных СУБД (и даже, бывает, в разных клиентах для одной СУБД) это реализуется по-разному. Поэтому на действия по умолчанию лучше не полагаться и всегда делать COMMIT / ROLLBACK явно.

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

Говоря о механизме транзакций в Oracle, можно было бы, наверно, ограничиться одной фразой: "Транзакции в Oracle практически полностью соответствуют стандарту SQL-92 - смотрите предыдущий абзац". Добавлю к этому, пожалуй, только два момента.

Транзакции только на чтение

Таковые, впрочем, также предусмотрены стандартом SQL-92. Если транзакция объявлена как "только на чтение" (SET TRANSACTION READ ONLY) - это гарантирует, что любой SELECT, выполненный в рамках этой транзакции, выдаст данные в том состоянии, в каком они были на момент начала транзакции. В пределах транзакции "только на чтение" запрещено любое изменение данных. При этом транзакция "только на чтение" не устанавливает никаких блокировок данных и не мешает изменять эти данные другим сессиям.

Автономные транзакции

Реализованный в Oracle механизм автономных транзакций позволяет создать новую транзакцию в пределах текущей. Подтверждение / откат изменений, сделанных в рамках автономной транзакции, производится независимо от родительской транзакции. Автономные транзакции могут быть вложенными, максимальный уровень вложенности определяется настройками сервера.

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

А вот в MySQL с транзакциями все гораздо хитрее...

Начнем с того, что в MySQL существуют таблицы нескольких типов: ISAM, HEAP, MyISAM, InnoDB, BDB... Из них транзакции могут поддерживать только два последних: InnoDB и BDB. При этом "родными" для MySQL являются таблицы типа MyISAM, которые транзакции не поддерживают. А для того, чтобы можно было создавать транзакционные таблицы типов InnoDB и BDB и работать с ними, сервер MySQL должен быть соответствующим образом сконфигурирован (что далеко не всегда во власти разработчиков приложений, особенно веб-приложений).

По умолчанию MySQL работает в режиме autocommit. Это означает, что результаты выполнения любого SQL-оператора, изменяющего данные, будут сразу фиксироваться (записываться на диск).

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

Для однократного перехода в транзакционный режим можно использовать команду START TRANSACTION (в MySQL начиная с версии 4.0.11) или BEGIN [WORK]:

START TRANSACTION;
UPDATE accounts SET AccSum = AccSum - 1000 WHERE AccNumber = 12345;
UPDATE accounts SET AccSum = AccSum + 1000 WHERE AccNumber = 67890;
COMMIT;

Обратите внимание на серьезный подводный камень. Если в транзакции участвуют таблицы разных типов (транзакционные и нетранзакционные), то вести они себя будут совсем по-разному. Изменения в нетранзакционные таблицы будут вноситься немедленно, несмотря на отключенный режим autocommit, и их невозможно будет откатить с помощью оператора ROLLBACK! А если ROLLBACK все же будет выполнен - изменения, внесенные в транзакционные таблицы, откатятся, а в нетранзакционные - сохранятся. К чему это может привести - думаю, разжевывать не надо...