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

В SQL существуют несколько основных видов блокировок, которые обеспечивают целостность данных в многопользовательской среде.

1. Пессимистическая блокировка (Pessimistic Locking) Блокирует строку или таблицу на время выполнения транзакции, предотвращая доступ других транзакций. Это гарантирует, что данные не изменятся до завершения операции.

-- Блокировка строки для обновления
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- Другие транзакции, пытающиеся получить доступ к этой строке, будут ждать

2. Оптимистическая блокировка (Optimistic Locking) Не блокирует данные на уровне БД, а использует проверку версии (timestamp или счетчик) перед обновлением. Если данные были изменены другой транзакцией, текущее обновление отклоняется.

-- Добавляем поле version в таблицу
ALTER TABLE products ADD version INT DEFAULT 1;

-- При обновлении проверяем версию
UPDATE products 
SET name = 'New Product', price = 99.99, version = version + 1
WHERE id = 456 AND version = 3;
-- Если affected_rows = 0, значит данные уже изменились

3. Блокировки уровня строки и таблицы

• Блокировка строки (Row-level lock) - блокирует только конкретные строки
• Блокировка таблицы (Table-level lock) - блокирует всю таблицу

  -- Блокировка таблицы (в MySQL)
  LOCK TABLES orders WRITE;
  -- Выполняем операции...
  UNLOCK TABLES;

4. Разделяемые (Shared) и эксклюзивные (Exclusive) блокировки

• Разделяемая блокировка (S) - позволяет читать данные, но не изменять
• Эксклюзивная блокировка (X) - позволяет и читать, и изменять данные

5. Взаимоблокировка (Deadlock) Ситуация, когда две или более транзакции взаимно блокируют друг друга. Например:

• Транзакция A блокирует строку 1 и пытается заблокировать строку 2
• Транзакция B блокирует строку 2 и пытается заблокировать строку 1

Практические рекомендации:

• Используйте короткие транзакции
• Применяйте пессимистические блокировки только при необходимости
• Для операций чтения используйте LOCK IN SHARE MODE вместо FOR UPDATE
• Настройте таймауты для предотвращения длительных блокировок
• Используйте правильный уровень изоляции транзакций