Что такое блокировка в БД?

Что такое блокировка в БД?

Блокировка (lock) — это механизм управления доступом в базах данных, который гарантирует, что несколько транзакций не будут одновременно модифицировать одни и те же данные. Блокировки обеспечивают консистентность и целостность данных в многопользовательской среде.

Основная идея

Когда одна транзакция блокирует ресурс (строку, таблицу), другие транзакции должны ждать, пока блокировка не будет снята. Это предотвращает race conditions и потерю данных.

Типы блокировок

1. Shared Lock (Читающая блокировка)

Множество транзакций могут одновременно читать данные. Новая транзакция не может получить exclusive lock.

import psycopg2

# PostgreSQL
conn = psycopg2.connect("dbname=mydb")
cur = conn.cursor()

# Читающая блокировка
cur.execute("SELECT * FROM accounts WHERE account_id = %s FOR SHARE", (123,))
account = cur.fetchone()

# Множество читателей могут получить доступ одновременно

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

Только одна транзакция может получить exclusive lock. Другие должны ждать.

import psycopg2

conn = psycopg2.connect("dbname=mydb")
cur = conn.cursor()

# Исключительная блокировка
cur.execute("SELECT * FROM accounts WHERE account_id = %s FOR UPDATE", (123,))
account = cur.fetchone()

# Другие транзакции будут ждать
cur.execute("UPDATE accounts SET balance = %s WHERE account_id = %s", (new_balance, 123))
conn.commit()

Блокировка на уровне строк

# PostgreSQL: FOR UPDATE (исключительная блокировка строки)
cur.execute("""
    SELECT * FROM orders 
    WHERE order_id = %s 
    FOR UPDATE
""", (order_id,))

order = cur.fetchone()
balance = order['total']

# Обновляем с гарантией, что никто другой не менял эту строку
cur.execute("""
    UPDATE orders 
    SET status = 'confirmed' 
    WHERE order_id = %s
""", (order_id,))

conn.commit()

3. Блокировка со Skip Locked

Полезна для избежания deadlocks. Пропускает заблокированные строки.

# Поиск первого доступного заказа
cur.execute("""
    SELECT * FROM orders 
    WHERE status = 'pending' 
    FOR UPDATE SKIP LOCKED
    LIMIT 1
""")

order = cur.fetchone()
if order:
    # Обновляем найденный заказ
    cur.execute("""
        UPDATE orders 
        SET status = 'processing' 
        WHERE order_id = %s
    """, (order['order_id'],))
    conn.commit()

Блокировка на уровне таблицы

# Исключительная блокировка всей таблицы
cur.execute("LOCK TABLE accounts IN EXCLUSIVE MODE")

# Сумма всех балансов с гарантией консистентности
cur.execute("SELECT SUM(balance) FROM accounts")
total = cur.fetchone()[0]

conn.commit()

Deadlock: проблема и решение

Deadlock — это ситуация, когда две транзакции ждут друг друга:

# Транзакция 1
begin()
lock(account_a)  # Блокирует счёт A
wait_for(account_b)  # Ждёт счёт B (заблокирован транзакцией 2)

# Транзакция 2
begin()
lock(account_b)  # Блокирует счёт B
wait_for(account_a)  # Ждёт счёт A (заблокирован транзакцией 1)

# Обе в deadlock!

Решение: консистентный порядок блокировок

def transfer_money(from_account, to_account, amount):
    # ВАЖНО: всегда блокируем в одном порядке (например, по ID)
    accounts = sorted([(from_account, 'from'), (to_account, 'to')], key=lambda x: x[0])
    
    begin()
    for account_id, role in accounts:
        lock(account_id)  # Всегда в одинаковом порядке
    
    if role == 'from':
        decrease_balance(from_account, amount)
    else:
        increase_balance(to_account, amount)
    
    commit()

Оптимистичная блокировка (версионирование)

Вместо физических блокировок используем версии:

class Account:
    def __init__(self, account_id, balance, version):
        self.account_id = account_id
        self.balance = balance
        self.version = version

def update_balance(account_id, old_version, new_balance):
    # Обновляем только если версия не изменилась
    cur.execute("""
        UPDATE accounts 
        SET balance = %s, version = version + 1
        WHERE account_id = %s AND version = %s
    """, (new_balance, account_id, old_version))
    
    # Проверяем, была ли обновлена ровно одна строка
    if cur.rowcount == 0:
        raise ConcurrencyError("Version conflict: data was modified")
    
    conn.commit()

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

Блокировки связаны с уровнями изоляции:

# Установка уровня изоляции PostgreSQL
conn.isolation_level = psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE

Лучшие практики

1. Минимизируйте время блокировки

   # Плохо
   cur.execute("SELECT * FROM accounts FOR UPDATE")
   time.sleep(5)  # Блокировка на 5 секунд!
   

2. Блокируйте в консистентном порядке — предотвращает deadlock

3. Используйте SKIP LOCKED для асинхронных задач

4. Предпочитайте оптимистичную блокировку когда конфликты редки

5. Обрабатывайте исключения deadlock

   import time
   
   for attempt in range(3):
       try:
           begin()
           # ... операции ...
           commit()
           break
       except DeadlockError:
           if attempt < 2:
               time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
               continue
           raise
   

Блокировки — это критический механизм для обеспечения консистентности данных в многопользовательских системах.