Транзакции в базах данных на примере PostgreSQL

в 5:16, , рубрики: acid, postgresql, транзакции, уровни изоляции бд

Общие сведения о транзакциях

Транзакция - это набор операций с базой данных. В этот набор может входить как одна операция, так и несколько. Операции внутри транзакции либо выполняются все и полностью, либо ни одна операция не выполняется. Это свойство еще называют атомарностью. Транзакция переводит базу данных из одного согласованного состояния в другое. Согласованность означает что данные в базе данных подчиняются определенным правилам, которые были заложены при ее создании. К примеру у нас есть две таблицы - Покупатели (Customer) и Покупки (Purchase).

Согласованность данных

Согласованность данных

У Покупателя есть поле total_sum - сумма всех покупок. У Покупки есть цена и ссылка на Покупателя. Очевидно что total_sum в таблице Покупатели должна быть равна сумме цен всех покупок пользователя. Если это не так, то данные будут считаться несогласованными.
Для создания транзакции в используется ключевое слово BEGIN. 

BEGIN;
UPDATE cars SET max_speed = max_speed + 10 WHERE model = 'Toyota';
SELECT * FROM cars;
COMMIT;

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

UPDATE cars SET max_speed = max_speed + 10 WHERE model = 'Toyota';

или выборке.

SELECT * FROM cars;

или других одиночных запросах.

Транзакция может иметь два исхода:

  • изменения, сделанные транзакцией фиксируются с помощью ключевого слова COMMIT

  • изменения, сделанные транзакцией отменяются с помощью ключевого слова ROLLBACK

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

Сначала списать деньги с одного счета.

UPDATE account SET money = money - 100 WHERE name = 'Я';

А затем зачислить списанную сумму на другой счет.

UPDATE another_account money = money + 100 WHERE name = 'Мама';

Если мы будем выполнять эти операции по отдельности, то возможна ситуация, при которой первая операция выполнится, а во время выполнения второй операции произойдет сбой и списанная сумма не зачислится. Чтобы таких ошибок не возникало эти операции следует объединить в одну транзакцию:

BEGIN;
UPDATE account SET money = money - 100 WHERE name = 'Я';
UPDATE another_account money = money + 100 WHERE name = 'Мама';
COMMIT;

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

Создание транзакций. Практика

Для начала установим PostgreSQL. Здесь установку я буду проводить на Ubuntu. Инструкции по установке для других операционных систем можно найти в официальной документации https://www.postgresql.org/download/ 

sudo apt install postgresql

Далее подключаемся к серверу базы данных используя консольную утилиту psql

sudo -u postgres psql postgres

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

CREATE TABLE cars (
id BIGSERIAL PRIMARY KEY,
model varchar(20),
max_speed INTEGER);

Заполним таблицу данными

INSERT INTO cars (model, max_speed) VALUES 
('Toyota', 150),
('Lada', 130),
('BMW', 200),
('Renault', 170),
('Mercedes', 230);

Посмотрим что получилось

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  3 | BMW      |       200
  4 | Renault  |       170
  5 | Mercedes |       230

Теперь создадим транзакцию и изменим данные.

BEGIN;
UPDATE cars SET max_speed = max_speed + 20 WHERE model = 'Renault';

Посмотрим что получилось.

SELECT * FROM cars WHERE model = 'Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       190

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

ROLLBACK;

Проверим зафиксировались ли изменения.

SELECT * FROM cars WHERE model = 'Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       170

Видим что изменения не зафиксировались, потому что мы сделали отмену изменений указав ROLLBACK
Теперь выполним те же запросы, но в этот раз зафиксируем изменения

BEGIN;
UPDATE cars SET max_speed = max_speed + 20 WHERE model = 'Renault';
SELECT * FROM cars WHERE model = 'Renault';
COMMIT;

Проверим что получилось теперь.

SELECT * FROM cars WHERE model = 'Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       190

На этот раз изменения зафиксировались в базе данных.

Параллельное выполнение транзакций

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

Транзакции должны обладать определенными свойствами:

  • Атомарность (atomicity). Транзакция фиксируется либо полностью, либо ни одна ее часть не фиксируется

  • Согласованность (consistency). Транзакция должна переводить базу данных из одного согласованного состояния в другое. Пример согласованности мы разбирали в начале статьи.

  • Изолированность (isolation). Во время выполнения транзакции другие транзакции не должны оказывать влияние на нее. То есть они не должны изменять данные внутри транзакции.

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

Этот набор свойств еще обозначают аббревиатурой ACID.

Феномены параллельного выполнения транзакций

При параллельном выполнении транзакций возникают разного рода феномены. Давайте их рассмотрим.

  • Потерянное обновление (lost update). Если разные транзакции меняют одни и те же данные, то может возникнуть ситуация при которой изменения одной транзакции перезапишутся другой транзакцией. Например есть две транзакции. Они производят обновление одной и той же строки. Вторая транзакция изменила строку и зафиксировала свои изменения. После этого зафиксировала свои изменения первая транзакция и перезаписала изменения второй транзакции.

Феномен потерянного обновления

Феномен потерянного обновления

  • Грязное чтение (dirty read). Первая транзакция изменила данные, но еще не зафиксировала свои изменения. В это время вторая транзакция читает измененные данные. Если первая транзакция сделает отмену своих изменений - ROLLBACK, то получится что вторая транзакция работает с данными, которых нет в базе данных.

Феномен грязного чтения

Феномен грязного чтения

  • Неповторяющееся чтение (non-repeatable read). Первая транзакция читает строку и получает одни значения. После этого вторая транзакция меняет ту же строку, которая прочитала первая транзакция и фиксирует свои изменения. Первая транзакция снова читает ту же строку и получает уже другие данные, потому что вторая транзакция изменила эту строку.

Феномен неповторяющегося чтения

Феномен неповторяющегося чтения

  • Фантомное чтение (phantom read). Этот феномен очень похож на неповторяющееся чтение, но здесь идет чтение нескольких строк. Первая транзакция делает выборку набора строк. После этого приходит вторая транзакция и удаляет или добавляет строки попадающие в эту выборку. Вторая транзакция фиксирует свои изменения. После этого первая транзакция снова делает ту же самую выборку и уже получает другой набор строк - их стало либо больше, либо меньше чем при первой выборке, так как вторая транзакция добавила / удалила строки.

Феномен фантомного чтения

Феномен фантомного чтения

  • Аномалия сериализации (serialization anomaly). Результат выполнения двух параллельных транзакции не совпадает ни с одним из результатов последовательного их выполнения. Если выполняются транзакции T1 и T2, то результат параллельного их выполнения не будет равен ни результату последовательного выполнения транзакции T1 затем T2 ни результату последовательного выполнения транзакции T2 затем T1.

Феномен аномалии сериализации

Феномен аномалии сериализации

Уровни изоляции транзакций

Уровни изоляции транзакций определяют то, на сколько одна транзакции может влиять на другую. Каждый уровень изоляции определяет те феномены, которые на данном уровне недопустимы. Каждый последующий уровень включает ограничения предыдущего.

  • Read uncommitted. Это самый низкий уровень изоляции. Согласно стандарту SQL на этом уровне разрешено чтение грязных данных. Но в PostgreSQL на этом уровне чтение грязных данных не допускается.

  • Read Committed. По стандарту SQL на этом уровне не допускается чтение грязных данных. Таким образом в PostgreSQL уровни read uncommitted и read committed совпадают. Этот уровень по умолчанию установлен в PostgreSQL.

  • Repeatable read. Не допускается грязное чтение и неповторяющееся чтение. В PostgreSQL не допускается так же фантомное чтение. Транзакция, использующая этот уровень изоляции делает снимок БД при первом запросе после начала транзакции и работает с этим снимком до конца транзакции. Поэтому не возникает неповторяемости чтения. При этом если другая транзакция изменила данные входящие в состав снимка, то текущая транзакция эти же данные изменить не сможет. Запрос на изменение будет отклонен и потребуется делать изменения уже в новой транзакции.

  • Serializable. Не допускается ни один из феноменов. На этом уровне снимок БД делается сразу после начала транзакции.

Уровни изоляции транзакций. Практика

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

sudo -u postgres psql postgres

Создадим таблицу и заполним ее данными. Если вы выполнили практику из раздела Создание транзакций. Практика, то этот шаг можно пропустить.

CREATE TABLE cars (
id BIGSERIAL PRIMARY KEY,
model varchar(20),
max_speed INTEGER);

Заполним таблицу данными

INSERT INTO cars (model, max_speed) VALUES 
('Toyota', 150),
('Lada', 130),
('BMW', 200),
('Renault', 190),
('Mercedes', 230);

Read Uncommitted / Read Committed

Потерянное обновление

Напомню что уровни read uncommitted и read committed в PostgreSQL совпадают. На этом уровне снимок базы данных создается перед каждым запросом. Сначала проверим что уровень изоляции Read Committed не допускает потерянное обновление.
В первом терминале установим уровень изоляции Read uncommitted и начнем транзакцию.

BEGIN ISOLATION LEVEL READ COMMITTED;

Проверим что есть в таблице

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  3 | BMW      |       200
  5 | Mercedes |       230
  4 | Renault  |       190

Во втором терминале проделаем те же действия.

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  3 | BMW      |       200
  5 | Mercedes |       230
  4 | Renault  |       190

Вернемся в первый терминал и обновим строку

UPDATE cars SET max_speed = max_speed + 50 WHERE model='BMW';

Посмотрим что получилось

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  5 | Mercedes |       230
  4 | Renault  |       190
  3 | BMW      |       250

Транзакция видит свои изменения, но они еще не зафиксированы.

Перейдем во второй терминал и проверим что видит вторая транзакция

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  3 | BMW      |       200
  5 | Mercedes |       230
  4 | Renault  |       190

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

UPDATE cars SET max_speed = max_speed + 100 WHERE model='BMW';

Видим что команда повисла в режиме ожидания. Это произошло из-за того, что первая транзакция заблокировала строку с BMW. Блокировка продлится до тех пор, пока первая транзакция не зафиксирует свои изменения, либо не сделает откат изменений.
В первом терминале зафиксируем изменения

COMMIT;

Перейдем во второй терминал. Видим что теперь команда UPDATE завершилась.
Посмотрим что теперь находится в нашей таблице

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  5 | Mercedes |       230
  4 | Renault  |       190
  3 | BMW      |       270

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

COMMIT;

Грязное чтение

Теперь проверим что на уровне изоляции Read Committed не допускается грязное чтение.
В первом терминале начнем транзакцию и установим уровень изоляции Read uncommitted.

BEGIN ISOLATION LEVEL READ COMMITTED;

Проверим что есть в таблице.

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  3 | BMW      |       270
  5 | Mercedes |       230
  4 | Renault  |       190

Обновим строку

UPDATE cars SET max_speed = 400 WHERE model='Lada';

И проверим что получилось

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  3 | BMW      |       270
  5 | Mercedes |       230
  4 | Renault  |       190
  2 | Lada     |       400

Видим что строка обновилась. Замечу, что мы еще не фиксировали транзакцию.

Теперь переходим во второй терминал и выполняем следующие команды

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  3 | BMW      |       270
  5 | Mercedes |       230
  4 | Renault  |       190

Видим в строке c моделью Lada по прежнему стоит значение 130. Таким образом параллельная транзакция не может читать незафиксированные данные другой транзакции.

Неповторяющееся чтение

На уровне изоляции Read Committed допускается неповторяющееся чтение. Давайте это проверим.
В первом терминале начнем транзакцию и сделаем выборку

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM cars WHERE model='Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       190

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

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE cars SET max_speed = max_speed + 30 WHERE model='Renault';
COMMIT;

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

SELECT * FROM cars WHERE model='Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       220

Видим что результат выборки отличается от первого. Таким образом на уровне изоляции Read Committed допускается феномен "Неповторяющееся чтение" и следующие за ним феномены согласно списка выше.

Repeatable Read

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

Неповторящееся чтение

Покажем что на уровне изоляции Repeatable Read не допускается неповторящееся чтение. В первом терминале начнем транзакцию и сделаем выборку

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM cars WHERE model='Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       220

Во втором терминале так же начнем транзакцию, обновим строку и зафиксируем изменения

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE cars SET max_speed = max_speed + 100 WHERE model='Renault';
COMMIT;

Теперь перейдем во первый терминал и снова сделаем выборку

SELECT * FROM cars WHERE model='Renault';
 id |  model  | max_speed
----+---------+-----------
  4 | Renault |       220

Видим что результат выборки не изменился. Таким образом на уровне изоляции Repeatable Read неповторяющееся чтение не допускается.

Фантомное чтение

Покажем что на уровне изоляции Repeatable Read не допускается фантомное чтение. В первом терминале начнем транзакцию и сделаем выборку

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  5 | Mercedes |       230
  3 | BMW      |       270
  4 | Renault  |       320

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

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DELETE FROM cars WHERE model = 'Renault';
COMMIT;

Теперь перейдем во первый терминал и снова сделаем выборку

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  1 | Toyota   |       150
  2 | Lada     |       130
  5 | Mercedes |       230
  3 | BMW      |       270
  4 | Renault  |       320

Видим что результат выборки не изменился и фантомное чтение не допускается.

Ошибка сериализации

Посмотрим как проявляется ошибка сериализациции на уровне изоляции Repeatable Read.
В первом терминале начнем транзакцию и обновим строку

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE cars SET max_speed = max_speed + 100 WHERE model = 'Toyota';

Перейдем во второй терминал и сделаем то же самое

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE cars SET max_speed = max_speed + 100 WHERE model = 'Toyota';

Видим что вторая транзакция повисла в режиме ожидания, она ждет завершения первой транзакции.
Перейдем в первый терминал и зафиксируем изменения.

COMMIT;

Теперь перейдем во второй терминал и здесь мы видим сообщение об ошибке сериализации

ERROR:  could not serialize access due to concurrent update

Это произошло потому, что снимок базы данных на уровне Repeatable Read делается при первом запросе. Вторая транзакция пытается обновить строку и видит что эта строка уже изменена первой транзакцией. Если выполнить обновление строки из второй транзакции то возникнет фантомный эффект "Потерянное обновление", который не допустимо на уровне изоляции Repeatable Read, поэтому при выполнении второй транзакции возникает ошибка. Если мы попытаемся зафиксировать изменения во второй транзакции, то выполнится отмена изменений, а не фиксация.

COMMIT;
ROLLBACK

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

SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  2 | Lada     |       130
  5 | Mercedes |       230
  3 | BMW      |       270
  1 | Toyota   |       250

Serializable

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

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  2 | Lada     |       130
  5 | Mercedes |       230
  3 | BMW      |       270
  1 | Toyota   |       250

Обновим строку и посмотрим что получилось. Обратите внимание на две строки BMW.

UPDATE cars SET model='BMW' WHERE model='Toyota';
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  2 | Lada     |       130
  5 | Mercedes |       230
  3 | BMW      |       270
  1 | BMW      |       250

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

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  2 | Lada     |       130
  5 | Mercedes |       230
  3 | BMW      |       270
  1 | Toyota   |       250

Теперь во втором терминале выполним обновление и посмотрим что получилось. Обратите внимание на две строки Toyota.

UPDATE cars SET model='Toyota' WHERE model='BMW';
SELECT * FROM cars;
 id |  model   | max_speed
----+----------+-----------
  2 | Lada     |       130
  5 | Mercedes |       230
  1 | Toyota   |       250
  3 | Toyota   |       270

Перейдем в первый терминал и зафиксируем изменения

COMMIT;

Перейдем во второй терминал и тоже зафиксируем изменения

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.

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

  1 | Toyota   |       250
  3 | Toyota   |       270

То есть в первой транзакции мы бы выполнили обновление Toyota -> BMW, а затем во второй выполнили бы обновление BMW -> Toyota.
А если бы мы выполняли сначала вторую, а затем первую транзакцию, то получили бы такой результат.

  1 | BMW      |       250
  3 | BMW      |       270

То есть во второй транзакции мы бы выполнили обновление BMW -> Toyota, а затем во первой выполнили бы обновление Toyota -> BMW.
Но если бы транзакции выполнялись параллельно и зафиксировались результаты обоих транзакций, то результат был бы таким

  1 | BMW      |       250
  3 | Toyota   |       270

А этот результат ни совпадает ни с одним из результатов последовательного выполнения транзакций.

На этом все, спасибо за внимание!

Автор: EvgeniyGar

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js