Приветствую тебя читатель, я решил написать про ACID и Транзакции PostgreSQL своим языком, с понятными примерами, эта статья ориентирована на людей готовящихся к собеседованию, кто захотел узнать нюансы транзакций в PostgreSQL или про ACID, а также для людей которые знают теорию, но сами ещё ни разу не писали транзакции. Я не ставил перед собой цели рассмотреть и объяснить работу транзакций на очень глубоком уровне. Была цель привести понятные примеры, дать макет работы с транзакциями, а также пощупать основные возможные проблемы при работе с транзакциями в PostgreSQL.

Также по мере изучения этого материала я настоятельно советую пробовать воспроизводить приведенные мной примеры, или попробовать самому воссоздать то или иное поведение это будет ещё лучше чем просто скопировать и вставить!

Приступим)

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

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

Идеальным примером когда мы хотим соблюдения требований ACID является работа с финансовыми данными. Чтобы ни происходило, мы не хотим ложных данных при совершении финансовых операций.

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

/* Начать транзакцию*/
BEGIN TRANSACTION; /* ИЛИ */ BEGIN;
/*Завершить изменения*/
COMMIT;

Все это дело я буду показывать на работе с контейнером PostgreSQL в Docker. Если у вас не установлен Docker, то вы просто можете пропустить шаги создания контейнера и перейти, к первым командам на PostgreSQL.

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

Первым делом создадим контейнер с образом PostgreSQL

docker run --name my-postgres-container -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres

Вторым через терминал войдём в этот контейнер

docker exec -it my-postgres-container sh

Третьим войдём в саму БД.

psql -U postgres

Команды для создания таблицы, и вставки тестовых данных в таблицу.

CREATE TABLE Wallet (    
  id SERIAL PRIMARY KEY,    
  wallet_id CHAR(10) NOT NULL,    
  balance FLOAT CHECK (balance >= 0)
);

INSERT INTO Wallet (wallet_id, balance) VALUES
('1234567890', 100.50),
('0987654321', 250.00),
('1122334455', 0.00),
('5566778899', 75.25),
('6677889900', 150.75);

Песочницу для игры подготовили, теперь давайте приступим.)

Транзакции и ACID тесно связанны, потому-что то что описывает ACID реализуется в транзакциях.

A - Atomicity (Атомарность) - Первое свойство ACID, говорящее нам о том, что все изменения с данными либо выполняются успешно, либо не выполняются вообще. То есть если в наборе операций, хотя бы одна операция не выполняется корректно, то будет откат всего набора операций.

Откроем два терминала, войдём в контейнер, затем в БД на каждом из них, на одном выведем все данные таблицы в трёх этапах:
До начала транзакции, в момент исполнения транзакции, после аварийного завершения транзакции.
Сейчас у нас два подключения к БД условно назовём их А и Б.

А Вводим следующую команду для отображения всех данных таблицы wallet.

SELECT * FROM wallet;

Результат:

 id | wallet_id  | balance
----+------------+---------  
1 | 1234567890 |   100.5  
2 | 0987654321 |     250  
3 | 1122334455 |       0  
4 | 5566778899 |   75.25  
5 | 6677889900 |  150.75
(5 rows)

На другом терминале B начинаем транзакцию:

BEGIN TRANSACTION;
UPDATE Wallet
SET balance = balance + 100
WHERE wallet_id = '1122334455';

Консоль возвращает:

UPDATE 1

Проверяем на другом терминале A , состояние данных:

SELECT * FROM wallet;

Результат:

 id | wallet_id  | balance
----+------------+---------  
1 | 1234567890 |   100.5  
2 | 0987654321 |     250  
3 | 1122334455 |       0  
4 | 5566778899 |   75.25  
5 | 6677889900 |  150.75
(5 rows)

Как видим, состояние данных не изменилось, все строки имеют те же значения, что и до начала транзакции.

Хорошо, теперь закрываем первый терминал, имитируя разрыв соединения.

И проверяем, на терминале B состояние данных:

SELECT * FROM wallet;

Результат:

 id | wallet_id  | balance
----+------------+---------  
1 | 1234567890 |   100.5  
2 | 0987654321 |     250  
3 | 1122334455 |       0  
4 | 5566778899 |   75.25  
5 | 6677889900 |  150.75
(5 rows)

Видим что данные все также имеют те же значения, что и до начала транзакции.

Это первое свойство транзакций - Атомарность, это означает что набор операций либо выполняется полностью, либо не выполняется вообще.

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

Тем самым, в транзакциях выполняется первое требование ACID.

C - Consistency (согласованность). На любом этапе выполнения транзакции, данные должны быть согласованны. Это означает, что если у нас есть ограничение что столбец balance > 0, это ограничение должно выполняться на каждом шагу транзакции.

Это очень легко проверяется:

Начинаем следующую транзакцию:

BEGIN TRANSACTION;
UPDATE Wallet
SET balance = balance - 100
WHERE wallet_id = '6677889900';
/* Ответ от терминала*/
UPDATE 1 UPDATE Wallet
SET balance = balance - 100
WHERE wallet_id = '6677889900';
/* Ответ от терминала*/
ERROR:  new row for relation "wallet" violates check constraint  "wallet_balance_check"
DETAIL:  Failing row contains (5, 6677889900, -49.25).
UPDATE Wallet
SET balance = balance + 100
postgres-!# WHERE wallet_id = '6677889900';
/* Ответ от терминала*/
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=!# COMMIT;
/* Ответ от терминала*/
ROLLBACK

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

Теперь перейдём к материи по сложнее.

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

Для лучшего понимания зачем и как это работает, начнём с обзора проблем которые могут возникать при некорректной настройки уровней транзакции.

Dirty Read(Грязное чтение) - Когда одна транзакция видит измененные данные другой транзакцией, которая вполне может быть откатана.

Для понимания данной проблемы, представьте что у нас параллельно идет процесс двух транзакций, и есть некий баланс значение которого изначально 200.
Первая транзакция: Увеличивает значение баланса на 300 тем самым баланс становится равным 500.
В это же время Вторая транзакция считывает значение баланса как 500.
Первая транзакция в силу каких-либо причин не смогла завершиться, и применился ROLLBACK(откат). Тем самым значения не закоммитились в бд, и значение баланса снова стало 200.
У нас появилась проблема, что вторая транзакция думает, что у нас баланс 500.

Так как я рассматриваю транзакции в Postgres, важно знать что такая проблема при работе с Postgre просто невозможна, так как создатели языка на самом низшем уровне изоляции (Read Uncommited) предотвратили изменения бд от неподтвержденных транзакций. Тем не менее про эту проблему важно знать, так как во многих БД самый низший уровень изоляции может допускать dirty read.

Вывод: Dirty Read в Postgres недопустим, но об этой проблеме неплохо знать.

Следующая ситуация, которая в отличии от Dirty Read уже может возникать в Postgres это Non Repeatable Read.

Non Repeatable Read (Неповторяющееся чтение) - Когда транзакция читает одни и те же данные, и получает разные значения. Ключевое отличие от грязного чтения(dirty read) в том, что в грязном чтении мы видим изменения от незавершенных транзакций, а в неповторяющемся чтении мы видим только результаты уже завершенных транзакций.

Сейчас увидим как это происходит на деле. Также будем работать с двух терминалов А и Б.

Для начала начнём транзакцию на одном терминале (А):

BEGIN;
SELECT balance FROM wallet WHERE wallet_id ='1234567890';
/*Ответ от терминала*/ balance
---------   100.5
(1 row)

Видим что параметр balance в первой транзакции равняется 100.5 , параллельно с другого терминала (B) начнём еще одну транзакцию и завершим её.

BEGIN;
UPDATE Wallet
SET balance = balance + 250
WHERE wallet_id = '1234567890';
COMMIT;
/*Ответ от терминала*/
COMMIT

Теперь вернемся к первой транзакции A и попробуем ещё раз прочитать параметр balance

BEGIN;
SELECT balance FROM wallet WHERE wallet_id ='1234567890';
/*Ответ от терминала*/ balance
---------   350.5
(1 row)
COMMIT;

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

Решается данная проблема переходом на более низкий уровень изоляции, Repeatable read, это делается путем объявления уровня изоляции в начале транзакции.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/*Советую повторить все те же операции, и убедиться как это работает.*/

Данный уровень изоляции работает следующим образом: На момент начала транзакции создает снимок Базы Данных и работает с ним. Тем самым, если данные в самой БД изменены (Добавлена/удалена строка, или изменили значения полей), транзакция эти изменения видеть не будет.

Ещё одна проблема которая может возникать при работе с транзакциями это Фантомное чтение (Phantom read). По сути своей схож с Non repeatable read, но ключевое отличие в том что при повторном чтение одного набора строк в ходе выполнения транзакции читаемый набор строк изменился.

Важно учитывать данная проблема как правило возможна на уровнях транзакций Read Uncommited и Read Commited и (Repeatable read, но не в PostgreSQL).

Сейчас я покажу как возникает данная проблема при работе с транзакциям уровня Read commited.

Код выполнения транзакции А

BEGIN; /*В PostgreSQL изначальный уровень транзакции Read commited, в PostgreSQL по сути нету уровня Read Uncommited*/
SELECT * FROM wallet;
/*Ответ от терминала*/ id | wallet_id  | balance
----+------------+---------  
1 | 1234567890 |   100.5  
2 | 0987654321 |     250  
3 | 1122334455 |       0  
4 | 5566778899 |   75.25  
5 | 6677889900 |  150.75
(5 rows)
/*Происходит полное выполнения второй транзакции*/
SELECT * FROM wallet;
/*Ответ от терминала*/ id | wallet_id  | balance
----+------------+---------  
2 | 0987654321 |     250  
3 | 1122334455 |       0  
4 | 5566778899 |   75.25  
5 | 6677889900 |  150.75
(4 rows)

Код второй транзакции B

BEGIN;
DELETE FROM wallet WHERE wallet_id = '1234567890';
COMMIT;

Вот так происходит фантомное чтение, также как и повторное чтение оно не доступно на уровне изоляции Repeatable read в PostgreSQL.

Последняя проблема которая может возникать при работе с транзакциями это аномалия сереализации.

Аномалия сериализации(Serialization Anomaly) - Когда результат выполнения группы транзакций оказывается несогласованным при всех допустимых вариациях выполнения транзакций по очереди. Эта проблема в PostgreSQL доступна на всех уровнях изоляции транзакций кроме, уровня Serializable (Самый нижний и строгий уровень изоляции транзакций).

Данные таблицы для работы с примером указаны ниже

 id | wallet_id  | balance
----+------------+---------  
1 | 1234567890 |   100.5  
2 | 0987654321 |     250  
3 | 1122334455 |       0  
4 | 5566778899 |   75.25  
5 | 6677889900 |  150.75

Допустим у нас есть две транзакции А и Б.
Первая увеличивает баланс всех кошельков значение которых больше 0
Вторая увеличивает баланс кошелька равного 0

Транзакция А:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM wallet WHERE balance > 0;
UPDATE wallet SET balance = balance + 10 WHERE balance > 0;
/*На этом моменте у нас запускается транзакция B и полностью выполняется.*/
COMMIT;

Транзакция Б:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM wallet WHERE balance = 0;
UPDATE wallet SET balance = balance + 100 WHERE balance = 0;
COMMIT;

Результат работы данных транзакций:

 id | wallet_id  | balance
----+------------+---------  
1 | 1234567890 |   110.5  
2 | 0987654321 |     260  
3 | 1122334455 |     100   
4 | 5566778899 |   85.25  
5 | 6677889900 |  160.75

Эта статья ориентировочна, на людей которые в первую очередь хотят понять что такое транзакции, уровни изоляции в PostgreSQL и как это работает, то есть она не ориентирована на человека который хочет очень глубоко узнать механизм работы транзакций и уровней изоляции. Поэтому я сейчас на всякий случай объясню почему базовый момент. Это аномалия сериализации, потому-что при разных вариантах выполнения транзакций, мы получаем разные результаты, я буду сразу смотреть только строку:

 id | wallet_id  | balance
----+------------+---------  
3 | 1122334455 |     0   /*0 Так как изначальный баланс был 0*/

1-ый Вариант Сначала транзакция А, потом Б. :
Все значения > 0 Получают + 10:

 id | wallet_id  | balance
----+------------+---------  3 | 1122334455 |     0   
/*Строка остается без изменений*/

Все значения = 0 получают + 100:

 id | wallet_id  | balance
----+------------+---------  3 | 1122334455 |     100   
/*Результат при очередности выполнения транзакций:А затем Б.*/

2-ый Вариант Сначала транзакция Б, потом А. :
Все значения = 0 Получают + 100:

 id | wallet_id  | balance
----+------------+---------  3 | 1122334455 |     100   

 id | wallet_id  | balance
----+------------+---------  3 | 1122334455 |     110   
/*Результат при очередности выполнения транзакций:Б затем А.*/

Соответственно мы получаем несогласованные результаты данных.
Уровень Serializable не допускает данного поведения, приведите данные строки c id=3 к исходным и попробуйте проделать эти же шаги с уровнем изоляции Serializable.

У нас есть все те же две транзакции А и Б.
Первая увеличивает balance всех кошельков > 0
Вторая увеличивает баланс кошелька равного 0
Но теперь мы их выполняем с уровне изоляции Serializable.

Транзакция А:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM wallet WHERE balance > 0;
UPDATE wallet SET balance = balance + 10 WHERE balance > 0;
/*На этом моменте у нас запускается транзакция B и полностью выполняется.*/
COMMIT;
/*Ответ от терминала:
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.*/

Транзакция Б:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM wallet WHERE balance = 0;
UPDATE wallet SET balance = balance + 100 WHERE balance = 0;
COMMIT;

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

На этом самый обширный пункт ACID подходит к концу, ниже скриншот уровней изоляций и возможного поведения для БД PostgreSQL.

Также прикреплю ссылку на официальную документацию на английском и отличную документацию на русском языке соответственно:

https://www.postgresql.org/docs/current/transaction-iso.html [ENG]
https://postgrespro.ru/docs/postgrespro/9.5/transaction-iso [RU]

Учитывайте, что я рассматриваю правила ACID и Транзакции именно PostgreSQL.
В целом SQL стандарт для уровней изоляций выглядит следующим образом:

Но это также не значит что во всех других БД именно так.

Возвращаемся к ACID

D - Durability (Надёжность) - Говорит нам о том, что все данные которые записаны уже выполненной транзакцией, не будут отменены. Условно выключили свет, пропал интернет, на данные которые записаны уже завершенной транзакцией, это никак не должно повлиять.

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

Также я веду свой небольшой блог разработчика на Golang, там я пишу небольшие технические посты, а также делюсь своими мыслями по мышлению, построению образа жизни. Если вам это интересно, вот мой канал: t.me/olivka_developer .