Введение
В этом лабораторном практикуме вы изучите управление транзакциями в 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 долларов с счета Алисы на счет Боба.
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;
Вы должны увидеть, что баланс Алисы уменьшился на 20 долларов, а баланс Боба увеличился на 20 долларов.
Наконец, выйдите из оболочки 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;
Проверьте успешность отката, запросив таблицу accounts.
SELECT * FROM accounts;
Вы должны увидеть, что таблица по-прежнему содержит только счета Алисы и Боба с их балансами из конца шага 1. Неудачная операция INSERT была успешно откатана.
Наконец, выйдите из оболочки 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 ISOLATION LEVEL READ COMMITTED;
Затем начните транзакцию.
BEGIN;
В Терминале 1 прочитайте баланс Алисы.
SELECT balance FROM accounts WHERE name = 'Alice';
Запомните баланс. Теперь в Терминале 2 начните транзакцию и обновите баланс Алисы.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;
В Терминале 1 снова прочитайте баланс Алисы.
SELECT balance FROM accounts WHERE name = 'Alice';
Поскольку уровень изоляции равен READ COMMITTED, вы увидите обновленный баланс (90.00), зафиксированный транзакцией из Терминала 2.
Теперь давайте проверим уровень изоляции REPEATABLE READ. В Терминале 1 откатите текущую транзакцию и установите уровень изоляции на REPEATABLE READ.
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
В Терминале 1 снова прочитайте баланс Алисы.
SELECT balance FROM accounts WHERE name = 'Alice';
Запомните баланс. Теперь в Терминале 2 начните транзакцию и снова обновите баланс Алисы.
BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;
В Терминале 1 снова прочитайте баланс Алисы.
SELECT balance FROM accounts WHERE name = 'Alice';
Поскольку уровень изоляции равен REPEATABLE READ, вы по-прежнему увидите исходный баланс, существовавший на момент начала транзакции в Терминале 1, даже если в Терминале 2 было зафиксировано новое значение.
Наконец, в Терминале 1 зафиксируйте транзакцию.
COMMIT;
Теперь, если вы снова прочитаете баланс Алисы в Терминале 1, вы увидите последнее зафиксированное значение (100.00).
Выйдите из обоих psql терминалов.
\q
Моделирование блокировок при одновременных обновлениях
В этом шаге вы смоделируете блокировки при одновременных обновлениях в PostgreSQL. Блокировки — это механизмы, используемые для предотвращения конфликтов между одновременными транзакциями, обеспечивая целостность данных.
Вы должны быть подключены к базе данных PostgreSQL из предыдущего шага. Если нет, подключитесь снова, используя:
sudo -u postgres psql
Откройте два отдельных терминала. В каждом терминале подключитесь к базе данных PostgreSQL как пользователь postgres. У вас должно быть два приглашения postgres=#.
Терминал 1:
sudo -u postgres psql
Терминал 2:
sudo -u postgres psql
В Терминале 1 начните транзакцию и обновите баланс Алисы. Важно использовать SELECT ... FOR UPDATE, чтобы заблокировать строку.
BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
Эта команда извлекает баланс Алисы и накладывает блокировку на строку, предотвращая другие транзакции от изменения её, пока эта транзакция не будет подтверждена или отменена.
В Терминале 2 начните транзакцию и попытайтесь обновить баланс Алисы.
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';
Обратите внимание, что эта команда в Терминале 2 может зависнуть. Это происходит потому, что она ожидает освобождения блокировки, удерживаемой Терминалом 1.
Теперь в Терминале 1 подтвердите транзакцию.
COMMIT;
После подтверждения транзакции в Терминале 1 команда UPDATE в Терминале 2 продолжится.
В Терминале 2 подтвердите транзакцию.
COMMIT;
Теперь в любом терминале запросите таблицу accounts, чтобы проверить изменения.
SELECT * FROM accounts;
Вы должны увидеть, что баланс Алисы был обновлён транзакцией в Терминале 2 после того, как Терминал 1 освободил блокировку.
Наконец, выйдите из обоих psql терминалов.
\q
Этот пример демонстрирует, как SELECT ... FOR UPDATE можно использовать для моделирования блокировок и предотвращения конфликтов при одновременных обновлениях. Без блокировки обе транзакции могли бы прочитать один и тот же начальный баланс и применить свои обновления на основе этого значения, что приведёт к потере обновления.
Резюме
В этом лабораторном практикуме вы изучили управление транзакциями в PostgreSQL. Вы начали с подключения к базе данных PostgreSQL с помощью psql и создания образцовой таблицы accounts с начальными данными.
Затем вы сосредоточились на демонстрации использования транзакций. Вы узнали, как начать транзакцию с помощью команды BEGIN, выполнить несколько операций с базой данных (обновить балансы Алисы и Боба), а затем зафиксировать транзакцию с помощью команды COMMIT, чтобы сделать изменения постоянными. Это продемонстрировало фундаментальный принцип транзакций: рассматривать серию операций как единое атомарное целое. Вы также изучили, как откатывать транзакции и устанавливать уровни изоляции. Наконец, вы смоделировали блокировки при одновременных обновлениях, чтобы понять, как предотвратить повреждение данных.
