Транзакционность — Основы SQL

Запросы внутри транзакции
Требования к транзакционной системе
Выводы

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

Запросы внутри транзакции

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

id	user_id	amount
1	10	100
2	30	100

View on DB Fiddle

Процесс перевода можно представить так:

Получаем количество денег пользователя:
```
SELECT amount FROM accounts WHERE user_id = 10;
```
Списываем необходимую сумму со счета этого пользователя:
```
UPDATE accounts SET amount = amount - 50 WHERE user_id = 10;
```
Зачисляем деньги другому пользователю:
```
UPDATE accounts SET amount = amount + 50 WHERE user_id = 30;
```

В результате таблица примет следующий вид:

id	user_id	amount
1	10	50
2	30	150

View on DB Fiddle

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

id	user_id	amount
1	10	50
2	30	100

Такое может произойти не только с деньгами, но и в большинстве других ситуаций. Приложения по возможности должны находиться в согласованном состоянии. В распределенных системах это невозможно, добиться этого можно с помощью механизма транзакций. Мы не будем подробно разбирать эту тему, но вы можете узнать о ней больше, изучив CAP-теорему и Eventual Consistency.

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

Операция снятия денег — это процесс, который приводит не только к множеству запросов в базу данных, но и к затрагиванию многих систем. У них есть свои процессы и базы данных внутри.

Мы ожидаем от любой подобной транзакции атомарность — когда операция либо завершается успешно, либо не проходит. Транзакции в базе данных в этом смысле проще, чем бизнес-транзакции. За обеспечением необходимых гарантий следит сама СУБД, а не программист:

BEGIN;
SELECT amount FROM accounts WHERE user_id = 10;
UPDATE accounts SET amount = amount - 50 WHERE user_id = 10;
UPDATE accounts SET amount = amount + 50 WHERE user_id = 30;
COMMIT;

View on DB Fiddle

Транзакции в PostgreSQL — это блок запросов, который обрамляется запросами:

BEGIN — открытие транзакции
COMMIT — закрытие транзакции

Любая ошибка внутри транзакции откатывает все изменения, которые были сделаны после запроса BEGIN:

Если нужно, транзакцию можно откатить самостоятельно. Для этого необходимо выполнить запрос ROLLBACK до COMMIT. Это нужно, когда выполняются запросы из кода приложения.

BEGIN;
UPDATE accounts SET amount = amount - 50 WHERE user_id = 10;
ROLLBACK;

View on DB Fiddle

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

Требования к транзакционной системе

В информатике есть набор требований к транзакционной системе, которые гарантируют ее надежность — ACID. К ним относятся:

Atomicity (Атомарность)
Consistency (Согласованность)
Isolation (Изолированность)
Durability (Устойчивость)

Разберем каждое требование подробнее

Atomicity (Атомарность)

Любая транзакция не может быть частично завершена — она либо выполнена, либо нет.

Consistency (Согласованность)

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

В примере выше снятие денег с одного счета приводит к тому, что данные рассинхронизированы. Но когда транзакция завершается, этого нет.

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

Isolation (Изолированность)

Когда транзакция выполняется, параллельные транзакции не должны оказывать влияния на ее результат. Ни одна транзакция не может увидеть изменения, которые сделаны другими незавершенными транзакциями. Изолированность — дорогое требование, поэтому в реальных БД существуют режимы, которые изолируют транзакцию не полностью — уровни изолированности Repeatable Read и ниже.

Durability (Устойчивость)

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

Выводы

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