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

Паттерн проектирования Optimistic Offline Lock

Описание Optimistic Offline Lock

Предотвращает конфликты между конкурирующими бизнес-транзакциями, выявляя их и откатывая транзакцию назад.

Зачастую бизнес-транзакция подразумевает выполнение нескольких системных транзакций. Выходя за пределы одиночной системной транзакции, мы не можем полагаться только на систему управления БД, чтобы быть уверенными в том, что бизнес-транзакция оставит данные в консистентном состоянии. Целостность данных находится под угрозой, каждый раз, когда две бизнес-транзакции начинают работу над одними и теми же данными. Также может случиться ситуация, когда один процесс читает данные в то время, как другой - обновляет.

Паттерн Optimistic Offline Lock решает эту проблему, проверяя завершённость одной транзакции и отсутствие конфликтов с другой. Успешная pre-commit проверка, в известном смысле, получает сигнал блокировки, что можно продолжать работать с изменениями в данных. Так как проверка происходит во время завершения каждой системной транзакции, бизнес-транзакции будут также консистентны.

Тогда как Pessimistic Offline Lock подразумевает, что шанс сессии на конфликт высок и по этому ограничивает системную конкуренцию, Optimistic Offline Lock подразумевает, что шансы на конфликт не велики. Такое предположение не очень подходит для одновременной работы нескольких пользователей над одними данными.

Реализация в популярных СУБД

PostgreSQL

PostgreSQL поддерживает оптимистичную блокировку через версионирование и проверку изменений:

Versioning - использование поля версии
Timestamp - использование временных меток
Checksum - проверка контрольных сумм

-- Создание таблицы с версионированием
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    version INTEGER DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Обновление с проверкой версии
UPDATE users 
SET name = 'New Name', 
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1 AND version = 5;

-- Проверка количества затронутых строк
SELECT ROW_COUNT(); -- Должно быть 1, иначе конфликт

MySQL (InnoDB)

MySQL поддерживает оптимистичную блокировку через механизмы версионирования:

Row Versioning - версионирование строк
Timestamp Comparison - сравнение временных меток
Checksum Validation - валидация контрольных сумм

-- Создание таблицы с версионированием
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    version INT DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Обновление с проверкой версии
UPDATE users 
SET name = 'New Name', 
    version = version + 1
WHERE id = 1 AND version = 5;

-- Проверка через ROW_COUNT()
SELECT ROW_COUNT(); -- 1 = успех, 0 = конфликт

Oracle Database

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

ORA_ROWSCN - системный номер изменения строки
Version Columns - пользовательские поля версий
Flashback Queries - запросы к предыдущим версиям

-- Создание таблицы с версионированием
CREATE TABLE users (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    email VARCHAR2(100),
    version NUMBER DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Обновление с проверкой версии
UPDATE users 
SET name = 'New Name', 
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1 AND version = 5;

-- Проверка через SQL%ROWCOUNT
IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Optimistic lock conflict');
END IF;

SQL Server

SQL Server поддерживает оптимистичную блокировку через различные механизмы:

Rowversion - автоматическое версионирование строк
Timestamp - временные метки (устаревший)
Checksum - контрольные суммы

-- Создание таблицы с версионированием
CREATE TABLE users (
    id INT IDENTITY PRIMARY KEY,
    name NVARCHAR(100),
    email NVARCHAR(100),
    version ROWVERSION,
    updated_at DATETIME2 DEFAULT GETDATE()
);

-- Обновление с проверкой версии
UPDATE users 
SET name = 'New Name',
    updated_at = GETDATE()
WHERE id = 1 AND version = @expected_version;

-- Проверка через @@ROWCOUNT
IF @@ROWCOUNT = 0
    THROW 50001, 'Optimistic lock conflict', 1;

SQLite

SQLite поддерживает оптимистичную блокировку через пользовательские механизмы:

Version Fields - поля версий
Timestamp Fields - поля временных меток
Checksum Fields - поля контрольных сумм

-- Создание таблицы с версионированием
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    version INTEGER DEFAULT 1,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Обновление с проверкой версии
UPDATE users 
SET name = 'New Name', 
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1 AND version = 5;

-- Проверка через changes()
SELECT changes(); -- 1 = успех, 0 = конфликт

Сравнение механизмов

СУБД	Встроенная поддержка	Производительность	Простота реализации
PostgreSQL	Частичная	Высокая	Средняя
MySQL	Частичная	Высокая	Средняя
Oracle	Полная	Очень высокая	Высокая
SQL Server	Полная	Высокая	Высокая
SQLite	Отсутствует	Средняя	Низкая

Паттерны реализации

Version Field Pattern

-- Добавление поля версии
ALTER TABLE users ADD COLUMN version INTEGER DEFAULT 1;

-- Обновление с проверкой версии
UPDATE users 
SET name = ?, version = version + 1
WHERE id = ? AND version = ?;

Timestamp Pattern

-- Добавление поля временной метки
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Обновление с проверкой времени
UPDATE users 
SET name = ?, updated_at = CURRENT_TIMESTAMP
WHERE id = ? AND updated_at = ?;

Checksum Pattern

-- Добавление поля контрольной суммы
ALTER TABLE users ADD COLUMN checksum VARCHAR(32);

-- Обновление с проверкой контрольной суммы
UPDATE users 
SET name = ?, checksum = MD5(CONCAT(name, email, updated_at))
WHERE id = ? AND checksum = ?;

Использована иллюстрация с сайта Мартина Фаулера.

Источник