Kontrak Retry API pada SQLite: Menangani SQLITE_BUSY dan Idempotensi

Batasan Single-Writer SQLite dan Akar Masalah SQLITE_BUSY

SQLite menggunakan model konkurensi berbasis file lock. Meskipun implementasi modern mendukung banyak reader simultan, engine ini membatasi operasi penulisan hanya pada satu thread atau proses dalam satu waktu (single-writer). Saat lonjakan request mutasi data terjadi, thread yang mencoba memperoleh lock penulisan akan terhambat jika ada transaksi tulis lain yang sedang aktif.

Jika antrean lock melampaui batas toleransi waktu yang ditentukan, SQLite mengembalikan error SQLITE_BUSY (error code 5). Masalah ini sering memicu kegagalan sistem pada API backend yang tidak dirancang dengan proteksi antrean di layer aplikasi. Dalam arsitektur produksi seperti yang diterapkan pada Lobste.rs, SQLite terbukti stabil menangani jutaan request asalkan transaksi tulis diminimalkan durasinya dan lapisan kontrak HTTP menangani penolakan sementara secara terstruktur.

Konfigurasi Engine: WAL Mode dan Busy Timeout

Secara default, SQLite menggunakan rollback journal yang mengunci seluruh database saat penulisan, memblokir operasi pembacaan (reader). Untuk memisahkan jalur baca dan tulis, aktifkan Write-Ahead Logging (WAL) dan atur timeout penanganan lock.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;

Penjelasan parameter:

journal_mode = WAL: Operasi tulis dicatat ke file -wal terpisah. Reader tidak memblokir writer, dan writer tidak memblokir reader.
busy_timeout = 5000: Menginstruksikan SQLite core untuk melakukan polling internal (tidur sejenak lalu mencoba kembali) hingga 5.000 ms sebelum mengembalikan error SQLITE_BUSY ke aplikasi.
synchronous = NORMAL: Mengurangi frekuensi operasi fsync ke disk pada mode WAL tanpa mengorbankan integritas data saat aplikasi crash (hanya berisiko kehilangan data transaksi terakhir jika OS mengalami kernel panic total).

Pencegahan Deadlock dengan Pola Transaksi BEGIN IMMEDIATE

Secara default, transaksi di SQLite dimulai dengan BEGIN DEFERRED. Lock database baru diubah dari SHARED (read) menjadi RESERVED (write) ketika query mutasi (INSERT/UPDATE/DELETE) pertama kali dieksekusi.

Pola DEFERRED rentan memicu deadlock pada API berkonsentrasi tinggi:

Koneksi A dan Koneksi B sama-sama membuka transaksi BEGIN DEFERRED. Keduanya memegang lock SHARED.
Koneksi A membaca data, lalu bersiap melakukan UPDATE. Koneksi A membutuhkan lock RESERVED.
Koneksi B melakukan hal yang sama secara paralel.
Koneksi A tidak bisa memperoleh lock karena Koneksi B masih memegang lock SHARED, dan Koneksi B tidak bisa melanjutkan karena terhalang Koneksi A.
Kedua koneksi saling menunggu hingga batas timeout habis dan melempar error SQLITE_BUSY.

Solusinya adalah mendeklarasikan transaksi tulis secara eksplisit menggunakan BEGIN IMMEDIATE. Perintah ini langsung mengambil lock RESERVED di awal transaksi sebelum membaca atau menulis data. Jika koneksi lain sedang menulis, transaksi baru langsung antre di level driver atau gagal lebih cepat tanpa mengalami deadlock lock-upgrading.

Perancangan Kontrak HTTP: Status 429, 503, dan Retry-After

Ketika batas busy_timeout tercapai dan query gagal akibat SQLITE_BUSY, server tidak boleh mengembalikan status 500 Internal Server Error. Database tidak rusak; sistem hanya mengalami saturasi konkurensi sementara. Kontrak HTTP harus memberi tahu client agar mencoba kembali nanti.

HTTP 503 Service Unavailable: Digunakan jika kegagalan penulisan disebabkan oleh antrean internal database yang jenuh. Cocok untuk endpoint umum.
HTTP 429 Too Many Requests: Digunakan jika lonjakan request berasal dari satu client atau token identik yang melebihi kapasitas pemrosesan write.
Header Retry-After: Sertakan durasi tunggu dalam detik (contoh: Retry-After: 1 atau Retry-After: 2). Durasi ini memberi waktu bagi writer aktif untuk menyelesaikan transaksi dan checkpointing WAL.

Integrasi Idempotency-Key untuk Menghindari Duplikasi Transaksi

Ketika client menerima status 503 atau 429 dan mengeksekusi retry otomatis, risiko duplicate writes meningkat signifikan (misal: client gagal membaca respon sukses sebelumnya karena jaringan putus sesaat setelah commit DB). Header Idempotency-Key wajib diimplementasikan pada seluruh mutasi penting (POST/PUT).

Skema Tabel Idempotensi

CREATE TABLE IF NOT EXISTS idempotency_keys (
    key TEXT PRIMARY KEY,
    request_path TEXT NOT NULL,
    request_hash TEXT NOT NULL,
    response_code INTEGER NOT NULL,
    response_body TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Alur Eksekusi dalam Transaksi

Terima header Idempotency-Key dari client HTTP.
Mulai transaksi dengan BEGIN IMMEDIATE.
Periksa tabel idempotency_keys: jika key sudah ada dan request hash cocok, segera kembalikan response_code dan response_body yang tersimpan tanpa menjalankan ulang logika bisnis.
Jika key belum ada, jalankan operasi mutasi bisnis.
Simpan respons ke dalam tabel idempotency_keys.
Jalankan COMMIT.

Implementasi Kode: Server API Berbasis Python

Contoh berikut menggunakan Python standard library (sqlite3) dan HTTP server minimal untuk mengilustrasikan penanganan SQLITE_BUSY, transaksi BEGIN IMMEDIATE, dan verifikasi idempotensi.

import hashlib
import json
import sqlite3
from http.server import BaseHTTPRequestHandler, HTTPServer

DB_FILE = "api_production.db"

def init_db():
    conn = sqlite3.connect(DB_FILE, timeout=5.0)
    with conn:
        conn.execute("PRAGMA journal_mode = WAL;")
        conn.execute("PRAGMA busy_timeout = 5000;")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                item TEXT NOT NULL,
                amount INTEGER NOT NULL
            );
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS idempotency_keys (
                key TEXT PRIMARY KEY,
                request_hash TEXT NOT NULL,
                response_code INTEGER NOT NULL,
                response_body TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        """)
    conn.close()

class OrderHandler(BaseHTTPRequestHandler):
    def do_POST(self):
        if self.path != "/orders":
            self.send_response(404)
            self.end_headers()
            return

        idempotency_key = self.headers.get("Idempotency-Key")
        if not idempotency_key:
            self.send_response(400)
            self.end_headers()
            self.wfile.write(b'{"error": "Missing Idempotency-Key header"}')
            return

        content_len = int(self.headers.get("Content-Length", 0))
        body = self.rfile.read(content_len)
        req_hash = hashlib.sha256(body).hexdigest()

        conn = sqlite3.connect(DB_FILE, timeout=2.0, isolation_level=None)
        # ponytail: sqlite connection per-request ceiling ~500 rps; upgrade path: connection pool
        try:
            cursor = conn.cursor()
            cursor.execute("BEGIN IMMEDIATE;")

            cursor.execute(
                "SELECT response_code, response_body, request_hash FROM idempotency_keys WHERE key = ?",
                (idempotency_key,)
            )
            row = cursor.fetchone()

            if row:
                resp_code, resp_body, cached_hash = row
                if cached_hash != req_hash:
                    cursor.execute("ROLLBACK;")
                    self.send_response(422)
                    self.end_headers()
                    self.wfile.write(b'{"error": "Idempotency key reuse with mismatched payload"}')
                    return
                cursor.execute("COMMIT;")
                self.send_response(resp_code)
                self.send_header("Content-Type", "application/json")
                self.end_headers()
                self.wfile.write(resp_body.encode())
                return

            data = json.loads(body.decode())
            cursor.execute(
                "INSERT INTO orders (item, amount) VALUES (?, ?)",
                (data["item"], data["amount"])
            )
            order_id = cursor.lastrowid
            response_data = json.dumps({"order_id": order_id, "status": "created"})
            status_code = 201

            cursor.execute(
                "INSERT INTO idempotency_keys (key, request_hash, response_code, response_body) VALUES (?, ?, ?, ?)",
                (idempotency_key, req_hash, status_code, response_data)
            )
            cursor.execute("COMMIT;")

            self.send_response(status_code)
            self.send_header("Content-Type", "application/json")
            self.end_headers()
            self.wfile.write(response_data.encode())

        except sqlite3.OperationalError as e:
            if "database is locked" in str(e) or "busy" in str(e).lower():
                try:
                    cursor.execute("ROLLBACK;")
                except Exception:
                    pass
                self.send_response(503)
                self.send_header("Retry-After", "2")
                self.send_header("Content-Type", "application/json")
                self.end_headers()
                self.wfile.write(b'{"error": "Database busy, retry after delay"}')
            else:
                self.send_response(500)
                self.end_headers()
        except Exception as e:
            try:
                cursor.execute("ROLLBACK;")
            except Exception:
                pass
            self.send_response(400)
            self.end_headers()
            self.wfile.write(str(e).encode())
        finally:
            conn.close()

Uji Validasi Konkurensi & Mutasi Data

Untuk memvalidasi bahwa transaksi tidak hilang (zero write loss) dan sistem mematuhi idempotensi saat menerima request paralel, gunakan script pengujian berikut:

import concurrent.futures
import json
import urllib.request
import urllib.error

URL = "http://127.0.0.1:8000/orders"

def send_request(order_id, key):
    payload = json.dumps({"item": "SSD NVMe", "amount": order_id}).encode()
    req = urllib.request.Request(
        URL,
        data=payload,
        headers={"Content-Type": "application/json", "Idempotency-Key": key},
        method="POST"
    )
    try:
        with urllib.request.urlopen(req) as response:
            return response.status, response.read().decode()
    except urllib.error.HTTPError as err:
        return err.code, err.headers.get("Retry-After")

# Eksekusi 20 request konkuren dengan Idempotency-Key yang sama
with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
    futures = [executor.submit(send_request, 100, "fixed-order-key-xyz") for _ in range(20)]
    results = [f.result() for f in concurrent.futures.as_completed(futures)]

# Evaluasi status
success = [r for r in results if r[0] == 201]
retries = [r for r in results if r[0] == 503]
print(f"Created: {len(success)}, 503 Retried: {len(retries)}")
assert len(success) == 1, "Hanya 1 transaksi yang boleh berhasil ditulis!"

Trade-off dan Batasan Operasional

Network Filesystem (NFS/CIFS): Jangan pernah menempatkan database SQLite pada shared storage jaringan. Implementasi POSIX lock pada NFS sering cacat, menyebabkan lock race condition yang memicu korupsi database. Gunakan selalu local storage (NVMe/SSD).
Long-Running Reads: Di mode WAL, checkpointing (mentransfer perubahan dari file -wal kembali ke database utama) akan tertunda jika ada query pembacaan yang menggantung terlalu lama. Jaga transaksi read tetap singkat agar ukuran file WAL tidak membengkak tanpa batas.
Pruning Kunci Idempotensi: Tabel idempotency_keys akan terus membesar seiring waktu. Jadwalkan cron job harian atau gunakan SQLite trigger untuk menghapus entri yang berusia lebih dari 24 atau 48 jam sesuai batas SLA retry client.

Quick Search

shopping_cart Keranjang