Какие знаешь блокировки в PostgreSQL?

Блокировки в PostgreSQL

Блокировки в PostgreSQL — механизм управления конкурентным доступом к данным. Правильное понимание блокировок критично для избежания deadlock'ов и race condition'ов.

1. Row-Level Locks (Блокировки на уровне строк)

FOR UPDATE (Exclusive Row Lock)

Эксклюзивная блокировка строки — другие транзакции не могут её изменять:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# В SQLAlchemy/Django ORM
from django.db import transaction
from django.db.models import F

with transaction.atomic():
    account = Account.objects.select_for_update().get(id=1)
    account.balance -= 100
    account.save()

FOR SHARE (Shared Row Lock)

Общая блокировка — другие транзакции могут читать, но не менять:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
SELECT * FROM accounts WHERE id = 2 FOR SHARE;
-- Обе строки заблокированы совместно
COMMIT;

FOR UPDATE SKIP LOCKED

Мощный инструмент для очередей — пропускает заблокированные строки:

-- Получить 10 свободных задач для обработки
SELECT * FROM tasks 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

# Получить противников для game matching
opponent = Player.objects.filter(
    status='waiting'
).select_for_update(skip_locked=True).first()

2. Page-Level Locks (Блокировки на уровне страниц)

Блокировки целой страницы данных (редко используется явно):

-- PostgreSQL использует их внутри для индексов
-- Явно задать можно через коллективные операции

3. Table-Level Locks (Блокировки на уровне таблиц)

ACCESS SHARE (самая слабая)

Другие транзакции могут читать и писать:

LOCK TABLE accounts IN ACCESS SHARE MODE;
SELECT COUNT(*) FROM accounts;  -- Разрешено
COMMIT;

ROW SHARE

LOCK TABLE accounts IN ROW SHARE MODE;
-- Другие транзакции не могут получить ROW EXCLUSIVE или выше

ROW EXCLUSIVE

LOCK TABLE accounts IN ROW EXCLUSIVE MODE;
UPDATE accounts SET balance = 0;  -- Идеально для Update

EXCLUSIVE

LOCK TABLE accounts IN EXCLUSIVE MODE;
-- Только SELECT разрешены, никаких изменений

ACCESS EXCLUSIVE (самая сильная)

Полная блокировка таблицы:

LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Или автоматически при ALTER TABLE
ALTER TABLE accounts ADD COLUMN status VARCHAR(50);

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

--          ACCESS | ROW    | ROW      | SHARE    | SHARE  | EXCLUSIVE | ACCESS
--          SHARE  | SHARE  | EXCLUSIVE| SHARE    | EXCE   |           | EXCLUSIVE
-- ACCESS S | ✓      | ✓      | ✓        | ✓        | ✓      | ✓         | ✗
-- ROW S    | ✓      | ✓      | ✗        | ✓        | ✗      | ✗         | ✗
-- ROW E    | ✓      | ✗      | ✗        | ✗        | ✗      | ✗         | ✗
-- SHARE    | ✓      | ✓      | ✗        | ✓        | ✗      | ✗         | ✗
-- SHARE E  | ✓      | ✗      | ✗        | ✗        | ✗      | ✗         | ✗
-- EXCLUSIVE| ✓      | ✗      | ✗        | ✗        | ✗      | ✗         | ✗
-- ACCESS E | ✗      | ✗      | ✗        | ✗        | ✗      | ✗         | ✗

4. Deadlocks (Взаимные блокировки)

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

# Сценарий deadlock'а:
# Транзакция 1: Заблокировать строку A, потом B
# Транзакция 2: Заблокировать строку B, потом A

# Transaction 1
with transaction.atomic():
    account1 = Account.objects.select_for_update().get(id=1)
    # Тут может прийти deadlock если Transaction 2 уже заблокировала account2
    account2 = Account.objects.select_for_update().get(id=2)

# Transaction 2
with transaction.atomic():
    account2 = Account.objects.select_for_update().get(id=2)
    # Тут может прийти deadlock если Transaction 1 уже заблокировала account1
    account1 = Account.objects.select_for_update().get(id=1)

Решение: Всегда блокировать в одном порядке

# Правильно — всегда сначала ID меньшего аккаунта
with transaction.atomic():
    id1, id2 = min(1, 2), max(1, 2)
    account1 = Account.objects.select_for_update().get(id=id1)
    account2 = Account.objects.select_for_update().get(id=id2)
    # Трансфер

5. Advisory Locks (Консультативные блокировки)

Пользовательские блокировки для произвольных ресурсов:

-- Заблокировать ресурс с ID 12345
SELECT pg_advisory_lock(12345);

-- Попробовать неблокирующую блокировку
SELECT pg_advisory_xact_lock(12345);  -- В рамках транзакции

-- Разблокировать
SELECT pg_advisory_unlock(12345);

# Используется для синхронизации между процессами
from django.db import connection

def acquire_lock(resource_id):
    with connection.cursor() as cursor:
        cursor.execute('SELECT pg_advisory_lock(%s)', [resource_id])

def release_lock(resource_id):
    with connection.cursor() as cursor:
        cursor.execute('SELECT pg_advisory_unlock(%s)', [resource_id])

# Кейс: Обработка только одной задачи одновременно
acquire_lock(task_id=123)
try:
    process_task(123)
finally:
    release_lock(task_id=123)

6. Transaction Isolation Levels (Уровни изоляции)

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

Read Uncommitted (READ UNCOMMITTED)

Очень опасно — видны незафиксированные изменения:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Может увидеть dirty reads (незавершённые изменения)

Read Committed (По умолчанию в PostgreSQL)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- (Другая транзакция обновляет на 500)

SELECT balance FROM accounts WHERE id = 1;  -- 500 (видим изменения)
COMMIT;

Repeatable Read

Защита от non-repeatable reads:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- (Другая транзакция обновляет на 500)

SELECT balance FROM accounts WHERE id = 1;  -- 1000 (видим старое значение)
COMMIT;

Serializable

Максимальная защита — как будто транзакции выполняются последовательно:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(balance) FROM accounts;  -- 10000
INSERT INTO audit_log VALUES ('snapshot', 10000);
COMMIT;
-- Гарантирует консистентность

7. View pg_locks (Просмотр текущих блокировок)

-- Какие блокировки сейчас активны?
SELECT 
    pid,
    usename,
    application_name,
    state,
    lock_type,
    relation::regclass
FROM pg_stat_activity
LEFT JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
WHERE pid != pg_backend_pid();

# Найти deadlock'и в логе
from django.db import connection

def find_blocking_queries():
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                blocked_locks.pid,
                blocked_locks.usename,
                blocking_locks.pid as blocking_pid,
                blocking_locks.usename as blocking_user
            FROM pg_catalog.pg_locks blocked_locks
            JOIN pg_catalog.pg_locks blocking_locks
                ON blocking_locks.locktype = blocked_locks.locktype
                AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
                AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
            WHERE NOT blocked_locks.granted AND blocking_locks.granted
        """)
        return cursor.fetchall()

Практические советы

1. Избегайте долгих транзакций

# ❌ Плохо — долгая транзакция
with transaction.atomic():
    users = User.objects.all()
    for user in users:  # Может быть миллион пользователей
        send_email(user)  # Долгая операция

# ✅ Хорошо — транзакция только для БД операций
users = User.objects.all()
for user in users:
    send_email(user)  # Без транзакции

2. Используйте SKIP LOCKED для очередей

# Безопасно обрабатывать задачи в нескольких workers
task = Task.objects.filter(
    status='pending'
).select_for_update(skip_locked=True).first()

if task:
    process_task(task)

3. Всегда блокируйте в одном порядке

# Предотвращает deadlock'и
from django.db.models import Q

ids = sorted([account1_id, account2_id])
accounts = Account.objects.filter(
    id__in=ids
).select_for_update()

Выводы

• FOR UPDATE — эксклюзивная блокировка строк
• FOR SHARE — общая блокировка строк
• SKIP LOCKED — идеально для очередей
• Advisory Locks — для синхронизации процессов
• Serializable — для критичных операций
• Deadlock'и — избегайте, блокируя в одном порядке
• Мониторьте — используйте pg_locks и pg_stat_activity