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

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

Уровни изолированности транзакций определяют, как и когда изменения, сделанные одной транзакцией, становятся видимыми для других транзакций. Существует четыре основных уровня изолированности:

1. Read Uncommitted (Чтение незафиксированных данных)

2. Read Committed (Чтение зафиксированных данных)

3. Repeatable Read (Повторяемое чтение)

4. Serializable (Сериализуемость)

Read Uncommitted (Чтение незафиксированных данных)

На этом уровне транзакция может читать данные, которые были изменены другой транзакцией, но еще не зафиксированы. Это может привести к проблемам, таким как "грязное чтение" (dirty read)

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Транзакция 2
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Может увидеть незафиксированные изменения

-- Транзакция 1
ROLLBACK; -- Откат изменений

Read Committed (Чтение зафиксированных данных)

На этом уровне транзакция может читать только те данные, которые были зафиксированы другими транзакциями. Это предотвращает "грязное чтение", но не решает проблему "неповторяемого чтения" (non-repeatable read).

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- Транзакция 2
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Видит только зафиксированные изменения

Repeatable Read (Повторяемое чтение)

На этом уровне транзакция гарантирует, что если она прочитала данные в начале, то эти данные не изменятся до завершения транзакции. Это предотвращает "неповторяемое чтение", но не решает проблему "фантомного чтения" (phantom read).

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Читает баланс

-- Транзакция 2
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- Транзакция 1
SELECT balance FROM accounts WHERE account_id = 1; -- Видит тот же баланс, что и в начале
COMMIT;

Serializable (Сериализуемость)

На этом уровне транзакции выполняются так, как если бы они были выполнены последовательно, одна за другой. Это предотвращает все виды аномалий чтения, включая "грязное чтение", "неповторяемое чтение" и "фантомное чтение".

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Читает баланс

-- Транзакция 2
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Блокируется до завершения транзакции 1

-- Транзакция 1
COMMIT;

-- Транзакция 2
-- Теперь может выполнить обновление
COMMIT;

Влияние уровней изолированности

• Read Uncommitted: Высокая производительность, но низкая изолированность. Возможны "грязные чтения".

• Read Committed: Баланс между производительностью и изолированностью. Предотвращает "грязные чтения".

• Repeatable Read: Высокая изолированность, предотвращает "грязные" и "неповторяемые" чтения.

• Serializable: Максимальная изолированность, но может привести к блокировкам и снижению производительности.

Конечно! Давайте рассмотрим основные аномалии, которые могут возникнуть при выполнении транзакций в базе данных, и как различные уровни изолированности помогают их предотвратить.

Основные аномалии транзакций:

1. Грязное чтение (Dirty Reads): Чтение данных, которые были изменены другой транзакцией, но не были зафиксированы. Возникает на уровне изоляции READ UNCOMMITTED.

2. Неповторяющееся чтение (Non-repeatable Reads): Повторное чтение той же строки возвращает разные результаты в одной и той же транзакции. Это может произойти на уровнях изоляции READ UNCOMMITTED и READ COMMITTED.

3. Фантомное чтение (Phantom Reads): При выполнении одной и той же операции чтения в транзакции могут быть возвращены разные наборы строк, из-за вставок, обновлений или удалений, выполненных другими транзакциями. Это может произойти на уровнях изоляции READ UNCOMMITTED, READ COMMITTED и REPEATABLE READ.

4. Потерянное обновление (Lost Updates): Две транзакции одновременно читают и изменяют одни и те же данные, и одно из изменений теряется. Это может произойти на уровне изоляции READ UNCOMMITTED и READ COMMITTED.

5. Неупорядоченное чтение (Out-of-order Reads): Чтения выполняются в произвольном порядке, что может привести к неправильным результатам в транзакциях. Это также возможно на уровне изоляции READ UNCOMMITTED и READ COMMITTED.

Эти проблемы демонстрируют различные компромиссы между производительностью и целостностью данных при использовании различных уровней изоляции транзакций в SQL.

Грязное чтение (Dirty Read)

Грязное чтение происходит, когда одна транзакция читает данные, которые были изменены другой транзакцией, но еще не зафиксированы. Если вторая транзакция откатится, первая транзакция будет работать с некорректными данными.

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Транзакция 2
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Видит незафиксированные изменения

-- Транзакция 1
ROLLBACK; -- Откат изменений

Неповторяемое чтение (Non-Repeatable Read)

Неповторяемое чтение происходит, когда одна транзакция читает одни и те же данные дважды и получает разные результаты, потому что другая транзакция изменила эти данные и зафиксировала изменения между двумя чтениями.

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Читает баланс

-- Транзакция 2
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- Транзакция 1
SELECT balance FROM accounts WHERE account_id = 1; -- Видит другой баланс
COMMIT;

Фантомное чтение (Phantom Read)

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

Пример:

-- Транзакция 1
BEGIN TRANSACTION;
SELECT * FROM employees WHERE department = 'Sales'; -- Читает список сотрудников

-- Транзакция 2
BEGIN TRANSACTION;
INSERT INTO employees (name, department) VALUES ('Jane Doe', 'Sales');
COMMIT;

-- Транзакция 1
SELECT * FROM employees WHERE department = 'Sales'; -- Видит новый список сотрудников
COMMIT;