Anatomi Masalah: Throughput Runtuh di Bawah Hardware Menganggur

Penurunan throughput database saat metrik CPU, I/O disk, dan alokasi memori (RAM) berada di batas normal mengindikasikan masalah serialisasi internal, bukan keterbatasan kapasitas hardware. Beban kerja konkuren yang tinggi sering kali tertahan oleh antrean sumber daya logis, seperti row-level locking, buffer pin latching, atau transaksi berdurasi panjang yang memblokir transaksi lain.

Ketika skalabilitas sistem mendatar (flattening) sementara utilisasi CPU tetap rendah (misalnya hanya 20-30%), penambahan node atau penambahan vCPU/RAM tidak menyelesaikan masalah. Fenomena lock contention ini memaksa thread eksekusi berada pada status wait daripada melakukan pekerjaan komputasi aktif.

Metrik Diagnostik: Membedakan Hardware Limit vs Lock Contention

Sebelum melakukan konfigurasi ulang pada database engine, pisahkan kategori bottleneck menggunakan data telemetri yang valid:

  • Hardware Bottleneck: CPU utilisasi konsisten di atas 85%, I/O Wait tinggi, storage queue depth meningkat, atau buffer pool swap meningkat tajam.
  • Lock Contention: Active connection count mencapai batas pool, latensi per query melonjak secara eksponensial, throughput transaksi (TPS/QPS) stagnan atau turun, sementara metrik utilisasi CPU dan I/O tetap rendah.

Query Diagnostik untuk Menemukan Pemblokir (Blocking Locks)

Identifikasi transaksi yang menahan kunci dan transaksi yang menunggu eksekusi menggunakan view katalog internal database.

1. PostgreSQL: Membedah pg_stat_activity dan pg_locks

Query berikut memetakan transaksi pemblokir (blocker) dan transaksi yang terhambat (blocked) secara hierarkis:

SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid    AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS blocking_statement,
    blocked_activity.wait_event_type,
    blocked_activity.wait_event
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

2. MySQL (InnoDB): Menggunakan sys.innodb_lock_waits

Pada MySQL 5.7+ atau 8.0+, schema sys mempermudah tracing lock chain:

SELECT
    waiting_trx_id,
    waiting_pid,
    waiting_query,
    blocking_trx_id,
    blocking_pid,
    blocking_query,
    wait_age,
    locked_table,
    locked_type
FROM sys.innodb_lock_waits;

Antipattern Umum: Batch Update Konkuren Tanpa Urutan Kunci

Salah satu penyebab paling umum serialisasi antrean dan deadlock adalah batch update yang dijalankan oleh beberapa worker konkuren tanpa urutan kunci yang deterministik.

Sebagai contoh, Transaction A memperbarui row [ID: 10, ID: 20], sementara Transaction B secara bersamaan memperbarui row [ID: 20, ID: 10]. Kedua transaksi akan saling menunggu kunci yang dimiliki pihak lain, memicu deadlock atau lock wait timeout.

-- Buruk: Urutan modifikasi bergantung pada query planner database
UPDATE inventory 
SET stock = stock - 1 
WHERE item_id IN (45, 12, 89);

Solusi dan Implementasi Perbaikan

1. Keyset Ordering (Deterministic Locking)

Pastikan aplikasi backend selalu mengurutkan primary key sebelum mengirim batch mutasi atau mengeksekusi SELECT FOR UPDATE. Pengurutan ini menjamin alokasi lock berlangsung searah, meminimalkan risiko siklus deadlock.

-- Baik: Mengurutkan ID secara eksplisit sebelum mengunci baris
SELECT item_id 
FROM inventory 
WHERE item_id IN (12, 45, 89) 
ORDER BY item_id ASC 
FOR UPDATE;

-- Jalankan pembaruan data secara berurutan sesuai hasil sort
UPDATE inventory SET stock = stock - 1 WHERE item_id = 12;
UPDATE inventory SET stock = stock - 1 WHERE item_id = 45;
UPDATE inventory SET stock = stock - 1 WHERE item_id = 89;

2. Optimasi Batas Isolasi Transaksi (Transaction Isolation Scope)

Menggunakan isolation level default (seperti REPEATABLE READ di MySQL) untuk semua transaksi sering kali memperluas scope penguncian, termasuk terciptanya gap locks atau next-key locks yang menahan jangkauan indeks non-existent.

  • Turunkan Isolation Level jika Memungkinkan: Untuk beban kerja update intensif berbasis primary key tunggal, gunakan READ COMMITTED. Di InnoDB, ini menonaktifkan gap locking untuk sebagian besar skenario, sehingga transaksi lain dapat menyisipkan data pada range yang berdekatan.
  • Persingkat Cakupan Transaksi: Jangan lakukan panggilan API eksternal, serialisasi data JSON berat, atau kalkulasi CPU intensif di dalam blok transaksi. Buka transaksi sesaat sebelum operasi database dimulai, dan lakukan COMMIT segera setelah selesai.
  • Gunakan SKIP LOCKED atau NOWAIT: Untuk sistem antrean berbasis tabel, hindari worker saling menunggu lock:
-- Memilih baris kerja tanpa terblokir antrean worker lain
SELECT job_id, payload 
FROM job_queue 
WHERE status = 'PENDING' 
ORDER BY id ASC 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

Kesimpulan

Mengatasi throughput database yang terhambat membutuhkan analisis pola akses kunci sebelum menambah kapasitas server. Gunakan query diagnostik katalog untuk membuktikan adanya lock queue serialization, terapkan keyset ordering pada batch update, batasi durasi transaksi, dan sesuaikan isolation level untuk mencegah penguncian yang berlebihan.