Управление транзакциями в PostgreSQL

Введение

В этой лабораторной работе вы познакомитесь с управлением транзакциями в PostgreSQL — важной частью обеспечения целостности данных. Вы научитесь начинать и фиксировать транзакции, объединяя несколько операций в одну рабочую единицу. Вы также узнаете, как отменять неудачные транзакции, задавать уровни изоляции и моделировать блокировки при параллельном обновлении данных.

Начало и фиксация транзакции

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

Сначала подключитесь к базе данных PostgreSQL от имени пользователя postgres. Откройте терминал и выполните команду:

sudo -u postgres psql

После подключения появится приглашение postgres=#.

Создайте таблицу accounts, на которой вы будете изучать транзакции.

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

Добавьте в таблицу accounts начальные данные.

INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);

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

BEGIN;

В рамках транзакции переведите 20 долларов со счёта Alice на счёт Bob.

UPDATE accounts SET balance = balance - 20.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 20.00 WHERE name = 'Bob';

Чтобы сохранить изменения, зафиксируйте транзакцию командой COMMIT.

COMMIT;

Проверьте результат, выполнив запрос к таблице accounts.

SELECT * FROM accounts;

Убедитесь, что баланс Alice уменьшился на 20 долларов, а баланс Bob увеличился на 20 долларов.

Пример вывода транзакции PostgreSQL

Выйдите из оболочки psql.

\q

Отмена неудачной транзакции

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

Вы должны оставаться подключёнными к базе данных PostgreSQL после предыдущего этапа. Если подключение завершено, подключитесь снова:

sudo -u postgres psql

Начните новую транзакцию.

BEGIN;

В этой транзакции выполните операцию, которая намеренно завершится ошибкой. Попробуйте добавить запись с уже существующим первичным ключом. Сначала узнайте наибольшее значение id в таблице.

SELECT MAX(id) FROM accounts;

Предположим, результат — 2. Теперь попробуйте добавить счёт с id = 1. Такое значение уже есть в таблице.

INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);

Команда вызовет ошибку ERROR: duplicate key value violates unique constraint "accounts_pkey".

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

ROLLBACK;

Убедитесь, что команда ROLLBACK сработала: выполните запрос к таблице accounts.

SELECT * FROM accounts;

В таблице должны остаться только счета Alice и Bob с балансами, которые были у них в конце первого этапа. Неудачная операция INSERT отменена.

Пример отмены транзакции PostgreSQL

Выйдите из оболочки psql.

\q

Настройка и проверка уровней изоляции

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

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

sudo -u postgres psql

Откройте два отдельных окна терминала. В каждом окне подключитесь к базе данных PostgreSQL от имени пользователя postgres. В обоих окнах должно появиться приглашение postgres=#.

Терминал 1:

sudo -u postgres psql

Терминал 2:

sudo -u postgres psql

В терминале 1 начните транзакцию и задайте для неё уровень изоляции READ COMMITTED (он используется по умолчанию). Команда SET TRANSACTION должна выполняться внутри транзакции до первого запроса.

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

В терминале 1 запросите баланс Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Запишите значение баланса. Затем в терминале 2 начните транзакцию и обновите баланс Alice.

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;

Снова запросите баланс Alice в терминале 1.

SELECT balance FROM accounts WHERE name = 'Alice';

При уровне изоляции READ COMMITTED вы увидите обновлённый баланс 90.00, зафиксированный в терминале 2.

Обновление баланса Alice при READ COMMITTED

Теперь проверьте уровень изоляции REPEATABLE READ. В терминале 1 отмените текущую транзакцию, начните новую и задайте для неё уровень REPEATABLE READ до чтения данных.

ROLLBACK;
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Снова запросите баланс Alice в терминале 1.

SELECT balance FROM accounts WHERE name = 'Alice';

Запишите значение баланса. Затем в терминале 2 начните транзакцию и ещё раз обновите баланс Alice.

BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;

Снова запросите баланс Alice в терминале 1.

SELECT balance FROM accounts WHERE name = 'Alice';

При уровне изоляции REPEATABLE READ вы по-прежнему увидите 90.00 — баланс из снимка данных, созданного первым запросом этой транзакции. Это произойдёт, даже если в терминале 2 уже зафиксировано значение 100.00.

В терминале 1 зафиксируйте транзакцию.

COMMIT;

Теперь ещё раз запросите баланс Alice в терминале 1. После завершения транзакции вы увидите последнее зафиксированное значение — 100.00.

SELECT balance FROM accounts WHERE name = 'Alice';

Выйдите из обеих оболочек psql.

\q

Моделирование блокировок при параллельном обновлении

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

sudo -u postgres psql

Терминал 1:

sudo -u postgres psql

Терминал 2:

sudo -u postgres psql

В терминале 1 начните транзакцию и обновите баланс Alice. Важно: используйте SELECT ... FOR UPDATE, чтобы заблокировать строку.

BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;

Команда возвращает баланс Alice и устанавливает блокировку на строку. Другие транзакции не смогут изменить её, пока эта транзакция не будет зафиксирована или отменена.

В терминале 2 начните транзакцию и попробуйте обновить баланс Alice.

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';

Обратите внимание: команда в терминале 2 будет выглядеть так, будто она зависла. Она ожидает, пока терминал 1 снимет блокировку.

Теперь зафиксируйте транзакцию в терминале 1.

COMMIT;

После фиксации транзакции в терминале 1 команда UPDATE в терминале 2 продолжит выполняться.

Зафиксируйте транзакцию в терминале 2.

COMMIT;

В любом из терминалов запросите данные из таблицы accounts, чтобы проверить изменения.

SELECT * FROM accounts;

Убедитесь, что баланс Alice обновился в результате транзакции из терминала 2 после того, как терминал 1 снял блокировку.

Пример параллельного обновления в PostgreSQL

Выйдите из обеих оболочек psql.

\q

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

Итоги

В этой лабораторной работе вы научились управлять транзакциями в PostgreSQL. Сначала вы подключились к базе данных с помощью psql и создали таблицу accounts с начальными данными.

Затем вы изучили работу с транзакциями: начали транзакцию командой BEGIN, выполнили несколько операций с базой данных — обновили балансы Alice и Bob — и сохранили изменения командой COMMIT. Так вы увидели основной принцип транзакций: несколько операций выполняются как одна атомарная единица. Вы также научились отменять транзакции и задавать уровни изоляции. В завершение вы смоделировали блокировки при параллельных обновлениях и узнали, как они помогают предотвращать повреждение данных.