Транзакции в БД: ACID, уровни изоляции и deadlock’и

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

При работе с базами данных мы сталкиваемся с ключевыми вызовами: как гарантировать, что при переводе денег средства не потеряются? Как предотвратить ситуацию, когда двое пользователей одновременно изменяют одни и те же данные, перезаписывая изменения друг друга? Как обеспечить, чтобы при сбое системы не возникло состояние с противоречивыми данными?

Ответ кроется в транзакциях — фундаментальном механизме безопасного взаимодействия с базой данных. Транзакции соответствуют свойствам ACID (Atomicity, Consistency, Isolation, Durability), которые гарантируют: операции с данными либо полностью успешны, либо вообще не происходят.

При одновременном доступе множества пользователей возникают новые сложности: уровни изоляции определяют степень влияния транзакций друг на друга, от простых проблем чтения до фантомных записей. Deadlock’и могут возникнуть, когда транзакции ждут друг друга и взаимно блокируют выполнение. Оптимистичная конкурентность предлагает современную альтернативу традиционным пессимистичным блокировкам, обнаруживая и разрешая конфликты при проверке.

Эти концепции не просто теория — они критичны для банковских систем, платформ электронной коммерции, социальных сетей и любого ПО, которое надёжно работает с постоянными данными.

Что такое транзакции?

Определение и основы

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

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

-- Транзакция как логическая единица
BEGIN TRANSACTION;

-- Операция 1: снять со счёта
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

-- Операция 2: зачислить на счёт  
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Либо обе операции успешны, либо ни одна
COMMIT;
-- или при ошибке: ROLLBACK;

Свойства ACID

Свойства ACID — это фундамент надёжных систем транзакций. Они гарантируют, что операции с базой данных остаются непротиворечивыми и надёжными даже при сбоях, отказах системы или одновременном доступе множества пользователей. Каждое свойство решает конкретные проблемы: Atomicity предотвращает частичные операции, Consistency соблюдает бизнес-правила, Isolation защищает от взаимного влияния, а Durability обеспечивает постоянное сохранение.

Atomicity (атомарность)

Atomicity гарантирует, что транзакция либо выполняется полностью, либо не выполняется вовсе. Это критично для операций, состоящих из нескольких шагов, как перевод денег между счётами, где нужно одновременно снять со счёта и зачислить на другой. Без атомарности сбой системы мог бы привести к незаконченным операциям и противоречивым данным.

-- Пример атомарности
CREATE TABLE Transaktionen (
    trans_id INT PRIMARY KEY AUTO_INCREMENT,
    von_konto INT,
    zu_konto INT,
    betrag DECIMAL(10,2),
    status VARCHAR(20),
    zeitpunkt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Атомарный перевод
DELIMITER //
CREATE PROCEDURE ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Проверить достаточность баланса
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unzureichendes Saldo';
    END IF;
    
    -- Снять деньги
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    
    -- Зачислить деньги
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    -- Записать транзакцию в журнал
    INSERT INTO Transaktionen (von_konto, zu_konto, betrag, status)
    VALUES (von_konto_id, zu_konto_id, betrag, 'ERFOLGREICH');
    
    COMMIT;
END //
DELIMITER ;

-- Вызов
CALL ueberweisung(1, 2, 100.00);

Что демонстрирует этот пример: сохранённая процедура обеспечивает идеальную атомарность через DECLARE EXIT HANDLER FOR SQLEXCEPTION, который при любой ошибке автоматически откатывает изменения. Весь перевод (проверка баланса, списание, зачисление, логирование) обрабатывается как неделимая единица.

Ключевые моменты в этом примере:

• Обработка ошибок: обработчик перехватывает все SQL-ошибки и гарантирует откат при сбое
• Валидация баланса: перед выполнением проверяется наличие достаточных средств
• Полнота: все четыре шага должны выполниться успешно, иначе ни один не будет применён
• Логирование: транзакция записывается в журнал только при успехе, обеспечивая согласованность аудита

Consistency (непротиворечивость)

Consistency гарантирует, что база данных остаётся в непротиворечивом состоянии после транзакции. Это свойство соблюдает бизнес-правила и целостность данных, обеспечивая выполнение всех определённых ограничений, триггеров и связей. При переводе денег между счётами это означает, что общий баланс всех счётов остаётся постоянным и ни один счёт не может стать отрицательным.

-- Пример правил консистентности
CREATE TABLE Konten (
    konto_id INT PRIMARY KEY,
    inhaber VARCHAR(100),
    saldo DECIMAL(10,2) NOT NULL,
    CHECK (saldo >= 0)  -- Баланс не может быть отрицательным
);

CREATE TABLE Ueberweisungsregeln (
    regel_id INT PRIMARY KEY,
    max_betrag_pro_tag DECIMAL(10,2),
    max_anzahl_pro_tag INT
);

-- Транзакция с гарантией консистентности
DELIMITER //
CREATE PROCEDURE konsistente_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Бизнес-правило: проверить максимальную сумму
    DECLARE max_betrag DECIMAL(10,2);
    SELECT max_betrag_pro_tag INTO max_betrag 
    FROM Ueberweisungsregeln WHERE regel_id = 1;
    
    IF betrag > max_betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Betrag exceeds maximum';
    END IF;
    
    -- Проверка баланса (обеспечивается CHECK constraint)
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Выполнить операции
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    COMMIT;
END //
DELIMITER ;

Что демонстрирует этот пример: обеспечение консистентности происходит через несколько механизмов: ограничение CHECK (saldo >= 0) предотвращает отрицательные балансы, сохранённая процедура проверяет бизнес-правила перед выполнением, а при нарушении транзакция автоматически откатывается.

Ключевые моменты в этом примере:

• Ограничения БД: CHECK-ограничение обеспечивает бизнес-правила на уровне БД
• Валидация бизнес-логики: сохранённая процедура реализует дополнительные бизнес-правила
• Автоматический откат: при нарушении ограничений транзакция прерывается
• Целостность данных: многоуровневая защита консистентности (ограничение + прикладная логика)

Isolation (Изоляция)

Isolation гарантирует, что одновременно выполняемые транзакции не влияют друг на друга. Это предотвращает классические проблемы конкурентности: Dirty Reads (чтение незафиксированных данных), Non-Repeatable Reads (разные результаты при повторном чтении) и Phantom Reads (новые записи появляются между операциями чтения). Уровень изоляции контролируется параметрами, которые обеспечивают компромисс между консистентностью и производительностью.

-- Пример изоляции
-- Сессия 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Сессия 2 (параллельно):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Сессия 1 читает данные
SELECT * FROM Konten WHERE konto_id = 1;

-- Сессия 2 изменяет данные
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Сессия 1 читает снова (в зависимости от уровня изоляции)
SELECT * FROM Konten WHERE konto_id = 1;

Durability (Долговечность)

Durability гарантирует постоянное сохранение изменений, внесённых транзакцией.

-- Пример долговечности
-- После COMMIT изменения постоянны
START TRANSACTION;
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;  -- Изменения теперь постоянны

-- Даже при сбое системы изменения сохраняются
-- (благодаря Write-Ahead Logging и другим механизмам)

Уровни изоляции

Уровни изоляции определяют, насколько сильно одновременно выполняемые транзакции могут влиять друг на друга. Они обеспечивают важный компромисс между консистентностью данных и производительностью системы: более высокая изоляция гарантирует большую безопасность, но требует больше ресурсов и может замедлить операции. Выбор правильного уровня изоляции зависит от конкретного приложения и требований к консистентности данных.

READ UNCOMMITTED

Самый низкий уровень изоляции, допускающий Dirty Reads. Этот уровень редко используется, так как может привести к несогласованным данным, но обеспечивает максимальную производительность благодаря минимальному блокированию.

-- Пример READ UNCOMMITTED
-- Сессия 1:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE Konten SET saldo = 500 WHERE konto_id = 1;
-- Ещё не завершена!

-- Сессия 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Konten WHERE konto_id = 1;
-- Читает незавершённое значение (500) - Dirty Read!

-- Сессия 1:
ROLLBACK;  -- Изменение отменяется

-- Сессия 2 прочитала невалидные данные

Что демонстрирует этот пример: READ UNCOMMITTED позволяет читать данные, которые ещё не были завершены. Сессия 2 читает значение (500), которое сессия 1 отменяет, что приводит к несогласованным данным.

Ключевые моменты:

• Проблема Dirty Read: Сессия 2 читает незафиксированные данные, которые позже становятся недействительными
• Преимущество производительности: Блокировка не требуется, максимальная скорость чтения
• Несогласованность данных: Риск получить противоречивые результаты
• Применение: Только для систем, где абсолютная консистентность некритична

READ COMMITTED

Предотвращает Dirty Reads, но допускает Non-Repeatable Reads.

-- Пример READ COMMITTED
-- Сессия 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Читает 1000

-- Сессия 2:
START TRANSACTION;
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Сессия 1 читает снова:
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Теперь читает 1500
-- Non-Repeatable Read!

REPEATABLE READ

Предотвращает Dirty Reads и Non-Repeatable Reads, но допускает Phantom Reads.

-- Пример REPEATABLE READ
-- Сессия 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Читает 3 счёта

-- Сессия 2:
START TRANSACTION;
INSERT INTO Konten VALUES (4, 'Anna Schmidt', 2000);
COMMIT;

-- Сессия 1 читает снова:
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Всё ещё 3 счёта
-- Новая строка не видна (нет Phantom Read в MySQL)

SERIALIZABLE

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

-- Пример SERIALIZABLE
-- Сессия 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT AVG(saldo) FROM Konten;  -- Вычисляет среднее

-- Сессия 2:
START TRANSACTION;
INSERT INTO Konten VALUES (5, 'Bernd Mueller', 3000);
-- Ждёт завершения сессии 1!

-- Сессия 1:
COMMIT;

-- Сессия 2 может продолжать
COMMIT;

Контроль конкурентности

Pessimistic Concurrency Control

Блокирует ресурсы заранее, чтобы предотвратить конфликты.

-- Пример Pessimistic Locking
-- Явные блокировки
START TRANSACTION;

-- Блокируем строку
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;

-- Другие транзакции должны ждать
-- Сессия 2:
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;
-- Ждёт завершения сессии 1

-- Выполняем операции
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

COMMIT;  -- Блокировка снимается

Optimistic Concurrency Control

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

-- Optimistic Concurrency с версионной колонкой
CREATE TABLE Produkte (
    produkt_id INT PRIMARY KEY,
    name VARCHAR(100),
    preis DECIMAL(10,2),
    bestand INT,
    version INT DEFAULT 0
);

-- Обновление с проверкой версии
DELIMITER //
CREATE PROCEDURE update_produkt_optimistic(
    IN produkt_id INT,
    IN neuer_preis DECIMAL(10,2),
    IN erwartete_version INT
)
BEGIN
    DECLARE affected_rows INT;
    
    START TRANSACTION;
    
    UPDATE Produkte 
    SET preis = neuer_preis, version = version + 1
    WHERE produkt_id = produkt_id AND version = erwartete_version;
    
    SET affected_rows = ROW_COUNT();
    
    IF affected_rows = 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Concurrency conflict - product was modified';
    ELSE
        COMMIT;
    END IF;
END //
DELIMITER ;

-- Использование
-- Сначала читаем
SELECT produkt_id, name, preis, version FROM Produkte WHERE produkt_id = 1;

-- Обновляем с ожидаемой версией
CALL update_produkt_optimistic(1, 29.99, 5);

Что демонстрирует этот пример: Optimistic Concurrency Control использует версионную колонку для обнаружения конфликтов. Инструкция UPDATE проверяет, актуальна ли ожидаемая версия, и увеличивает версию при успешном обновлении.

Ключевые моменты:

• Версионная колонка: Каждое изменение увеличивает версию, что позволяет отслеживать историю
• Обнаружение конфликтов: Предложение WHERE проверяет версию перед обновлением
• Без блокировки: Явные блокировки не требуются, лучшая производительность при чтении
• Логика повтора: При конфликтах приложение должно повторить транзакцию

Deadlocks

Обнаружение и предотвращение deadlock’ов

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

-- Пример deadlock'а
-- Session 1:
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
-- Ожидает konto_id = 2
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Session 2 (параллельно):
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
-- Ожидает konto_id = 1
UPDATE Konten SET saldo = saldo + 50 WHERE konto_id = 1;

-- DEADLOCK! Обе транзакции ждут друг друга

Что показывает этот пример: классический сценарий deadlock’а. Session 1 блокирует счёт 1 и хочет получить доступ к счёту 2, а Session 2 блокирует счёт 2 и ожидает доступа к счёту 1. Обе транзакции взаимно блокируют друг друга.

Ключевые моменты в этом примере:

• Circular Wait: обе транзакции ожидают ресурсы, заблокированные друг другом
• Resource Holding: каждая транзакция уже держит одну блокировку и ждёт другую
• Deadlock Detection: база данных должна обнаружить deadlock и откатить одну из транзакций
• Prevention Strategy: согласованный порядок блокировок мог бы предотвратить этот deadlock

Стратегии предотвращения deadlock’ов

-- 1. Согласованный порядок блокировок
DELIMITER //
CREATE PROCEDURE sichere_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- Всегда блокируем в одинаковом порядке
    IF von_konto_id < zu_konto_id THEN
        SET @lock1 = von_konto_id;
        SET @lock2 = zu_konto_id;
    ELSE
        SET @lock1 = zu_konto_id;
        SET @lock2 = von_konto_id;
    END IF;
    
    START TRANSACTION;
    
    -- Блокировки в согласованном порядке
    SELECT * FROM Konten WHERE konto_id = @lock1 FOR UPDATE;
    SELECT * FROM Konten WHERE konto_id = @lock2 FOR UPDATE;
    
    -- Выполняем операции
    IF von_konto_id < zu_konto_id THEN
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    ELSE
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    END IF;
    
    COMMIT;
END //
DELIMITER ;

-- 2. Повторные попытки с timeout'ом
DELIMITER //
CREATE PROCEDURE ueberweisung_mit_retry(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 3;
    DECLARE deadlock_detected BOOLEAN DEFAULT FALSE;
    
    retry_loop: WHILE retry_count < max_retries DO
        BEGIN
            DECLARE EXIT HANDLER FOR 1213  -- Deadlock error code
            BEGIN
                SET deadlock_detected = TRUE;
                SET retry_count = retry_count + 1;
                IF retry_count < max_retries THEN
                    -- Небольшая задержка перед повторной попыткой
                    DO SLEEP(0.1 * retry_count);
                END IF;
            END;
            
            -- Выполняем транзакцию
            CALL sichere_ueberweisung(von_konto_id, zu_konto_id, betrag);
            
            -- Успешно - выходим из цикла
            LEAVE retry_loop;
        END;
        
        IF deadlock_detected AND retry_count >= max_retries THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Max retries exceeded';
        END IF;
        
        SET deadlock_detected = FALSE;
    END WHILE;
END //
DELIMITER ;

Мониторинг deadlock’ов

-- Получить информацию о deadlock'е (MySQL)
SHOW ENGINE INNODB STATUS;

-- Наблюдать за транзакциями в deadlock'е
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Показать статус блокировок
SELECT 
    object_name,
    lock_type,
    lock_mode,
    lock_status,
    engine_transaction_id
FROM performance_schema.data_locks
WHERE object_name = 'Konten';

Управление транзакциями в различных базах данных

MySQL/MariaDB

-- MySQL-специфические возможности
-- Управление autocommit
SET autocommit = 0;  -- Ручное управление транзакциями
SET autocommit = 1;  -- Автоматический commit (по умолчанию)

-- Savepoint'ы для частичного отката
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
SAVEPOINT sp1;

UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
SAVEPOINT sp2;

-- Откатиться к savepoint'у
ROLLBACK TO sp1;
COMMIT;  -- Остаётся только первое изменение

-- XA транзакции для распределённых систем
XA START 'xid1';
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

PostgreSQL

-- PostgreSQL-специфические возможности
-- Уровни изоляции транзакций
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory Lock'и (блокировки на уровне приложения)
SELECT pg_advisory_lock(12345);  -- Получить lock
SELECT pg_advisory_unlock(12345);  -- Освободить lock

-- Снимки транзакций
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM Konten WHERE konto_id = 1;

-- В другой сессии:
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;

-- Оригинальная сессия всё ещё видит старое значение
SELECT * FROM Konten WHERE konto_id = 1;

Oracle

-- Oracle-специфические возможности
-- Read-Only транзакции
SET TRANSACTION READ ONLY;
SELECT * FROM Konten;  -- Гарантированно согласованный вид

-- Autonomous Transactions
DELIMITER //
CREATE PROCEDURE log_transaktion(
    IN transaktion_id INT,
    IN beschreibung VARCHAR(200)
)
AS
BEGIN
    -- Autonomous Transaction
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    INSERT INTO Transaktionslog (trans_id, beschreibung, zeitpunkt)
    VALUES (transaktion_id, beschreibung, SYSTIMESTAMP);
    
    COMMIT;  -- Commit только для autonomous transaction
END;
//

-- Savepoint'ы
SAVEPOINT sp1;
-- Операции
ROLLBACK TO sp1;

Best practices управления транзакциями

1. Держать транзакции короткими

-- Плохо: длинная транзакция
START TRANSACTION;
SELECT * FROM grosse_tabelle;  -- Длинный запрос
-- ... много других операций ...
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

-- Хорошо: короткая, сосредоточенная транзакция
SELECT * FROM grosse_tabelle;  -- Вне транзакции
START TRANSACTION;
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

2. Выбирать правильный уровень изоляции

-- Для большинства случаев
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Для аналитических запросов
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Для критических финансовых операций
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. Реализация обработки ошибок

-- Надёжная обработка ошибок
DELIMITER //
CREATE PROCEDURE robuste_transaktion()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
                                   @errno = MYSQL_ERRNO,
                                   @text = MESSAGE_TEXT;
        
        ROLLBACK;
        
        -- Логирование
        INSERT INTO error_log (error_time, error_code, error_message)
        VALUES (NOW(), @errno, @text);
        
        -- Передача ошибки дальше
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Логика транзакции
    INSERT INTO tabelle1 (wert) VALUES (1);
    UPDATE tabelle2 SET wert = 2 WHERE id = 1;
    
    COMMIT;
END //
DELIMITER ;

4. Использование Connection Pooling

// Пример на Java с пулом соединений
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class TransactionService {
    private DataSource dataSource;
    
    public void executeInTransaction(TransactionCallback callback) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            
            callback.execute(conn);
            
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("Transaction failed", e);
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    @FunctionalInterface
    public interface TransactionCallback {
        void execute(Connection conn) throws SQLException;
    }
}

Концепции для проверки знаний

Ключевые ACID-свойства

Свойство	Описание	Реализация
Atomicity	Все или ничего	Rollback, Write-Ahead Logging
Consistency	Непротиворечивое состояние	Constraints, Triggers
Isolation	Отсутствие помех	Locks, Isolation Levels
Durability	Постоянное хранение	Redo Logs, Checkpoints

Уровни изоляции в сравнении

Уровень	Грязные чтения	Неповторяемые чтения	Фантомные чтения	Производительность
READ UNCOMMITTED	✅	✅	✅	Максимальная
READ COMMITTED	❌	✅	✅	Высокая
REPEATABLE READ	❌	❌	✅ (MySQL: ❌)	Средняя
SERIALIZABLE	❌	❌	❌	Минимальная

Типичные экзаменационные задачи

1. Объясните ACID-свойства
2. Сравните уровни изоляции
3. Реализуйте избежание deadlock
4. Выберите подходящие стратегии транзакций
5. Проанализируйте проблемы конкурентности

Основные моменты

Управление транзакциями критично для надёжных приложений баз данных:

• ACID-свойства обеспечивают целостность данных
• Уровни изоляции управляют поведением при конкурентности
• Избежание deadlock гарантирует стабильность системы
• Оптимистичная vs Пессимистичная конкурентная контроль
• Best Practices оптимизируют производительность и надёжность

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

---

Рекомендуемая литература: Базы данных