Jaringan komputer tidak pernah sepenuhnya dapat diandalkan. Ketika client API atau payment gateway mengalami network timeout saat memanggil endpoint mutasi, client umumnya akan mengirim ulang request yang sama (retry). Jika server memproses kedua request tersebut tanpa mekanisme kontrol yang ketat, mutasi ganda seperti pendebitan saldo ganda atau pembuatan pesanan duplikat akan terjadi.

Solusi standar industri untuk masalah ini adalah Idempotency Key. Namun, banyak implementasi di database relasional terjebak pada antipattern check-then-act yang rentan terhadap kondisi balapan (race condition). Artikel ini membahas cara mengimplementasikan pola atomic claim di Oracle Database menggunakan eksepsi DUP_VAL_ON_INDEX (ORA-00001).

Kelemahan Pola 'Check-Then-Act' pada Permintaan Konkuren

Pola implementasi yang paling sering disalahpahami adalah membaca status terlebih dahulu sebelum mengeksekusi aksi:

-- ANTIPATTERN: Rawan race condition
SELECT status FROM idempotency_records WHERE idempotency_key = :key;

-- Jika tidak ditemukan di aplikasi:
INSERT INTO idempotency_records (idempotency_key, status) VALUES (:key, 'PROCESSING');
-- Jalankan mutasi bisnis...

Jika Client A mengirimkan dua request identik secara paralel dalam selisih milidetik (misalnya karena aggressive timeout pada reverse proxy):

  1. Thread 1 menjalankan SELECT: data tidak ditemukan.
  2. Thread 2 menjalankan SELECT sebelum Thread 1 melakukan COMMIT: data juga tidak ditemukan.
  3. Kedua thread melanjutkan ke blok eksekusi logika bisnis secara bersamaan.
  4. Mutasi terjadi dua kali. Database mungkin menolak salah satu INSERT di akhir, namun efek samping pada sistem eksternal sudah terlanjur dieksekusi.

Solusi yang benar membalik alur kerja: klaim hak eksekusi terlebih dahulu menggunakan operasi penulisan atomik, baru jalankan logika bisnis jika klaim berhasil.

Skema Tabel Idempotency dengan Proteksi Integritas

Tabel idempotency memerlukan kolom untuk membedakan konteks, mendeteksi manipulasi payload, menyimpan cache respons, dan menangani masa berlaku (TTL). Skema berikut menggunakan composite primary key antara scope (misal: ID merchant atau endpoint) dan idempotency_key.

CREATE TABLE idempotency_records (
    scope              VARCHAR2(64)  NOT NULL,
    idempotency_key    VARCHAR2(128) NOT NULL,
    request_hash       VARCHAR2(64)  NOT NULL,
    status             VARCHAR2(20)  NOT NULL,
    response_code      NUMBER(3),
    response_body      CLOB,
    created_at         TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    updated_at         TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    expires_at         TIMESTAMP WITH TIME ZONE NOT NULL,
    CONSTRAINT pk_idempotency PRIMARY KEY (scope, idempotency_key),
    CONSTRAINT chk_idemp_status CHECK (status IN ('PROCESSING', 'COMPLETED', 'FAILED'))
);

CREATE INDEX idx_idemp_expires ON idempotency_records(expires_at);

Implementasi Atomic Claim Menggunakan ORA-00001

Strategi atomik mengandalkan unique constraint violation (ORA-00001). PL/SQL menangkap eksepsi ini sebagai DUP_VAL_ON_INDEX. Agar klaim status PROCESSING langsung terlihat oleh thread konkuren tanpa terpengaruh oleh rollback pada transaksi bisnis utama, prosedur klaim dijalankan dalam konteks PRAGMA AUTONOMOUS_TRANSACTION.

CREATE OR REPLACE PACKAGE pkg_idempotency AS
    PROCEDURE claim_or_resolve (
        p_scope          IN  VARCHAR2,
        p_key            IN  VARCHAR2,
        p_request_hash   IN  VARCHAR2,
        p_ttl_minutes    IN  NUMBER,
        p_stale_minutes  IN  NUMBER,
        o_action         OUT VARCHAR2,
        o_response_code  OUT NUMBER,
        o_response_body  OUT CLOB
    );

    PROCEDURE complete_claim (
        p_scope          IN VARCHAR2,
        p_key            IN VARCHAR2,
        p_response_code  IN NUMBER,
        p_response_body  IN CLOB
    );
END pkg_idempotency;
/

CREATE OR REPLACE PACKAGE BODY pkg_idempotency AS

    PROCEDURE claim_or_resolve (
        p_scope          IN  VARCHAR2,
        p_key            IN  VARCHAR2,
        p_request_hash   IN  VARCHAR2,
        p_ttl_minutes    IN  NUMBER,
        p_stale_minutes  IN  NUMBER,
        o_action         OUT VARCHAR2,
        o_response_code  OUT NUMBER,
        o_response_body  OUT CLOB
    )
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
        v_existing_hash  VARCHAR2(64);
        v_status         VARCHAR2(20);
        v_updated_at     TIMESTAMP WITH TIME ZONE;
        v_rows_updated   NUMBER;
    BEGIN
        -- Langkah 1: Coba klaim kunci secara atomik
        BEGIN
            INSERT INTO idempotency_records (
                scope, idempotency_key, request_hash, status,
                created_at, updated_at, expires_at
            ) VALUES (
                p_scope, p_key, p_request_hash, 'PROCESSING',
                SYSTIMESTAMP, SYSTIMESTAMP,
                SYSTIMESTAMP + NUMTODSINTERVAL(p_ttl_minutes, 'MINUTE')
            );
            
            COMMIT;
            o_action := 'PROCEED';
            RETURN;
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                -- Baris sudah ada, proses dialihkan ke pengecekan status
                NULL;
        END;

        -- Langkah 2: Evaluasi baris yang sudah ada
        SELECT request_hash, status, response_code, response_body, updated_at
        INTO v_existing_hash, v_status, o_response_code, o_response_body, v_updated_at
        FROM idempotency_records
        WHERE scope = p_scope AND idempotency_key = p_key;

        -- Validasi integritas payload
        IF v_existing_hash != p_request_hash THEN
            o_action := 'PAYLOAD_MISMATCH';
            RETURN;
        END IF;

        -- Validasi status eksekusi
        IF v_status = 'COMPLETED' THEN
            o_action := 'SERVE_CACHE';
            RETURN;
        ELSIF v_status = 'PROCESSING' THEN
            -- Deteksi apakah worker sebelumnya mengalami crash (stale lock)
            IF v_updated_at < (SYSTIMESTAMP - NUMTODSINTERVAL(p_stale_minutes, 'MINUTE')) THEN
                UPDATE idempotency_records
                SET updated_at = SYSTIMESTAMP
                WHERE scope = p_scope 
                  AND idempotency_key = p_key 
                  AND status = 'PROCESSING'
                  AND updated_at = v_updated_at;

                v_rows_updated := SQL%ROWCOUNT;
                COMMIT;

                IF v_rows_updated = 1 THEN
                    o_action := 'PROCEED'; -- Berhasil mengambil alih lock kedaluwarsa
                ELSE
                    o_action := 'CONFLICT_RETRY'; -- Direbut oleh thread lain
                END IF;
                RETURN;
            ELSE
                o_action := 'IN_FLIGHT'; -- Masih dikerjakan thread lain secara valid
                RETURN;
            END IF;
        ELSE
            -- Status 'FAILED'
            o_action := 'PREVIOUS_FAILED';
            RETURN;
        END IF;
    END claim_or_resolve;

    PROCEDURE complete_claim (
        p_scope          IN VARCHAR2,
        p_key            IN VARCHAR2,
        p_response_code  IN NUMBER,
        p_response_body  IN CLOB
    )
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        UPDATE idempotency_records
        SET status = 'COMPLETED',
            response_code = p_response_code,
            response_body = p_response_body,
            updated_at = SYSTIMESTAMP
        WHERE scope = p_scope AND idempotency_key = p_key;
        
        COMMIT;
    END complete_claim;

END pkg_idempotency;
/

Penanganan Tiga Skenario Edge Case

1. In-Flight Collision (Permintaan Konkuren Aktif)

Ketika dua request tiba hampir bersamaan, thread pertama mendapatkan aksi PROCEED, sementara thread kedua mendapati status masih PROCESSING dan updated_at masih baru. Prosedur mengembalikan flag IN_FLIGHT. Pada layer aplikasi, kembalikan HTTP 409 Conflict atau 425 Too Early disertai header Retry-After: 2 agar client menahan retry sesaat.

2. Stale Lock (Server/Worker Crash)

Jika server aplikasi mengalami out-of-memory crash di tengah eksekusi, status pada database tertinggal di PROCESSING. Tanpa batas waktu pemulihan, request tersebut akan terkunci selamanya. Parameter p_stale_minutes menyelesaikan masalah ini: jika waktu pembaruan sudah melewati ambang batas toleransi (misalnya 5 menit), transaksi lain berhak mengambil alih eksekusi secara aman menggunakan kontrol konkurensi optimistik (WHERE updated_at = v_updated_at).

3. Reused Key dengan Payload Berbeda (Payload Mismatch)

Client yang mengirimkan Idempotency-Key lama untuk transaksi baru yang berbeda (misalnya merubah nominal transfer) dapat merusak konsistensi data. Dengan menyimpan hash SHA-256 dari request body pada kolom request_hash, database dapat mendeteksi manipulasi ini dan mengembalikan status PAYLOAD_MISMATCH. Layer aplikasi wajib merespons dengan HTTP 422 Unprocessable Entity.

Simulasi Konkurensi

Blok anonim berikut mendemonstrasikan perilaku Oracle saat dua sesi memanggil prosedur secara berurutan dengan kunci yang sama:

DECLARE
    v_action VARCHAR2(30);
    v_code   NUMBER;
    v_body   CLOB;
    c_scope  CONSTANT VARCHAR2(32) := 'CHECKOUT_SERVICE';
    c_key    CONSTANT VARCHAR2(64) := 'ord-uuid-9921-8812';
    c_hash   CONSTANT VARCHAR2(64) := 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855';
BEGIN
    -- Simulasi Request Pertama
    pkg_idempotency.claim_or_resolve(
        p_scope         => c_scope,
        p_key           => c_key,
        p_request_hash  => c_hash,
        p_ttl_minutes   => 60,
        p_stale_minutes => 5,
        o_action        => v_action,
        o_response_code => v_code,
        o_response_body => v_body
    );
    
    DBMS_OUTPUT.PUT_LINE('Request 1 Action: ' || v_action); -- Menghasilkan: PROCEED

    -- Selesaikan transaksi pertama
    IF v_action = 'PROCEED' THEN
        -- Simulasikan eksekusi transaksi bisnis...
        pkg_idempotency.complete_claim(
            p_scope         => c_scope,
            p_key           => c_key,
            p_response_code => 201,
            p_response_body => '{"order_id": 4501, "status": "CREATED"}'
        );
    END IF;

    -- Simulasi Retry dari Client (Request Kedua dengan data sama)
    pkg_idempotency.claim_or_resolve(
        p_scope         => c_scope,
        p_key           => c_key,
        p_request_hash  => c_hash,
        p_ttl_minutes   => 60,
        p_stale_minutes => 5,
        o_action        => v_action,
        o_response_code => v_code,
        o_response_body => v_body
    );

    DBMS_OUTPUT.PUT_LINE('Request 2 Action: ' || v_action); -- Menghasilkan: SERVE_CACHE
    DBMS_OUTPUT.PUT_LINE('Cached Code: ' || v_code);
    DBMS_OUTPUT.PUT_LINE('Cached Body: ' || v_body);
END;
/

Trade-offs dan Pertimbangan Operasional

  • Pembersihan Data Lama (Housekeeping): Tabel idempotency akan tumbuh seiring volume transaksi. Terapkan strategi Range Partitioning berdasarkan kolom created_at per hari atau per minggu. Anda dapat melakukan operasi DROP PARTITION secara terjadwal melalui DBMS_SCHEDULER tanpa menimbulkan overhead log penghapusan individual (DELETE).
  • Overhead Transaksi Otonom: Penggunaan PRAGMA AUTONOMOUS_TRANSACTION membutuhkan alokasi konteks transaksi baru di Oracle. Untuk sistem dengan throughput puluhan ribu request per detik, pastikan konfigurasi parameter TRANSACTIONS pada database mencukupi untuk mencegah bottleneck antrean transaksi.
  • Ukuran Respons pada CLOB: Jika payload respons berukuran masif (>1 MB), pertimbangkan untuk hanya menyimpan checksum atau ID entitas pada database, lalu ambil representasi lengkap melalui storage engine atau cache lapisan kedua.