Нормализация - это процесс организации данных в базе данных, чтобы они были свободны от избыточности и зависимости. Она помогает устранить несоответствия и аномалии данных, тем самым улучшая целостность данных. Нормализация представляет собой набор правил или руководств по проектированию схемы базы данных таким образом, чтобы избежать дублирования данных, избыточности данных и несоответствий данных.
Основная цель нормализации базы данных - избежать сложностей, устранить дублирование и организовать данные в согласованной форме. Нормализация уменьшает избыточность и зависимость данных, делая базу данных более эффективной, гибкой и масштабируемой. Она также помогает поддерживать согласованность и точность данных, обеспечивая правильное обработку обновлений и удалений.
Существует 6 + 3 нормальные формы, но на практике дальше чем 3-я нормальная форма почти никогда не применяется и они нужны скорее для академических исследований либо для очень специфических случаев.
Нормализованные таблицы:
- Легче понять
- Легче улучшать и расширять
- Защищены от:
- аномалий вставки
- аномалий обновления
- аномалий удаления
А кто такие вообще аномалии и зачем от них защищаться?
Аномалии это ситуации которые вызывают неожиданное поведение, из-за неправильной структуры. Давайте разбираться.
Существуют обстоятельства, при которых определенные факты невозможно записать. Например, каждая запись в отношении
"Преподаватели и их курсы" может содержать ID преподавателя, Имя преподавателя и Код курса. Следовательно,
детали
любого преподавателя, который преподает хотя бы один курс, могут быть записаны. Но, недавно нанятый преподаватель,
которому еще не назначены курсы, не может быть записан, за исключением установки кода курса в NULL.
| ID преподавателя | Имя преподавателя | Код курса |
|---|---|---|
| 389 | Dr. Headstrong | ENG-206 |
| 407 | Dr. Mugwort | CMP-101 |
| 407 | Dr. Mugwort | CMP-201 |
| ID преподавателя | Имя преподавателя | Код курса |
|---|---|---|
| 321 | Dr. Newone | ??? |
В этой ситуации мы получаем NULL там где мы его не ожидали, либо ошибку, если у поля есть модификатор NOT NULL
Одна и та же информация может быть выражена в нескольких строчках, поэтому обновление отношения может привести к логической несогласованности.
Например, каждая запись в отношении «Навыки работников» может содержать идентификатор работника, Адрес работника и Навык. Таким образом, может возникнуть потребность в обновлении адреса для определенного работника в нескольких записях (по одному для каждого навыка).
Если обновление происходит лишь частично – адрес работника обновляется в одних записях, но не в других – то отношение остается в несогласованном состоянии. В частности, отношение дает противоречивые ответы на вопрос о том, каков адрес конкретного работника.
Вот таблица с данными:
| Employee ID | Employee Address | Skill |
|---|---|---|
| 426 | 230 Parkland Crescent | typing |
| 426 | 230 Parkland Crescent | shorthand |
| 529 | 158 Watkins Place | public speaking |
| 529 | 108 Church Street | carpentry |
У нас в данный момент две записи для сотрудника с номером 529 и разные адреса.
При определенных обстоятельствах удаление данных, представляющих определенные факты, приводит к удалению данных, представляющих совсем другие факты. Отношение «Преподаватели и их курсы», описанное в предыдущем примере, страдает этим типом аномалии, так как если преподаватель временно перестает быть назначен на любой курс, последняя запись, в которой он появляется, должна быть удалена, фактически удаляя и самого преподавателя.
| ID преподавателя | Имя преподавателя | Код курса |
|---|---|---|
| 389 | Dr. Headstrong | ENG-206 |
| 407 | Dr. Mugwort | CMP-101 |
| 407 | Dr. Mugwort | CMP-201 |
Удаляем первую строчку, и вместе с курсом, удаляем и самого преподавателя.
Именно для того что бы избежать аномалий, нам необходимо приводить все данные в 3-ю нормальную форму.
Каждая следующая нормальная форма может быть достигнута, только когда данные приведены в предыдущую нормальную форму!!
Нельзя из 1-ой перейти в 3-ю, или вообще без нормализации, сразу во 2-ую. Только по очереди. Привели к 1-ой. Привели ко 2-ой. Привели к 3-ей.
Опытные разработчики сразу продумывают данные в 3-ей нормальной форме
Первая нормальная форма требует, чтобы:
- Все атрибуты должны были атомарными. (Нельзя хранить списки, или любые другие коллекции в одной ячейке)
- Порядок строк не должен использоваться для передачи информации.
- Каждая запись в таблице была уникальной. (Таблицы должны иметь первичные ключи, это гарантирует что запись является уникальной)
- Значения в колонках должны быть однотипными. (Типы данных не должны смешиваться в одном столбце (и СУБД не позволит вам этого сделать в любом случае), но всегда можно нарушить это правило сохраняя числа в виде строки)
Допусти мы разрабатываем базу данных для онлайн игры, и нам надо хранить информацию о персонажах, их инвентаре и уровне.
Наша базовая таблица будет выглядеть так:
| Username | Inventory | Level | Skill level |
|---|---|---|---|
| prombery87 | 2 амулета, 3 кольца | 9 | Advanced |
| wheed1997 | 18 медных монет, 1 лук, 5 стрел | almost 5 | Medium |
| acen1999 | 1 меч, 1 щит, 4 кольца | 2.5 | Beginner |
тут username - это имя пользователя, inventory - инвентарь пользователя, level - уровень
персонажа, skill level - уровень продвинутости персонажа (зависит от его уровня, пусть будет 1-3 это Beginner, 4-6
это Medium, 7-9 это Advanced)
Давайте разбираться что с этой таблицей не так и как мы можем привести ее к первой нормальной форме.
Все ли атрибуты у нас не содержат никаких коллекций? Нет, у нас инвентарь содержит не атомарную информацию!
Давайте это изменим. Нужно вынести коллекции в отдельные записи. Заменим инвентарь, на две колонки, item
и item_quantity:
| Username | Item | Item quantity | Level | Skill level |
|---|---|---|---|---|
| prombery87 | амулет | 2 | 9 | Advanced |
| prombery87 | кольцо | 3 | 9 | Advanced |
| wheed1997 | медные монеты | 18 | almost 5 | Medium |
| wheed1997 | лук | 1 | almost 5 | Medium |
| wheed1997 | стрелы | 5 | almost 5 | Medium |
| acen1999 | меч | 1 | 2.5 | Beginner |
| acen1999 | щит | 1 | 2.5 | Beginner |
| acen1999 | кольцо | 4 | 2.5 | Beginner |
В нашем случае, мы не используем порядок строк для передачи информации. Тут ничего менять не надо.
Является ли каждая запись в нашей таблице уникальной? По сути да, мы можем в качестве первичного ключа использовать
сочетание username и item.
В реальности там с вероятностью в 99% был бы еще id так как он сильно упрощает использование таблиц. Поэтому давайте
мы тоже его добавим.
| id | Username | Item | Item quantity | Level | Skill level |
|---|---|---|---|---|---|
| 1 | prombery87 | амулет | 2 | 9 | Advanced |
| 2 | prombery87 | кольцо | 3 | 9 | Advanced |
| 3 | wheed1997 | медные монеты | 18 | almost 5 | Medium |
| 4 | wheed1997 | лук | 1 | almost 5 | Medium |
| 5 | wheed1997 | стрелы | 5 | almost 5 | Medium |
| 6 | acen1999 | меч | 1 | 2.5 | Beginner |
| 7 | acen1999 | щит | 1 | 2.5 | Beginner |
| 8 | acen1999 | кольцо | 4 | 2.5 | Beginner |
Являются ли наши значения однотипными? Нет, в колонке level мы храним и целое число, и дробное и строку. Так быть не
должно, давайте все таки преобразуем эту колонку в целочисленный тип.
| id | Username | Item | Item quantity | Level | Skill level |
|---|---|---|---|---|---|
| 1 | prombery87 | амулет | 2 | 9 | Advanced |
| 2 | prombery87 | кольцо | 3 | 9 | Advanced |
| 3 | wheed1997 | медные монеты | 18 | 4 | Medium |
| 4 | wheed1997 | лук | 1 | 4 | Medium |
| 5 | wheed1997 | стрелы | 5 | 4 | Medium |
| 6 | acen1999 | меч | 1 | 3 | Beginner |
| 7 | acen1999 | щит | 1 | 3 | Beginner |
| 8 | acen1999 | кольцо | 4 | 3 | Beginner |
Теперь наша таблица приведена в первую нормальную форму
Вторая нормальная форма требует выполнения условий первой нормальной формы, а также чтобы каждый неключевой атрибут зависел от всего ключа.
В первую очередь нам надо понять, что такое не ключевой атрибут, и что мы можем с этим сделать.
Для этого нам надо понять, что именно мы вообще храним в таблице. И для чего она нам нужна.
Данная таблица хранит слишком много информации...
Тут мы храним, и данные о персонаже, и его инвентарь.
А это значит, что мы, как минимум, должны разнести данные по разным таблицам. Потому что в данный момент нашим ключевым
атрибутом является id и мы даже не можем сказать идентификатор чего это вообще.
В первую очередь нам надо определить зависимости между полями.
username -> level -> skill_level; # уровень навыков зависит от уровня персонажа, а уровень в свою очередь от имени персонажа. Мы же не можем сказать, что кто-то 5-ого уровня, а кто мы понятия не имеем
username + item -> item_quantity; # Какой-то персонаж владеет каким то количеством объектов, значит что нам надо сохранить информацию о том кто и чем владеет, но сохранить информацию о кол-ве в этой таблице.Таблица для персонажей:
| id | Username | Level | Skill level |
|---|---|---|---|
| 1 | prombery87 | 9 | Advanced |
| 2 | wheed1997 | 4 | Medium |
| 3 | acen1999 | 3 | Beginner |
Таблица для инвентаря
| id | user_id | Item | Item quantity |
|---|---|---|---|
| 1 | 1 | амулет | 2 |
| 2 | 1 | кольцо | 3 |
| 3 | 2 | медные монеты | 18 |
| 4 | 2 | лук | 1 |
| 5 | 2 | стрелы | 5 |
| 6 | 3 | меч | 1 |
| 7 | 3 | щит | 1 |
| 8 | 3 | кольцо | 4 |
Но, объект item тоже не является ключевым элементом, а значит должен быть вынесен в отдельную таблицу!
Таблица для персонажей:
| id | Username | Level | Skill level |
|---|---|---|---|
| 1 | prombery87 | 9 | Advanced |
| 2 | wheed1997 | 4 | Medium |
| 3 | acen1999 | 3 | Beginner |
Таблица для предметов:
| id | Item |
|---|---|
| 1 | амулет |
| 2 | кольцо |
| 3 | медные монеты |
| 4 | лук |
| 5 | стрелы |
| 6 | меч |
| 7 | щит |
Таблица для инвентаря
| id | user_id | Item | Item quantity |
|---|---|---|---|
| 1 | 1 | 1 | 2 |
| 2 | 1 | 2 | 3 |
| 3 | 2 | 3 | 18 |
| 4 | 2 | 4 | 1 |
| 5 | 2 | 5 | 5 |
| 6 | 3 | 6 | 1 |
| 7 | 3 | 7 | 1 |
| 8 | 3 | 2 | 4 |
Теперь наши данные находятся во второй нормальной форме!
Третья нормальная форма требует выполнения условий второй нормальной формы и отсутствия транзитивных зависимостей.
Транзитивная зависимость возникает в реляционной базе данных, когда один неключевой атрибут (колонка) зависит от другого неключевого атрибута, а тот, в свою очередь, зависит от первичного ключа.
То есть, если в таблице есть атрибуты A, B и C, и если:
- A -> B (B зависит от A)
- B -> C (C зависит от B)
Тогда можно сказать, что A транзитивно определяет C через B (A -> C через B).
Предположим, у нас есть таблица студентов с информацией о факультетах:
| Student_ID | Student_Name | Faculty_ID | Faculty_Name |
|---|---|---|---|
| 1 | Alice | F1 | Engineering |
| 2 | Bob | F2 | Science |
| 3 | Charlie | F1 | Engineering |
В этой таблице транзитивная зависимость выглядит так:
- Student_ID -> Faculty_ID (каждый студент связан с конкретным факультетом)
- Faculty_ID -> Faculty_Name (каждый факультет имеет уникальное название)
Таким образом, Student_ID транзитивно определяет Faculty_Name через Faculty_ID.
Изначальная таблица содержит транзитивные зависимости:
| Student_ID | Student_Name | Faculty_ID | Faculty_Name |
|---|---|---|---|
| 1 | Alice | F1 | Engineering |
| 2 | Bob | F2 | Science |
| 3 | Charlie | F1 | Engineering |
Для приведения к 3NF, мы разделим таблицу на две:
Таблица Студенты:
| Student_ID | Student_Name | Faculty_ID |
|---|---|---|
| 1 | Alice | F1 |
| 2 | Bob | F2 |
| 3 | Charlie | F1 |
Таблица Факультеты:
| Faculty_ID | Faculty_Name |
|---|---|
| F1 | Engineering |
| F2 | Science |
Теперь у нас нет транзитивных зависимостей, и обе таблицы находятся в 3NF.
Вернемся к нашей игре.
Есть ли у нас транзитивные зависимости?
Есть и я ее описал уже на этапе второй формы!
username -> level -> skill_levelТо что мы вынесли всю цепочку в отдельную таблицу не позволяет нам сказать, что мы от зависимости избавились!
Что же нужно сделать? Нужно вынести связь между уровнями и навыком в отдельную таблицу!
Таблица для зависимости уровня от навыка:
Для этого примера я не буду создавать отдельное поле
id(на самом деле в некоторых примерах выше (item, user, даже для инвентаря) можно было тоже обойтись без него, достаточно того, что бы у нас был уникальный атрибут или совокупность атрибутов)
| level | skill level |
|---|---|
| 1 | Beginner |
| 2 | Beginner |
| 3 | Beginner |
| 4 | Medium |
| 5 | Medium |
| 6 | Medium |
| 7 | Advanced |
| 8 | Advanced |
| 9 | Advanced |
Таблица для персонажей:
| id | Username | Level |
|---|---|---|
| 1 | prombery87 | 9 |
| 2 | wheed1997 | 4 |
| 3 | acen1999 | 3 |
Таблица для предметов:
| id | Item |
|---|---|
| 1 | амулет |
| 2 | кольцо |
| 3 | медные монеты |
| 4 | лук |
| 5 | стрелы |
| 6 | меч |
| 7 | щит |
Таблица для инвентаря
| id | user_id | Item | Item quantity |
|---|---|---|---|
| 1 | 1 | 1 | 2 |
| 2 | 1 | 2 | 3 |
| 3 | 2 | 3 | 18 |
| 4 | 2 | 4 | 1 |
| 5 | 2 | 5 | 5 |
| 6 | 3 | 6 | 1 |
| 7 | 3 | 7 | 1 |
| 8 | 3 | 2 | 4 |
Наши данные теперь приведены в 3-ю нормальную форму! И они защищены от аномалий вставки, обновления и удаления!
Транзакция — это последовательность одной или нескольких операций с базой данных, которые выполняются как единое логическое целое. Транзакция должна либо выполниться полностью, либо не выполниться вовсе. Это гарантирует целостность и консистентность данных в базе данных.
- Начало транзакции.
- Выполнение одной или нескольких операций (например,
INSERT,UPDATE,DELETE). - Фиксация изменений (команда
COMMIT) или откат изменений (команда ROLLBACK).
TCL - это часть языка SQL отвечающая за транзакции
В TCL используются следующие ключевые слова для управления транзакциями:
- BEGIN TRANSACTION или START TRANSACTION
- COMMIT
- ROLLBACK
- SAVEPOINT
- RELEASE SAVEPOINT
- SET TRANSACTION
Эти ключевые слова используются для явного начала новой транзакции. В большинстве баз данных транзакция начинается
автоматически, когда выполняется первая команда SQL, и завершается при выполнении COMMIT или ROLLBACK.
Пример:
BEGIN TRANSACTION;
-- или
START TRANSACTION;COMMIT используется для фиксации всех изменений, сделанных в рамках текущей транзакции. Это означает, что все
операции, выполненные после начала транзакции, станут постоянными и будут видны другим пользователям.
Пример:
BEGIN TRANSACTION;
INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000);
INSERT INTO accounts (name, balance)
VALUES ('Bob', 500);
COMMIT;ROLLBACK откатывает все изменения, сделанные в рамках текущей транзакции, возвращая базу данных в состояние до начала
транзакции.
Пример:
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 100
WHERE name = 'Bob';
ROLLBACK;SAVEPOINT используется для создания промежуточной точки в транзакции, к которой можно откатиться, не отменяя всю
транзакцию.
Пример:
BEGIN TRANSACTION;
INSERT INTO accounts (name, balance)
VALUES ('Charlie', 700);
SAVEPOINT savepoint1;
INSERT INTO accounts (name, balance)
VALUES ('Dave', 300);
ROLLBACK TO savepoint1;
COMMIT;RELEASE SAVEPOINT используется для удаления указанного savepoint. После этого откат к этому savepoint станет
невозможным.
Пример:
BEGIN TRANSACTION;
INSERT INTO accounts (name, balance)
VALUES ('Eve', 400);
SAVEPOINT savepoint2;
UPDATE accounts
SET balance = balance + 100
WHERE name = 'Eve';
RELEASE SAVEPOINT savepoint2;
COMMIT;SET TRANSACTION используется для установки уровня изоляции транзакции (обсудим ниже) или других параметров транзакции.
Пример:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO accounts (name, balance)
VALUES ('Frank', 600);
COMMIT;BEGIN TRANSACTION;
INSERT INTO products (name, price)
VALUES ('Product1', 100);
INSERT INTO products (name, price)
VALUES ('Product2', 150);
COMMIT;Все изменения будут сохранены в базе данных.
BEGIN TRANSACTION;
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';
DELETE
FROM products
WHERE price > 1000;
ROLLBACK;Все изменения будут отменены.
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2024-08-01');
SAVEPOINT savepoint1;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 2);
-- Ошибка во вставке данных
ROLLBACK TO savepoint1;
COMMIT;Вставка в таблицу orders будет сохранена, а вставка в order_items отменена.
Rollback (откат) является важным инструментом в управлении транзакциями, несмотря на то, что он откатывает все
изменения. Рассмотрим, почему ROLLBACK нужен и какие ситуации делают его применение критически важным.
-
Обеспечение целостности данных: В случае ошибок или некорректных изменений ROLLBACK позволяет вернуть базу данных в состояние до начала транзакции, предотвращая частичные изменения, которые могут нарушить целостность данных.
-
Корректировка ошибок: Если в процессе выполнения транзакции обнаруживается ошибка (например, нарушение ограничения целостности, синтаксическая ошибка или ошибка логики), ROLLBACK позволяет отменить все изменения, сделанные в рамках транзакции, и начать с чистого листа.
-
Обработка исключений: При возникновении непредвиденных ситуаций, таких как сбои системы, ошибки приложения или проблемы с сетевым подключением, ROLLBACK позволяет откатить все изменения и избежать некорректных данных в базе.
-
Проверка данных: Иногда разработчики и администраторы баз данных используют ROLLBACK для тестирования операций. Они могут выполнить серию операций, проверить результаты и затем откатить изменения, чтобы база данных осталась неизменной.
Предположим, у вас есть таблица employees, и вы хотите повысить зарплату сотрудникам отдела 'Sales' на 10%. Во время
выполнения обновления происходит ошибка (например, попытка установить отрицательную зарплату). ROLLBACK поможет откатить
изменения:
BEGIN TRANSACTION;
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';
-- Ошибка: попытка установить отрицательную зарплату (например, из-за неправильного вычисления)
IF @@ERROR != 0
BEGIN
ROLLBACK;
PRINT 'Ошибка при обновлении данных. Изменения откатаны.';
END
ELSE
BEGIN
COMMIT;
END
Предположим, вы добавляете новые заказы в таблицу orders и соответствующие товары в таблицу order_items. Если на
каком-то этапе вы обнаруживаете нарушение ограничения целостности (например, отсутствующий продукт), вы можете
использовать ROLLBACK:
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2024-08-01');
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 2);
-- Проверка на наличие продукта
IF NOT EXISTS (SELECT 1 FROM products WHERE product_id = 10)
BEGIN
ROLLBACK;
PRINT 'Ошибка: продукт не существует. Изменения откатаны.';
END
ELSE
BEGIN
COMMIT;
END
Представьте, что во время выполнения транзакции происходит сбой системы (например, отключение питания). В этом случае, благодаря использованию ROLLBACK, при следующем запуске базы данных изменения будут откатаны, и система вернется в согласованное состояние.
ACID — это набор свойств, которые гарантируют надежность транзакций в базах данных. ACID расшифровывается как:
- Atomicity (Атомарность)
- Consistency (Согласованность)
- Isolation (Изоляция)
- Durability (Долговечность)
Рассмотрим каждое из этих свойств более подробно.
Атомарность гарантирует, что все операции в транзакции будут выполнены полностью или не будут выполнены вовсе. Транзакция считается атомарной единицей работы. Если в ходе выполнения транзакции произошел сбой, все изменения, сделанные в ее рамках, будут отменены.
Пример:
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1, 100, 200);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 1);
COMMIT;Если вторая операция не выполнится (например, из-за ограничения целостности), первая операция также будет отменена.
Согласованность гарантирует, что транзакция переводит базу данных из одного согласованного состояния в другое. Это означает, что после выполнения транзакции все данные будут соответствовать установленным правилам целостности.
Пример:
BEGIN TRANSACTION;
UPDATE products
SET stock = stock - 1
WHERE product_id = 10;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 1);
COMMIT;Если stock становится отрицательным, это нарушает правило целостности, и транзакция должна быть отклонена.
Изоляция гарантирует, что параллельные транзакции не будут мешать друг другу. Уровни изоляции определяют, как одна транзакция видит данные, измененные другой параллельной транзакцией.
Пример:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Транзакция 1
SELECT balance
FROM accounts
WHERE account_id = 1;
-- Транзакция 2 (зависит от уровня изоляции, сможет или не сможет изменить данные)
COMMIT;Про эту тему поговорим ниже отдельно
Долговечность гарантирует, что результаты зафиксированной транзакции будут сохранены даже в случае сбоя системы. Это достигается с помощью журналирования изменений (логирования) и механизмов восстановления.
Пример:
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 1;
COMMIT;После выполнения команды COMMIT изменение баланса будет сохранено на диск, и данные останутся в базе данных даже при
отключении питания.
Рассмотрим несколько примеров транзакций с учетом свойств ACID.
- Перевод денег между счетами:
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
IF @@ERROR <> 0
BEGIN
ROLLBACK;
END
ELSE
BEGIN
COMMIT;
ENDВ этом примере транзакция атомарна, согласована, изолирована (в зависимости от уровня изоляции) и долговечна.
- Обновление данных с проверкой целостности:
BEGIN TRANSACTION;
UPDATE inventory
SET quantity = quantity - 10
WHERE product_id = 5;
IF
(SELECT quantity FROM inventory WHERE product_id = 5)
< 0
BEGIN
ROLLBACK;
END
ELSE
BEGIN
COMMIT;
ENDЭтот пример показывает, как можно обеспечить согласованность данных, проверяя состояние после выполнения операций.
Этот вопрос просто убийственный на собеседованиях. Если ты хочешь что бы человек не прошел собеседование, спроси его про уровни изоляции транзакций. И скорее всего ты своего добьешься.
В реляционных базах данных транзакции играют ключевую роль в обеспечении целостности данных и управлении многопользовательским доступом. Одна из важных характеристик транзакций — это уровень изоляции. Уровень изоляции определяет, как одна транзакция видит данные, измененные другими транзакциями. SQL стандарт определяет четыре уровня изоляции:
- Read Uncommitted (Чтение неподтвержденных данных)
- Read Committed (Чтение подтвержденных данных)
- Repeatable Read (Повторяемое чтение)
- Serializable (Сериализуемость)
Прежде чем углубиться в уровни изоляции, необходимо понять, какие аномалии могут возникнуть при работе с транзакциями:
-
Грязное чтение (Dirty Read): Одна транзакция читает данные, которые были изменены, но не подтверждены другой транзакцией. Если другая транзакция откатит изменения, прочитанные данные окажутся неверными.
-
Неповторяющееся чтение (Non-repeatable Read): Одна транзакция повторно читает те же данные и получает разные значения, так как другая транзакция изменила эти данные и подтвердила изменения между двумя чтениями.
-
Фантомное чтение (Phantom Read): Одна транзакция повторно выполняет запрос, который возвращает набор строк, и видит добавленные или удаленные строки, измененные другой транзакцией.
- Описание: Этот уровень позволяет транзакциям читать данные, которые были изменены, но не подтверждены другими транзакциями.
- Аномалии:
- Грязное чтение: возможно.
- Неповторяющееся чтение: возможно.
- Фантомное чтение: возможно.
- Использование: Обычно используется, когда важна скорость чтения данных и допустимы потенциальные ошибки.
- Описание: Транзакции могут читать только те данные, которые были подтверждены другими транзакциями.
- Аномалии:
- Грязное чтение: невозможно.
- Неповторяющееся чтение: возможно.
- Фантомное чтение: возможно.
- Использование: Является наиболее распространенным уровнем изоляции. Балансирует между целостностью данных и производительностью.
- Описание: Обеспечивает, что если транзакция читает данные, эти данные не могут быть изменены другими транзакциями до завершения первой транзакции.
- Аномалии:
- Грязное чтение: невозможно.
- Неповторяющееся чтение: невозможно.
- Фантомное чтение: возможно.
- Использование: Используется, когда важно, чтобы данные, прочитанные в начале транзакции, оставались неизменными на протяжении всей транзакции.
- Описание: Обеспечивает максимальный уровень изоляции. Транзакции выполняются так, как если бы они были сериализованы (выполнены одна за другой).
- Аномалии:
- Грязное чтение: невозможно.
- Неповторяющееся чтение: невозможно.
- Фантомное чтение: невозможно.
- Использование: Применяется в случаях, когда требуется максимальная целостность данных и изоляция транзакций, несмотря на снижение производительности.
Выбор уровня изоляции транзакции в SQL зависит от требований конкретного приложения к целостности данных и производительности.
Важно понимать, какие аномалии могут возникнуть при каждом уровне и как они могут повлиять на ваше приложение. В большинстве случаев, компромисс между производительностью и изоляцией достигается на уровне Read Committed.
Для критических систем, где требуется абсолютная целостность данных, следует использовать Serializable, несмотря на возможное снижение производительности.
Пример:
Предположим, у нас есть таблица accounts с колонками id и balance.
-- Транзакция 1
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- Транзакция 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT balance
FROM accounts
WHERE id = 1;
-- Здесь транзакция 2 может увидеть баланс, уменьшенный на 100, даже если транзакция 1 еще не подтвердила изменения.
COMMIT;
-- Транзакция 1
ROLLBACK;
-- Если транзакция 1 откатывается, баланс возвращается к исходному значению, но транзакция 2 уже прочитала неверные данные.Пример:
-- Транзакция 1
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- Транзакция 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT balance
FROM accounts
WHERE id = 1;
-- Здесь транзакция 2 не увидит изменений, пока транзакция 1 не завершится.
COMMIT;
-- Транзакция 1
COMMIT;
-- После коммита транзакции 1, изменения становятся видимыми для других транзакций.Пример:
-- Транзакция 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT balance
FROM accounts
WHERE id = 1;
-- Возвращает баланс, например, 500.
-- Транзакция 2
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance + 100
WHERE id = 1;
COMMIT;
-- Транзакция 1
SELECT balance
FROM accounts
WHERE id = 1;
-- Возвращает все тот же баланс 500, несмотря на изменения, сделанные транзакцией 2.
COMMIT;Пример:
-- Транзакция 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT SUM(balance)
FROM accounts
WHERE balance > 100;
-- Возвращает сумму, например, 300.
-- Транзакция 2
BEGIN TRANSACTION;
INSERT INTO accounts (id, balance)
VALUES (3, 150);
COMMIT;
-- Транзакция 1
SELECT SUM(balance)
FROM accounts
WHERE balance > 100;
-- Возвращает все ту же сумму 300, даже несмотря на добавление новой строки транзакцией 2.
COMMIT;- Грязное чтение: В примере транзакция 2 читает баланс, уменьшенный на 100, даже если транзакция 1 не подтвердила изменения и откатила их.
- Грязное чтение: В примере транзакция 2 не может видеть изменения, пока транзакция 1 не завершит свои операции. Грязное чтение предотвращается.
- Неповторяющееся чтение: Если транзакция 1 изменит данные и подтвердит изменения, транзакция 2 может увидеть разные значения при повторных чтениях.
- Грязное чтение: Нет, так как данные, которые читаются, уже подтверждены.
- Неповторяющееся чтение: Нет, так как повторные чтения тех же данных вернут одинаковые результаты.
- Фантомное чтение: Возможно. В примере фантомное чтение не рассматривается, но при выполнении повторного запроса, добавленные строки могут быть видимыми.
- Грязное чтение: Нет.
- Неповторяющееся чтение: Нет.
- Фантомное чтение: Нет. В примере транзакция 1 видит те же данные, несмотря на изменения, сделанные транзакцией 2.
Эти примеры иллюстрируют, как уровни изоляции транзакций помогают справляться с аномалиями и обеспечивать целостность данных в многопользовательских системах.
Есть два вида людей:
- которые делают бекапы
- и которые уже делают бекапы
© Джейсон Стетхем, VI век до нашей эры
Резервное копирование — это процесс создания копии данных, которую можно использовать для восстановления базы данных в случае потери данных или повреждения. Основные причины, по которым необходимо делать резервные копии:
- Защита от потери данных: Аппаратные сбои, ошибки пользователей, атаки вирусов и хакеров могут привести к потере данных.
- Восстановление после сбоев: Возможность быстро восстановить работоспособность базы данных после сбоя.
- Исторические данные: Возможность доступа к старым версиям данных для анализа или аудита.
- Разработка и тестирование: Копии данных могут использоваться для разработки, тестирования и экспериментов без риска повреждения основной базы данных.
pg_dump — это утилита для создания резервных копий базы данных PostgreSQL. Она позволяет экспортировать данные и схему базы данных в файл.
-
Экспорт всей базы данных:
pg_dump -U username -h hostname -d dbname > backup.sqlПараметры:
-U: имя пользователя.-h: хост, на котором работает база данных.-d: имя базы данных.
-
Экспорт в формат tar или custom:
pg_dump -U username -h hostname -d dbname -F t > backup.tarПараметр
-Fуказывает формат резервной копии:p: plain текст (по умолчанию).c: custom.t: tar.
-
Экспорт конкретной таблицы:
pg_dump -U username -h hostname -d dbname -t tablename > table_backup.sqlПараметр
-tуказывает таблицу для экспорта. -
Сжатие резервной копии:
pg_dump -U username -h hostname -d dbname | gzip > backup.sql.gz
-
Экспорт всей базы данных с указанием формата:
pg_dump -U postgres -h localhost -d mydatabase -F c > mydatabase.backup -
Экспорт схемы без данных:
pg_dump -U postgres -h localhost -d mydatabase -s > schema_only.sql -
Экспорт данных без схемы:
pg_dump -U postgres -h localhost -d mydatabase -a > data_only.sql
pg_restore — это утилита для восстановления базы данных из резервной копии, созданной с помощью pg_dump в формате tar или custom.
-
Восстановление базы данных:
pg_restore -U username -h hostname -d dbname backup_file
-
Создание новой базы данных и восстановление:
createdb -U username -h hostname new_dbname pg_restore -U username -h hostname -d new_dbname backup_file
-
Восстановление только схемы:
pg_restore -U username -h hostname -d dbname -s backup_file
-
Восстановление только данных:
pg_restore -U username -h hostname -d dbname -a backup_file
-
Восстановление базы данных из tar-архива:
pg_restore -U postgres -h localhost -d mydatabase mydatabase.backup
-
Восстановление с применением дополнительных параметров:
pg_restore -U postgres -h localhost -d mydatabase -j 4 --verbose mydatabase.backup
Параметр
-jуказывает количество параллельных потоков для ускорения процесса восстановления.
-
Планирование резервного копирования: Регулярно выполняйте резервное копирование базы данных, особенно перед внесением значительных изменений.
-
Проверка резервных копий: Периодически проверяйте резервные копии, чтобы убедиться, что они корректно восстанавливаются.
-
Автоматизация: Настройте автоматическое резервное копирование с помощью cron или других планировщиков задач.
-
Безопасность: Храните резервные копии в безопасных местах и защищайте их паролями или шифрованием.
-
Документация: Ведите документацию по процессу резервного копирования и восстановления, чтобы упростить обучение новых сотрудников и улучшить управляемость.
Использование
pg_dumpиpg_restoreпозволяет эффективно управлять резервными копиями и восстанавливать данные в PostgreSQL. Эти утилиты предоставляют гибкость и надежность, необходимые для обеспечения безопасности и доступности данных. Регулярное резервное копирование и проверка его целостности — залог стабильной работы любой системы управления базами данных.
- Регулярное резервное копирование: В крупных компаниях, где потеря данных может привести к серьезным финансовым потерям, резервное копирование проводится ежедневно.
- Восстановление после сбоя: Если база данных повреждена из-за сбоя оборудования, можно быстро восстановить данные из последнего бэкапа.
- Создание тестовых сред: Разработчики могут использовать резервные копии для создания тестовых баз данных, что позволяет тестировать новые функции без риска для основной базы.
Резервное копирование и восстановление — это критически важные процессы для обеспечения безопасности данных в PostgreSQL. Понимание различных методов резервного копирования и восстановления поможет вам эффективно управлять базами данных и минимизировать риски потери данных. Спасибо за внимание!