Что такое SKIP LOCKED в SQL?

SKIP LOCKED в SQL: оптимистичное блокирование для высоконагруженных систем

SKIP LOCKED — это режим блокирования в SQL, который позволяет транзакции пропускать строки, которые уже заблокированы другими транзакциями, вместо того чтобы ждать. Это критичный инструмент для высоконагруженных систем (например, очереди обработки задач).

Проблема: обычная блокировка

-- Без SKIP LOCKED
BEGIN;
SELECT * FROM tasks WHERE status = 'pending' LIMIT 10 FOR UPDATE;
-- Если одна из строк уже заблокирована другой транзакцией,
-- эта транзакция ЗАВИСНЕТ, ждя освобождения
-- (может быть долго!)
COMMIT;

Проблема в высоконагруженной системе:

Транзакция A: SELECT ... FOR UPDATE на task_1
↓ (берет lock на task_1)

Транзакция B: SELECT ... FOR UPDATE на task_1
↓ (хочет эту же строку, жидет!!)
⏳ БЛОКИРОВКА (может быть 1 сек, 10 сек, 1 мин...)

Результат: очередь застаивается, throughput падает

Решение: SKIP LOCKED

-- С SKIP LOCKED
BEGIN;
SELECT * FROM tasks 
WHERE status = 'pending' 
LIMIT 10 
FOR UPDATE SKIP LOCKED;
-- Если задача заблокирована — просто пропускаем её
-- Берем следующую доступную задачу
COMMIT;

Результат:

Транзакция A: SELECT ... FOR UPDATE SKIP LOCKED
↓ task_1 заблокирована, пропускаем
↓ task_2 свободна, берем её

Транзакция B: SELECT ... FOR UPDATE SKIP LOCKED
↓ task_3 заблокирована, пропускаем
↓ task_4 свободна, берем её

✅ Обе транзакции работают параллельно, никто не ждет!

Реальный пример: очередь обработки задач

// Задача: есть очередь из 10,000 задач
// 100 workers обрабатывают их параллельно
// Каждый worker должен взять следующую доступную задачу

@Repository
public class TaskRepository {
    
    // ❌ БЕЗ SKIP LOCKED — workers ждут друг друга
    @Query("""
        SELECT * FROM tasks 
        WHERE status = 'pending' 
        ORDER BY created_at ASC
        LIMIT 1
        FOR UPDATE
    """)
    public Optional<Task> getNextTask() {
        // Если task_1 заблокирована worker A,
        // worker B зависнет, ждя её
        // = throughput падает в N раз (где N = количество workers)
    }
    
    // ✅ СО SKIP LOCKED — workers независимо берут задачи
    @Query("""
        SELECT * FROM tasks 
        WHERE status = 'pending' 
        ORDER BY priority DESC, created_at ASC
        LIMIT 1
        FOR UPDATE SKIP LOCKED
    """)
    public Optional<Task> getNextTaskOptimized() {
        // Если task_1 заблокирована worker A,
        // worker B просто возьмет task_2, task_3 и т.д.
        // = все workers работают параллельно на полной скорости!
    }
}

@Service
public class TaskProcessingService {
    private final TaskRepository taskRepository;
    
    @Transactional
    public void processNextTask() {
        Optional<Task> task = taskRepository.getNextTaskOptimized();
        
        if (task.isPresent()) {
            // Обработка задачи
            Task t = task.get();
            System.out.println("Processing task: " + t.getId());
            
            // Долгая операция (DB query, API call, etc)
            Thread.sleep(1000);
            
            // Отмечаем как завершенную
            t.setStatus("completed");
            taskRepository.save(t);
        } else {
            System.out.println("No tasks available");
        }
    }
}

Пример: система распределенных заказов

-- Таблица: заказы ждут распределения на курьеров
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT,
    status VARCHAR(20),  -- pending, assigned, completed
    created_at TIMESTAMP
);

-- Без SKIP LOCKED: проблема
BEGIN;
-- Курьер 1 берет заказ 1-10
SELECT * FROM orders 
WHERE status = 'pending'
LIMIT 10
FOR UPDATE;  -- заказы заблокированы

-- Курьер 2 хочет взять заказы
SELECT * FROM orders 
WHERE status = 'pending'
LIMIT 10
FOR UPDATE;  -- зависает! ждет, пока курьер 1 закончит
COMMIT;

-- Результат: только курьер 1 работает, остальные 99 ждут
-- = throughput: 1 курьер из 100

-- ✅ СО SKIP LOCKED: параллельная обработка
BEGIN;
SELECT * FROM orders 
WHERE status = 'pending'
LIMIT 10
FOR UPDATE SKIP LOCKED;  -- пропускает заблокированные
COMMIT;

-- Результат: все 100 курьеров работают независимо
-- = throughput: 100x лучше!

Пример: дистрибьютед очередь (Redis-style)

@Service
public class MessageQueueService {
    private final jdbcTemplate: JdbcTemplate;
    
    @Transactional
    public Message consumeMessage() {
        // Вариант 1: без SKIP LOCKED
        // Если message_1 обрабатывается consumer A,
        // consumer B зависнет, ждя message_1
        // = только 1 consumer работает из 100
        
        // Вариант 2: со SKIP LOCKED (правильно!)
        List<Message> messages = jdbcTemplate.query(
            """
            SELECT * FROM messages 
            WHERE status = 'pending'
            ORDER BY created_at ASC
            LIMIT 1
            FOR UPDATE SKIP LOCKED
            """,
            new MessageRowMapper()
        );
        
        if (messages.isEmpty()) {
            return null;  // нет доступных сообщений
        }
        
        Message msg = messages.get(0);
        
        // Обновляем статус
        jdbcTemplate.update(
            "UPDATE messages SET status = 'processing' WHERE id = ?",
            msg.getId()
        );
        
        return msg;
    }
}

SKIP LOCKED vs FOR UPDATE

┌─────────────────────────────────────────────────────────┐
│                                                         │
│            FOR UPDATE (обычное)                         │
│                                                         │
│    T1: SELECT * WHERE ... FOR UPDATE                   │
│    ↓ Берет EXCLUSIVE lock на строку                    │
│                                                         │
│    T2: SELECT * WHERE ... FOR UPDATE                   │
│    ↓ Хочет тот же lock                                │
│    ↓ ЖДЕТ (может быть долго)                          │
│    ✅ Гарантирует, что ВСЕ нужные строки обработаны    │
│    ❌ Но может быть медленно (много ожиданий)         │
│                                                         │
└─────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────┐
│                                                         │
│       FOR UPDATE SKIP LOCKED                            │
│                                                         │
│    T1: SELECT * WHERE ... FOR UPDATE SKIP LOCKED       │
│    ↓ Берет lock на строку 1                            │
│                                                         │
│    T2: SELECT * WHERE ... FOR UPDATE SKIP LOCKED       │
│    ↓ Строка 1 занята, пропускаем                      │
│    ↓ Берем строку 2 (доступна)                        │
│    ✅ Быстро, параллельно                              │
│    ⚠️ Может обработать не ВСЕ строки (некоторые       │
│       обработает другой процесс)                       │
│                                                         │
└─────────────────────────────────────────────────────────┘

Важно: SELECT FOR UPDATE vs SELECT FOR UPDATE SKIP LOCKED

// Сценарий: есть 100 задач, 10 workers

// Без SKIP LOCKED
// Worker 1: SELECT ... FOR UPDATE
// -> берет lock на задачи 1-10
// Worker 2: SELECT ... FOR UPDATE
// -> видит, что 1-10 заблокированы
// -> ЖДЕТ (может быть 1 мин, пока worker 1 не завершит)
// Worker 3-10: также ждут
// = кол-во параллельных worker'ов = 1 (остальные idle)

// СО SKIP LOCKED
// Worker 1: SELECT ... FOR UPDATE SKIP LOCKED
// -> берет lock на задачи 1-10
// Worker 2: SELECT ... FOR UPDATE SKIP LOCKED
// -> видит, что 1-10 заблокированы, пропускает
// -> берет 11-20
// Worker 3: берет 21-30
// ... и так далее
// = все 10 workers работают параллельно!
// = throughput в 10x выше!

Примеры в разных СУБД

-- PostgreSQL (SKIP LOCKED)
SELECT * FROM tasks 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- MySQL 8.0+ (SKIP LOCKED)
SELECT * FROM tasks 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- Oracle (SKIP LOCKED)
SELECT * FROM tasks 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
FETCH FIRST 10 ROWS ONLY;

-- SQL Server (нет SKIP LOCKED, но есть альтернатива)
-- Используй READPAST вместо SKIP LOCKED (аналогично)
SELECT TOP 10 * FROM tasks 
WHERE status = 'pending'
WITH (UPDLOCK, READPAST);

Когда использовать SKIP LOCKED

✅ Используй, если:

• Высоконагруженная система (много параллельных worker'ов)
• Очередь обработки задач
• Нужна максимальная throughput
• Не критично, что отдельные элементы обработает другой worker

❌ Не используй, если:

• Нужна гарантия обработки ВСЕХ строк в одной транзакции
• Мало параллельных процессов (нет конкуренции)
• Логика требует ACID консистентность (все или ничего)

Best Practices

@Service
public class TaskQueue {
    private final jdbcTemplate: JdbcTemplate;
    
    @Transactional
    public void processTask() {
        // 1. Используй SKIP LOCKED для взятия задачи
        Task task = getNextTaskWithSkipLocked();
        
        // 2. Обработай задачу
        processTask(task);
        
        // 3. Обнови статус
        task.setStatus("completed");
        save(task);
        
        // Transaction commit -> lock освобождается
        // Следующий worker может взять следующую задачу
    }
    
    private Task getNextTaskWithSkipLocked() {
        return jdbcTemplate.queryForObject(
            """
            SELECT * FROM tasks
            WHERE status = 'pending'
            ORDER BY priority DESC, created_at ASC
            LIMIT 1
            FOR UPDATE SKIP LOCKED
            """,
            new TaskRowMapper()
        );
    }
}

Вывод

SKIP LOCKED — это essential паттерн для высоконагруженных систем:

1. Проблема: обычный FOR UPDATE блокирует workers, ждающих доступа
2. Решение: SKIP LOCKED пропускает заблокированные строки
3. Результат: все workers работают параллельно, throughput в 10-100x выше
4. Применение: очереди задач, consumer-producer системы, распределенная обработка

Это критично для production систем, обрабатывающих миллионы событий в секунду.