Лабораторная работа № 6: Транзакции и управление ими в PostgreSQL

Цель работы

Ознакомиться с основами работы с транзакциями в PostgreSQL, изучить уровни изоляции транзакций, а также методы обработки ошибок.

Содержание

Транзакции в PostgreSQL представляют собой последовательность операций, которые выполняются как единое целое. Основная идея заключается в том, что все операции внутри транзакции должны быть выполнены успешно, иначе они должны быть отменены. Это свойство называется атомарностью и является частью концепции ACID (Atomicity, Consistency, Isolation, Durability).

Основные команды для работы с транзакциями

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

  • BEGIN: Начало транзакции.
  • COMMIT: Завершение транзакции с применением всех изменений.
  • ROLLBACK: Отмена транзакции, возвращающая базу данных в состояние до начала транзакции.

Пример использования:

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Если в процессе выполнения транзакции возникает ошибка, можно использовать ROLLBACK для отмены всех изменений:

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Если произошла ошибка
ROLLBACK;

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

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

  1. Read Uncommitted: Позволяет читать данные, которые были изменены, но еще не зафиксированы другими транзакциями. Этот уровень изоляции не поддерживается в PostgreSQL.
  2. Read Committed: Позволяет транзакции видеть только те изменения, которые были зафиксированы до начала выполнения запроса.
  3. Repeatable Read: Гарантирует, что все запросы в рамках транзакции будут видеть одни и те же данные, даже если другие транзакции вносят изменения.
  4. Serializable: Самый строгий уровень изоляции, который предотвращает любые конфликты между транзакциями, обеспечивая полную изоляцию.

Установка уровня изоляции:

sql

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Обработка ошибок

При работе с транзакциями важно правильно обрабатывать ошибки. PostgreSQL позволяет использовать блоки BEGIN ... EXCEPTION для обработки исключений. Например:

sql

BEGIN;
UPDATE accounts SET balance = balance / 0 WHERE id = 1; -- Это вызовет ошибку
EXCEPTION
    WHEN division_by_zero THEN
        ROLLBACK;
        RAISE NOTICE 'Ошибка: деление на ноль';
END;

Задания для самостоятельной работы

Задание 1: Создание базы данных и таблицы

  • Создайте новую базу данных bank_db.
  • Создайте таблицу accounts с полями id (целочисленный, первичный ключ) и balance (числовой).

Задание 2: Вставка данных

  • Вставьте несколько записей в таблицу accounts с различными значениями баланса.

Задание 3: Основы транзакций

  • Запустите транзакцию, которая будет уменьшать баланс одного из аккаунтов на 100. Используйте команды BEGIN, UPDATE и COMMIT.

Задание 4: Откат транзакции

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

Задание 5: Уровни изоляции

  • Изучите уровни изоляции транзакций. Установите уровень изоляции SERIALIZABLE и выполните транзакцию, которая будет блокировать запись.

Задание 6: Конфликты транзакций

  • Создайте две сессии и выполните транзакции, которые будут пытаться изменить один и тот же аккаунт. Наблюдайте за конфликтами и ошибками.

Задание 7: Обработка ошибок

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

Задание 8: Использование SAVEPOINT

  • Создайте транзакцию с использованием SAVEPOINT, чтобы сохранить промежуточное состояние. Затем выполните ROLLBACK TO SAVEPOINT, чтобы отменить изменения до сохраненной точки.

Задание 9: Атомарные операции

  • Реализуйте атомарную операцию перевода средств между двумя аккаунтами. Убедитесь, что обе операции (уменьшение и увеличение баланса) выполняются в одной транзакции.

Задание 10: Заключение и анализ

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