itmo_conspects

Лекция 8. Транзакции

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

Транзакция должна соблюдать свойства ACID:

Atomicity - атомарность: транзакция неделима - либо выполняются все действия, либо ни одного
Consistency - согласованность: транзакция переводит одно согласованное состояние базы данных в другое согласованное состояние базы данных без соблюдения поддержки согласованности в промежуточных точках
Isolation - изоляция: если запущено несколько конкурирующих транзакций, то любой обновление, выполненное одной транзакцией, скрыто от других до ее завершения
Durability - долговечность: когда транзакция завершена, ее результаты сохраняются, даже если в следующий момент произойдет сбой

Чтобы соблюдать согласованность и целостность данных, в PostgreSQL можно установить ограничения на атрибуты:

NOT NULL - значение атрибута не может принимать NULL
UNIQUE - значения атрибута должны быть уникальны
PRIMARY KEY - первичный ключ (по сути NOT NULL + UNIQUE)
FOREIGN KEY - внешний ключ (значение атрибута должна быть ключом в другом отношении)
CHECK - проверка условий
EXCLUSION CONSTRAINTS - расширенные условия

Чтобы соблюдать изоляцию транзакций, в PostgreSQL существуют 4 уровня изоляции:

Уровень изоляции	Грязное чтение	Неповторяемое чтение	Фантомное чтение	Аномалия сериализации
Незавершенное чтение	Разрешено, но не в PostgreSQL	Возможно	Возможно	Возможно
Завершенное чтение	Невозможно	Возможно	Возможно	Возможно
Воспроизводимое чтение	Невозможно	Невозможно	Разрешено, но не в PostgreSQL	Возможно
Сериализуемость	Невозможно	Невозможно	Невозможно	Невозможно

Незавершенное чтение (или Read uncommitted) позволяет только одной транзакции изменять кортеж (не используется в PostgreSQL)
Завершенное чтение (или Read committed) по умолчанию включено в PostgreSQL - транзакция видит только закоммиченные изменения
Воспроизводимое чтение (или Repeatable read) запрещает транзакциям изменять кортежи, которые читает другая транзакция
Сериализуемость (или Serializable) запрещает добавлять кортежи в отношении, если другая транзакция может их прочитать

Для управления транзакция в PostgreSQL есть такие инструкции:

Для начала транзакции:
```
BEGIN;
```

Для начала транзакции с определенным уровнем:

BEGIN ISOLATION LEVEL READ UNCOMMITTED;
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

Для коммита обновлений:
```
COMMIT;
```
Для отката:
```
ROLLBACK;
```

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

Грязная запись - аномалия, при которой обе транзакции записывают данные в один и тот же кортеж до коммита. Если какая-либо из них откатиться, то потеряется запись другой:

Транзакция 1	Транзакция 2
`BEGIN;`	`BEGIN;`
`UPDATE accounts SET balance=200 WHERE id=1;`
	`UPDATE accounts SET balance=300 WHERE id=1;`
`COMMIT;`		Транзакция 1 коммитит 300
	`ROLLBACK;`	Откат до 100, хотя транзакция 1 выполнилась успешно

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

Транзакция 1	Транзакция 2
`BEGIN;`	`BEGIN;`	Обе читают исходное значение
`SELECT balance FROM accounts WHERE id=1;`		Транзакция 1 читает 100
	`SELECT balance FROM accounts WHERE id=1;`	Транзакция 2 читает 100
`UPDATE accounts SET balance=100+50 WHERE id=1;`		Транзакция 1 записывает 150
	`UPDATE accounts SET balance=100+20 WHERE id=1;`	Транзакция 2 записывает 120
`COMMIT;`
	`COMMIT;`	Обновление транзации 1 потеряно

Грязное чтение - такая аномалия возникает, когда транзакция читает еще не зафиксированные изменения, сделанные другой транзакцией:

Транзакция 1	Транзакция 2
`BEGIN;`	`BEGIN;`
`UPDATE accounts SET balance=200 WHERE id=1;`
	`SELECT balance FROM accounts WHERE id = 1;`	Транзакция 2 читает 200
`ROLLBACK;`		Откат от 200 к 100
	`SELECT balance FROM accounts WHERE id = 1;`	Транзакция 2 читает 100

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

Транзакция 1	Транзакция 2
`BEGIN;`	`BEGIN;`
`SELECT balance FROM accounts WHERE id=1;`		Транзакция 1 читает 100
	`UPDATE accounts SET balance=200 WHERE id=1;`
	`COMMIT;`	Коммит значения 200
`SELECT balance FROM accounts WHERE id=1;`		Транзакция 1 читает 200 в той же транзакции

Фантомное чтение возникает, когда между чтениям кортежей в одной транзакции другая транзакция добавляет новый кортеж:

Транзакция 1	Транзакция 2
`BEGIN;`	`BEGIN;`
`SELECT * FROM accounts WHERE balance > 100;`		Транзакция 1 видит 2 строки
	`INSERT INTO accounts(id, balance) VALUES (3, 300);`
	`COMMIT;`
`SELECT * FROM accounts WHERE balance > 100;`		Tранзакция 1 теперь видит 3 строки

Например, здесь

IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
  UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
END IF;

может возникнуть фантомное чтение или грязное чтение. Чтобы исправить, можно:

использовать UPDATE ON CONFLICT
использовать SELECT ... FOR UPDATE
использовать другой уровень изоляции

This site is open source. Improve this page.