Механизм реализации транзакций - основная часть реляционных баз данных. Он упрощает разработку приложений, в которых гарантируется целостность данных. Стандарт SQL регламентирует, часть свойств по поддержке транзакций, но многие детали не стандартизованы. Как следствие, реализация поддержки транзакций в разных базах данных может существенно различаться. В настоящее время, многие пытаются перейти с Oracle на PostgreSQL. Для миграции приложений важно понимать различия в реализации работы транзакций, иначе можно столкнуться с неприятными сюрпризами, которые могут поставить под угрозу производительность и целостность данных. Поэтому я решил, что полезно сравнить реализацию работы транзакций в Oracle и PostgreSQL и свести различия в одной статье.

ACID : что транзакции предоставляют приложениям

Нет, речь не о химии и не о кислотах. ACID - это акроним слов:

Атомарность (Atomicity): гарантируется, что все команды в транзакции полностью выполнятся или не выполнится ни одна команда. Команды в транзакции можно рассматривать как единое целое. Атомарность обеспечивается всегда и во всех случаях, включая сбои железа.

Целостность (Consistency): гарантируется, что после фиксации транзакции не нарушаются декларативные ограничения целостности (constraints).

Изоляция (Isolation): гарантия того, что одновременно (параллельно) выполняющиеся транзакции не приведут к аномалиям (данные будут видны в таком состоянии, которое не могло бы возникнуть, если бы транзакции выполнялись последовательно, друг за другом).

Долговечность (Durability): гарантия того, что зафиксированная (успешно выполненная) транзакция останется зафиксированной и не потеряется, даже в случае сбоя операционной системы или железа.

Сходства между транзакциями в Oracle и PostgreSQL

К счастью, много что работает одинаково. Обе СУБД:

используют многоверсионность: читатели и писатели не блокируют друг друга. Вместо этого читатель получает старые данные, пока работает транзакция, выполняющая обновление или удаление.

сохраняют блокировки до конца транзакции.

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

поддерживают SELECT ... FOR UPDATE для явного управления блокировками, но есть различия, которые будут описываться дальше.

по умолчанию используют уровень изоляции транзакций READ COMMITTED, который ведёт себя схожим образом.

Сравнение атомарности в Oracle и PostgreSQL

 Имеются тонкие различия в работе атомарности:

Автокоммит

В Oracle любая команда DML неявно начинает транзакцию, если транзакция в сессии ещё не была начата. Любые открытые транзакции нужно будет явно завершить командами   COMMIT или ROLLBACK. Команды открытия транзакции BEGIN TRANSACTION или START TRANSACTION в Oracle нет.

В отличие от Oracle, PostgreSQL работает в режиме автокоммита: если не дать команду начала транзакции (START TRANSACTION или BEGIN [TRANSACTION]), то каждая команда будет выполняться в своей собственной транзакции. В конце такой однокомандной транзакции серверный процесс самостоятельно и автоматически фиксирует такую транзакцию.

Клиентские API имеют возможность отключить автокоммит. Поскольку серверный процесс PostgreSQL не поддерживает отключение автокоммита, то клиент эмулирует отключение, отправляя команды BEGIN серверному процессу. При использовании отключения автокоммита в таких API вы не заметите разницы между Oracle и PostgreSQL.

Откат последней команды

В Oracle команда SQL, завершившаяся с ошибкой, не прерывает транзакцию. Oracle откатывает последствия этой команды и транзакция продолжается. Транзакция остаётся открытой и можно зафиксировать транзакцию командой COMMIT или откатить командой ROLLBACK или продолжить транзакцию продолжая выполнять в ней команды.

Если в PostgreSQL SQL-команда вызовет ошибку, то вся транзакция прерывается и зафисксировать её нельзя. Серверный процесс будет игнорировать любые команды, пока вы не завершите транзакцию командой ROLLBACK или COMMIT (обе команды приведут к откату транзакции).

postgres=!# COMMIT;
ROLLBACK

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

Транзакционный DDL

В Oracle любая команда DDL выполняется в отдельной транзакции, которая автоматически фиксируется, поэтому в Oracle невозможно откатить команды DDL.

В PostgreSQL такого ограничения нет. За некоторыми исключениями (команды VACUUM, CREATE DATABASE, CREATE INDEX CONCURRENTLY и т.п.), можно откатить любую команду.

Сравнение согласованности в Oracle и PostgreSQL

В соблюдении согласованности транзакций почти нет различий: и Oracle и PostgreSQL следят за тем, чтобы ни одна транзакция не нарушала декларативные ограничения целостности (констрейнты).

Стоит упомянуть, что Oracle позволяет включать и отключать ограничения с помощью команды ALTER TABLE. Например, можно отключить ограничение, выполнить изменения в строках таблицы, которые бы нарушили это ограничение, а затем включить ограничение командой ENABLE NOVALIDATE. Для первичных и уникальных ключей это просто реализовать, если ограничение имеет свойство откладывать проверку до фиксации транзакции - DEFERRABLE, в таком случае индекс, поддерживающий ограничение неуникальный. Если ограничение NOT DEFERRABLE, то индекс уникальный и при отключении он будет удалён, а при включении создан, что трудоёмко.

В PostgreSQL только суперпользователь может отключить триггеры, реализующие внешние ключи, а также откладываемые уникальные и первичные ключи. Также только суперпользователь может установить значение параметра set session_replication_role = replica, что является еще одним способом отключения таких триггеров.

Момент проверки первичных и уникальных ключей в Oracle и PostgreSQL

Следующие команды SQL выполнятся без ошибок в Oracle Database:

CREATE TABLE tab (id NUMBER PRIMARY KEY);
INSERT INTO tab (id) VALUES (1);
INSERT INTO tab (id) VALUES (2);
COMMIT;

UPDATE tab SET id = id + 1;
COMMIT;

Эквивалентные команды (выполняются в режиме автокоммита, поэтому команды COMMIT отсутствуют) вызовут ошибку в PostgreSQL:

CREATE TABLE tab (id numeric PRIMARY KEY);
INSERT INTO tab (id) VALUES (1);
INSERT INTO tab (id) VALUES (2);

UPDATE tab SET id = id + 1;
ERROR:  duplicate key value violates unique constraint "tab_pkey"
DETAIL:  Key (id)=(2) already exists.

Причина в том, что PostgreSQL (в нарушение стандарта SQL) проверяет ограничение целостности после обновления каждой строки, а Oracle - в конце команды. Чтобы PostgreSQL вёл себя так же, как Oracle, можно создать ограничение целостности как DEFERRABLE. Такое ограничение целостности PostgreSQL будет проверять в конце команды.

Сравнение уровней изоляции транзакций в Oracle и PostgreSQL

В этой области различия между Oracle и PostgreSQL довольно существенны.

Стандарт SQL определяет четыре уровня изоляции транзакций: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE. По сравнению с обычным уровнем описания функционала в стандарте SQL, уровни изоляции транзакций прописаны не однозначно. Например, в стандарте говорится, что "грязное чтение" (чтение незафиксированных данных из других транзакций) "возможны" на уровне изоляции READ UNCOMMITTED, но неясно, является ли это обязательным требованием.

Oracle предлагает только уровни изоляции READ COMMITTED и SERIALIZABLE. Однако, вместо SERIALIZABLE Oracle, скорее, предлагает изоляцию моментальных снимков. Например, обе транзакции завершаются успешно (команды второй сессии выделены отсутпом):

CREATE TABLE tab (name VARCHAR2(50), is_highlander NUMBER(1) NOT NULL);
-- начало новой транзакции уровня SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM tab WHERE is_highlander = 1;
  COUNT(*)
----------
        0
                       -- начало новой транзакции уровня SERIALIZABLE
                       SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
                       SELECT count(*) FROM tab WHERE is_highlander = 1;
                         COUNT(*)
                       ----------
                               0
-- число строк нулевое, поэтому вставим строку
INSERT INTO tab VALUES ('MacLeod', 1);
COMMIT;
                       -- число строк нулевое, поэтому вставим строку
                       INSERT INTO tab VALUES ('Kurgan', 1);
                       COMMIT;

Если выполнять транзакции одну за другой в любом порядке (первую за второй и вторую за первой), то такого результата не удалось бы достичь: SELECT выдал бы число строк 1, а не нуль.

Лоренц считает, что это не правильно и реализация уровня SERIALIZABLE в Oracle некачественная. Например, если вы создадите таблицу без опции SEGMENT CREATION IMMEDIATE, а затем попытаетесь вставить первую строку в транзакции уровня SERIALIZABLE, то вы получите ошибку сериализации. Технически это допустимо, поскольку база данных вправе выдавать ошибки сериализации на этом уровне и нужно быть к этому готовым. Но, похоже, Oracle выдаёт ошибки сериализации всякий раз, когда что-то идёт не так, например, команда вызывает деление страницы индекса, это сложно реализовать корректно и Oracle предпочитает выдать ошибку сериализации. По сути, SERIALIZABLE практически не пригоден  для использования в базе данных Oracle.

Сравнение параллельного изменения данных на уровне READ COMMITTED в PostgreSQL и Oracle

По умолчанию используется уровень изоляции READ COMMITED, а он не самый строгий. На этом уровне могут возникать аномалии конкурентного доступа.  Одна из таких аномалий была описана в статье. Не хочу повторять всё здесь. По сути, ситуация выглядит следующим образом:

1) первая транзакция изменила строки таблицы, но еще не зафиксирована

2) вторая транзакция выполняет команду, которая пытается заблокировать строки (например,  SELECT ... FOR UPDATE или просто UPDATE) и зависает на первой заблокированной строке

3) первая транзакция фиксируется.

Какие данные прочтёт вторая транзакция? И в Oracle, и в PostgreSQL вторая транзакция увидит зафиксированные данные от первой транзакции на уровне READ COMMITTED, но есть различие:

в PostgreSQL команда во второй транзакции повторно перечитает только заблокированные строки, что быстро, но может привести к некорректным результатам, как описано в статье.

В Oracle вторая транзакция повторно выполнит весь запрос (перечитает все данные), что будет медленнее, но обеспечит согласованный результат.

Примечание: и так для каждой заблокированной строки, на которую наткнётся команда в обеих базах.

Сравнение надежности Oracle и PostgreSQL

Обе СУБД обеспечивают надёжность хранения данных с помощью журнала транзакций (называемого "redo log" в Oracle и "write ahead log" в PostgreSQL). Oracle и PostgreSQL предоставляют одинаковые гарантии свойства долговечности транзакций.

Другие различия между транзакциями в Oracle и PostgreSQL

Ограничения на размер и длительность транзакции

Различия в этой части обусловлены радикально разным способом реализации многоверсионности в Oracle и PostgreSQL. В Oracle используется служебное табличное пространство типа UNDO, в которое копируются старые версии изменённых строк, а PostgreSQL хранит все старые версии строк в блоках таблицы.

По этой причине, объем изменений данных в активных транзакциях в базах данных Oracle ограничивается размером табличного пространства UNDO. Массовые удаления и обновления, обычно, выполняются по частям, с промежуточными COMMIT. В PostgreSQL такого ограничения нет, но массовые обновления "раздувают" таблицу, поэтому вы также можете захотеть обновления разбивать на части с промежуточной фиксацией транзакций (и даже выполнять VACUUM между частями). Но, в отличие от Oracle, в PostgreSQL нет причин ограничивать массового удаления строк командой DELETE, так как удаление строк не раздувает таблицы и индексы.

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

Сравнение SELECT ... FOR UPDATE в Oracle и PostgreSQL

Обе СУБД поддерживают эту команду для одновременного чтения и блокировки строки таблицы. Кроме того, Oracle и PostgreSQL поддерживают выражения NOWAIT и SKIP LOCKED. В PostgreSQL отсутствует выражение WAIT число_секунд и для ограничения времени  ожидания получения блокировки приходится динамически менять значение параметра lock_timeout.

Главное отличие в том, что в PostgreSQL вообще не стоит использовать FOR UPDATE. Если вы не планируете удалять строку или изменять значение в столбце первичного или уникального ключа, правильным режимом блокировки будет FOR NO KEY UPDATE. Этому посвящена статья https://habr.com/ru/articles/940066/

Wraparound

Wraparound существует только в PostgreSQL. Реализация многоверсионности в PostgreSQL сохраняет номера транзакций в заголовке каждой строки. Номера транзакций используют 32-битный счётчик и через 4 миллиарда транзакций номера перейдут через ноль. PostgreSQL приходится выполнять специальные операции по обслуживанию кластера баз данных, чтобы избежать потери данных во время такого перехода. В базах данных с большой частотой транзакций это может стать проблемой, требующей особого внимания и настройки работы PostgreSQL.

Заключение

В большинстве случаев транзакции в Oracle и PostgreSQL работают практически одинаково. Однако, существуют различия, которые стоит учитывать при планировании миграции на PostgreSQL. Сравнение работы транзакций, приведённое в этой статье, поможет вам выявить потенциальные проблемы при миграции.