Что произойдет, если две транзакции пытаются изменить одну запись в БД?

Две транзакции изменяют одну запись в БД

Что происходит

Это зависит от уровня изоляции (isolation level) транзакций. Разные БД и конфигурации по-разному это решают.

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

Стандарт SQL описывает 4 уровня (от слабого к сильному):

1. READ UNCOMMITTED   — Грязное чтение ❌
2. READ COMMITTED     — Чтение коммиченных данных ✅
3. REPEATABLE READ    — Стабильное чтение ✅✅
4. SERIALIZABLE       — Полная изоляция ✅✅✅

Сценарий: две транзакции меняют поле user.balance

Исходное состояние:

User ID=1, balance=$100

Что происходит:

Тransaction A (T1)          Transaction B (T2)
─────────────────           ─────────────────
BEGIN TRANSACTION
SELECT balance              BEGIN TRANSACTION
  WHERE user_id = 1
  (вернёт $100)
                            SELECT balance
                              WHERE user_id = 1
                              (вернёт $100)

UPDATE balance = 100 - 50 = $50
                            UPDATE balance = 100 + 20 = $120

COMMIT                      COMMIT
(balance = $50)             (balance = $120)
                            ← Потеря обновления T1!

Результат: Обновление транзакции A потеряно! Баланс стал $120, а должен быть $70 ($100 - $50 + $20)

Проблемы конкурентности

1. Dirty Read (грязное чтение)

T1                          T2
──────────                  ──────────
BEGIN TRANSACTION
UPDATE balance = 50
                            BEGIN TRANSACTION
                            SELECT balance
                            (вернёт 50 — невидимые данные!)
ROLLBACK
(отмена изменений)
                            -- T2 читает отменённые данные!

2. Non-Repeatable Read (нестабильное чтение)

T1                          T2
──────────                  ──────────
BEGIN TRANSACTION
SELECT balance
  (первое чтение: $100)
                            BEGIN TRANSACTION
                            UPDATE balance = 50
                            COMMIT

SELECT balance
  (второе чтение: $50!)
-- Одна транзакция видит разные значения!
COMMIT

3. Phantom Read (фантомное чтение)

T1                          T2
──────────                  ──────────
BEGIN TRANSACTION
SELECT COUNT(*)
  FROM users
  WHERE age > 18
  (результат: 1000)
                            BEGIN TRANSACTION
                            INSERT INTO users (...)
                            COMMIT (добавили 100 пользователей)

SELECT COUNT(*)
  WHERE age > 18
  (результат: 1100!)
-- Количество строк изменилось!
COMMIT

4. Lost Update (потеря обновления) — самая серьезная

T1: UPDATE balance = 100 - 50 = $50
T2: UPDATE balance = 100 + 20 = $120

Результат: только одно обновление видно
Потеряны данные из T1

Разные уровни изоляции

1. READ UNCOMMITTED (самый слабый)

from sqlalchemy import create_engine, text

engine = create_engine('postgresql://...')

# Позволяет грязное чтение
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"))
    # Может читать неподтвержденные изменения
    result = conn.execute(text("SELECT balance FROM users WHERE id = 1"))

⚠️ Проблемы:

• Грязное чтение (dirty read)
• Non-repeatable read
• Phantom read

✅ Использование: Отчёты, аналитика (когда точность не критична)

---

2. READ COMMITTED (дефолт в PostgreSQL и Oracle)

# PostgreSQL дефолт
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"))
    # Читает только коммиченные данные
    result = conn.execute(text("SELECT balance FROM users WHERE id = 1"))

✅ Решает:

• ❌ Грязное чтение

⚠️ Остаются проблемы:

• Non-repeatable read
• Phantom read
• Lost update!

Пример потери обновления:

T1: SELECT balance (читает коммиченное значение $100)
T2: SELECT balance (читает коммиченное значение $100)
T1: UPDATE balance = 50, COMMIT
T2: UPDATE balance = 120, COMMIT

РЕЗУЛЬТАТ: $120 (потеря $50)

---

3. REPEATABLE READ (дефолт в MySQL)

# MySQL дефолт
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"))
    # Первое чтение «замораживает» строку
    result1 = conn.execute(text("SELECT balance FROM users WHERE id = 1"))
    # Последующие чтения той же строки видят то же значение
    result2 = conn.execute(text("SELECT balance FROM users WHERE id = 1"))
    # result1 == result2 гарантированно

✅ Решает:

• ❌ Грязное чтение
• ❌ Non-repeatable read

⚠️ Остаются проблемы:

• Phantom read
• Lost update (в MySQL с MVCC!)

---

4. SERIALIZABLE (самый сильный)

# Максимальная изоляция
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"))
    # Транзакции выполняются как будто последовательно
    result = conn.execute(text("SELECT balance FROM users WHERE id = 1"))

✅ Решает:

• ❌ Грязное чтение
• ❌ Non-repeatable read
• ❌ Phantom read
• ❌ Lost update

❌ Цена:

• Очень медленно (контакты между транзакциями)
• Частые deadlocks
• Потребление памяти

Практический пример: PostgreSQL

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session
import threading
import time

engine = create_engine('postgresql://user:password@localhost/testdb')

def transaction_a():
    with engine.connect() as conn:
        with conn.begin():
            # Начинаем транзакцию
            balance = conn.execute(
                text("SELECT balance FROM users WHERE id = 1")
            ).scalar()
            print(f"T1: Прочитал баланс {balance}")
            
            time.sleep(1)  # Даём T2 время начать
            
            new_balance = balance - 50
            conn.execute(
                text(f"UPDATE users SET balance = {new_balance} WHERE id = 1")
            )
            print(f"T1: Обновил баланс на {new_balance}")
            # COMMIT при выходе из контекста

def transaction_b():
    time.sleep(0.5)  # Начинаем позже T1
    
    with engine.connect() as conn:
        with conn.begin():
            balance = conn.execute(
                text("SELECT balance FROM users WHERE id = 1")
            ).scalar()
            print(f"T2: Прочитал баланс {balance}")
            
            time.sleep(0.5)  # Даём T1 время обновить
            
            new_balance = balance + 20
            conn.execute(
                text(f"UPDATE users SET balance = {new_balance} WHERE id = 1")
            )
            print(f"T2: Обновил баланс на {new_balance}")
            # COMMIT при выходе из контекста

# Исходная БД: balance = 100
thread1 = threading.Thread(target=transaction_a)
thread2 = threading.Thread(target=transaction_b)

thread1.start()
thread2.start()

thread1.join()
thread2.join()

# Итоговый баланс (READ COMMITTED):
with engine.connect() as conn:
    result = conn.execute(text("SELECT balance FROM users WHERE id = 1")).scalar()
    print(f"Финальный баланс: {result}")
    # Вероятно: 120 (потеря T1 обновления на 50)
    # Должно быть: 70 (100 - 50 + 20)

Как избежать проблем

1. Optimistic Locking (версионирование)

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    balance = Column(Integer)
    version = Column(Integer, default=0)  # Версия для оптимистичного лока

# При обновлении:
def update_balance(user_id, amount):
    from sqlalchemy.orm import Session
    from sqlalchemy import and_
    
    with Session(engine) as session:
        user = session.query(User).filter(User.id == user_id).first()
        old_version = user.version
        
        user.balance += amount
        user.version += 1
        
        # Обновляем только если version не изменилась
        result = session.execute(
            text("""
            UPDATE users 
            SET balance = :new_balance, version = :new_version
            WHERE id = :id AND version = :old_version
            """),
            {
                "id": user_id,
                "new_balance": user.balance,
                "new_version": user.version,
                "old_version": old_version
            }
        )
        
        if result.rowcount == 0:
            raise Exception("Optimistic lock failed - record was modified")
        
        session.commit()

2. Pessimistic Locking (блокировка строк)

with Session(engine) as session:
    # SELECT FOR UPDATE блокирует строку
    user = session.query(User).with_for_update().filter(
        User.id == 1
    ).first()
    
    # Никакая другая транзакция не может изменять
    user.balance -= 50
    session.commit()

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

# Для финансовых операций
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"))
    # Максимальная безопасность, но медленнее

# Для чтения отчетов
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"))
    # Быстрее, а точность не критична

Заключение

Что происходит при двух транзакциях на одну запись:

1. Зависит от уровня изоляции БД
2. READ COMMITTED (дефолт) — может быть потеря обновления
3. REPEATABLE READ — стабильнее, но может быть phantom read
4. SERIALIZABLE — полная безопасность, но медленно
5. Используй Optimistic/Pessimistic Locking для критичных операций

Золотое правило: Для финансовых операций используй SERIALIZABLE или Pessimistic Locking.