Какие знаешь схемы транзакции в реляционных базах данных?

Схемы транзакций в реляционных базах данных

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

1. ACID свойства

Фундамент надёжности любой транзакции:

ACID = {
    "Atomicity": "Либо всё, либо ничего. Нет частичного выполнения",
    "Consistency": "БД переходит из одного консистентного состояния в другое",
    "Isolation": "Транзакции не видят друг друга грязные данные",
    "Durability": "После commit данные в БД гарантированно сохранены"
}

# Пример ACID транзакции:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql://...')
Session = sessionmaker(bind=engine)
session = Session()

try:
    # Atomicity: либо обе операции, либо ни одна
    user = session.query(User).filter(User.id == 1).first()
    user.balance -= 100
    
    account = session.query(Account).filter(Account.user_id == 1).first()
    account.balance += 100
    
    session.commit()  # Durability: всё сохранилось
except Exception as e:
    session.rollback()  # Consistency: откатываем всё
    raise

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

Определяют, как транзакции видят изменения друг друга:

a) Read Uncommitted (уровень 0)

Самый слабый. Разрешены все аномалии:

# Транзакция A читает грязные данные (dirty reads)
Transaction A:                    Transaction B:
BEGIN                             BEGIN
                                  balance = 1000
                                  balance -= 500
                                  (не commit ещё)
READ balance -> 500  # Dirty!
                                  ROLLBACK

# Проблема: A видела данные, которых нет в БД

b) Read Committed (уровень 1) — По умолчанию в PostgreSQL

Блокирует dirty reads. Разрешены phantom reads и non-repeatable reads:

# Non-repeatable read
Transaction A:                    Transaction B:
BEGIN                             BEGIN
SELECT * FROM users -> 5 rows     
                                  INSERT INTO users -> 6-я запись
                                  COMMIT
SELECT * FROM users -> 6 rows
# Проблема: А видит разные результаты одного запроса
COMMIT

c) Repeatable Read (уровень 2) — По умолчанию в MySQL InnoDB

Блокирует dirty reads и non-repeatable reads. Разрешены phantom reads:

# Phantom read
Transaction A:                    Transaction B:
BEGIN                             BEGIN
SELECT * FROM users WHERE age > 20 -> 5 rows
                                  INSERT INTO users (age: 25)
                                  COMMIT
SELECT * FROM users WHERE age > 20 -> 6 rows
# Проблема: новая запись появилась в диапазоне
COMMIT

d) Serializable (уровень 3) — Строжайший

Блокирует все аномалии. Но самый медленный:

TRANSACTION_ISOLATION = "SERIALIZABLE"
# Транзакции выполняются одна за другой, как будто никакого параллелизма нет
# Максимум безопасности, минимум производительности

3. Сравнение уровней

4. Практическое использование в Python

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.pool import StaticPool

# PostgreSQL
engine = create_engine(
    'postgresql://user:pass@localhost/db',
    isolation_level="READ_COMMITTED"  # По умолчанию
)

# MySQL
engine = create_engine(
    'mysql+pymysql://user:pass@localhost/db',
    isolation_level="REPEATABLE_READ"  # По умолчанию в InnoDB
)

with Session(engine) as session:
    with session.begin():  # Явная транзакция
        user = session.query(User).with_for_update().filter(User.id == 1).first()
        user.balance -= 100
        session.flush()  # Отправляет на БД, но не commit
    # Здесь автоматический commit

5. Блокировки

Prevent concurrency issues:

# Pessimistic locking: заблокируй сразу
user = session.query(User)\
    .with_for_update()  # SELECT ... FOR UPDATE\
    .filter(User.id == 1)\
    .first()
# Никто другой не может менять этого пользователя
user.balance -= 100
session.commit()

# Optimistic locking: проверь при commit
# Требует version колонки
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    balance = Column(Integer)
    version = Column(Integer, default=0)  # Increment на каждый update

# При update проверяется version
# Если кто-то изменил -> StaleDataError

6. Типичные проблемы и решения

# Проблема 1: Race condition (двойная трата денег)
balance = user.balance  # 100
if balance > 50:
    user.balance = balance - 50  # может быть race condition
    session.commit()

# Решение:
with session.begin():
    user = session.query(User).with_for_update().filter(User.id == 1).first()
    if user.balance > 50:
        user.balance -= 50
    # Если две транзакции конкурируют, вторая будет ждать

# Проблема 2: Deadlock (взаимная блокировка)
Transaction A:                    Transaction B:
LOCK table_1                      LOCK table_2
Wait for table_2                  Wait for table_1
# Deadlock!

# Решение: всегда блокируй таблицы в одном порядке

7. Savepoints

Откат к промежуточной точке в транзакции:

from sqlalchemy import create_engine, text

with engine.begin() as connection:
    connection.execute(text("INSERT INTO users VALUES (1, 'Alice')"))
    
    # Сохраняем точку
    connection.execute(text("SAVEPOINT sp1"))
    
    try:
        connection.execute(text("INSERT INTO users VALUES (1, 'Bob')"))  # Ошибка: duplicate
    except Exception:
        # Откатываемся только до sp1
        connection.execute(text("ROLLBACK TO sp1"))
    
    # Alice всё ещё в БД, Bob удален
    connection.execute(text("INSERT INTO users VALUES (2, 'Bob')"))  # OK

8. Distributed Transactions (2PC)

Two-Phase Commit для систем с несколькими БД:

# Фаза 1: Prepare
# Все участники проверяют, могут ли выполнить операцию

# Фаза 2: Commit
# Если все согласны -> commit; если один не готов -> rollback

# Используется редко из-за сложности, но критично для финансовых систем

9. Рекомендации

results = {
    "Для большинства случаев": "READ_COMMITTED",
    "Если нужна консистентность": "REPEATABLE_READ",
    "Финансовые операции": "SERIALIZABLE или with_for_update()",
    "Высоконагруженные системы": "READ_COMMITTED + optimistic locking",
    "Redis/кеш": "Учитывай staleness явно"
}

10. PostgreSQL vs MySQL

# PostgreSQL (лучше для ACID):
# - Serializable читает версионно (MVCC)
# - Меньше deadlock'ов
# - Более предсказуемо

# MySQL InnoDB:
# - По умолчанию REPEATABLE_READ
# - Phantom reads возможны даже на REPEATABLE_READ
# - Проще начать с 0, но сложнее масштабировать

Ы: Выбор уровня изоляции — это компромисс между безопасностью и производительностью. В most cases READ_COMMITTED достаточно, но для критичных операций используй блокировки или SERIALIZABLE.