Транзакції та дослідження плану запиту

Мета та потрібні знання

Відтворити узгоджене резервування ресурсу й дослідити індекс за планом виконання. Потрібно знати UPDATE, WHERE, RETURNING, BEGIN, COMMIT та ROLLBACK. Результат — SQL-скрипти, журнал двох сесій, плани до й після індексу та пояснення меж висновків.

Теоретичний мінімум

Ресурс має невід’ємний залишок. Умовне UPDATE одночасно перевіряє наявність і зменшує значення. Конкуруючі зміни одного рядка узгоджуються СКБД. Індекс допомагає знайти підмножину рядків, але має вартість зберігання й оновлення. EXPLAIN ANALYZE реально виконує запит, тому дослід вимірювання тут застосовуємо до SELECT.

Підготовка середовища

Використовуйте локальний PostgreSQL або окремий навчальний Supabase project. Для локального сервера підключіться через pgAdmin Query Tool або psql -d назва_навчальної_бази. У Supabase відкрийте SQL Editor власного навчального проєкту. Потрібні права створення схеми й таблиць. Перевірте з’єднання командою SELECT version(), current_database(), current_user;.

Скрипт нижче створює власну схему один раз і не видаляє наявних даних. Якщо схема вже існує після минулого запуску, використайте іншу навчальну назву та змініть SET search_path відповідно. Не виконуйте роботу у production-базі. Для окремих експериментів відкривайте новий запит і після помилки в явній транзакції виконуйте ROLLBACK.

Повний демонстраційний SQL

CREATE SCHEMA lab03_demo;
SET search_path TO lab03_demo;
CREATE TABLE stock (
  id integer PRIMARY KEY,
  remaining integer NOT NULL CHECK (remaining >= 0)
);
INSERT INTO stock VALUES (1,1);
BEGIN;
UPDATE stock SET remaining = remaining - 1
WHERE id = 1 AND remaining > 0 RETURNING remaining;
ROLLBACK;
SELECT remaining FROM stock WHERE id = 1;
CREATE TABLE event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category integer NOT NULL,
  payload text NOT NULL
);
INSERT INTO event (category,payload)
SELECT n % 1000, 'event-' || n FROM generate_series(1,50000) AS g(n);
ANALYZE event;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id,payload FROM event WHERE category = 17;
CREATE INDEX event_category_idx ON event(category);
ANALYZE event;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id,payload FROM event WHERE category = 17;
SELECT COUNT(*) FROM event WHERE category = 17;

Пояснення й очікуваний результат

Перший UPDATE тимчасово змінює залишок на 0; після ROLLBACK він знову 1. Event має 50000 рядків, рівномірно розподілених між 1000 категоріями. Для category17 очікується 50 рядків. Індекс не змінює результат; він створює додатковий спосіб доступу. План після створення може використати Bitmap Index Scan чи інший вузол відповідно до статистики й витрат.

У плані збережіть назви вузлів, estimated rows, actual rows, buffers та execution time. Estimated і actual можуть різнитися; це привід дослідити статистику. Час залежить від апаратури й кешу, тому не підмінюйте його числом із чужого звіту. Порівняйте кілька запусків і поясніть, які умови були однаковими.

Покроковий експеримент двох сесій

Відкрийте два незалежні з’єднання A і B до тієї самої навчальної бази. У кожному задайте SET search_path TO lab03_demo. Переконайтеся, що stock1 має remaining1.

У A виконайте BEGIN, потім те саме умовне UPDATE й залиште транзакцію відкритою. Результат A дорівнює 0. У B виконайте BEGIN і умовне UPDATE. B має очікувати звільнення блокування. Потім у A виконайте COMMIT. На стандартному READ COMMITTED запит B завершиться з нулем змінених рядків, бо після очікування умова remaining > 0 вже хибна. У B виконайте COMMIT.

Збережіть порядок дій і фінальний SELECT remaining. Результат має бути 0. Якщо замість COMMIT у A виконати ROLLBACK, B зможе зменшити відновлений залишок. Це окремий повтор досліду: перед ним встановіть remaining1 у завершеній транзакції та переконайтеся, що обидві сесії не мають відкритих транзакцій.

Самостійне завдання

Створіть окрему модель запасу двох видів обладнання й журналу резервувань. Одна операція повинна зменшити доступний залишок та додати запис журналу в одній транзакції. Якщо місць немає або журнал не може бути записаний через обмеження, зміна залишку має скасуватися. Самостійно визначте ключі, обмеження та SQL-команди.

Перевірте доступний ресурс, вичерпаний ресурс, відсутній id та дві паралельні спроби отримати останню одиницю. Поясніть, як клієнт дізнається про успіх за RETURNING. Не тримайте транзакцію відкритою для ручного введення у звичайному застосунку; тут затримка служить лише контрольованому експерименту.

Окремо створіть індекс для власного запиту за двома умовами у таблиці event, наприклад категорією й діапазоном id. Порівняйте план із наявним одностовпцевим індексом і поясніть порядок стовпців у запропонованому складеному індексі. Повного готового рішення основного резервування тут немає: демонстрація показує механізми для його побудови.

Налагодження та типові помилки

Завислий запит у B може означати очікування блокування A. Перевірте відкриту транзакцію та завершіть її. Довга транзакція впливає на інших користувачів, тому проводьте дослід лише у своїй навчальній базі. На іншому рівні ізоляції можливий інший результат, зокрема помилка, яка потребує повтору всієї транзакції; зафіксуйте SHOW transaction_isolation.

Якщо індекс не обраний, це не автоматична помилка: для широкого фільтра послідовне читання може бути дешевшим. Оновіть статистику й зіставте фактичну частку рядків. Не вимикайте планувальник як спосіб довести користь індексу.

Результат і контрольні питання

Подайте обидва SQL-файли, журнал конкуренції та два плани без вигаданого часу. Яка межа атомарності власної операції? Чому перевірка залишку окремим SELECT недостатня? Що робить ROLLBACK? Який індекс прискорює саме ваш запит і яка його вартість для запису?

Схема процесу

Самоперевірка

Підсумок

Транзакція узгоджує пов’язані зміни та конкуренцію. Результат умовного UPDATE визначає успіх резервування. Ефективність індексу оцінюємо за реальним планом і повторюваним експериментом.

Джерела

• PostgreSQL Tutorial
• SQL commands
• Ізоляція
• EXPLAIN