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

Введение

В этой лабораторной работе вы изучите управление транзакциями в PostgreSQL, что является важнейшим аспектом обеспечения целостности данных. Вы узнаете, как начинать и фиксировать (commit) транзакции, рассматривая серию операций как единую единицу работы. Вы также научитесь откатывать (roll back) неудачные транзакции, устанавливать уровни изоляции (isolation levels) и имитировать блокировки (locks) при параллельных обновлениях (concurrent updates).

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/row_edit("Update Single Row") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") subgraph Lab Skills postgresql/db_access -.-> lab-550964{{"Управление транзакциями в PostgreSQL"}} postgresql/table_init -.-> lab-550964{{"Управление транзакциями в PostgreSQL"}} postgresql/row_add -.-> lab-550964{{"Управление транзакциями в PostgreSQL"}} postgresql/data_where -.-> lab-550964{{"Управление транзакциями в PostgreSQL"}} postgresql/row_edit -.-> lab-550964{{"Управление транзакциями в PostgreSQL"}} postgresql/row_drop -.-> lab-550964{{"Управление транзакциями в PostgreSQL"}} end

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

В этом шаге вы узнаете, как начать и зафиксировать (commit) транзакцию в PostgreSQL. Транзакции обеспечивают целостность данных, рассматривая серию операций как единую единицу работы. Если какая-либо операция внутри транзакции завершается неудачно, вся транзакция откатывается (rolled back), предотвращая частичные обновления и поддерживая согласованность.

Сначала подключитесь к базе данных 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.

COMMIT;

Убедитесь, что транзакция прошла успешно, запросив таблицу accounts.

SELECT * FROM accounts;

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

Наконец, выйдите из оболочки psql.

\q

Откат (Roll Back) неудачной транзакции

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

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

sudo -u postgres psql

Давайте начнем новую транзакцию.

BEGIN;

В рамках этой транзакции мы попытаемся выполнить операцию, которая намеренно завершится неудачей. Давайте попробуем вставить дублирующийся первичный ключ (duplicate primary key). Сначала давайте найдем следующее доступное значение 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".

Поскольку в транзакции произошла ошибка, откатите (roll back) транзакцию, чтобы отменить все внесенные изменения. Используйте команду ROLLBACK.

ROLLBACK;

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

SELECT * FROM accounts;

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

Наконец, выйдите из оболочки psql.

\q

Установка и тестирование уровней изоляции (Isolation Levels)

В этом шаге вы узнаете об уровнях изоляции транзакций (transaction isolation levels) в PostgreSQL, а также о том, как их устанавливать и тестировать. Уровни изоляции контролируют степень, в которой параллельные транзакции изолированы друг от друга. Более высокие уровни изоляции обеспечивают большую защиту от повреждения данных, но могут снизить параллелизм (concurrency).

sudo -u postgres psql

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

Терминал 1:

sudo -u postgres psql

Терминал 2:

sudo -u postgres psql

В Терминале 1 установите уровень изоляции READ COMMITTED (хотя это значение по умолчанию, мы установим его явно для демонстрации).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Затем начните транзакцию.

BEGIN;

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

SELECT balance FROM accounts WHERE name = 'Alice';

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

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

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

SELECT balance FROM accounts WHERE name = 'Alice';

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

Теперь давайте протестируем уровень изоляции REPEATABLE READ. В Терминале 1 откатите (roll back) текущую транзакцию и установите уровень изоляции REPEATABLE READ.

ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

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

SELECT balance FROM accounts WHERE name = 'Alice';

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

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

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

SELECT balance FROM accounts WHERE name = 'Alice';

Поскольку уровень изоляции — REPEATABLE READ, вы по-прежнему будете видеть исходный баланс с момента начала транзакции, даже если Терминал 2 зафиксировал (committed) новое значение.

Наконец, в Терминале 1 зафиксируйте (commit) транзакцию.

COMMIT;

Теперь, если вы снова прочитаете баланс Alice в Терминале 1, вы увидите последнее зафиксированное (committed) значение (100.00).

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

\q

Моделирование блокировок (Locks) с параллельными обновлениями (Concurrent Updates)

В этом шаге вы смоделируете блокировки (locks) с параллельными обновлениями (concurrent updates) в 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 и устанавливает блокировку (lock) на строку, предотвращая изменение другими транзакциями до тех пор, пока эта транзакция не будет зафиксирована (committed) или отменена (rolled back).

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

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

Обратите внимание, что эта команда в Терминале 2 будет казаться зависшей. Это потому, что она ждет освобождения блокировки (lock), удерживаемой Терминалом 1.

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

COMMIT;

После фиксации (committing) транзакции в Терминале 1 команда UPDATE в Терминале 2 будет выполнена.

В Терминале 2 зафиксируйте (commit) транзакцию.

COMMIT;

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

SELECT * FROM accounts;

Вы должны увидеть, что баланс Alice был обновлен транзакцией в Терминале 2 после того, как Терминал 1 снял блокировку (released the lock).

Наконец, выйдите из обеих оболочек psql.

\q

Этот пример демонстрирует, как SELECT ... FOR UPDATE можно использовать для моделирования блокировок (simulate locks) и предотвращения вмешательства параллельных обновлений (concurrent updates) друг в друга. Без блокировки (lock) обе транзакции могли бы прочитать один и тот же начальный баланс и применить свои обновления на основе этого значения, что привело бы к потере обновления (lost update).

Итог (Summary)

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

Затем вы сосредоточились на демонстрации использования транзакций. Вы узнали, как инициировать транзакцию (initiate a transaction) с помощью команды BEGIN, выполнять несколько операций с базой данных (обновление балансов Alice и Bob), а затем зафиксировать (commit) транзакцию с помощью команды COMMIT, чтобы сделать изменения постоянными. Это проиллюстрировало фундаментальный принцип транзакций: рассмотрение серии операций как единого атомарного блока (single atomic unit). Вы также узнали, как откатывать (roll back) транзакции и устанавливать уровни изоляции (isolation levels). Наконец, вы смоделировали блокировки (locks) с параллельными обновлениями (concurrent updates), чтобы понять, как предотвратить повреждение данных.