Какие знаешь типы блокировок бд?

Типы блокировок в базах данных

Блокировки используются для управления конкурентным доступом к данным и предотвращения конфликтов между транзакциями.

Уровни блокировок

1. Table-level блокировки (блокировка таблицы)

Блокируется вся таблица целиком. Самая грубая и быстрая блокировка.

Типы:

• LOCK IN SHARE MODE — чтение таблицы, другие могут читать, но не писать
• LOCK IN EXCLUSIVE MODE — блокировка на запись, никто не может ни читать, ни писать

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

async def get_with_table_lock(session: AsyncSession):
    # MySQL: блокировка таблицы на чтение
    await session.execute(text("LOCK TABLES users READ"))
    result = await session.execute(text("SELECT * FROM users"))
    await session.execute(text("UNLOCK TABLES"))
    return result.fetchall()

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

2. Row-level блокировки (блокировка строки)

Блокируется конкретная строка или несколько строк.

Типы:

Shared Lock (Общая блокировка, Read Lock)

Много транзакций могут одновременно читать, но никто не может писать:

async def read_with_lock(session: AsyncSession, user_id: int):
    # PostgreSQL: SELECT FOR SHARE
    result = await session.execute(
        text("SELECT * FROM users WHERE id = :id FOR SHARE"),
        {"id": user_id}
    )
    return result.scalar()

Пример конфликта:

Транзакция A: SELECT FOR SHARE (user_id = 1) ✓ Читает
Транзакция B: SELECT FOR SHARE (user_id = 1) ✓ Читает (OK)
Транзакция C: UPDATE (user_id = 1)            ✗ Ждёт (блокирована)

Exclusive Lock (Исключительная блокировка, Write Lock)

Только одна транзакция может иметь эту строку, остальные ждут:

async def update_with_lock(session: AsyncSession, user_id: int):
    # PostgreSQL: SELECT FOR UPDATE
    result = await session.execute(
        text("SELECT * FROM users WHERE id = :id FOR UPDATE"),
        {"id": user_id}
    )
    user = result.scalar()
    
    # Изменяем
    await session.execute(
        text("UPDATE users SET balance = :balance WHERE id = :id"),
        {"balance": user.balance - 100, "id": user_id}
    )

Пример конфликта:

Транзакция A: SELECT FOR UPDATE (user_id = 1) ✓ Имеет блокировку
Транзакция B: SELECT FOR UPDATE (user_id = 1) ✗ Ждёт
Транзакция C: SELECT FOR SHARE (user_id = 1)  ✗ Ждёт

3. Intent Locks (намеренные блокировки)

Вспомогательные блокировки для оптимизации. Говорят: "я планирую заблокировать строки в этой таблице".

Типы:

• Intent Shared (IS) — нужна Shared Lock на некоторые строки
• Intent Exclusive (IX) — нужна Exclusive Lock на некоторые строки
• Shared Intent Exclusive (SIX) — таблица заблокирована на чтение, плюс Exclusive Lock на строки

# Внутри PostgreSQL автоматически используются Intent Locks
# Разработчику не нужно их указывать явно

4. Predicate Locks (блокировки предиката)

Блокируют не конкретные строки, а условие (range of rows).

Используются для решения Phantom Read на уровне SERIALIZABLE:

async def count_orders(session: AsyncSession):
    async with session.begin(isolation_level="SERIALIZABLE"):
        # Блокирует ВСЕ строки с status = pending, даже будущие
        count = await session.scalar(
            text("SELECT COUNT(*) FROM orders WHERE status = :status"),
            {"status": "pending"}
        )
        return count

Другая транзакция не сможет добавить строку с status = pending пока эта транзакция активна.

Deadlock (Взаимная блокировка)

Две или более транзакции ждут друг друга:

# Транзакция A
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  # Блокирует user 1
await asyncio.sleep(0.5)  # Ждём
SELECT * FROM users WHERE id = 2 FOR UPDATE;  # Ждём user 2

# Параллельно транзакция B
BEGIN;
SELECT * FROM users WHERE id = 2 FOR UPDATE;  # Блокирует user 2
await asyncio.sleep(0.5)  # Ждём
SELECT * FROM users WHERE id = 1 FOR UPDATE;  # Ждём user 1

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

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

async def transfer_money(
    session: AsyncSession,
    from_user_id: int,
    to_user_id: int,
    amount: float,
):
    async with session.begin():
        # ВСЕГДА меньший ID сначала
        first_id = min(from_user_id, to_user_id)
        second_id = max(from_user_id, to_user_id)
        
        await session.execute(
            text("SELECT 1 FROM users WHERE id = :id FOR UPDATE"),
            {"id": first_id}
        )
        await session.execute(
            text("SELECT 1 FROM users WHERE id = :id FOR UPDATE"),
            {"id": second_id}
        )
        
        # Теперь безопасно переводим

Грануляция блокировок

Таблица
  ↓ более мелкие
Партиция
  ↓ более мелкие
Страница (page)
  ↓ более мелкие
Строка (row)
  ↓ более мелкие
Поле (field/column) — редко

Page-level блокировка (промежуточный вариант):

• Блокирует 8KB страницы БД
• Быстрее чем table-level, но медленнее чем row-level
• Используется в MySQL MyISAM и некоторых других СУБД

Практический пример: предотвращение race condition

from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text, select

async def buy_ticket(session: AsyncSession, event_id: int) -> bool:
    """
    Безопасная покупка билета с предотвращением overselling
    """
    async with session.begin():
        # Шаг 1: Блокируем строку события
        event = await session.execute(
            text("""
                SELECT id, available_tickets FROM events 
                WHERE id = :id FOR UPDATE
            """),
            {"id": event_id}
        )
        event = event.scalar()
        
        # Шаг 2: Проверяем наличие билетов
        if event.available_tickets <= 0:
            return False  # Билеты закончились
        
        # Шаг 3: Уменьшаем счётчик
        await session.execute(
            text("""
                UPDATE events 
                SET available_tickets = available_tickets - 1 
                WHERE id = :id
            """),
            {"id": event_id}
        )
        
        # Шаг 4: Создаём запись о покупке
        await session.execute(
            text("""
                INSERT INTO tickets (event_id, created_at) 
                VALUES (:event_id, NOW())
            """),
            {"event_id": event_id}
        )
        
        return True  # COMMIT автоматический

Без SELECT FOR UPDATE две параллельные транзакции могли бы обе увидеть available_tickets = 1, оба вычли бы его (= 0), и произошла бы перепродажа!

Таблица совместимости блокировок

Заключение

Выбор блокировки зависит от задачи:

• Row-level Exclusive (SELECT FOR UPDATE) — для критичных операций (финансы, инвентарь)
• Row-level Shared (SELECT FOR SHARE) — когда нужна консистентность при чтении
• Table-level — редко, только когда блокируешь большую часть таблицы
• Избегай deadlocks — всегда блокируй в одном порядке