343.52K

Category:

database

ОПБД. Лекции на дистант

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

3.

Логика работы
транзакций

4.

Ключевые свойства транзакций
ACID (Atomicity, Consistency, Isolation, Durability) — набор из четырёх
требований к транзакционной системе, обеспечивающих максимально
надёжную и предсказуемую работу:
1. Атомарность. Гарантирует, что каждая транзакция будет выполнена
полностью или не будет выполнена совсем.
2. Согласованность. Данные переходят из одного согласованного
состояния в другое..
3. Изолированность. Параллельные транзакции не влияют друг на друга.
4. Долговечность. Данные сохраняются в БД даже после сбоя системы

5.

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

6.

Нарушение атомарности
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Сбой
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

7.

Нарушение
согласованности
UPDATE accounts SET balance = balance - 500 WHERE id = 1; – Баланс < 0
COMMIT;

8.

Проблемы параллельного выполнения
транзакций

9.

Потерянное обновление
Две транзакции изменяют одну и ту же запись, но одна из них теряется.

10.

Грязное чтение
Транзакция читает данные, которые ещё не зафиксированы (не
подтверждены COMMIT) другой транзакцией.

11.

Неповторяющееся чтение
В одной транзакции один и тот же SELECT может вернуть разные данные,
если параллельная транзакция изменила и зафиксировала их (COMMIT).

12.

Фантомное чтение
Транзакция дважды выполняет запрос, но во второй раз получает другой
результат, потому что другая транзакция добавила/удалила строки.

13.

Уровни изолированности транзакций
Уровень изолированности транзакций (transaction isolation level) —
условное значение, показывающее, насколько внутреннее состояние базы
данных в момент выполнения транзакции доступно другим, одновременно
выполняемым транзакциям.

14.

READ COMMITED
Чтение подтверждённых данных (read committed) — допускает чтение всех
изменений, выполненных самой транзакцией, и только подтверждённых
(зафиксированных) изменений, выполненных другими (параллельными)
транзакциями.
Этот уровень обеспечивает отсутствие потерянных обновлений и грязного
чтения, при этом допускаются неповторяющееся чтение и фантомное
чтение.

15.

REPEATABLE READ
Повторяющееся чтение (repeatable read) — допускает только чтение
изменений, выполненных самой транзакцией, а прочитанные ею данные
становятся недоступными для изменения параллельным транзакциям.
Этот уровень обеспечивает отсутствие потерянных обновлений, грязного
чтения и неповторяющегося чтения, но допускает фантомное чтение.

16.

SERIALIZABLE
Сериализация (serializable) — допускает только такое выполнение
изменений данных, словно все модифицирующие данные транзакции
выполняются не параллельно, а последовательно.
Этот уровень обеспечивает отсутствие всех проблем, т.е. потерянных
обновлений, грязного чтения, неповторяющегося чтения и фантомного
чтения.

17.

Аномалия
READ
UNCOMMITTED
READ
COMMITTED
REPEATABLE
READ
SERIALIZA
BLE
Потерянное
обновление
Нет
Нет
Нет
Нет
Грязное чтение
Да
Нет
Нет
Нет
Неповторяющеес
я чтение
Да
Да
Нет
Нет
Фантомное
чтение
Да
Да
Да
Нет

18.

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

19.

Основные операторы управления транзакциями
BEGIN - начало транзакции,
COMMIT - фиксация(сохранение) изменений,
ROLLBACK - отмена всех операций в текущей транзакции,
SAVEPOINT - установка точки сохранения,
ROLLBACK TO SAVEPOINT - откат к точке сохранения.

20.

21.

22.

23.

BEGIN;
UPDATE accounts SET balance = balance - 100
WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100
SELECT * FROM accounts;
WHERE name = 'Bob';
id |
SAVEPOINT first_transfer;
UPDATE accounts SET balance = balance - 300
WHERE name = 'Bob';
name
----+---------+--------3 | Charlie |
700.00
1 | Alice
|
700.00
2 | Bob
|
800.00
(3 строки)
ROLLBACK TO SAVEPOINT first_transfer;
COMMIT;
COMMIT
Query returned successfully in 78 msec.
| balance

24.

BEGIN;
UPDATE accounts SET balance = balance - 150
WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 150
WHERE name = 'Bob';
SAVEPOINT first_transfer;
SELECT * FROM accounts;
id |
name
| balance
----+---------+--------UPDATE accounts SET balance = balance - 200
WHERE name = 'Bob';
UPDATE accounts SET balance = balance + 200
WHERE name = 'Charlie';
SAVEPOINT second_transfer;
ROLLBACK;
ROLLBACK
Query returned successfully in 104 msec.
3 | Charlie |
700.00
1 | Alice
|
700.00
2 | Bob
|
800.00
(3 строки)

25.

Уровни изоляции(примеры)

26.

27.

28.

29.

30.

31.

Снова 1я сессия:
Во 2й сессии:
ankopylov=> BEGIN;
BEGIN
ankopylov=*> UPDATE
products SET stock = stock - 2
WHERE id = 1;
UPDATE 1
ankopylov=*> COMMIT;
COMMIT
ankopylov=*> SELECT * FROM products WHERE
id = 1;
id | name | stock | price
----+--------+-------+--------1 | Laptop | 5 | 1000.00
(1 строка)
ankopylov=*> UPDATE products SET stock = stock
+ 5 WHERE id = 1;
ERROR: could not serialize access due to
concurrent update
ankopylov=!>

32.

Запросы WITH(Общие табличные
выражения)

33.

WITH (Common Table Expressions, CTE)
Используется для создания временных подзапросов, которые можно
переиспользовать в основном запросе. Эти подзапросы называются Common
Table Expressions (CTE).
Общий синтаксис:
WITH имя [(список_колонок)] AS (
подзапрос
)
основной_запрос;

34.

WITH moscow_flights AS (
SELECT f.* FROM flights AS f
JOIN airports AS a ON
Найти все рейсы, вылетающие из
Москвы.
f.departure_airport_id = a.airport_id
WHERE a.city = 'Москва'
)
SELECT * FROM moscow_flights;

35.

flight_id | flight_number | departure_airport_id | arrival_airport_id | departure_time | arrival_time
-----------+---------------+----------------------+--------------------+---------------------+--------------------1 | AA100
|
1|
2 | 2025-02-10 08:00:00 | 2025-02-10 10:00:00
2 | BB200
|
2|
3 | 2025-02-10 12:00:00 | 2025-02-10 14:00:00
3 | CC300
|
1|
3 | 2025-02-11 06:30:00 | 2025-02-11 09:30:00
(3 строки)

36.

WITH flight_durations AS (
SELECT
flight_id,
flight_number,
departure_time,
arrival_time,
arrival_time - departure_time AS
Найти среднее время в пути для
всех рейсов
duration
FROM flights
)
SELECT
AVG(duration) AS avg_flight_duration
FROM flight_durations;
avg_flight_duration
--------------------02:48:00
(1 строка)

37.

WITH frequent_passengers AS (
SELECT
p.passenger_id,
p.name,
COUNT(b.booking_id) AS total_bookings
FROM passengers p
JOIN bookings b ON p.passenger_id =
Найти пассажиров, у которых
более 2-х бронирований
b.passenger_id
GROUP BY p.passenger_id, p.name
)
SELECT * FROM frequent_passengers WHERE
total_bookings > 2;
passenger_id | name | total_bookings
--------------+------+---------------(0 строк)

38.

WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

39.

WITH RECURSIVE t(n) AS (
Необязательное указание RECURSIVE
превращает WITH из просто удобной
синтаксической конструкции в средство
реализации того, что невозможно в
стандартном SQL. Используя
RECURSIVE, запрос WITH может
обращаться к собственному результату.
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
sum
-----5050
(1 строка)

40.

CREATE TABLE categories (
id INT PRIMARY KEY,
name TEXT,
parent_id INT REFERENCES
categories(id)
);
INSERT INTO categories (id, name,
parent_id) VALUES
(1, 'Электроника', NULL),
(2, 'Мобильные телефоны', 1),
(3, 'Ноутбуки', 1),
(4, 'Телевизоры', 1),
(5, 'Смартфоны', 2),
(6, 'Айфоны', 5),
(7, 'Игры', 3),
(8, 'Аксессуары', 3);

41.

42.

CREATE TABLE flight_logs (
WITH deleted_flights AS (
log_id SERIAL PRIMARY KEY,
DELETE FROM flights
flight_id INT,
WHERE flight_id = 3
flight_number VARCHAR(10),
RETURNING flight_id, flight_number,
departure_airport_id INT,
departure_airport_id, arrival_airport_id,
arrival_airport_id INT,
departure_time, arrival_time
departure_time TIMESTAMP,
)
arrival_time TIMESTAMP,
INSERT INTO flight_logs (flight_id,
action VARCHAR(50),
flight_number, departure_airport_id,
log_timestamp TIMESTAMP DEFAULT
arrival_airport_id, departure_time, arrival_time,
CURRENT_TIMESTAMP
action)
);
SELECT flight_id, flight_number,
departure_airport_id, arrival_airport_id,
CREATE TABLE
departure_time, arrival_time, 'DELETE'
FROM deleted_flights;
Query returned successfully in 67 msec.
INSERT 0 1

43.

44.

Преимущества
● Упрощение сложных запросов:
○
Использование WITH позволяет вынести сложные подзапросы или агрегации в
отдельные блоки, что делает запросы более понятными и поддерживаемыми.
● Читаемость:
○
Вместо того, чтобы переписывать одни и те же подзапросы несколько раз, можно
использовать CTE для хранения промежуточных результатов и работать с ними как с
обычными таблицами.
● Повторное использование:
○
CTE могут быть использованы несколько раз в одном запросе, что позволяет избежать
дублирования логики.
● Рекурсивные запросы:
○
С помощью WITH RECURSIVE можно эффективно работать с иерархическими данными
(например, деревья категорий или родственные связи).

45.

Составные типы

46.

Составной тип
Набор атрибутов, каждый из которых имеет свое имя и свой тип. Является
объектом БД, его объявление регистрирует новый тип в системном каталоге,
после чего он становится полноценным типом SQL. Составной тип можно
использовать как любой другой тип SQL, например, создавать столбцы
таблиц этого типа и т. п.

47.

Создание
Для создания нового составного типа данных используется команда CREATE TYPE.
Синтаксис:
CREATE TYPE type_name AS (
eld_name1 data_type1,
eld_name2 data_type2,
...
eld_nameN data_typeN
);

48.

Создание типа:
Использование типа:
CREATE TYPE city_info AS (
CREATE TABLE cities (
name text,
id serial PRIMARY KEY,
population integer,
info city_info
founded_year integer
);
);

49.

Способы вставки
Значения составного типа можно формировать:
● в виде строки, внутри которой в скобках перечислены значения.
● с помощью конструктора ROW
● с помощью скобок(если в типе больше 1го поля)

50.

В виде строки, внутри которой в скобках перечислены
значения
INSERT INTO cities (info)
VALUES ('("Moscow",12655050, 1147)');
INSERT 0 1

51.

Конструктор ROW:
INSERT INTO cities (info)
VALUES (ROW('Moscow', 12655050, 1147));
INSERT 0 1

52.

Без кавычек и ROW(если больше
1го поля в типе):
INSERT INTO cities (info)
VALUES (('Moscow', 12655050, 1147));
INSERT 0 1

53.

Атрибуты составного типа как отдельные значения
Обращение к отдельному атрибуту составного типа — по сути то же, что и
обращению к столбцу таблицы, ведь строка таблицы — это и есть составной
тип.
При работе с атрибутами составного типа в SQL, они могут быть извлечены
как отдельные значения в запросах. Это делается с помощью точечной
нотации и использовании скобок.
синтаксис:
SELECT (table_name.type_name).type_value FROM table_name;
table_name - необязательно

54.

55.

Или в случае, когда используется выражение:
SELECT (('Moscow', 12655050,
1147)::city_info).name;

56.

Операции над значениями
составных типов

57.

Обновление
Здесь работают те же правила, что и для вставки

58.

UPDATE cities
SET info = ROW('Kemerovo', 560000,
1147)::city_info
WHERE (info).name = 'Moscow';
UPDATE 3

59.

UPDATE cities
SET info = ('Мыски', 560000, 1147)
WHERE (info).name = 'Kemerovo';
UPDATE 3

60.

Сравнение
Сравнение происходит поэлементно (примерно так же, как строки
сравниваются посимвольно)

61.

SELECT (info).name
FROM cities
WHERE (info).population > 50000;
name
-------
Мыски
Мыски
Мыски
(3 строки)

62.

Кастинг
Мы можем приводить значения составных типов к другим типам, если это
необходимо

63.

SELECT (info).name, ((info).population::numeric / 2) AS
half_population
FROM cities;
name
|
half_population
-------+---------------------
Мыски | 280000.000000000000
Мыски | 280000.000000000000
Мыски | 280000.000000000000
(3 строки)

64.

Типы перечислений(Enumerated Types, ENUM)
Специальный пользовательский тип данных, который позволяет хранить в
колонке одно из заранее определённых значений.
Синтаксис:
CREATE TYPE name AS ENUM (value1, value2, …)

65.

CREATE TABLE flights (
flight_id SERIAL PRIMARY KEY,
flight_number VARCHAR(10) NOT NULL,
CREATE TYPE flight_status AS ENUM
departure_airport_id INT,
('scheduled', 'delayed', 'cancelled',
arrival_airport_id INT,
'departed', 'arrived');
departure_time TIMESTAMP NOT NULL,
arrival_time TIMESTAMP NOT NULL,
status flight_status NOT NULL DEFAULT
CREATE TYPE
'scheduled'
Query returned successfully in 261 msec.
);
CREATE TABLE
Query returned successfully in 95 msec.

66.

INSERT INTO flights (flight_number,
departure_airport_id, arrival_airport_id,
departure_time, arrival_time, status)
VALUES ('SU123', 1, 2, '2025-03-01 10:00:00', '2025-0301 14:00:00', 'scheduled');
INSERT 0 1
Query returned successfully in 62 msec.
INSERT INTO flights (flight_number,
departure_airport_id, arrival_airport_id,
departure_time, arrival_time, status)
VALUES ('SU123', 1, 2, '2025-03-01 10:00:00',
'2025-03-01 14:00:00', 'test');
ERROR:
invalid input value for enum flight_status:
"test"
LINE 2: ...1, 2, '2025-03-01 10:00:00', '2025-03-01
14:00:00', 'test');
^
SQL state: 22P02
Character: 187

67.

Порядок значений в перечислении определяется последовательностью, в
которой были указаны значения при создании типа.
SELECT flight_number, status
FROM flights
SELECT flight_number, status FROM
flight_number |
status
flights WHERE status > 'scheduled';
---------------+----------SU123
| scheduled
flight_number |
SU123
| scheduled
---------------+-----------
SU123
| delayed
SU123
| delayed
SU123
| cancelled
SU123
| cancelled
SU123
| scheduled
(2 строки)
(5 строк)
status

68.

Тонкости
● В метках значений регистр и пробелы имеют значение(Happy и happy не одно и то же);
● Работает быстрее, чем просто VARCHAR;
● Хорошо подходит списков предопределённых значений.
● Нельзя удалить значение или изменить порядок их порядок;

69.

В enum можно добавить новое значение,
но нельзя изменить или удалить уже
существующее
ALTER TYPE flight_status ADD VALUE 'boarding';
ALTER TYPE
Query returned successfully in 139 msec.

English Русский Rules