Deadlock pada basis data relasional terjadi ketika dua transaksi konkuren atau lebih saling menunggu pelepasan kunci (lock) yang dipegang transaksi lain, membentuk siklus sirkular tanpa jalan keluar. Kasus ini sering muncul pada operasi multi-baris seperti batch pembaruan inventaris toko online atau mutasi transfer saldo ganda. RDBMS seperti PostgreSQL atau MySQL (InnoDB) akan memutus siklus ini secara paksa dengan membatalkan (abort) salah satu transaksi korban (victim transaction).

Identifikasi Gejala: SQLSTATE 40001 dan Retry Storm

Saat deadlock terjadi, database mengembalikan kode status error standar ANSI SQL. Aplikasi backend akan menangkap pengecualian berikut:

  • PostgreSQL: ERROR: deadlock detected (SQLSTATE 40P01) atau representasi serialisasi SQLSTATE 40001.
  • MySQL/InnoDB: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

Pada dashboard APM (Application Performance Monitoring) seperti Datadog atau New Relic, gejalanya tampak sebagai lonjakan tajam HTTP 500 atau kegagalan worker job. Bahaya sekunder yang sering menyertai deadlock adalah retry storm. Ketika aplikasi langsung mencoba ulang transaksi yang gagal secara instan tanpa penundaan (immediate retry), beban konkurensi di database meningkat drastis. Siklus lock yang sama akan terulang kembali, meningkatkan latensi sistem dan memicu cascade failure.

Root Cause: Siklus Graf Kunci (Wait-For Graph)

Engine database mendeteksi deadlock melalui Wait-For Graph (WFG), yaitu graf berarah yang memetakan transaksi mana yang sedang menunggu transaksi lain. Siklus terjadi akibat inkonsistensi urutan eksekusi.

Perhatikan skenario transfer saldo atau batch checkout inventaris berikut:

  • Transaksi 1: Memproses item [ID: 10, ID: 50]. Transaksi memperoleh exclusive lock pada baris 10, lalu meminta lock pada baris 50.
  • Transaksi 2: Berjalan bersamaan memproses item [ID: 50, ID: 10]. Transaksi memperoleh lock pada baris 50, lalu meminta lock pada baris 10.

Transaksi 1 menunggu Transaksi 2 melepaskan baris 50, sementara Transaksi 2 menunggu Transaksi 1 melepaskan baris 10. Database mendeteksi graf siklik Tx1 -> Tx2 -> Tx1 dan melempar error 40001 ke salah satu transaksi.

Membaca Log Deadlock Database

Pada MySQL/InnoDB, jalankan perintah berikut untuk memeriksa detail siklus terakhir:

SHOW ENGINE INNODB STATUS;

Cari bagian LATEST DETECTED DEADLOCK. Output akan memperlihatkan dua transaksi yang bertikai, baris SQL terakhir yang dieksekusi, serta lock mode (biasanya lock_mode X locks rec but not gap) yang diminta dan ditahan.

Pada PostgreSQL, aktifkan logging deadlock di postgresql.conf:

log_lock_waits = on
deadlock_timeout = 1000ms

Log PostgreSQL akan secara eksplisit mencetak graf dependensi:

LOG: process 12345 detected deadlock while waiting for ShareLock on transaction 67890
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.
Process 67891 waits for ExclusiveLock on tuple (0,5) of relation "inventory"; blocked by process 12345.

Solusi Utama: Standarisasi Urutan Kunci Deterministik

Deadlock akibat transaksi multi-baris dapat dieliminasi tanpa mengorbankan isolation level dengan menerapkan aturan deterministik: selalu akuisisi lock dalam urutan ID yang terurut (misal: ascending).

Jika Transaksi 1 dan Transaksi 2 sama-sama mengunci baris 10 terlebih dahulu sebelum baris 50, Transaksi 2 akan diblokir di awal (menunggu Transaksi 1 menyelesaikan baris 10). Tidak ada siklus yang terbentuk karena dependensi kunci bergerak satu arah.

Contoh Implementasi: Kode Bermasalah vs Kode Perbaikan

Berikut perbandingan kode dalam Python dengan transaksi raw SQL / DB-API. Pola logika ini berlaku sama untuk Node.js, Go, PHP, atau bahasa backend lainnya.

1. Kode Bermasalah (Urutan ID Acak Berdasarkan Input Payload)

def update_inventory_problematic(db_cursor, item_updates: dict[int, int]):
    # item_updates format: {item_id: quantity_deducted}
    # Payload input bisa dalam urutan tidak beraturan: [50, 10] atau [10, 50]
    for item_id, qty in item_updates.items():
        # Kunci baris satu per satu mengikuti urutan dictionary payload
        db_cursor.execute(
            "SELECT id, stock FROM inventory WHERE id = %s FOR UPDATE;",
            (item_id,)
        )
        row = db_cursor.fetchone()
        if row[1] < qty:
            raise ValueError(f"Stok tidak mencukupi untuk item {item_id}")
        
        db_cursor.execute(
            "UPDATE inventory SET stock = stock - %s WHERE id = %s;",
            (qty, item_id)
        )

2. Kode Perbaikan (Urutan Kunci Deterministik)

def update_inventory_fixed(db_cursor, item_updates: dict[int, int]):
    # Standarisasi: Urutkan ID secara deterministik (ascending) sebelum akuisisi lock
    sorted_item_ids = sorted(item_updates.keys())
    
    # 1. Kunci semua baris sekaligus dalam urutan terurut
    # Query SELECT ... FOR UPDATE dengan klausa IN terurut mencegah cycle antar-transaksi
    placeholders = ",".join(["%s"] * len(sorted_item_ids))
    query = f"""
        SELECT id, stock 
        FROM inventory 
        WHERE id IN ({placeholders}) 
        ORDER BY id ASC 
        FOR UPDATE;
    """
    db_cursor.execute(query, tuple(sorted_item_ids))
    rows = {row[0]: row[1] for row in db_cursor.fetchall()}
    
    # 2. Validasi stok setelah seluruh row berhasil dikunci
    for item_id in sorted_item_ids:
        qty = item_updates[item_id]
        if rows[item_id] < qty:
            raise ValueError(f"Stok tidak cukup untuk item {item_id}")
            
    # 3. Jalankan mutasi data
    for item_id in sorted_item_ids:
        qty = item_updates[item_id]
        db_cursor.execute(
            "UPDATE inventory SET stock = stock - %s WHERE id = %s;",
            (qty, item_id)
        )

Mitigasi Sekunder: Safe Retry Pattern dengan Backoff dan Jitter

Meskipun standarisasi urutan kunci mengeliminasi deadlock pada entity tunggal, deadlock antar-tabel (misalnya Table A -> Table B vs Table B -> Table A) masih berpotensi terjadi jika urutan akses modul berbeda. Untuk menjaga reliabilitas, bungkus pemanggilan transaksi dengan fungsi retry yang menggunakan Exponential Backoff dan Full Jitter.

import time
import random
import psycopg2

def execute_with_deadlock_retry(conn, operation_func, max_attempts=5, base_delay=0.05, max_delay=1.0):
    attempt = 0
    while True:
        try:
            with conn:
                with conn.cursor() as cursor:
                    return operation_func(cursor)
        except psycopg2.Error as e:
            # PostgreSQL code 40P01 = deadlock_detected; 40001 = serialization_failure
            if e.pgcode in ("40P01", "40001") and attempt < max_attempts:
                attempt += 1
                conn.rollback()
                
                # Exponential backoff + Full Jitter
                temp_delay = min(max_delay, base_delay * (2 ** attempt))
                sleep_duration = random.uniform(0, temp_delay)
                
                time.sleep(sleep_duration)
            else:
                conn.rollback()
                raise

Verifikasi: Skrip Uji Konkurensi

Gunakan script multi-thread berikut untuk menguji efektivitas standarisasi urutan kunci di database lokal Anda. Script ini mensimulasikan dua worker yang memproses item berkebalikan.

import threading
import psycopg2

DATABASE_URL = "dbname=test_db user=postgres password=secret host=localhost"

def run_worker(items_order):
    conn = psycopg2.connect(DATABASE_URL)
    conn.autocommit = False
    cursor = conn.cursor()
    try:
        for item_id in items_order:
            cursor.execute("SELECT id FROM inventory WHERE id = %s FOR UPDATE;", (item_id,))
            time.sleep(0.01) # Jeda artifisial untuk memicu overlap thread
        conn.commit()
        print(f"Sukses: {items_order}")
    except psycopg2.Error as e:
        conn.rollback()
        print(f"Gagal: {items_order} -> {e.pgcode}: {e.pgerror.strip()}")
    finally:
        conn.close()

# Simulasi tanpa sorting deterministik:
# Worker 1 kunci 1 lalu 2. Worker 2 kunci 2 lalu 1.
# Hasil: Salah satu thread melempar 40P01 (deadlock detected).
t1 = threading.Thread(target=run_worker, args=([1, 2],))
t2 = threading.Thread(target=run_worker, args=([2, 1],))

t1.start(); t2.start()
t1.join(); t2.join()

# Simulasi dengan standarisasi sorting:
# Kedua worker dieksekusi dengan args sorted([1, 2]) dan sorted([2, 1]) -> [1, 2]
# Hasil: Keduanya berhasil secara serial tanpa deadlock.

Ringkasan Praktik Terbaik

  1. Sort Keys Secara Konsisten: Selalu lakukan sorted() pada list primary key/foreign key di layer aplikasi sebelum mengeksekusi operasi DML atau SELECT ... FOR UPDATE.
  2. Perpendek Durasi Transaksi: Jangan lakukan operasi lambat seperti network I/O atau panggilan API pihak ketiga di dalam blok transaksi database.
  3. Cegah Gap Lock Berlebih (MySQL): Pastikan klausa WHERE pada transaksi mengacu langsung ke kolom berindeks unik (Primary Key) untuk menghindari table/gap lock menyeluruh.
  4. Pasang Retry dengan Jitter: Selalu tangkap error SQLSTATE 40001 dan lakukan retry dengan jeda eksponensial acak untuk meredam contention storm.