Worker Queue di Oracle: Atasi Lock Contention via SKIP LOCKED

Anatomi Lock Contention pada Worker Queue di Oracle

Pola antrean berbasis tabel (table-backed queue) sering diimplementasikan pada Oracle Database menggunakan query konvensional SELECT ... FOR UPDATE. Masalah muncul saat beban sistem meningkat dan jumlah proses worker diperbanyak untuk mengonsumsi antrean secara konkuren. Sistem mengalami degradasi performa drastis akibat perebutan data yang sama di baris antrean.

Ketika beberapa worker mengeksekusi query seperti:

SELECT id, payload 
FROM app_job_queue 
WHERE status = 'PENDING' 
FETCH FIRST 1 ROWS ONLY 
FOR UPDATE;

Oracle akan mengidentifikasi kandidat baris pertama yang cocok dengan predikat berdasarkan eksekusi index scan. Worker pertama sukses mendapatkan Exclusive Row-Level Lock (TX Lock) pada baris tersebut. Namun, worker kedua, ketiga, dan seterusnya yang menjalankan query identik pada waktu yang hampir bersamaan akan mencoba mengunci baris yang sama persis.

Oracle Database memblokir sesi worker berikutnya hingga transaksi worker pertama melakukan COMMIT atau ROLLBACK. Kondisi ini memicu wait event enq: TX - row lock contention pada v$session_wait. Akibatnya, alih-alih memproses beban kerja secara paralel, seluruh worker terhenti dalam antrean antarsesi yang terserialisasi, memboroskan koneksi database, dan menaikkan latensi pemrosesan pesan.

Solusi Dequeue Non-Blocking Menggunakan SKIP LOCKED

Untuk menghilangkan serialisasi tanpa bergantung pada koordinator antrean eksternal, Oracle menyediakan klausa SKIP LOCKED. Sintaks ini menginstruksikan query engine Oracle untuk memeriksa baris kandidat: jika suatu baris sedang dikunci oleh transaksi lain, database tidak akan menunggu pembebasan kunci melainkan langsung melewati (skip) baris tersebut dan mencari baris berikutnya yang memenuhi kriteria filter.

SELECT id, payload 
FROM app_job_queue 
WHERE status = 'PENDING' 
ORDER BY created_at ASC
FETCH FIRST 1 ROWS ONLY 
FOR UPDATE SKIP LOCKED;

Dengan mekanisme ini:

Worker 1 mengunci baris ID 101.
Worker 2 mengeksekusi query, mendeteksi bahwa ID 101 terkunci, melompati ID 101, lalu mengunci ID 102 tanpa jeda tunggu (non-blocking).
Wait event enq: TX - row lock contention tereliminasi secara signifikan karena tidak ada sesi yang tertahan menunggu pelepasan lock sesi lain.

Desain Skema Tabel dan Composite Index Optimal

Penggunaan SKIP LOCKED membutuhkan strategi pengindeksan yang presisi. Tanpa indeks yang tepat, Oracle terpaksa melakukan Full Table Scan untuk menyisir baris yang belum terkunci, yang berisiko meningkatkan I/O dan konsumsi CPU.

CREATE TABLE app_job_queue (
    id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload       CLOB NOT NULL,
    status        VARCHAR2(20) DEFAULT 'PENDING' NOT NULL,
    retry_count   NUMBER DEFAULT 0 NOT NULL,
    max_retries   NUMBER DEFAULT 3 NOT NULL,
    locked_at     TIMESTAMP WITH TIME ZONE,
    locked_by     VARCHAR2(64),
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT chk_job_status CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED'))
);

-- Composite Index untuk optimasi dequeue
CREATE INDEX idx_job_queue_fetch ON app_job_queue (status, created_at, id);

Komposisi indeks (status, created_at, id) memungkinkan optimizer langsung menuju blok indeks yang bernilai status = 'PENDING' dan mengambil data terlama (FIFO) secara efisien tanpa membaca baris berstatus COMPLETED atau PROCESSING.

Prosedur Atomik Dequeue dan Pengelolaan Commit Boundary

Menjaga transaksi terbuka selama tugas komputasi atau panggilan HTTP eksternal berjalan merupakan anti-pattern. Hal ini menyebabkan penggunaan segmen undo berkepanjangan dan potensi deadlock sistem. Pendekatan yang dianjurkan adalah memisahkan transaksi klaim (dequeue) dari transaksi eksekusi payload.

CREATE OR REPLACE PROCEDURE dequeue_job (
    p_worker_id   IN  VARCHAR2,
    p_job_id      OUT NUMBER,
    p_payload     OUT CLOB
) AS
BEGIN
    p_job_id := NULL;
    p_payload := NULL;

    -- Langkah 1: Kunci baris dengan transaksi terisolasi
    SELECT id, payload
    INTO p_job_id, p_payload
    FROM app_job_queue
    WHERE status = 'PENDING'
    ORDER BY created_at ASC
    FETCH FIRST 1 ROWS ONLY
    FOR UPDATE SKIP LOCKED;

    -- Langkah 2: Ubah status menjadi PROCESSING dan set metadata worker
    UPDATE app_job_queue
    SET status     = 'PROCESSING',
        locked_at  = SYSTIMESTAMP,
        locked_by  = p_worker_id,
        updated_at = SYSTIMESTAMP
    WHERE id = p_job_id;

    -- Langkah 3: Lepaskan lock baris secepat mungkin
    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_job_id := NULL;
        p_payload := NULL;
        ROLLBACK;
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END dequeue_job;
/

Setelah klaim berhasil di-COMMIT, worker menjalankan pemrosesan bisnis di layer aplikasi. Setelah selesai, worker memperbarui status job menjadi COMPLETED melalui transaksi terpisah.

Mitigasi Masalah Operasional: Crash Worker dan Poison Pill

1. Reclaiming Orphaned Locks (Worker Crash)

Jika proses worker mati mendadak (out-of-memory, kill -9) setelah status berubah menjadi PROCESSING, job tersebut tertinggal tanpa penyelesaian. Masalah ini diatasi menggunakan mekanisme heartbeat timeout yang dieksekusi secara berkala oleh scheduler maintenance:

-- Reset job yang ditinggalkan worker yang crash (misal timeout > 10 menit)
UPDATE app_job_queue
SET status      = 'PENDING',
    locked_at   = NULL,
    locked_by   = NULL,
    retry_count = retry_count + 1,
    updated_at  = SYSTIMESTAMP
WHERE status = 'PROCESSING'
  AND locked_at < SYSTIMESTAMP - INTERVAL '10' MINUTE
  AND retry_count < max_retries;

2. Pencegahan Poison Pill Menggunakan Retry Limit

Poison pill adalah pesan rusak yang menyebabkan worker crash setiap kali diproses. Batasi loop kegagalan tak hingga dengan memeriksa kolom retry_count. Jika ambang batas tercapai, alihkan ke status FAILED (Dead Letter Queue):

UPDATE app_job_queue
SET status     = 'FAILED',
    updated_at = SYSTIMESTAMP
WHERE status = 'PROCESSING'
  AND locked_at < SYSTIMESTAMP - INTERVAL '10' MINUTE
  AND retry_count >= max_retries;

Monitoring Kinerja dan Lock Menggunakan Dynamic Views

Identifikasi apakah transaksi masih mengalami bottleneck menggunakan Dynamic Performance Views (V$ views):

-- Memeriksa sesi yang mengalami row lock contention
SELECT 
    s.sid, 
    s.serial#,
    s.username, 
    s.event, 
    s.seconds_in_wait,
    s.sql_id
FROM v$session s
WHERE s.event = 'enq: TX - row lock contention';

-- Memeriksa objek antrean yang sedang terkunci
SELECT 
    lo.session_id, 
    o.object_name, 
    lo.locked_mode
FROM v$locked_object lo
JOIN dba_objects o ON lo.object_id = o.object_id
WHERE o.object_name = 'APP_JOB_QUEUE';

Panduan Pemilihan: Table-Based Queue vs Oracle Advanced Queuing (AQ)

Sebelum memilih arsitektur, bandingkan karakteristik kebutuhan aplikasi:

Gunakan Table-Backed Queue (SKIP LOCKED) jika:
- Kebutuhan throughput moderat (di bawah ribuan dequeue per detik).
- Memerlukan kesederhanaan schema migration dan debugging langsung menggunakan query SQL standar.
- Tidak memerlukan fitur routing kompleks, publish-subscribe, atau protokol JMS.
Migrasi ke Oracle Advanced Queuing (AQ/TxEventQ) jika:
- Sistem membutuhkan throughput antrean sangat tinggi dengan persistensi terjamin.
- Dibutuhkan fitur native seperti message retention, delayed delivery, automatic retry backoff, dan transaksional publish/subscribe antar-database via propagation.
- Aplikasi terintegrasi dengan ekosistem enterprise via API JMS (Java Message Service) atau Kafka connector.

Quick Search

shopping_cart Keranjang