Дослідження блокувань SQL баз даних: всебічний посібник з оптимістичного та песимістичного підходів

Управління конкурентними процесами є основою сучасних систем баз даних, що дозволяє їм обробляти кілька одночасних операцій, зберігаючи цілісність даних. Два ключові підходи до управління конкурентністю — це оптимістичне блокування (Optimistic Locking) та песимістичне блокування (Pessimistic Locking). У цій статті ми розглянемо унікальні особливості цих методів, їх переваги, недоліки та практичні застосування.

Важливість блокувань у базах даних

У середовищах, де кілька користувачів або процесів отримують доступ до спільних даних, конфлікти неминучі. Механізми блокування допомагають координувати доступ, забезпечуючи виконання операцій послідовно та надійно. Завдяки ретельному контролю доступу ці механізми запобігають корупції даних і зберігають цілісність системи.

Песимістичне блокування: Проактивне попередження конфліктів

Песимістичне блокування передбачає, що конфлікти ймовірні, і приймає запобіжні заходи, негайно блокуючи ресурси, щойно до них звертаються. Це гарантує, що жодна інша транзакція не зможе змінити ресурс, поки блокування не буде знято.

Як це працює

1. Отримання блокування: Коли ресурс доступний, він негайно блокується.
2. Ексклюзивний доступ: Інші транзакції блокуються від доступу до ресурсу, поки блокування не буде знято.
3. Завершення транзакції: Блокування знімається після того, як транзакція буде зафіксована або скасована.

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

Уявіть систему, яка управляє попередньо згенерованими номерами банківських рахунків. Користувачі обирають рахунок для транзакцій, і система гарантує, що лише одна транзакція може змінювати рахунок одночасно. Ось як це можна реалізувати:

BEGIN TRANSACTION;  
-- Вибір доступного рахунку, який не заблокований  
SELECT account_number  
FROM accounts  
WHERE is_locked = 0 AND user_id = :user_id  
FOR UPDATE SKIP LOCKED  
LIMIT 1;  

-- Припускаємо, що :selected_account отримано з запиту  
-- Явне блокування вибраного рахунку  
UPDATE accounts  
SET is_locked = 1  
WHERE account_number = :selected_account;  

-- Виконання транзакції  
UPDATE accounts  
SET balance = balance - 100  
WHERE account_number = :selected_account;  

-- Зняття блокування  
UPDATE accounts  
SET is_locked = 0  
WHERE account_number = :selected_account;  
COMMIT;

Пояснення:

• Ключове слово FOR UPDATE SKIP LOCKED дозволяє пропустити заблоковані рахунки, даючи змогу іншим транзакціям працювати з незаблокованими рахунками.
• Флаг is_locked відслідковує, чи заблоковано рахунок.
• Коли транзакція завершується, блокування знімається через оновлення значення is_locked на 0.

Переваги

• Висока цілісність даних: Запобігає одночасним змінам і забезпечує консистентність.
• Простота: Легко зрозуміти та реалізувати у критичних додатках.

Недоліки

• Знижена продуктивність: Інші транзакції блокуються, що може призвести до затримок.
• Ризик виникнення дедлоків (deadlocks): Кругова очікуваність між транзакціями може спричинити дедлоки.

Примітка: У наведеному вище SQL прикладі з песимістичним блокуванням ми уникли дедлоків, використовуючи FOR UPDATE SKIP LOCKED. Це забезпечує пропуск рядків, які вже заблоковані іншою транзакцією, що дозволяє уникнути нескінченного очікування і запобігає дедлокам.

Ідеальні випадки використання

• Сценарії з високими або чутливими операціями, такі як фінансові системи.
• Додатки, де цілісність даних важливіша за продуктивність.

Оптимістичне блокування: Вирішення конфліктів на льоту

Оптимістичне блокування використовує більш м’який підхід, передбачаючи, що конфлікти рідкісні. Ресурси не блокуються під час операцій, але зміни перевіряються перед фіксацією.

Операційні кроки

1. Читання даних: Транзакція отримує дані та їх версію або мітку часу.
2. Обробка: Зміни вносяться без блокування ресурсу.
   3.
   Валідація: Перед фіксацією система перевіряє, чи змінилася версія або мітка часу ресурсу.
3. Вирішення конфліктів: Якщо конфлікт виявлений, транзакція повторюється з оновленими даними.

Приклад: Оптимістичне блокування для банківських рахунків

Розглянемо реалізацію оптимістичного блокування для системи, що керує попередньо згенерованими номерами банківських рахунків:

BEGIN TRANSACTION;  
-- Вибір рахунку з його версією  
SELECT account_number, version  
FROM accounts  
WHERE is_locked = 0 AND user_id = :user_id  
LIMIT 1;  

-- Припускаємо, що :selected_account та :retrieved_version отримано  
-- Спроба заблокувати вибраний рахунок  
UPDATE accounts  
SET is_locked = 1  
WHERE account_number = :selected_account AND version = :retrieved_version;  

IF ROW_COUNT() = 0 THEN  
 -- Обробка конфлікту: рахунок заблокований іншою транзакцією  
 ROLLBACK;  
 -- Логіка повтору або повідомлення користувача  
ELSE  
 -- Виконання операції  
 UPDATE accounts  
 SET balance = balance - 100, version = version + 1, is_locked = 0  
 WHERE account_number = :selected_account;  
 COMMIT;  
END IF;

Пояснення:

• Поле version допомагає виявляти зміни під час транзакції.
• Виявлення конфлікту відбувається, якщо інша транзакція змінює version до завершення поточної транзакції.

Переваги

• Висока конкуренція: Підтримує паралельні операції, уникнувши блокувань.
• Без дедлоків: Відсутній ризик виникнення дедлоків (deadlocks).

Недоліки

• Перевантаження повторів: Транзакції можуть вимагати повтору при конфліктах, що збільшує затримки.
• Складність реалізації: Керування версіями потребує додаткових зусиль.

Найкращі застосування

• Системи з низьким рівнем конфліктів, такі як інструменти звітності або аналітичні платформи.
• Середовища, де пріоритет надається масштабованості та пропускній здатності.

Порівняння стратегій блокування

| Характеристика | Песимістичне блокування (Pessimistic Locking) | Оптимістичне блокування (Optimistic Locking) |
| ------------------------- | -------------------------------------------- | ------------------------------------------- |
| Рівень конкуренції | Низький через блокування | Високий, підтримує паралельність |
| Обробка конфліктів | Запобіжна | Реактивна |
| Ризик дедлоків | Присутній | Відсутній |
| Вплив на продуктивність| Повільніше через очікування | Швидше з повторними спробами |
| Сценарії використання | Високий рівень конфліктів | Низький рівень конфліктів, висока швидкість|

Вибір правильної стратегії блокування

Вибір між оптимістичним і песимістичним блокуванням залежить від вимог вашої системи:

• Песимістичне блокування ідеальне для критичних операцій, де цілісність даних є непорушною.
• Оптимістичне блокування є кращим у сценаріях, де потрібна висока продуктивність і мінімальна конкуренція.

Висновок

Механізми блокування є ключовими для забезпечення надійності операцій бази даних. Розуміння нюансів оптимістичного та песимістичного блокувань дозволяє приймати обґрунтовані рішення для підвищення надійності та продуктивності вашої системи. Чи то запобігання конфліктам заздалегідь, чи їх розв’язання на льоту, кожна стратегія має своє місце у світі проєктування баз даних. Головне — правильно поєднати стратегію з унікальними вимогами вашого додатку, відкриваючи потенціал для масштабованих та безпечних систем.

Корисні посилання:
* Реалізація блокувань у DBMS
* Різниця між песимістичним та оптимістичним підходами у DBMS

Перекладено з: Exploring SQL Database Locking: A Comprehensive Guide to Optimistic and Pessimistic Approaches