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

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

Представьте гипотетическую ситуацию, когда приложение в одном SQL запросе изменяет баланс пользователя, а в другом должно на эти самые деньги купить какой-нибудь товар. И в от этот самый второй запрос и дает сбой – не столь важно по какой причине, важно, что деньги уже списаны. Конечно, если рассуждать с точки зрения владельца системы, то он в какой-то степени в выигрыше:), но вот мы, как простые пользователи, будем в лучшем случае просто расстроены(в худшем начнем искать способы, чтобы вторая фаза выполнялась успешно, в первая не работала:)).  

Грамотный разработчик будет использовать транзакции и обработку ошибок. Поймав вторую ошибку, он сделает откат первого изменения и выдаст информацию пользователю, что в по той или иной причине его запрос не может быть обработан.

Так это выглядит в теории.

Подавляющее число разработчиков на perl используют DBI для работы с базами данных. Он достаточно прост и удобен в использовании, хотя в управлении транзакциями он слабоват. DBI предоставляет только две функции – commit и rollback. 

Функция commit вызывается, когда необходимо зафиксировать(сохранить изменения в базу и сделать их видимыми для других процессов) транзакции, rollback приводит к откату изменений.  

Подавляющее большинство драйверов под DBI работают со включенным режимом AutoCommit. Это значит, что любое изменение, вызываемое командами DML будет зафиксировано. Например, у Вас идет десяток insert-ов и на 7-ом происходит сбой. Включенный режим автофиксации приведет к тому, что в базе окажутся первые 6 записей. Плохо это или хорошо зависит только от логики приложения.  

Итак, давайте рассмотрим пару примеров. Для начала создадим таблицу:

create table com_test(id serial, name varchar(30));

Напишем небольшую тестовую программу:

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:Pg:dbname=test","rimas", "", {RaiseError => 1, PrintError=>0});
#Отключим автофиксацию
$dbh->{AutoCommit} = 0;
#Попытаемся записать строку
$dbh->do("insert into com_test(name) values('test1')");
#Выходим без принудительной фиксации
$dbh->disconnect();

Переходим в консоль и проверяем, что же находится у нас в таблице:

select * from com_test;

id | name
----+------
(0 rows)

Как и ожидалось, там не сильно много:)

Теперь изменим код таким образом, чтобы фиксировать транзакцию:

#Попытаемся записать строку
$dbh->do("insert into com_test(name) values('test1')");
#Фиксируем транзакцию
$dbh->commit();

В результате выполнения скрипта в таблицу будет добавлена одна запись.

select * from com_test;
id | name
----+-------
2 | test1
(1 запись)

Обратите внимание на ID – он равен 2. В независимости от того, был сделан откат транзакции или нет, последовательность изменила свое значение.

А что будет, если кто-то будет читать данные из параллельного потока? По хорошему, существуют несколько видов транзакций с разным уровнем изоляции – например, когда из внешнего мира не видно совсем ничего из того, что делает приложение или, например, эти самые изменения видны, но существует возможность сделать откат.

Модифицируем скрипт следующим образом:

$dbh->do("insert into com_test(name) values('test1')");
#Ожидаем ввода пользователя или просто останавливаем выполнение программы
<STDIN>;
$dbh->rollback();
$dbh->disconnect();

Когда программа была остановлена, проверим из консоли, что же находится в таблице. В моем случае новых записей не появилось. Это говорит о том, что приложение выполняется с уровнем полной изоляции.

Меня всегда интересовал вопрос – а как будут вести себя подпрограммы на стороне сервера, если я выключил автофиксацию. Для тестов был написал следующий скрипт на plpgsql: 

create or replace function insert_into_coms() returns void as '
   declare
   begin
      insert into com_test(name) values(''from plgpsql'');
   end;
' language plpgsql;

 который был потом загружен в базу:

test=# \i demo.sql
CREATE FUNCTION

Скрипт переписан для вызова функции:

$dbh->do("select insert_into_coms()");
#Выходим без фиксации
$dbh->disconnect();

В результате новых записей обнаружено не было. Думаю, излишне будет говорить, что при добавлении $dbh->commit изменения были записаны в таблицу.

Следует отметить, что выключение автофиксации скажется на производительности сервера баз данных. Но это уже работа администраторов баз данных, как правильно настроить сегменты, распределить tablespace-ы и назначить контрольные точки. Если логика приложения требует атомарных операций – выключайте автофиксацию и работайте на уровне commit/rollback. Потери производительности ничто по сравнению с потерями данных.

Проверено:)