Skip to content

Latest commit

 

History

History
122 lines (81 loc) · 12.9 KB

TCL.md

File metadata and controls

122 lines (81 loc) · 12.9 KB

TCL

Все транзакции PostgreSQL, Oracle подчиняются основным свойства транзакции БД, известной как свойства ACID.

Расшифровывается так:

  • Свойство атомарности (Atomicity) выражается в том, что транзакция должна быть выполнена в целом или не выполнена вовсе. Пример: транзакция обновляет 100 строк. Если после 20 произошла ошибка, то вся БД откатится до состояния до изменения этих 20 строк.
  • Свойство согласованности (Consistency) гарантирует, что по мере выполнения транзакций данные переходят из одного согласованного состояния в другое — транзакция не разрушает взаимной согласованности данных. Пример: осуществляется перевод средств с одного счёта на другой. Сбой не должен допустить того, что деньги снимутся с одного счёта, но не добавятся на другой, что приведёт к несогласованным данным.
  • Свойство изолированности (Isolation) означает, что конкурирующие за доступ к базе данных транзакции физически обрабатываются последовательно, изолированно друг от друга, но для пользователей это выглядит так, как будто они выполняются параллельно. Пример: пользователь, обновляющий таблицу не видит незафиксированных изменений, сделанных одновременно другим пользователем.
  • Свойство долговечности (Durability) трактуется следующим образом: если транзакция завершена успешно, то те изменения в данных, которые были ею произведены, не могут быть потеряны ни при каких обстоятельствах (даже в случае последующих ошибок).

PostgreSQL

Транзакции - один из важнейших инструментов, когда дело касается операций INSERT, UPDATE, DELETE. Эти операторы меняют данные в таблицах. Представьте, у вас сложная задача, которая затрагивает несколько таблиц. Вам нужно сходить в одну таблицу и добавить туда данные, получить id добавленной записи и добавить 5 записей в другую таблицу, а затем что-то удалить из третьей. В идеальном мире, конечно, всё всегда будет выполняться без ошибок. Но в реальном такого не бывает. Например, во второй таблицу произошла ошибка при добавлении 4-ой записи. Т.е. добавилось в первую, добавилось 3 записи во вторую и всё. Операция не была выполнена полностью, но в базу данных изменения частично были внесены. Такого не должно быть. А что если вы переводите деньги с своего счёта на чужой. С вас списывают деньги, но потом произошла какая-то ошибка и выполниться запрос добавления денег на другой счёт не смог. Как раз об этом говорит первое свойство - Atomicity и Consistency.

В PostgreSQL транзакция начинается с BEGIN (или SET TRANSACTION).

Чтобы утвердить изменения используется команда COMMIT. Закоммитить изменения - означает утвердить их. Дальше такая фраза будет часто встречаться.

Есть ещё ситуация, которую решает транзакция. Допустим, к одним и тем же данным хотят получить 2 операции. Поведение в таких случаях определяется в зависимости от уровней изоляции.

Уровни изоляции

Существуют следующие явления:

Dirty read: операции в транзакции видят данные, записанные в параллельных незакоммиченных транзакциях.

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

Phantom read: транзакция повторно выполняет запрос, возвращающий набор строк, которые удовлетворяют условию поиска и обнаруживает, что набор строк изменился из-за другой недавно завершенной транзакции.

serialization anomaly: результат успешного коммита группы транзакций несовместим со всеми возможными порядками запуска этих транзакций по одному.

Более подробнее: https://www.postgresql.org/docs/12/transaction-iso.html

Isolation Level Dirty Read Nonrepeatable Read Phantom Read Serialization Anomaly
Read uncommitted Не возможно в PG Возможно Возможно Возможно
Read committed Не возможно Возможно Возможно Возможно
Repeatable read Не возможно Не возможно Не возможно в PG Возможно
Serializable Не возможно Не возможно Не возможно Не возможно

Read Commited

Стандартный уровень изоляции. SELECT видит только данные, которые были закоммичены перед началом запроса. Он не видит изменения данных, которые выполняются в других транзакциях. Однако SELECT видит изменения в пределах своей транзакции, даже если они ещё не закоммичены. Ещё 2 последовательных SELECT в пределях одной транзакции могут видеть разные данные, если после начала первого и до начала второго были закоммичены данные из других транзакций.

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE видят данные также, как SELECT. Однако есть отличие. Целевая строка, с которой хотят взаимодействовать эти операторы уже была обновлена (удалена или заблокирована) другой транзакций к тому времени, когда была найдена. В таком случае он будет ждать выполнения первой транзакции. Если транзакция, которая заблокировала строки откатывается, то вторая транзакция продолжает обновление первоначально найденной строки. Если первая транзакция коммитит изменения, то в случае, если строка была удалена - вторая транзакция проигнорирует её, если она есть, то попытается применить свою операцию к обновленной версии строки. Условие WHERE пересчитывается, чтобы определить удовлетворяет ли ему измененённая версия. Если да, то вторая транзакция продолжает работу.

Рассмотрим следующую ситуацию:

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

Запускается транзакция, которая увеличивает hits на 1. После выполнения UPDATE, но до COMMIT запускается другая транзакция, которая удаляет запись с hits 10.

Допустим, на момент запуска транзакции в базе есть hits = 9 (id = 1) и hits = 10 (id = 2).

Что сделает DELETE? Ответ: ничего.

Почему так произойдёт?

  1. UPDATE заблокировал все строки, потому что выполняет с ними изменения
  2. DELETE нашёл строку с hits = 10 (id = 2), но она заблокирована. Ждём.
  3. UPDATE выполняет commit изменений. у id = 1 hits становится равным 10, у id = 2 hits становится равным 11.
  4. DELETE начинает работать, но строка с id = 2 уже не подходит по условию, т.е. условие WHERE пересчитывается, поэтому DELETE ничего не делает. Он не ищет заново строки, которые могли измениться и стали равным 10.

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

Repeatable Read

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

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

Oracle

Создадим таблицу:

CREATE table "  trTable" (
"id"  NUMBER(5,0),
"sum"  NUMBER(10,0),
constraint  "transaction_TABLE_PK" primary key ("id")
)

Добавим данные:

Insert into trTable values (1, 200000);
Insert into trTable values (2, 100000);
Insert into trTable values (3, 300000);
Insert into trTable values (4, 400000);

Сумма денег – 1 000 000.

Перевод денег с номера 4 на номер 1:

Update trtable set sum = sum - 10000 where id = 4;
Update trtable set sum = sum + 10000 where id = 1;

Допустим, мы хотим перевести с номера 4 на номер 1 10000, с номера 2 на номер 3 10000. В первом случае сделаем rollback, во втором commit.

SET TRANSACTION NAME 'sum_update';
Update trtable set sum = sum - 10000 where id = 4;
Update trtable set sum = sum + 10000 where id = 1;
rollback;
SET TRANSACTION NAME 'sum_update2';
Update trtable set sum = sum - 10000 where id = 2;
Update trtable set sum = sum + 10000 where id = 3;
commit;

Как видно по результатам запроса – 1-ый перевод на выполнился, а 2-ой – выполнился.