Какие есть виды блокировок в SQL?

Виды блокировок в SQL

В SQL блокировки (англ. Locking) — это механизмы управления параллельным доступом к данным в многопользовательской системе. Они предотвращают конфликты при одновременных операциях чтения и записи, обеспечивая целостность данных и соблюдение ACID-транзакций. Основные виды блокировок классифицируются по типу операции, уровню блокировки и режиму совместимости.

1. Блокировки по типу операции (совместимости)

• Блокировки совместного доступа (Shared Locks — S)

    *   Используются для операций **чтения** (`SELECT`). Несколько транзакций могут одновременно устанавливать shared lock на одну запись, но при этом блокировка исключает возможность изменения данных другими транзакциями.

```sql
-- Пример в SQL Server (блокировка при SELECT с определенным уровнем изоляции)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Products WHERE Id = 1; -- На строку устанавливается shared lock
COMMIT;
```

• Блокировки исключительного доступа (Exclusive Locks — X)

    *   Используются для операций **модификации** (`INSERT`, `UPDATE`, `DELETE`). Только одна транзакция может установить exclusive lock на ресурс. Любая другая попытка чтения или записи будет блокироваться до завершения транзакции.

```sql
BEGIN TRANSACTION;
UPDATE Products SET Price = 100 WHERE Id = 1; -- На строку устанавливается exclusive lock
-- Другие транзакции не могут читать или изменять эту строку до COMMIT/ROLLBACK
COMMIT;
```

• Блокировки обновления (Update Locks — U)

    *   Специальный тип, используемый для предотвращения **deadlock** в последовательности `SELECT -> UPDATE`. Транзакция сначала получает update lock при планировании изменения, который позже преобразуется в exclusive lock. Update lock совместим с shared lock, но не совместим с другим update или exclusive lock.

```sql
-- SQL Server может использовать update lock автоматически для определенных UPDATE
UPDATE Products WITH (UPDLOCK) SET Price = Price * 1.1 WHERE Category = 'Electronics';
```

2. Блокировки по уровню (granularity) охвата

• Блокировка строки (Row-Level Lock)

    *   Блокирует только одну конкретную строку в таблице. Минимизирует конфликты, но может приводить к **overhead** при большом количестве строк.

• Блокировка страницы (Page-Level Lock)

    *   Блокирует физическую страницу данных (группу строк, обычно 8KB). Используется в некоторых системах (например, старых версий SQL Server) как промежуточный уровень.

• Блокировка таблицы (Table-Level Lock)

    *   Блокирует всю таблицу. Применяется при операциях, затрагивающих большинство строк (`ALTER TABLE`, массовые `UPDATE` без условий). Может серьезно ограничивать параллельность.

• Блокировка базы данных (Database-Level Lock)

    *   Используется для административных операций (например, восстановление, изменение схемы).

3. Режимы и расширенные типы блокировок

• Intent Locks (Intention Locks)

    *   Системные блокировки высшего уровня (таблица), указывающие на планирование блокировки нижнего уровня (строки). Например, **Intent Shared (IS)** сигнализирует, что транзакция планирует установить shared lock на некоторые строки таблицы. Это предотвращает конфликты при попытке другой транзакцией установить блокировку на всю таблицу.

• Schema Locks

    *   Защищают структуру схемы базы данных. **Schema Modification Lock (Sch-M)** используется при изменении схемы таблицы (`ALTER TABLE`), **Schema Stability Lock (Sch-S)** — при компиляции запросов.

• Блокировка диапазона ключей (Key-Range Lock)

    *   Особый тип в SQL Server для уровня изоляции **SERIALIZABLE**. Блокирует не только существующие строки, но и логический диапазон между ними, предотвращая **phantom reads** (фантомное чтение).

```sql
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE Total BETWEEN 100 AND 500;
-- Блокируется диапазон ключей, даже если в него будут пытаться добавить новые строки
COMMIT;
```

Проблемы и управление блокировками

Блокировки могут приводить к:

• Deadlock: циклическая зависимость, где две транзакции взаимно блокируют друг друга. Решается автоматическим обнаружением и откатом одной из транзакций.
• Blocking: нормальная временная блокировка одной транзакцией другой.
• Lock escalation: автоматическое повышение уровня блокировки (например, с множества строк на всю таблицу) для снижения нагрузки на систему управления блокировками.

В разных СУБД реализация и названия могут отличаться:

• MySQL/InnoDB: использует row-level locks с алгоритмом next-key locking (сочетание блокировки строки и диапазона).
• PostgreSQL: использует Multi-Version Concurrency Control (MVCC) и минимизирует блокировки, но exclusive locks для UPDATE/DELETE присутствуют.
• SQL Server: богатая система с intent locks, key-range locks и управляемыми режимами через WITH (LOCK_HINTS).

Вывод: Понимание видов блокировок критически важно для разработчика Backend, чтобы правильно проектировать транзакции, выбирать уровни изоляции и предотвращать проблемы производительности в высоконагруженных приложениях.