Точки сохранения (savepoints) и механизм целостности в СУБД Firebird
Точки сохранения (далее сокращенно ТС) – это внутренний механизм СУБД, привязывающий любые изменения в БД к конкретному моменту времени в рамках транзакции, и позволяющий при необходимости отменить все изменения, выполненные после установки данной ТС (так называемый откат до ТС).
По умолчанию сервер использует глобальную ТС (то есть уровня транзакции, ведь ТС не имеют смысла вне ее контекста) для осуществления отката транзакции. Данная ТС устанавливается автоматически при старте транзакции и является первой в ее контексте. Когда инициируется откат транзакции, то все изменения, выполненные в ее контексте, откатываются с помощью глобальной ТС транзакции, после чего данная транзакция подтверждается (!) в TIP (Transaction Inventory Page). Но если количество изменений, выполненных в контексте транзакции, становится слишком велико (порядка 104 – 106 записей), то хранение списков отката получается дорогостоящим и сервер удаляет глобальную ТС транзакции, переходя к стандартному механизму TIP для пометки транзакции как отмененной. Поэтому, если вы ожидаете, что в рамках транзакции будет выполнено большое количество изменений, то имеет смысл указать параметр транзакции isc_tpb_no_auto_undo , который отключает использование глобальной ТС для отката транзакции. Это в некоторых случаях позволит увеличить быстродействие сервера при массовых операциях.
Помимо использования ТС для отката транзакции, сервер также использует их для обработки исключений. Каждый DSQL и/или PSQL оператор обрамляется ТС-фреймом, позволяющим откатить именно этот оператор, не затрагивая предыдущие. Это гарантирует, что оператор либо выполнился успешно, либо все его изменения автоматически отменены и инициирована соответствующая ошибка. Для обработки исключений в PSQL, каждый BEGIN…END блок также обрамляется фреймом, позволяющим отменить все изменения, выполненные данным блоком. Более подробно об этом механизме описано ниже.
Как это устроено?
ТС представляет собой структуру данных, размещенную в динамической памяти сервера (в пуле транзакции) и имеющую уникальный числовой идентификатор. К каждой ТС привязан список действий, совершенных в ее контексте (так называемый undo log или журнал отмены). В пределах транзакции ТС образуют стек и, следовательно, их откат всегда возможен только последовательно. Фрагменты журнала отмены распределены между ТС, которые инкрементно хранят историю всех изменений, выполненных в контексте транзакции.
ТС, активная на момент изменения какой-либо записи, называется текущей. Информация об изменении записи помещается в журнал отмены текущей ТС. В случае инициации отката до ТС журнал отмены раскручивается в обратную сторону, реконструируя запись к виду, в каком она существовала на момент установки данной ТС. После реконструкции всех измененных записей ТС обычно удаляется из контекста транзакции. В случае отсутствия обработчиков исключений в контексте текущей ТС, данный процесс может повторяться, отменяя изменения вышестоящих ТС. Помимо операции отката до ТС существует еще и операция штатного удаления (освобождения) ТС. В случае удаления ТС ее журнал отмены объединяется с журналом отмены предыдущей в стеке ТС. С учетом вышесказанного можно говорить о вложенности ТС.
Очевидно, что штатное удаление подмножества ТС, расположенных «глубже» глобальной, приведет к переносу всех изменений с их контекста на контекст глобальной ТС транзакции. Как будет показано ниже, каждый клиентский DSQL-запрос и представляет собой такое подмножество ТС. Таким образом, совокупность всех изменений, успешно выполненных в контексте транзакции, хранится в ее журнале отмены, обеспечивая этим упомянутую выше возможность замены состояния dead транзакции в TIP на committed . При указании параметра isc_tpb_no_auto_undo при старте транзакции глобальная ТС не создается и в случае штатного удаления текущего стека ТС совокупный журнал отмены просто удаляется. Важно понимать, что данный параметр не отключает механизм ТС как таковой, это невозможно с точки зрения гарантии атомарности SQL-операторов. Он отключает только ведение журнала отмены транзакции как единого целого.
Системные точки сохранения и обработка исключений
- Выполнение любого клиентского SQL-запроса. Как уже было сказано выше, это делается для обеспечения атомарности данного запроса, то есть при возниковении любого исключения во время выполнения запроса, изменения, внесенные им в БД, всегда будут отменены. По окончании выполнения запроса ТС автоматически удаляется.
- Выполнение BEGIN…END блока в PSQL (процедуре или триггере) в случае, если этот блок содержит обработчик ошибок (WHEN-блок), либо если любой из вышестоящих блоков содержит обработчик ошибок. В этом случае каждый оператор BEGIN устанавливает точку сохранения и соответствующий ему оператор END удаляет ее.
- Выполнение SQL-оператора в контексте BEGIN…END блока, непосредственно содержащего обработчик ошибок (WHEN-блок).
Теперь вкратце рассмотрим механизм обработки ошибок в сервере. В случае возникновения исключения происходит автоматический откат до последней установленной ТС, вследствие чего отменяются все действия, выполненные ошибочным SQL-запросом. Далее, в случае PSQL-блока, анализируется наличие подходящего пользовательского WHEN-обработчика. При наличии такового управление передается ему и по выходу из него ТС блока освобождается. При отсутствии же подходящего обработчика, происходит откат до ТС блока. Далее процесс повторяется рекурсивно, до окончания вложенных обработчиков. Таким образом, при отсутствии в хранимой процедуре обработчика, способного перехватить данную ошибку, все действия процедуры будут последовательно отменены.
Из вышесказанного можно сделать несколько выводов. Во-первых, при отсутствии WHEN-обработчика блок является атомарным и в случае ошибки всегда будет отменен целиком. Во-вторых, при наличии подходящего WHEN-обработчика возникновение ошибки приводит к откату единственного оператора, после чего управление передается обработчику. При отсутствии же подходящего WHEN-обработчика блок будет отменен, но в два этапа – сначала отмена SQL-оператора и затем, после неудачного поиска обработчика, откат до ТС блока.
Однако, существует особенность в обработке PSQL-исключений, про которую часто забывают. Любой откат выполняется только до последнего вызова оператора SUSPEND, который как бы «разрывает» атомарность блока, в пределах которого он находится. И это вполне объяснимо, ведь достаточно странно требовать отката действий, результат выполнения которых уже был отправлен на клиентскую сторону.
Теперь отметим известную аномалию, которая не укладывается в описанную выше схему. Она состоит в том, что пункт 3 (см. выше) истинен только для SQL-операторов, да и то не для всех. То есть, например, оператор присваивания не будет обрамлен ТС-фреймом. Как результат, ошибка в выполнении присваивания приведет к нормальному откату до предыдущей ТС, которым в данном случае является… да, именно – ТС блока. То есть даже при наличии WHEN-обработчика ошибка может привести к откату всего блока перед передачей управления в обработчик. Вот полный список операторов, для которых создается ТС-фрейм: INSERT, UPDATE, DELETE, EXCEPTION, EXECUTE STATEMENT [INTO].
Примечание. Начиная с Firebird 2.0, данная аномалия устранена и все PSQL-операторы обрамляются ТС-фреймом в случае наличия WHEN-обработчика.
Для понимания этой аномалии следует знать, что в случае ошибки откат до последней ТС осуществляется безусловно. Эта означает следующее – при возникновении ошибки в операторах не из вышеприведенного списка (то есть не окруженных ТС-фреймом) на самом деле удаляется «чужая» ТС – уровня блока. Таким образом независимо от наличия WHEN-обработчика блок всегда будет отменен. Ситуация усугубляется еще и тем, что откат до ТС блока также осуществляется безусловно (без проверки идентификатора ТС). Таким образом, может возникнуть ситуация, когда целых две «лишних» ТС удалены. Пример подобного случая:
CREATE PROCEDURE PROC1
AS
DECLARE VARIABLE X INT;
— start savepoint #1
BEGIN
— start savepoint #anchor_2
INSERT INTO TAB (COL) VALUES (1);
— end savepoint #anchor_2
— start savepoint #3
BEGIN
X = 1 / 0;
WHEN EXCEPTION TEST DO
— start savepoint #anchor_4
EXCEPTION;
— end savepoint #anchor_4
END
— end savepoint #3
WHEN ANY DO
EXIT;
— end savepoint #1
END
В данном случае у нас присутствует ТС-фрейм вокруг оператора присваивания. Рассмотрим по шагам, что происходит при возникновении ошибки (деление на ноль). Сначала безусловно отменяется ближайшая ТС, которой является является ТС блока #3. Далее начинается поиск подходящего обработчика, которого в нашем примере нет. Тут происходит откат до ТС блока. который только что уже был удален. Так как откат производится безусловно, то удаляется предыдущая ТС #1. Соответственно, оператор INSERT будет отменен, хотя этого происходить не должно.
Примечание. Начиная с Firebird 2.0, откат до ТС блока всегда производится с проверкой идентификатора ТС, что устраняет подобные ошибочные ситуации.
Приведем еще несколько примеров. Для наглядности работу сервера с ТС в данных примерах отметим комментариями.
Процедура с WHEN-обработчиком и генерацией исключения в операторе присваивания:
CREATE PROCEDURE PROC2
AS
DECLARE VARIABLE X INT;
— start savepoint #1
BEGIN
— start savepoint #anchor_2
INSERT INTO TAB (COL) VALUES (2);
— end savepoint #anchor_2
X = 1 / 0;
WHEN ANY DO
EXIT;
— end savepoint #1
END
В данном случае INSERT будет отменен, так как ближайшей к ошибочному оператору ТС является #1 (ТС #anchor_2 была удалена непосредственно перед выполнением присваивания) и, следовательно, произойдет откат всего BEGIN. END блока перед входом в обработчик.
Та же процедура с явным вызовом исключения, заключенным в блок без WHEN-обработчика:
CREATE PROCEDURE PROC3
AS
DECLARE VARIABLE X INT;
— start savepoint #1
BEGIN
— start savepoint #anchor_2
INSERT INTO TAB (COL) VALUES (3);
— end savepoint #anchor_2
— start savepoint #3
BEGIN
X = 1 / 0;
— end savepoint #3
END
WHEN ANY DO
EXIT;
— end savepoint #1
END
При возникновении исключения первым делом удаляется ближайшая ТС #3. Далее производится откат атомарного блока и, вследствии вышеописанной ошибки сервера, удаляется ТС #1. Результат – INSERT снова отменен.
Та же процедура с явным вызовом исключения, заключенным в блок, содержащий WHEN-обработчик:
CREATE PROCEDURE PROC4
AS
DECLARE VARIABLE X INT;
— start savepoint #1
BEGIN
— start savepoint #anchor_2
INSERT INTO TAB (COL) VALUES (4);
— end savepoint #anchor_2
— start savepoint #3
BEGIN X = 1 / 0;
WHEN ANY DO
EXIT;
— end savepoint #3
END
WHEN ANY DO
EXIT;
— end savepoint #1
END
Сначала опять же будет удалена ТС #3. Однако, на этот раз у нас присутствует подходящий обработчик, которому и будет передано управление, после чего выполнение внешнего блока будет продолжено. В результате оператор INSERT остается нетронутым.
Процедура с WHEN-обработчиком и явным вызовом исключения:
CREATE PROCEDURE PROC5 AS
— start savepoint #1
BEGIN
— start savepoint #anchor_2
INSERT INTO TAB (COL) VALUES (5);
— end savepoint #anchor_2
— start savepoint #3
EXCEPTION E;
— end savepoint #3
WHEN ANY DO
EXIT;
— end savepoint #1
END
В данном случае INSERT не будет отменен, ибо инициация исключения E приведет только к откату до ТС #3 и последующей передачи управления в обработчик. Чтобы в данном случае оператор INSERT все же был отменен, необходимо отказаться от использования обработчика исключений:
CREATE PROCEDURE PROC6
AS
BEGIN
INSERT INTO TAB (COL) VALUES (6);
EXCEPTION E;
END
Если рассматривать вышеописанные примеры с точки зрения правильности, то абсолютно корректно были выполнены только последние два примера (PROC5 и PROC6). Пример PROC4 выдает правильный результат, но только в данном конкретном случае – при наличии любого SQL-оператора в одном блоке перед присваиванием результат уже будет неправильным. Первые же три примера (PROC1 – PROC3) отрабатывают неправильно.
Примечание. Как уже было отмечено выше, в Firebird 2.0 все примеры выполняются корректно.
Напоследок рассмотрим соответствие обработки исключений SQL-стандарту. Последний определяет три вида обработчиков в PSQL – CONTINUE, EXIT и UNDO. В случае CONTINUE-обработчика сервер должен откатить ошибочный оператор, выполнить код обработчика, после чего продолжить выполнение блока с оператора, следующего за вызвавшим ошибку. EXIT-обработчик требует завершения выполнения блока сразу после выхода из кода обработчика. UNDO-обработчик требует отката всех действий блока до входа в обработчик. Текущие версии сервера не поддерживают явное указание типа обработчика и работают по принципу EXIT (однако, с возможностью UNDO-поведения вследствие описанной выше аномалии). Полагаю, что в будущем было бы желательно обеспечить возможность выбора между UNDO и EXIT поведением обработчика.
Пользовательские точки сохранения
Помимо внутренней реализации ТС уровня транзакции и уровня оператора/блока, последние версии серверов (InterBase 7.1, Firebird 1.5, Yaffil 1.1) обеспечивают еще и SQL-интерфейс к данному механизму.
Примечание. Синтаксис и семантика ТС декларированы в стандарте SQL-99 (раздел 4.37.1 спецификации).
Пользовательские ТС (иногда называемые также вложенными транзакциями – nested transactions) обеспечивают удобный метод обработки ошибок в бизнес-логике без необходимости отката транзакции целиком.
Примечание. Откат до ТС также иногда называется частичным откатом транзакции.
Декларирован новый SQL-оператор SAVEPOINT для идентификации точки в контексте транзакции, до которой впоследствии возможно произвести откат:
Для отката до ТС используется следующий оператор:
ROLLBACK [WORK] TO [SAVEPOINT]
Примечание. Ключевое слово SAVEPOINT является обязательным в InterBase 7.1.
- Откатываются все изменения, выполненные после установки данной ТС;
- Удаляются все ТС, установленные после данной. Текущая ТС остается нетронутой, так что можно последовательно выполнять несколько откатов до одной ТС. Предыдущие ТС также остаются нетронутыми.
Примечание. Реализация отката до ТС в InterBase 7.1 удаляет указанную ТС.
- Освобождаются все явные и неявные блокировки записей, захваченные после установки данной ТС. При этом другие транзакции, запросившие ранее доступ к записям, заблокированным данной транзакцией после установки данной ТС, продолжают ожидать завершения текущей транзакции. Прочие транзакции, еще не успевшие запросить доступ к этим записям, могут продолжать свое выполнение и получить доступ к ним.
Примечание. Данное поведение относится к Firebird 1.5 и может быть изменено в следующих версиях.
Так как каждая ТС занимает определенные системные ресурсы, а также засоряет пространство имен, то имеет смысл освободить (удалить) ТС тогда, когда в ней больше нет необходимости. Для этого предусмотрен следующий оператор:
RELEASE SAVEPOINT
Данная команда удаляет указанную и все последующие ТС из контекста транзакции. Опция ONLY позволяет удалить только указанную ТС, сохранив последующие. Если ТС не была освобождена явно, то она будет автоматически удалена по завершению транзакции.
Примечание. Опция ONLY является нестандартным расширением и не поддерживается в InterBase 7.1.
Ниже приведен простейший пример работы ТС:
create table test (id int);
commit;
insert into test (id) values (1);
commit;
insert into test (id) values (2);
savepoint y;
delete from test;
select * from test; — возвращает пустой набор
rollback to y;
select * from test; — возвращает две записи
rollback;
select * from test; — возвращает одну запись
Теперь приведем пример использования ТС в бизнес-логике. Допустим, в приложении есть операция массовой отработки документов в учете, причем в случае возникновения ошибки требуется показать ее на экран (или сохранить для последующего показа всех ошибок списком) и допустить продолжение данной массовой операции. Так как операция отработки документа не атомарна, использование штатных средств обработки исключений на клиенской стороне нам не подходит, ибо мы не можем продолжать транзакцию зная, что исключение откатило только половину операции. Такая задача может быть решена отработкой каждого документа в отдельной транзакции, последовательно. Но это влияет на внутренние ресурсы сервера (количество записей в TIP, инкремент счетчика транзакций), так что не является оптимальным вариантом. Кроме того, если есть необходимость зафиксировать набор документов в рамках процесса отработки (например, посредством единого режима изоляции транзакции или явной блокировки типа SELECT … WITH LOCK), то приходим к жесткой необходимости использовать именно одну транзакцию для всего пакета изменений. При использовании же ТС просто используется следующий алгоритм (в псевдокоде):
START TRANSACTION;
OPEN C FOR ( SELECT … );
FOR ( C ) DO
LOOP
TRY
SAVEPOINT DOC;
// пакет команд отработки одного документа в учете
EXCEPT
ROLLBACK TO SAVEPOINT DOC;
// заносим ошибку в протокол или выводим на экран
END
END
CLOSE C;
COMMIT;
Примечание. Использование ТС в циклах имеет небольшое дополнительное удобство – нет необходимости каждый раз вызывать RELEASE, так как повторная установка ТС автоматически удаляет предыдущую ТС с таким же именем.
Другий пример – неоткатываемый аудит. Пусть, например, нам нужно каждое действие сопровождать записью в какой-либо журнал, причем так, чтобы в случае ошибки запись в журнале аудита оставалась (с соответствующей пометкой):
START TRANSACTION;
INSERT INTO AUDIT_LOG (ID, EVENT, STATUS) VALUES (:ID, :EVENT, 1);
SAVEPOINT OPER;
TRY
// действия над БД
EXCEPT
ROLLBACK TO SAVEPOINT OPER;
UPDATE AUDIT_LOG SET STATUS = 0 WHERE /> END
COMMIT;
Точки сохранения в PSQL
Теперь обсудим весьма тонкий момент, а именно использование пользовательских ТС в процедурах и триггерах. На первый взгляд, это выглядит очень заманчивым и полезным, тем более что данная функциональность декларирована в InterBase 7.1. Но действительно ли все так красиво и безоблачно?
Во-первых, ТС не должны нарушать атомарность SQL-операторов. Это значит, что ни одна из команд не может быть отменена частично. Так как EXECUTE PROCEDURE является допустимым SQL-оператором, а операторы обновления могут приводить к исполнению триггеров, приходим к вопросу области видимости ТС. Совершенно очевидно, что для удовлетворения указанного требования атомарности, команды управления ТС в процедуре не должны иметь доступ к ТС транзакции (установленными через глобальный оператор SAVEPOINT), а также ТС процедуры должны быть локальными и иметь область действия, определяемой данной процедурой. То есть мы можем иметь ТС с именем S1 как в транзакции, так и в процедурах и триггерах, выполняющихся в контексте данной транзакции, причем они будут изолированы друг от друга. Отметим, что именно так это и реализовано в InterBase 7.1.
Во-вторых, возникает вопрос – а как пользовательские ТС в PSQL будут пересекаться с внутренними ТС, управляемыми сервером?
Рассмотрим простой пример использования ТС в PSQL, предлагаемый корпорацией Borland в документации к серверу InterBase 7.1:
CREATE PROCEDURE ADD_EMP_PROJ2 (
EMP_NO SMALLINT,
EMP_NAME VARCHAR(20),
PROJ_ID CHAR(5) )
AS
BEGIN
BEGIN
SAVEPOINT EMP_PROJ_INSERT;
INSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID) VALUES (:EMP_NO, :PROJ_ID);
WHEN SQLCODE -530 DO
BEGIN
ROLLBACK TO SAVEPOINT EMP_PROJ_INSERT;
EXCEPTION UNKNOWN_EMP_ID;
END
END
END
Данный пример демонстрирует обработку исключительных ситуаций с использованием ТС. То есть при возникновении исключения с кодом –530 (нарушение ссылочной целостности по внешнему ключу) мы откатываем операцию вставки и инициируем пользовательское исключение. На самом деле, пример абсолютно бесполезный, ибо ТС здесь не нужна:
BEGIN
INSERT INTO …
WHEN SQLCODE –530 DO
EXCEPTION unknown_emp_id;
END
Этот код выполнит ту же самую функцию, ибо сервер сам откатит операцию INSERT при возникновении в ней исключения.
Рассмотрим чуть более сложный вариант:
FOR SELECT ID, … INTO :REC_ID, …
BEGIN
SAVEPOINT S1;
INSERT INTO TABLE1 …
INSERT INTO TABLE2 …
INSERT INTO TABLE3 …
EXECUTE PROCEDURE …
…
WHEN ANY DO
BEGIN
ROLLBACK TO SAVEPOINT S1;
ERROR = REC_ID;
SUSPEND;
END
END
Здесь мы пытаемся обработать все документы, но не прекращая работу при сбое, а выдавая на выход процедуры все неудачные попытки. Стандартная логика сервера здесь отменит ошибочный оператор и передаст управление в обработчик, который уже откатит действия всего блока. Таким образом, с помощью пользовательских ТС мы легко превращаем EXIT-обработчик в UNDO в случае необходимости. Очевидно, этой же функциональности можно добиться и стандартными средствами:
FOR SELECT ID, … INTO :REC_ID, …
BEGIN
BEGIN
INSERT INTO TABLE1 …
INSERT INTO TABLE2 …
INSERT INTO TABLE3 …
EXECUTE PROCEDURE …
…
END
WHEN ANY DO
BEGIN
ERROR = REC_ID;
SUSPEND;
END
END
Здесь все операторы внутри цикла будут отменены в случае исключения автоматически, ибо они находятся в атомарном блоке, посредством которого был выключен механизм ТС-фреймов для SQL-операторов. После чего сервер раскрутит цепочку вложенных блоков и передаст управление в обработчик.
Получается, что практически во всех случаях можно реализовать ту же семантику на штатных механизмах сервера, то есть на внутренних системных ТС вместо пользовательских, ценой несколько большей громоздкости исходного кода. Таким образом, ТС в PSQL есть не что иное, как более простая и понятная альтернатива явному управлению BEGIN…WHEN…END блоками.
Разумеется, существуют ситуации, когда применение пользовательских ТС оправдано. Например, может понадобиться откатить несколько операторов или итераций цикла в зависимости от какого-либо сложного условия. Подобной функциональности будет нелегко добиться стандартными средствами. Однако, понимаемость подобной логики оставляет желать лучшего и зачастую приводит к трудноуловимым ошибкам.
Но теперь вернемся от теории к практике и проверим наши рассуждения в InterBase 7.1. Результат откровенно удручает – ни один из примеров, приведенных выше, не работает (. ), выдавая ошибку:
Statement failed, SQLCODE = -504
Savepoint unknown.
Даже первый пример, который был взят из Release Notes (!). Но совсем примитивные примеры типа:
SAVEPOINT S1;
INSERT …
ROLLBACK TO SAVEPOINT S1;
- ТС образуют стек и могут быть отменены только последовательно
- Каждый блок PSQL-кода с обработчиком заключается во фрейм
SAVEPOINT S1;
…
BEGIN
…
ROLLBACK TO SAVEPOINT S1;
…
WHEN
неработоспособен по определению, ибо для отката ТС S1 понадобится удалить и системную ТС, созданную сервером для обработки исключений в BEGIN…END блоке. А это было бы действием, приводящим к нарушению внутреннего журнала отмены и потенциальной порче БД. Слава богу, в Borland не дошли до такой кардинальной реализации и сервер пытается отменить непосредственно предыдущую (последнюю) ТС, если она имеет данное имя. Так как системные ТС безымянны, то в этом случае такая попытка обречена на провал, что мы и наблюдаем в виде упомянутого сообщения об ошибке. Отсюда можно сделать вывод, что механизм работы с ТС в PSQL ограничен единым уровнем вложенности в случае блока с WHEN-обработчиком.
Но, как оказалось, самое интересное еще впереди. Очень занимательна реакция сервера на ошибку, инициированную оператором ROLLBACK TO SAVEPOINT или RELEASE SAVEPOINT. Продемонстрируем это на примитивном примере:
BEGIN
INSERT INTO TABLE1 …
ROLLBACK TO SAVEPOINT S1;
INSERT INTO TABLE2 …
END
Здесь эмулирована ошибка ненахождения нужной ТС в пределах даже одного блока кода. Как и ожидалось, выполнение процедуры возвращает ту же ошибку. Но. Исполнение процедуры на этом не заканчивается, а вместо этого выполняется второй INSERT (легко проверяется его заменой на оператор вида EXCEPTION E_TEST). Спрашивается, зачем? Но это не все. Оказывается, что данную ошибку не удается обработать в процедуре, то есть следующий код:
INSERT INTO TABLE1 …
BEGIN
ROLLBACK TO SAVEPOINT S1;
WHEN ANY DO
EXCEPTION E_TEST;
END
не выбросит исключения E_TEST, как можно бы было ожидать. Но и это еще не все. Несмотря на то, что выполняется код, находящийся после ROLLBACK TO SAVEPOINT, это в результате абсолютно ни к чему не приводит. То есть в случае возникновения в процедуре указанной ошибки, все изменения, вызванные данной процедурой, будут безусловно (!) отменены. Независимо от того, какой код выполнялся до или после этой команды. Разъяснение этого феномена оставим инженерам Borland’а.
Резюме: существуют особенности логики ТС, которые затрудняют реализацию их поддержки в PSQL в полном объеме. Анализ поведения InterBase 7.1 это полностью подтверждает. Основной причиной этого является наличие системных ТС, взаимодействие с которыми со стороны пользовательских ограничено из-за требований целостности данных. Именно по этим соображениям данная функциональность не доступна в Firebird и Yaffil.
Примечание. К слову, эти же причины не дают возможности использовать commit/rollback retaining в PSQL, ибо при этом будет разрушен ТС-фрейм процедуры.
Точки сохранения в распределенных транзакциях
InterBase 7.1 также декларирует возможность работы с ТС в распределенных транзакциях. Для этого введены три новые функции API:
ISC_STATUS isc_start_transaction(ISC_STATUS* status,
isc_tr_handle* trans, char* name);
ISC_STATUS isc_release_transaction(ISC_STATUS* status,
isc_tr_handle* trans, char* name);
ISC_STATUS isc_rollback_transaction(ISC_STATUS* status,
isc_tr_handle* trans, char* name, short option);
Как видно из прототипа, эти функции не получают дескриптор соединения (database handle), то есть соответствующие SQL-команды посылаются на все БД, задействованные транзакцией. Выглядит абсолютно логичным, ибо, формально говоря, ТС – это часть транзакции, а не соединения. Но здесь есть один нюанс. Рассмотрим следующий фрагмент программы (обработка ошибок опущена):
/* Подключаемся к БД */
isc_attach_database(status, 0, database1, &db1, 0, NULL);
isc_attach_database(status, 0, database2, &db2, 0, NULL);
/* Стартуем распределенную транзакцию */
isc_start_transaction(status, &trans, 2, &db1, 0, NULL, &db2, 0, NULL);
/* Создаем ТС */
isc_start_savepoint(status, &trans, «A»);
/* Удаляем ТС явно, через дескриптор второго соединения */
isc_dsql_execute_immediate(status, &db2, &trans, 0, «RELEASE SAVEPOINT A», 1, NULL);
/* Откатываемся до ТС */
isc_rollback_savepoint(status, &trans, «A», 0);
/* Коммитим распределенную транзакцию *
/ isc_commit_transaction (status, &trans);
/* Отключаемся от БД*/
isc_detach_database(status, &db1);
isc_detach_database(status, &db2);
В результате отката до ТС я ожидаю, что этот откат произойдет в обоих БД, с которыми я работаю. Затем я подтверждаю транзакцию, после чего я должен увидеть все свои данные на месте, ибо операторы DELETE были отменены. И так бы оно и было, если бы не выполненный вручную «RELEASE SAVEPOINT A». Откат ТС сначала выполнился для первого соединения и все изменения были отменены. Затем та же операция была предпринята для второго соединения и . ой . а ТС ведь уже и нету. Возвращаем клиенту ошибку. Но ведь откат одного из DELETE прошел успешно (!) Получаем ситуацию, когда распределенная операция нарушила свою собственную целостность и не позволяет корректно обработать данный случай. Механизм двухфазной фиксации транзакций, призванный исключить такие случаи как класс, просто не предназначен для работы с ТС. То есть новые функции InterBase 7.1 просто формируют соответствующие SQL-команды и циклически исполняют их для всех задействованных БД. В случае отказа хотя бы одной – возвращается ошибка. Которая, в общем случае, никаким образом не может охарактиризовать текущую ситуацию с точки зрения корректности операции как единого целого.
Разумеется, можно сказать, что должен использоваться только один способ работы с ТС – либо через SQL, либо через API. И что теперь вы прекрасно знаете, чем это может кончиться. Однако, новый API для работы с ТС в InterBase 7.1 может быть полностью заменен стандартными средствами сервера и является избыточным и внушающим ложные предположения о правильной работе распределенных ТС. Сервер должен либо пресекать возможность явно управлять ТС в отдельных соединениях в случае распределенных транзакций, либо вообще не декларировать их работоспособность. Отмечу, что разработчики Firebird и Yaffil выбрали последний вариант и предпочли не давать пользователям такой сомнительной возможности.
Впервые опубликовано на www.ibase.ru.
Copyright iBase.ru © 2002-2023
Что такое транзакция
Транзакция — это набор операций по работе с базой данных (БД), объединенных в одну атомарную пачку.
Транзакционные базы данных (базы, работающие через транзакции) выполняют требования ACID, которые обеспечивают безопасность данных. В том числе финансовых данных =) Поэтому разработчики их и выбирают.
Я расскажу о том, что такое транзакция. Как ее открыть, и как закрыть. И почему это важно — закрывать транзакцию. И тогда при написании запросов к базе у вас будет осознанное понимание, что происходит там, под капотом, и зачем же нужен этот обязательный коммит после апдейта.
Что такое транзакция
Транзакция — это архив для запросов к базе. Он защищает ваши данные благодаря принципу «всё, или ничего».
Представьте, что вы решили послать другу 10 файликов в мессенджере. Какие есть варианты:
- Кинуть каждый файлик отдельно.
- Сложить их в архив и отправить архив.
Вроде бы разницы особой нет. Но что, если что-то пойдет не так? Соединение оборвется на середине, сервер уйдет в ребут или просто выдаст ошибку.
В первом случае ваш друг получит 9 файлов, но не получит один.
Во втором не получит ничего. Нет промежуточных состояний. Или получил всё, или не получил ничего. Но зато если произошла ошибка, вы снова перешлете сообщение. И друг получит все файлики разом, не придется проверять «не потерялся ли кто».
Казалось бы, ну недополучил файлик, что с того? А если это критично? Если это важные файлики? Например, для бухгалтерии. Потерял один файлик? Значит, допустил ошибку в отчете для налоговой. Значит, огребешь штраф и большие проблемы! Нет, спасибо, лучше файлы не терять!
И получается, что тебе надо уточнять у отправителя:
— Ты мне сколько файлов посылал?
— 10
— Да? У меня только 9. Давай искать, какой продолбался.
И сидите, сравниваете по названиям. А если файликов 100 и потеряно 2 штуки? А названия у них вовсе не «Отчет 1», «Отчет 2» и так далее, а «hfdslafebx63542437457822nfhgeopjgrev0000444666589.xml» и подобные. Уж лучше использовать архив! Тогда ты или точно всё получил, или не получил ничего и делаешь повторную попытку отправки.
Так вот! Транзакция — это тот же архив для запросов. Принцип «всё, или ничего». Или выполнены все запросы, которые разработчик упаковал в одну транзакцию, или ни один.
Допустим, вы переводите все деньги с одной карточки на другую. Выглядит это «внутри» системы как несколько операций:
delete from счет1 where счет = счет 1
insert into счет2 values (‘сумма’)
Принцип «всё или ничего» тут очень помогает. Было бы обидно, если бы деньги со счета1 списались, но на счет2 не поступили. Потому что соединение оборвалось или вы в номере счета опечатались и система выдала ошибку.
Но благодаря объединению запросов в транзакцию при возникновении ошибки зачисления мы откатываем и операцию списания. Деньги снова вернулись на счет 1!
Если говорить по-научному, то транзакция — упорядоченное множество операций, переводящих базу данных из одного согласованного состояния в другое. Согласованное состояние — это состояние, которое подходит под бизнес-логику системы. То есть у нас не остается отрицательный баланс после перевода денег, номер счета не «зависает в воздухе», не привязанный к человеку, и тому подобное.
Как отправить транзакцию
Чтобы обратиться к базе данных, сначала надо открыть соединение с ней. Это называется коннект (от англ. connection, соединение). Коннект — это просто труба, по которой мы посылаем запросы.
Чтобы сгруппировать запросы в одну атомарную пачку, используем транзакцию. Транзакцию надо:
- Открыть.
- Выполнить все операции внутри.
- Закрыть.
Как только мы закрыли транзакцию, труба освободилась. И ее можно переиспользовать, отправив следующую транзакцию.
Можно, конечно, каждый раз закрывать соединение с БД. И на каждое действие открывать новое. Но эффективнее переиспользовать текущие. Потому что создание нового коннекта — тяжелая операция, долгая.
При настройке приложения администратор указывает, сколько максимально открытых соединений с базой может быть в один момент времени. Это называется пул соединений — количество свободных труб.
Разработчик берет соединение из пула и отправляет по нему транзакцию. Как только транзакция закрывается (неважно, успешно она прошла или откатилась), соединение возвращается в пул, и его может использовать следующая бизнес-операция.
Как открыть транзакцию
Зависит от базы данных. В Oracle транзакция открывается сама, по факту первой изменяющей операции. А в MySql надо явно писать «start transaction».
Как закрыть транзакцию
Тут есть 2 варианта:
- COMMIT — подтверждаем все внесенные изменения;
- ROLLBACK — откатываем их;
И вся фишка транзакционной базы в том, что база сначала применяет запрос «виртуально», реально ничего в базе не изменив. Ты можешь посмотреть, как запрос изменит базу, ничего при этом не сохраняя.
Например, я пишу запрос:
insert into clients (name, surname) values ('Иван', 'Иванов'); -- добавь в таблицу клиентов запись с именем «Иван» и фамилиев «Иванов»Запрос выполнен успешно, хорошо! Теперь, если я сделаю select из этой таблицы, прям тут же, под своим запросом — он находит Иванова! Я могу увидеть результат своего запроса.
Но! Если открыть графический интерфейс программы, никакого Иванова мы там не найдем. И даже если мы откроем новую вкладку в sql developer (или в другой программе, через которую вы подключаетесь к базе) и повторим там свой select — Иванова не будет.
А все потому, что я не сделала коммит, не применила изменения:
insert into clients (name, surname) values ('Иван', 'Иванов'); commit;Я могу добавить кучу данных. Удалить полтаблицы. Изменить миллион строк. Но если я закрою вкладку sql developer, не сделав коммит, все эти изменения потеряются.
Когда я впервые столкнулась с базой на работе, я часто допускала такую ошибку: подправлю данные «на лету» для проведения теста, а в системе ничего не меняется! Почему? Потому что коммит сделать забыла.
На самом деле это удобно. Ведь если ты выполняешь сложную операцию, можно посмотреть на результат. Например, удаляем тестовые данные. Написали кучу условий из серии:
Удалили. Делаем select count — посмотреть количество записей в таблице. А там вместо миллиона строк осталось 100 тысяч! Если база реальная, то это очень подозрительно. Врядли там было СТОЛЬКО тестовых записей.
Проверяем свой запрос, а мы там где-то ошиблись! Вместо «И» написали «ИЛИ», или как-то еще. Упс. Хорошо еще изменения применить не успели. Вместо коммита делаем rollback.
Тут может возникнуть вопрос — а зачем вообще нужен ROLLBACK? Ведь без коммита ничего не сохранится. Можно просто не делать его, и всё. Но тогда транзакция будет висеть в непонятном статусе. Потому что ее просто так никто кроме тебя не откатит.
Или другой вариант. Нафигачили изменений:
Но видим, что операцию надо отменять. Проверочный select заметил, что база стала неконсистентной. А мы решили «Ай, да ладно, коммит то не сделали? Значит, оно и не сохранится». И вернули соединение в пул.
Следующая операция бизнес-логики берет это самое соединение и продолжает в нем работать. А потом делает коммит. Этот коммит относился к тем 3 операциям, что были внутри текущей транзакции. Но мы закоммитили еще и 10 других — тех, что в прошлый раз откатить поленились. Тех, которые делают базу неконсистентной.
Так что лучше сразу сделайте откат. Здоровей система будет!
Итого
Транзакция — набор операций по работе с базой данных, объединенных в одну атомарную пачку.
Одной операции всегда соответствует одна транзакция, но в рамках одной транзакции можно совершить несколько операций (например, несколько разных insert можно сделать, или изменить и удалить данные. ).
Чтобы отправить транзакцию к базе, нам нужно создать соединение с ней. Или переиспользовать уже существующее. Соединение называют также коннект (англ connection) — это просто труба, по которой отправляются запросы. У базы есть пул соединений — место, откуда можно взять любое и использовать, они там все свободные.
В некоторых системах транзакцию нужно открыть, в других она открывается сама. А вот закрыть ее нужно самостоятельно. Варианты:
- COMMIT — подтверждаем все внесенные изменения;
- ROLLBACK — откатываем их;
Делая комит, мы заканчиваем одну бизнес-операцию, и возвращаем коннект в пул без открытой транзакции. То есть просто освобождаем трубу для других. Следующая бизнес-операция берет эту трубу и фигачит в нее свои операции. Поэтому важно сделать rollback, если изменения сохранять не надо. Не откатите и вернете соединение в пул? Его возьмет кто-то другой и сделает коммит. Своих изменений, и ваших, неоткаченных.
Не путайте соединение с базой (коннект) и саму транзакцию. Коннект — это просто труба, операции (update, delete…) мы посылаем по трубе, старт транзакции и commit /rollback — это группировка операций в одну атомарную пачку.
См также:
Блокировки транзакций — что может пойти не так при одновременном редактировании
Введение в транзакции в MySQL
Транзакция — это операция, состоящая из одного или нескольких запросов к базе данных. Суть транзакций — обеспечить корректное выполнение всех запросов в рамках одной транзакции, а так-же обеспечить механизм изоляции транзакций друг от друга для решения проблемы совместного доступа к данным.
Любая транзакция либо выполняется полностью, либо не выполняется вообще.
В транзакционной модели есть два фундаментальных понятия: COMMIT и ROLLBACK. COMMIT означает фиксацию всех изменений в транзакции. ROLLBACK означает отмену (откат) изменений, произошедших в транзакции.
При старте транзакции все последующие изменения сохраняются во временном хранилище. В случае выполнения COMMIT, все изменения, выполненные в рамках одной транзакции, сохранятся в физическую БД. В случае выполнения ROLLBACK произойдет откат и все изменения, выполненные в рамках этой транзакции, не сохранятся.
В MySQL транзакции поддерживаются только таблицами innoDB. Таблицы MyISAM транзакции не поддерживают. В innoDB по умолчанию включен autocommit, это значит, что по умолчанию каждый запрос эквивалентен одной транзакции.
Транзакция начинается со специального запроса «START TRANSACTION», либо «BEGIN». Чтобы закончить транзакцию, нужно либо зафиксировать изменения (запрос COMMIT), либо откатить их (запрос ROLLBACK).
Пример с COMMIT:
set autocommit=0; //отключаем autocommit Start transaction; (также, можно написать BEGIN; ) …какие-то действий с БД (insert, update,delete…) commit; //Фиксация действий, запись их в физическую БД
Пример с ROLLBACK:
set autocommit=0; //отключаем autocommit Start transaction; …какие-то действия с БД (insert, update,delete…) rollback; // отменяем серию действий, не производим запись в физическую БД
В MySQL не существует механизма вложенных транзакций. Одно соединение с БД — одна транзакция. Новая транзакция в пределах одного соединения может начаться только после завершения предыдущей.
Для некоторых операторов нельзя выполнить откат с помощью ROLLBACK. Это операторы языка определения данных (Data Definition Language — DDL). Сюда входят запросы CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.
Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):
- ALTER TABLE
- DROP DATABASE
- LOAD MASTER DATA
- SET AUTOCOMMIT = 1
- BEGIN
- DROP INDEX
- LOCK TABLES
- START TRANSACTION
- CREATE INDEX
- DROP TABLE
- RENAME TABLE
- TRUNCATE TABLE
Обратите внимание, что в случае SQL ошибки, транзакция сама по себе не откатится. Обычно ошибки обрабатываются уже с помощью sql wrapper’ов в самом приложении, таких как PHP PDO например. Если вы захотите откатывать изменения в случае ошибки прямо в MySQL, можно создать специальную процедуру и уже в ней выполнять ROLLBACK в обработчике :
CREATE PROCEDURE prc_test() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; //Вот здесь откатываем транзакцию в случае ошибки END; START TRANSACTION; INSERT INTO tmp_table VALUES ('null'); COMMIT; END; CALL prc_test();
Но этот способ скорее просто для ознакомления, а не руководство к действию. Почему? Я крайне не рекомендую так поступать, так как в основном ошибки базы данных обрабатываются с помощью SQL оберток на стороне приложения, таких как PHP PDO например, чтобы оттуда полностью управлять транзакциями.
Рассмотрим практический пример: есть 2 таблицы, пользователи — users и информация о пользователях — user_info. Представим, что нам нужно либо выполнить 3 запроса к базе данных, либо не выполнять их вообще, так как иначе это приведет к сбоям в работе приложения.
Start transaction; INSERT INTO user (id, nik) VALUES (1, 'nikola'); INSERT INTO user_info (id, id_user, item_name, item_value) VALUES (1, 1, 'Имя', 'Николай'); INSERT INTO user_info (id, id_user, item_name, item_value) VALUES (2, 1, 'Возраст', '24'); commit;
В целом я думаю принцип работы транзакции понятен. Но все не так просто. Существуют проблемы параллельных транзакций. Рассмотрим пример. Представим, что во время выполнения этой транзакции, другой пользователь создал вторую параллельную транзакцию и сделал запрос SELECT * FROM user после того, как в нашей транзакции был выполнен первый запрос «INSERT INTO user (id, nik) VALUES (1, ‘nikola’)». Что увидит пользователь второй транзакции? Сможет ли он увидеть вставленную запись даже тогда, когда результаты первой транзакции еще не зафиксировались (не произошел COMMIT)? Или он сможет увидеть изменения только после того, как результаты первой транзакции будут зафиксированы? Оказывается имеют место быть оба варианта. Все зависит от уровня изоляции транзакции.
У транзакций есть 4 уровня изоляции:
- 0 — Чтение неподтверждённых данных (грязное чтение) (Read Uncommitted, Dirty Read) — самый низкий уровень изоляции. При этом уровне возможно чтение незафиксированных изменений параллельных транзакций. Как раз в этом случае второй пользователь увидит вставленную запись из первой незафиксированной транзакции. Нет гарантии, что незафиксированная транзакция будет в любой момент откачена, поэтому такое чтение является потенциальным источником ошибок.
- 1 — Чтение подтверждённых данных (Read Committed) — здесь возможно чтение данных только зафиксированных транзакций. Но на этом уровне существуют две проблемы. В этом режиме строки, которые участвуют в выборке в рамках транзакции, для других параллельных транзакций не блокируются, из этого вытекает проблема № 1: «Неповторяемое чтение» (non-repeatable read) — это ситуация, когда в рамках транзакции происходит несколько выборок (SELECT) по одним и тем же критериям, и между этими выборками совершается параллельная транзакция, которая изменяет данные, участвующие в этих выборках. Так как параллельная транзакция изменила данные, результат при следующей выборке по тем же критериям в первой транзакции будет другой. Проблема № 2 — «Фантомное чтение» — этот случай рассмотрен ниже.
- 2 — Повторяемое чтение (Repeatable Read, Snapshot) — на этом уровне изоляции так же возможно чтение данных только зафиксированных транзакций. Так же на этом уровне отсутствует проблема «Неповторяемого чтения», то есть строки, которые участвуют в выборке в рамках транзакции, блокируются и не могут быть изменены другими параллельными транзакциями. Но таблицы целиком не блокируются. Из-за этого остается проблема «фантомного чтения». «Фантомное чтение» — это когда за время выполнения одной транзакции результат одних и тех же выборок может меняться по причине того, что блокируется не вся таблица, а только те строки, которые участвуют в выборке. Это означает, что параллельные транзакции могут вставлять строки в таблицу, в которой совершается выборка, поэтому два запроса SELECT * FROM table могут дать разный результат в разное время при вставке данных параллельными транзакциями.
- 3 — Сериализуемый (Serializable) — сериализуемые транзакции. Самый надежный уровень изоляции транзакций, но и при этом самый медленный. На этом уровне вообще отсутствуют какие либо проблемы параллельных транзакций, но за это придется платить быстродействием системы, а быстродействие в большинстве случаев крайне важно.
По умолчанию в MySQL установлен уровень изоляции № 2 (Repeatable Read). И, как я считаю, разработчики MySQL не зря сделали по умолчанию именно этот уровень, так как он наиболее удачный для большинства случаев. С первого раза может показаться, что самый лучший вариант № 3 — он самый надежный, но на практике вы можете испытать большие неудобства из-за очень медленной работы вашего приложения. Помните, что многое зависит не от того, насколько хорош уровень изоляции транзакций в БД, а от того, как спроектировано ваше приложение. При грамотном программировании, можно даже использовать самый низкий уровень изоляции транзакций — все зависит от особенностей структуры и грамотности разработки вашего приложения. Но ненужно стремиться к самому низкому уровню изоляции — нет, просто если вы используйте не самый защищенный режим, следует помнить о проблемах параллельных транзакций, в этом случае вы не растеряетесь и все сделайте правильно.
SET TRANSACTION — этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса.
- SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
Существующие соединения не затрагиваются. Для выполнения этого оператора нужно иметь привилегию SUPER. Применение ключевого слова SESSION устанавливает уровень изоляции по умолчанию всех будущих транзакций только для текущего сеанса.
Вы можете также установить начальный глобальный уровень изоляции для сервера mysqld, запустив его с опцией —transaction-isolation
2.19. Транзакции
Язык запросов Transact-SQL взял свое название от слова транзакция. Я думаю, что Microsoft не зря сконцентрировало на этом понятии особое внимание, ведь транзакции действительно являются очень мощным средством управления базой данных.
Прежде чем мы рассмотрим примеры, немного опустимся в теорию, чтобы вы смогли лучше понять, что такое транзакции и с чем их едят. Почему-то для многих этот вопрос является наиболее сложным, но все оказывается достаточно простым, если правильно подобрать пример и наглядно показать его работу.
Рассмотрим классическую задачу – банковскую проводку. Допустим, что у нас есть таблица из двух полей – номер счета в банке и сумма денег на этом счету. Нам необходимо перевести деньги с одного счета на другой. Для этого нужно выполнить запрос UPDATE, чтобы уменьшить сумму первого счета на нужную сумму. После этого выполняем UPDATE, чтобы увеличить значение второго счета. Все вроде бы нормально. А что, если после уменьшения первого счета выключат свет и сервер не успеет пополнить другой счет? Деньги уже сняты, но никуда не записаны, а значит, они пропали.
Если сначала пополнять счет, а потом снимать деньги, то если снятие не успеет произойти, то банк может оказаться банкротом, ведь появляется лишняя сумма, снятия не происходит.
Некоторые могут сказать, что сбои в электроэнергии и работе компьютера происходят редко, но помимо этого бывают блокировки записей. Один пользователь может заблокировать запись для изменения и это не позволит запросу изменить ее и снятие или увеличение счета не произойдет.
Проблему решает транзакция. Перед выполнением операций обновления необходимо явно начать транзакцию. После этого выполняем две операции UPDATE и по их окончанию завершаем транзакцию. Если в момент выполнения одного из запросов происходит сбой, то все изменения, происшедшие после начала транзакции отменяются.
Существует две разновидности транзакций в SQL Server:
- Скрытые транзакции, каждый оператор, такой как INSERT, UPDATE или DELETE выполняется в транзакции. Неявными транзакциями можно управлять и об этом мы поговорим в разделе 4.1.2;
- Явные транзакции объявленные пользователем – операторы, сгруппированные в BEGIN TRANSACTION и COMMIT TRANSACTION.
Очень важно понимать, что транзакции необходимы только при модификации данных, т.е. использовании операторов INSERT, UPDATE или DELETE. Простая выборка SELECT не изменяет данных, и запоминать или откатывать нечего. Нет, выполнять операции выборки в транзакции можно, но если транзакция не изменяет данные, то незачем ее вообще начинать.
В транзакции можно включать далеко не все операторы. Внутри транзакции не может быть следующих операторов:
- ALTER DATABASE
- BUCKUP LOG
- CREATE DATABASE
- DROP DATABASE
- RECONFIGURE
- RESTORE DATABASE
- RESTORE LOG
- UPDATE STATISTICS
Работа транзакций обеспечивается с помощью журнала транзакция базы данных. Так как журнал записывает всю активность и происходящие изменения, SQL Server может восстанавливать данные автоматически в момент потери питания, ошибки системны, проблемы клиенты или запроса отмены журнала.
Сервер SQL автоматически гарантирует, что все завершенные транзакции отражаются в базе данных в момент и после системной ошибки. Для этого используется журнал транзакций, с помощью которого завершаются удачно законченные транзакции и отменяются не завершенные.
С помощью скрытых транзакция сервер гарантирует, что если оператор добавления, изменения или удаления данных выполнен удачно, то данные будут сохранены в таблице. Если во время изменений произошла ошибка, то все изменения откатываются. Представим, что оператор UPDATE изменяет 1000 строк. Если на 500-й строке произошла ошибка, то сервер откатывает все уже сделанные изменения, как если бы они происходили в явной транзакции.
Пользователь может устанавливать точку сохранения или маркер внутри транзакции. Точка сохранения определяет расположение, до которого транзакция может быть возвращена. Транзакция должна продолжить выполнения или должны быть полностью откатана.
Начало транзакции в MS SQL Server имеет следующий синтаксис:
BEGIN TRAN[SACTION] [transaction name | @transaction name variable [WITH MARK[‘description]]]
Опция Transaction name указывает имя транзакции определенное пользователем. Опция WITH MARK указывает, что транзакция маркирована в журнале транзакций.
По завершению транзакции, изменения необходимо запомнить в базе данных. Для этого используется команда COMMIT:
COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ]
Отмена транзакции и всех изменений производиться командой ROLLBACK, которая в общем виде выглядит следующим образом:
ROLLBACK [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] ]
Очень важно понимать, если начата транзакция и изменены какие-то записи, то эти записи блокируются, пока транзакция не будет завершена. Давайте посмотрим это на примере, заодно познакомимся с самой командой. Выполните следующие команды в Query Analyzer:
-- Начинаем транзакцию BEGIN TRANSACTION -- Очищаем таблицу товаров DELETE Товары
Теперь откройте еще одну копию программы или установите новое соединение, выбрав меню File/Connect (Файл/Соединиться). В новом окне напишем и выполним следующий запрос:
SELECT * FROM Товары
Таблица товаров достаточно маленькая, но не смотря на это, запрос будет выполняться долго. А если быть точнее, он не выполниться, потому что во втором окне с другой сессией выполняется транзакция удаления, и эта транзакция еще не завершилась.
Нам удалять данные не нужно, поэтому давайте вернемся в первое окно, где мы создавали транзакции и выполним откат:
ROLLBACK TRANSACTION
Вот теперь запрос завершит свое выполнение, и при этом все данные останутся на месте, не смотря на то, что мы выполняли команду удаления всех записей. Данные не удалились, потому что команда выполнялась внутри транзакции, которую мы откатили.
Теперь проведем еще один эксперимент. Снова начинаем транзакцию и удаляем все записи. Теперь попробуйте выполнить запрос на выборку данных из этого же окна. Запрос завершиться моментально и в результате будет пустая выборка (ни одной строки). Получается, что для нашей сессии, которая удаляла строки, таблица является пустой.
Теперь откатываем транзакцию, выполняя команду ROLLBACK TRANSACTION. Снова выполняем запрос SELECT и видим, что данные вернулись на родину. Транзакция удачно отклонена и физического удаления из базы данных не произошло. Почему мы в этот раз без проблем смогли просмотреть таблицу, а из другой сессии просмотр изменяемой в транзакции таблицы не доступен? Блокировки происходит для всех сессий кроме той, которая выполняет транзакцию. В листинге 2.8 показан весь код эксперимента с подробными комментариями.
Листинг 2.8. Пример эксперимента с удалением данных в транзакции
-- Начинаем транзакцию BEGIN TRANSACTION -- Очищаем таблицу товаров DELETE Товары -- Проверяем и убеждаемся, что таблица пуста SELECT * FROM Товары -- Откатываем транзакцию ROLLBACK TRANSACTION -- Можете убедиться, что товары на месте SELECT * FROM Товары
Все эти команды нужно выполнять в одном и том же окне. К тому же, если в одном окне (сессии) вы начали транзакцию, то именно в этом окне вы должны ее завершить (COMMIT) или откатить (ROLLBACK).
Если в листинге 2.8 заменить вызов команды ROLLBACK TRANSACTION на COMMIT TRANSACTION, то произойдет физическое удаление всех записей из таблицы товаров. Теперь удаленные строки вернуть уже невозможно.
Теперь посмотрим еще один пример в листинге 2.9.
Листинг 2.9. Пример работы с транзакциями
-- Начинаем транзакцию BEGIN TRANSACTION -- Вставляем строку данных в -- таблицу товаров INSERT INTO Товары (Дата, [Название товара], Цена, Количество) VALUES ('3.3.2005', 'КАРТОФЕЛЬ', 12.50, 10) -- Обновить данные в последней строке UPDATE Товары SET Цена = 15 WHERE [Название товара] LIKE 'КАРТОФЕЛЬ' COMMIT TRANSACTION -- Обновить данные в последней строке UPDATE Товары SET Цена = 17 WHERE [Название товара] LIKE 'КАРТОФЕЛЬ' -- Откатить транзакцию ROLLBACK TRANSACTION -- Выбрать все данные из таблицы SELECT * FROM Товары
Тут достаточно много действий, поэтому давайте их рассмотрим поэтапно:
- Начинаем транзакцию;
- Добавляем запись о покупке товара с названием Картофель;
- Обновить цену картофеля, увеличив ее до 15 рублей;
- Завершаем транзакцию, запоминая изменения;
- Обновляем цену до 17 руб.;
- Откатываем транзакцию;
- Просматриваем содержимое таблицы.
Что произошло с содержимым таблицы? Запись о картофеле добавлена, а значит, все что было до запоминания изменений (шаг 4) выполнено удачно. А вот цена равна 17-ти рублям. Почему? Неужели на шаге 6 мы не откатили изменение цены? Да, отката не произошло, потому что новая транзакция не начиналась. На шаге 1 мы начали транзакцию, а на шаге 4 завершили. Новая транзакция не начиналась, а значит откатывать нечего и шаг 6 завершиться ошибкой:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Запрос ROLLBACK TRANSACTION не имеет соответствующего BEGIN TRANSACTION.
Мы говорили, что во время добавления, изменения и удаления записей автоматически и неявно начинается новая транзакция. Ее откатить мы не можем, и откатом неявных транзакций занимается только сам сервер.
Для каждого оператора BEGIN TRANSACTION должен быть только один оператор COMMIT TRANSACTION или ROLLBACK TRANSACTION.
А что если внутри транзакции начать новую транзакцию? Результат неожиданный и давайте его увидим на примере (см. листинг 2.10).
2.10. Вложенные транзакции
-- Начинаем транзакцию 1 BEGIN TRANSACTION -- Вставляем строку данных в таблицу товаров INSERT INTO Товары (Дата, [Название товара], Цена, Количество) VALUES ('4.3.2005', 'МОРКОВЬ', 11.30, 1) -- Начинаем транзакцию 2 BEGIN TRANSACTION -- Обновить данные в последней строке UPDATE Товары SET Цена = 14 WHERE [Название товара] LIKE 'МОРКОВЬ' ROLLBACK TRANSACTION -- Запоминаем изменения COMMIT TRANSACTION -- Выбрать все данные из таблицы SELECT * FROM Товары
Логика запроса следующая:
- Начать транзакцию;
- Вставить строку;
- Начать транзакцию;
- Обновить таблицу;
- Откатить транзакцию;
- Запомнить изменения.
По логике вещей, на шаге 5 мы должны были откатить вторую транзакцию (т.е. изменение таблицы), а на шаге 6 запоминаем транзакцию 1, в которой происходит добавление записи. Посмотрите содержимое таблицы. Ни добавления, ни тем более изменения. Почему? Если посмотреть сообщения, которые выдал сервер, то вы увидите, что на шаге 6 произошла ошибка о том, что нет соответствующего начала транзакции и нечего начинать. Получается, что оператор ROLLBACK TRANSACTION откатывает все начатые транзакции.
Но это не значит, что невозможно использовать вложенные транзакции. Просто откатывать транзакции нельзя на один шаг назад. Если заменить оператор ROLLBACK TRANSACTION на COMMIT, то ошибки не будет.
Посмотрим на листинг 2.11. В нем показан такой же пример, но с именованными транзакциями и без отката.
Листинг 2.11. Использование вложенных транзакций
BEGIN TRANSACTION T1 -- Вставляем строку данных в таблицу товаров INSERT INTO Товары (Дата, [Название товара], Цена, Количество) VALUES ('4.3.2005', 'МОРКОВЬ', 11.30, 1) -- Начинаем транзакцию 2 BEGIN TRANSACTION T2 -- Обновить данные в последней строке UPDATE Товары SET Цена = 14 WHERE [Название товара] LIKE 'МОРКОВЬ' COMMIT TRANSACTION T2 -- Запоминаем изменения COMMIT TRANSACTION T1 -- Выбрать все данные из таблицы SELECT * FROM Товары
В данном примере после операторов BEGIN TRANSACTION и COMMIT TRANSACTION указывается имя T1 и T2. Таким образом, мы идентифицируем транзакции и завершаем их в обратном порядке объявлению.
Но как же тогда можно откатывать транзакции до определенной точки? Достаточно просто и вложенные транзакции тут не причем. Для этого нужно просто поставить точку сохранения с помощью оператора SAVE TRAN, который имеет следующий вид:
SAVE TRAN [ SACTION ]
Минимум, что необходимо указать – это сам оператор и имя точки сохранения. Например, следующий оператор создает точку сохранения с именем point1:
SAVE TRAN point1
В листинге 2.12 показан пример, в котором наконец-то создаем строку, изменяем ее и откатываем только изменение, а не всю транзакцию, вместе с добавлением строки.
Листинг 2.12. Откат до определенной точки
-- Начинаем транзакцию 1 BEGIN TRANSACTION T1 -- Вставляем строку данных в -- таблицу товаров INSERT INTO Товары (Дата, [Название товара], Цена, Количество) VALUES ('4.3.2005', 'МОРКОВЬ', 11.30, 1) -- Сохраняем транзакцию SAVE TRAN ins_complete -- Обновить данные в последней строке UPDATE Товары SET Цена = 14 WHERE [Название товара] LIKE 'МОРКОВЬ' -- Откатываем транзакцию ROLLBACK TRANSACTION ins_complete -- Запоминаем изменения COMMIT TRANSACTION T1 -- Выбрать все данные из таблицы SELECT * FROM Товары
Давайте снова разобьем логику выполнения этого сценария по шагам, чтобы лучше увидеть происходящее:
- Начинаем транзакцию;
- Добавляем строку;
- С помощью оператора SAVE TRAN сохраняем состояние таблицы. Точнее сказать, ставим точку в журнале, ведь пока все изменения происходят только в журнале транзакций;
- Обновляем цену последней добавленной строки;
- Восстанавливаем состояние таблицы на точку сохранения, установленную на третьем шаге. В этот момент из журнала транзакций удаляется запись о необходимости обновить цену, а остается только запись о необходимости добавить строку;
- Запоминаем изменения, а в журнале транзакций находиться только добавление строки и именно это сохраняется в таблице товаров.
Последним этапом выбираются данные из таблицы товаров. Убедитесь, что данные в добавленной строке только те, которые были указаны при вставке таблицы.
Так как транзакции блокируют записи, я рекомендую делать их максимально быстрыми, и не рекомендуется использовать вложенных транзакций. Транзакции должны выполняться максимально быстро. Долгие транзакции увеличивают вероятность, что пользователи не получат доступ к заблокированным данным.
Следующие рекомендации позволят вам сделать транзакции как можно короче:
- Без особой надобности не выполняйте запросы SELECT. Выборка данных должна происходить вне транзакции;
- Для уменьшения времени транзакции, будьте внимательны, когда используете долго выполняемые операторы Transact-SQL, такие как циклы и создание объектов базы данных;
- Не требуйте от пользователя ввода данных во время выполнения транзакции. Делайте ввод данных до начала выполнения транзакции.
- Операторы INSERT, UPDATE и DELETE должны быть главными в транзакции и они должны быть написаны так, чтобы получать минимальный набор строк, что позволяет повысить скорость работы любого запроса.
- Все проверки данных и подготовительные расчеты необходимо произвести до начала транзакции. После оператора BEGIN TRANSACTION должно выполняться только изменение данных.