Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
В продолжении статей о языке PLpgSQL в PostgreSQL. Предыдущая статья этой серии здесь
Понятие транзакции
Транзакция является важнейшим понятием реляционных баз данных. Транзакция представляет собой последовательность действий, выполняющихся как одна операция с данными. Транзакция может быть выполнена полностью или не выполнена совсем. Транзакция должна соответствовать принципам ACID:
1. Atomicity (атомарность) — транзакция либо выполняется полностью, либо не выполняется совсем.
2. Consistency (согласованность) — после того, как транзакция завершиться, данные должны находиться в согласованном состоянии, в частности, должны выполняться все условия целостности баз данных.
3. Isolation (изолированность) — выполняющиеся одновременно транзакции не влияют друг на друга. Другими словами, изменения, которые происходят в ходе выполнения одной транзакции, не видны другим транзакциям, пока транзакция не завершена.
4. Durability (долговечность) — после того, как транзакция завершена, изменения в базе данных должны сохраняться, даже если произойдёт сбой в системе.
Есть важная особенность транзакционного механизма в PostgreSQL. При выполнении команд множества DML автоматически создаётся транзакция. Например, при выполнении команды update. В последнем случае команда update может менять в таблице большое количество данных. Но если в процессе выполнения этой команды произойдёт сбой, то состояние таблицы автоматически откатиться на момент перед выполнением этой команды.
Важно отметить, что при выполнении команды SQL может быть вызвана хранимая функция. Тогда вся хранимая функция будет выполняться в рамках созданной командой транзакции. Если в этой хранимой функции есть команды SQL, то все они будут выполняться в рамках созданной транзакции, не создавая своих собственных транзакций. Если из хранимой функции вызывается другая хранимая функция, то та также будет выполняться как часть созданной транзакций и не может создавать своих транзакций. В PostgreSQL отсутствует понятие вложенных транзакций, в отличие от некоторых других СУБД.
Транзакции в хранимых функциях и процедурах PostgreSQL
Ниже представлена хранимая функция. В ней имеются две команды insert. Вторая команда, если учесть, что столбец id является первичным ключом и в нём не могут стоять «значения» null, должна вызвать ошибку исключения. Но поскольку весь код функции содержится внутри одной транзакция, вся транзакция не будет завершена и предыдущая команда insert также не будет выполнена (точнее произойдёт её откат).
create or replace function test()
returns bool
as
$$
declare
nm1 text = 'Русского языка';
nm2 text;
begin
nm2 := null;
execute 'insert into facult (nazvanie) values ($1)' using nm1;
execute 'insert into facult (id) values ($1)' using nm2;
return TRUE;
end;
$$
language 'plpgsql';
Хранимые процедуры в PostgreSQL обладают возможностью использовать некоторые команды управления транзакциями, не доступные в хранимых функциях. Правда набор не столь разнообразен, как в других СУБД. Это команда rollback — откат транзакции на начало и команда commit — зафиксировать транзакцию. Пример хранимой процедуры представлен ниже.
create or replace procedure test2()
as
$$
declare
nm1 text = 'Русского языка';
nm2 integer = 0;
begin
nm2 := null;
execute 'insert into facult (nazvanie) values ($1)' using nm1;
if nm2 is null then
rollback;
else
execute 'insert into facult (id) values ($1)' using nm2;
end if;
commit;
end;
$$
language 'plpgsql';
Прокомментируем представленную выше хранимую процедуру.
1. Транзакция в хранимой процедуре начинается не с ключевого слова begin, а с первой команды DML. И далее все DML-команды в данной процедуре, а также в функциях и процедурах, которые вызваны командами select и call, будут выполняться в контексте данной транзакции.
2. Команда commit заканчивает данную транзакцию, фиксируя её. Также, если commit не используется, транзакция будет зафиксирована после конца работы команды call, которая вызвала данную процедуру.
3. Далее, если параметр nm2 равен null, то происходит откат (rollback) всей транзакции на её начало (перед первой командой insert). В противном случае выполняется вторая команда insert и транзакция фиксируется.
Отметим важный момент. Если из хранимой процедуры вызывается хранимая процедура с помощью команды call, то в ней также действуют команды управления транзакциями. Если есть целая цепочка вызовов через call, то это правило распространяется на всю цепочку. Это правило не распространяется, если вызывается хранимая функция (через select).
Ещё один интересный способ управления транзакциями, это использование исключений.
create or replace procedure test22()
as
$$
declare
nm1 text = 'Русского языка';
nm2 integer = 0;
begin
nm2 := null;
execute 'insert into facult (nazvanie) values ($1)' using nm1;
begin
execute 'insert into facult (id) values ($1)' using nm2;
exception
when others then
raise notice 'Ошибка!';
end;
execute 'insert into facult (nazvanie) values ($1)' using nm1;
commit;
end;
$$
language 'plpgsql';
Пояснение к представленной выше хранимой процедуре.
1. Важный момент, появление внутри уже существующих begin...end вложенных begin...end. Вот это вложение позволяет изолировать вторую команду insert. С помощью ключевого слова exception обрабатывается исключение, которая в данном случае возникает из-за невозможности присвоить первичному ключу null. Если говорить в терминах управления транзакциями, то в блоке begin...exception...end создаётся точка сохранения, и откат при ошибке происходит до этой точки сохранения.
2. when others — обработка всех возможных ошибок, которые могут вызвать исключение. Есть также варианты условий после ключевого слова when: devision_by_zero — деление на ноль, no_data_found — нет данных, too_many_rows — слишком много строк.
3. Команда raise играет особую роль в PLpgSQL. Она передаёт вызывающей стороне сообщение об ошибке. Как получить это сообщение в клиенте, мы разберём, когда рассмотрим некоторые возможности написания клиентов для СУБД PostgreSQL.
4. Наконец, в тексте хранимой процедуры есть ещё одна команда insert. Она выполнится обычным образом и результат её как и первой команды insert будет зафиксирован командой commit.
Следующая статья тут
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
