Locking #

Locking adalah mekanisme yang membuat database bisa melayani banyak transaksi secara bersamaan tanpa membiarkan mereka saling merusak data satu sama lain. Tanpa locking, dua transaksi yang membaca dan memodifikasi data yang sama secara paralel bisa menghasilkan state yang tidak valid — data yang tertulis setengah-setengah, angka yang salah, atau perubahan yang hilang begitu saja.

Tapi locking adalah pedang bermata dua. Digunakan dengan tepat, ia menjadi penjaga konsistensi yang tidak terlihat — bekerja di latar belakang, memastikan setiap transaksi mendapat gambaran data yang konsisten. Digunakan dengan sembarangan, ia menjadi sumber bottleneck terbesar di sistem: query yang seharusnya selesai dalam milidetik menunggu detik lamanya, connection pool penuh oleh proses yang mengantre, throughput turun padahal CPU masih idle, dan deadlock yang mem-rollback transaksi tanpa peringatan yang jelas.

Perbedaan antara keduanya bukan terletak pada apakah menggunakan locking atau tidak — melainkan pada seberapa dalam kamu memahami cara kerjanya, kapan mengaktifkannya, dan bagaimana meminimalkan dampaknya terhadap konkurensi sistem.

Tiga Anomali yang Ingin Dicegah Locking #

Sebelum membahas jenis-jenis lock, penting untuk memahami masalah apa yang ingin diselesaikannya. Ada tiga anomali klasik yang bisa terjadi ketika beberapa transaksi mengakses data yang sama tanpa koordinasi.

Dirty Read #

Dirty read terjadi ketika satu transaksi membaca data yang sedang dimodifikasi oleh transaksi lain yang belum commit. Jika transaksi yang belum commit itu akhirnya di-rollback, maka data yang dibaca tadi adalah data yang tidak pernah benar-benar ada.

Skenario dirty read:

  Waktu   Transaksi A                         Transaksi B
  ──────  ───────────────────────────────────  ───────────────────────────────────
  T=0ms   BEGIN
  T=1ms   UPDATE orders SET status='shipped'
          WHERE id = 10
          -- belum COMMIT
  T=2ms                                        SELECT status FROM orders
                                               WHERE id = 10
                                               -- membaca 'shipped' ← dirty read
  T=3ms   ROLLBACK  ← batal karena error
  T=4ms                                        → data yang dibaca tidak pernah ada

  Transaksi B mengambil keputusan berdasarkan data yang tidak valid.

Non-Repeatable Read #

Non-repeatable read terjadi ketika transaksi membaca baris yang sama dua kali dan mendapat nilai berbeda — karena di antara dua pembacaan itu, transaksi lain sudah mengubah dan meng-commit data tersebut.

Skenario non-repeatable read:

  Waktu   Transaksi A                         Transaksi B
  ──────  ───────────────────────────────────  ───────────────────────────────────
  T=0ms   BEGIN
  T=1ms   SELECT balance FROM wallets
          WHERE id = 1
          -- hasil: 100.000
  T=2ms                                        UPDATE wallets SET balance = 50000
                                               WHERE id = 1
                                               COMMIT ✓
  T=3ms   SELECT balance FROM wallets
          WHERE id = 1
          -- hasil: 50.000 ← berbeda dari T=1ms!

  Dalam satu transaksi yang sama, data yang sama memberikan hasil berbeda.
  Laporan atau kalkulasi yang bergantung pada konsistensi ini menjadi tidak bisa dipercaya.

Phantom Read #

Phantom read terjadi ketika transaksi menjalankan query dengan kondisi tertentu dua kali, dan di antara keduanya ada transaksi lain yang menyisipkan baris baru yang memenuhi kondisi tersebut. Baris baru itu “muncul” seperti hantu di pembacaan kedua.

Skenario phantom read:

  Waktu   Transaksi A                         Transaksi B
  ──────  ───────────────────────────────────  ───────────────────────────────────
  T=0ms   BEGIN
  T=1ms   SELECT COUNT(*) FROM orders
          WHERE status = 'pending'
          -- hasil: 5
  T=2ms                                        INSERT INTO orders (status)
                                               VALUES ('pending')
                                               COMMIT ✓
  T=3ms   SELECT COUNT(*) FROM orders
          WHERE status = 'pending'
          -- hasil: 6 ← baris baru "muncul"

  Transaksi A tidak mengubah apapun, tapi hitungannya berubah.
  Ini bermasalah untuk operasi seperti "proses semua order pending".

Ketiga anomali ini dicegah oleh kombinasi locking dan isolation level — dan memahami ketiganya adalah kunci untuk memilih strategi locking yang tepat.

Tujuh Jenis Lock yang Perlu Dipahami #

Shared Lock (S Lock) #

Shared lock digunakan ketika transaksi ingin membaca data dan memastikan data itu tidak berubah selama ia membacanya. Banyak transaksi boleh memegang shared lock pada baris yang sama secara bersamaan — mereka semua hanya membaca, tidak ada yang menulis, jadi tidak ada konflik.

Konflik terjadi ketika ada transaksi yang ingin menulis: transaksi yang ingin menulis tidak bisa mendapat exclusive lock selama ada shared lock yang masih aktif.

-- Shared lock: transaksi membaca dan tidak ingin data berubah saat dibaca
SELECT * FROM products WHERE id = 42 LOCK IN SHARE MODE;

-- Transaksi lain BISA membaca baris yang sama (shared lock tidak konflik)
-- Transaksi lain TIDAK BISA menulis baris yang sama sampai shared lock dilepas

-- Kapan digunakan:
-- Saat kamu perlu membaca data untuk validasi, tapi khawatir data berubah
-- sebelum kamu selesai memproses. Lebih ringan dari exclusive lock.

Exclusive Lock (X Lock) #

Exclusive lock digunakan ketika transaksi akan menulis data. Hanya satu transaksi yang boleh memegang exclusive lock pada satu baris — tidak ada transaksi lain yang boleh membaca (dengan shared lock) atau menulis (dengan exclusive lock) baris tersebut sampai lock dilepas.

-- Exclusive lock: transaksi akan menulis, tidak ada yang boleh membaca atau menulis
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;

-- Transaksi lain TIDAK BISA membaca dengan LOCK IN SHARE MODE
-- Transaksi lain TIDAK BISA menulis
-- Transaksi lain BISA membaca tanpa lock (non-locking read) — ini tergantung isolation level

-- Kapan digunakan:
-- Saat kamu membaca data yang akan segera kamu modifikasi
-- dan tidak ingin ada yang mengubahnya di antara read dan write kamu.

Tabel kompatibilitas lock:

              Lock yang Diminta oleh Transaksi Baru
              ─────────────────────────────────────
  Lock yang   │  Shared (S)  │  Exclusive (X)
  Sudah Ada   │              │
  ────────────┼──────────────┼───────────────
  Shared (S)  │  Compatible  │  Conflict
              │  (boleh)     │  (menunggu)
  ────────────┼──────────────┼───────────────
  Exclusive(X)│  Conflict    │  Conflict
              │  (menunggu)  │  (menunggu)

Intent Lock #

Intent lock adalah mekanisme internal yang digunakan database engine (seperti InnoDB) untuk mengoordinasikan lock di berbagai level — row level dan table level. Sebelum mengunci satu baris, database secara otomatis memasang intent lock di level tabel untuk menandakan niatnya.

Ini memungkinkan database memeriksa kompatibilitas lock secara efisien tanpa harus memeriksa setiap baris satu per satu.

Intent lock — dipasang otomatis oleh database, tidak perlu ditulis manual:

  Intent Shared (IS)   → Saya akan memasang shared lock di beberapa baris tabel ini
  Intent Exclusive (IX) → Saya akan memasang exclusive lock di beberapa baris tabel ini

  Mengapa penting untuk dipahami:
  Ketika kamu melihat deadlock log dan ada "TABLE LOCK table... trx id... lock mode IX",
  itu adalah intent exclusive lock — tanda bahwa transaksi ini akan menulis ke tabel ini.
  Memahami ini membantu membaca dan mendiagnosis deadlock dengan lebih akurat.

Row-Level Lock #

Row-level lock mengunci baris spesifik, bukan seluruh tabel. Ini adalah jenis lock yang digunakan oleh InnoDB secara default dan merupakan alasan mengapa InnoDB jauh lebih baik untuk workload concurrent dibanding MyISAM yang menggunakan table-level lock.

Dengan row-level lock, dua transaksi bisa memodifikasi tabel yang sama secara bersamaan selama mereka menyentuh baris yang berbeda — tidak ada konflik.

-- Row-level lock: hanya baris dengan user_id = 1 yang dikunci
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;

-- Transaksi lain masih bisa mengakses baris user_id = 2, 3, dst.
-- Hanya baris user_id = 1 yang diblokir untuk write.

-- PENTING: row-level lock hanya terjadi jika query menggunakan index.
-- Jika query tidak menggunakan index, InnoDB akan melakukan table lock.
-- Ini adalah jebakan yang sangat umum dan sering tidak disadari.

Table-Level Lock #

Table-level lock mengunci seluruh tabel. Tidak ada transaksi lain yang bisa mengakses tabel tersebut (untuk operasi yang konflik) sampai lock dilepas. Konkurensi turun drastis — hanya satu transaksi yang bisa bekerja pada tabel pada satu waktu.

-- Table lock eksplisit — hampir tidak pernah harus digunakan di aplikasi modern
LOCK TABLES orders WRITE;
-- Semua operasi ke tabel orders dari transaksi lain diblokir

-- Biasanya terjadi secara implisit ketika:
-- 1. Query tidak menggunakan index dan InnoDB melakukan full table scan dengan locking
-- 2. ALTER TABLE (tergantung jenis operasi dan versi MySQL)
-- 3. LOAD DATA INFILE

UNLOCK TABLES;

Jika kamu melihat LOCK TABLES ... WRITE di codebase aplikasi produksi, itu hampir selalu merupakan tanda masalah desain. Table lock sangat jarang diperlukan di aplikasi modern dan hampir selalu bisa diganti dengan row-level lock yang lebih presisi.

Gap Lock #

Gap lock adalah lock yang mengunci ruang kosong di antara nilai index, bukan baris yang sudah ada. Tujuannya adalah mencegah transaksi lain menyisipkan baris baru di rentang yang sedang dikunci — untuk menghindari phantom read.

-- Query ini akan memasang gap lock pada rentang id > 10 AND id < 20
-- Tidak ada baris dengan id 11-19, tapi ruang itu dikunci
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;

-- Transaksi lain yang mencoba INSERT dengan id = 15 akan diblokir
-- meskipun baris itu belum ada sebelumnya

Visualisasi gap lock:

  Index:  ... [8] [9] [10] (gap) [20] [21] ...
                            ─────
                        gap lock di sini
                   mencegah INSERT di antara 10 dan 20

  Baris yang ada: id=10, id=20 (keduanya juga dikunci oleh row lock)
  Gap yang dikunci: semua nilai antara 10 dan 20 (exclusive)

Gap lock adalah sumber kebingungan yang sangat umum karena ia mengunci data yang “tidak terlihat”. Transaksi bisa diblokir karena mencoba menyisipkan baris di rentang yang dikunci — bahkan jika baris itu belum ada sebelumnya.

Gap lock hanya aktif di isolation level REPEATABLE READ ke atas. Di READ COMMITTED, gap lock tidak digunakan — yang berarti phantom read bisa terjadi, tapi INSERT tidak akan diblokir oleh operasi range di transaksi lain.

Next-Key Lock #

Next-key lock adalah kombinasi dari row lock dan gap lock — ia mengunci baris yang ada sekaligus ruang setelah baris itu. InnoDB menggunakan next-key lock secara default di isolation level REPEATABLE READ untuk mencegah phantom read.

Visualisasi next-key lock untuk query: SELECT * FROM orders WHERE id <= 20 FOR UPDATE

  Index:  ... [10] (gap) [15] (gap) [20] (gap setelah 20) ...
               ────────────────────────────────────────────
               semua ini dikunci: row lock + gap lock digabungkan

  Baris id=10: row lock (tidak ada yang bisa update/delete)
  Gap 10-15:   gap lock (tidak ada yang bisa INSERT di sini)
  Baris id=15: row lock
  Gap 15-20:   gap lock
  Baris id=20: row lock
  Gap setelah 20: gap lock (mencegah INSERT di atas 20 yang masih masuk range)

Locking dan Isolation Level #

Isolation level menentukan seberapa agresif database memasang lock dan anomali mana yang dicegah. Memilih isolation level yang terlalu tinggi mengorbankan konkurensi — memilih yang terlalu rendah membuka risiko anomali data.

Hubungan isolation level, lock, dan anomali:

┌──────────────────────┬──────────────────────────────┬────────────┬──────────────┬─────────────┐
│ Isolation Level      │ Perilaku Locking              │ Dirty Read │ Non-Rep Read │ Phantom Read│
├──────────────────────┼──────────────────────────────┼────────────┼──────────────┼─────────────┤
│ READ UNCOMMITTED     │ Hampir tidak ada lock         │ Bisa       │ Bisa         │ Bisa        │
│ READ COMMITTED       │ Lock dilepas setelah read     │ Dicegah    │ Bisa         │ Bisa        │
│ REPEATABLE READ      │ Row lock + gap lock           │ Dicegah    │ Dicegah      │ Dicegah*    │
│ SERIALIZABLE         │ Semua read jadi locking read  │ Dicegah    │ Dicegah      │ Dicegah     │
└──────────────────────┴──────────────────────────────┴────────────┴──────────────┴─────────────┘

* InnoDB REPEATABLE READ mencegah phantom read melalui next-key lock,
  meski standar SQL menyatakan phantom read masih mungkin di level ini.

MySQL InnoDB menggunakan REPEATABLE READ sebagai default — dan ini adalah pilihan yang tepat untuk sebagian besar aplikasi. SERIALIZABLE memberikan jaminan terkuat tapi throughput-nya bisa turun signifikan karena setiap SELECT menjadi locking read.

-- Mengecek isolation level yang aktif
SELECT @@transaction_isolation;

-- Mengubah isolation level untuk session ini
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Mengubah secara global (butuh restart atau reconnect untuk efek penuh)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Empat Dampak Buruk Locking yang Tidak Tepat #

Long Transaction — Lock yang Terlalu Lama Dipegang #

Setiap lock dipegang selama transaction masih terbuka. Semakin lama transaction, semakin lama lock dipegang, semakin lama proses lain menunggu. Di sistem dengan high concurrency, satu transaction yang terbuka 5 detik bisa membuat puluhan request lain mengantre.

Dampak long transaction:

  T=0s    Transaksi A membuka lock pada rows X, Y, Z
  T=0s    Request 1 menunggu lock X
  T=1s    Request 2 menunggu lock X
  T=2s    Request 3 menunggu lock X
  ...
  T=5s    Transaksi A selesai, lock dilepas
  T=5s    Request 1-N akhirnya bisa berjalan

  Jika timeout connection lebih pendek dari 5s, request-request itu sudah error
  sebelum sempat mendapat lock.

Penyebab paling umum dari long transaction yang tidak disengaja: melakukan HTTP call ke service eksternal, operasi file, atau komputasi berat di dalam transaction yang sedang memegang lock.

-- ANTI-PATTERN: operasi lambat di dalam transaction
START TRANSACTION;

SELECT * FROM orders WHERE id = 10 FOR UPDATE;

-- [HTTP call ke payment gateway: 1-3 detik]
-- Lock dipegang selama HTTP call berlangsung
-- Semua proses lain yang butuh order id=10 menunggu

UPDATE orders SET status = 'paid' WHERE id = 10;
COMMIT;

-- BENAR: operasi eksternal di luar transaction
payment_result = payment_gateway.charge(amount)  -- di luar transaction

START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
UPDATE orders SET status = payment_result.status WHERE id = 10;
COMMIT;
-- Transaction hanya memegang lock selama operasi database — milidetik, bukan detik

Lock Contention — Berebut Lock yang Sama #

Lock contention terjadi ketika banyak transaksi bersaing untuk lock pada baris atau tabel yang sama. Semakin tinggi contention, semakin banyak waktu yang dihabiskan untuk menunggu — bukan untuk bekerja.

Tanda-tanda lock contention:

  ✗ CPU server rendah tapi throughput juga rendah
  ✗ Response time naik padahal tidak ada query baru yang berat
  ✗ SHOW PROCESSLIST menunjukkan banyak query dengan status "Waiting for lock"
  ✗ Slow query log dipenuhi query yang seharusnya cepat

-- Mendeteksi lock contention di MySQL InnoDB
SHOW ENGINE INNODB STATUS\G

-- Perhatikan bagian TRANSACTIONS:
-- "lock wait X lock struct(s)" → ada transaksi yang menunggu lock
-- "heap size", "row lock(s)" → detail lock yang dipegang
-- "WAITING FOR THIS LOCK TO BE GRANTED" → lock yang sedang dinantikan

Deadlock — Saling Menunggu Tanpa Ujung #

Deadlock adalah kondisi di mana dua atau lebih transaksi masing-masing memegang lock yang dibutuhkan oleh yang lain, dan tidak ada yang mau melepaskan. Database mendeteksi ini dan men-rollback salah satu transaksi sebagai korban.

Anatomi deadlock:

  Transaksi A memegang lock pada: baris Wallets(id=1)
  Transaksi A menunggu lock pada: baris Orders(id=10)

  Transaksi B memegang lock pada: baris Orders(id=10)
  Transaksi B menunggu lock pada: baris Wallets(id=1)

  → Tidak ada yang bisa maju → deadlock
  → Database rollback salah satu sebagai korban
  → Aplikasi harus menangani error dan retry

Kode error MySQL: 1213 (ER_LOCK_DEADLOCK)
"Deadlock found when trying to get lock; try restarting transaction"

-- Melihat informasi deadlock terakhir di InnoDB
SHOW ENGINE INNODB STATUS\G

-- Bagian LATEST DETECTED DEADLOCK akan menampilkan:
-- - Transaction yang terlibat
-- - Lock yang dipegang masing-masing
-- - Lock yang masing-masing tunggu
-- - Transaksi mana yang dipilih sebagai korban (TRANSACTION ... WAS VICTIM)

Throughput Turun Tanpa Sebab yang Jelas #

Ini adalah gejala yang paling membingungkan: CPU rendah, memory cukup, network normal — tapi database terasa lambat. Hampir selalu penyebabnya adalah lock: proses-proses mengantre untuk mendapat giliran mengakses data yang sama, dan waktu tunggu itu tidak tercermin di metrik CPU atau memory.

Best Practice Menggunakan Locking #

Jaga Transaction Sesingkat Mungkin #

Ini adalah aturan paling penting dalam locking. Lock dipegang selama transaction terbuka — semakin singkat transaction, semakin singkat lock dipegang, semakin sedikit proses lain yang terdampak.

-- ANTI-PATTERN: banyak logika dan query di dalam satu transaction panjang
START TRANSACTION;

SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- [banyak SELECT untuk data pendukung]
-- [kalkulasi kompleks]
-- [validasi bisnis panjang]
UPDATE users SET balance = balance - 10000 WHERE id = 1;
INSERT INTO transactions (user_id, amount, type) VALUES (1, 10000, 'debit');
-- [kirim notifikasi?]
-- [update statistik?]

COMMIT;

-- BENAR: pisahkan preparasi dari eksekusi
-- Step 1: kumpulkan semua data yang dibutuhkan SEBELUM membuka transaction
user_data      = SELECT * FROM users WHERE id = 1;            -- tanpa lock
product_data   = SELECT * FROM products WHERE id = 5;         -- tanpa lock
-- [semua kalkulasi dan validasi dilakukan di sini, di luar transaction]
final_amount   = calculate_final_amount(user_data, product_data)

-- Step 2: buka transaction hanya untuk operasi write yang perlu dilindungi
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;                  -- lock dimulai di sini
-- [validasi minimal: apakah saldo masih cukup setelah kita lock?]
UPDATE users SET balance = balance - final_amount WHERE id = 1;
INSERT INTO transactions (user_id, amount) VALUES (1, final_amount);
COMMIT;                                                        -- lock dilepas di sini
-- Transaction hanya selebar operasi write yang benar-benar perlu dilindungi

Pastikan Query yang Menggunakan Lock Selalu Memanfaatkan Index #

Ini adalah jebakan yang sangat sering tidak disadari. Ketika SELECT ... FOR UPDATE dijalankan pada kolom yang tidak ter-index, InnoDB tidak bisa melakukan row-level lock — ia akan melakukan full table scan dan mengunci semua baris yang ditemui, bahkan yang tidak relevan.

-- ANTI-PATTERN: FOR UPDATE pada kolom tanpa index → implicit table lock
-- Asumsikan kolom 'status' tidak memiliki index
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- InnoDB akan scan seluruh tabel dan lock semua baris yang ditemui
-- Bukan hanya yang status='pending', tapi semua baris dalam scan path

-- BENAR: pastikan kolom di WHERE clause ter-index sebelum menggunakan FOR UPDATE
-- Tambahkan index jika belum ada:
ALTER TABLE orders ADD INDEX idx_orders_status (status);

-- Atau lebih baik, gunakan primary key atau unique key untuk lock yang paling presisi:
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
-- Hanya baris id=42 yang dikunci — tidak ada baris lain yang terdampak

-- Cara verifikasi bahwa query menggunakan index (bukan full scan):
EXPLAIN SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- Perhatikan kolom 'type':
-- 'ref' atau 'range' → menggunakan index ✓
-- 'ALL' → full table scan → akan menyebabkan lock yang luas ✗

Hindari Gap Lock yang Tidak Perlu #

Gap lock muncul ketika query menggunakan range condition (BETWEEN, >, <, >=, <=) dengan FOR UPDATE. Gap lock memblokir INSERT di rentang yang dikunci — bahkan untuk nilai yang belum ada — dan ini sering menyebabkan blocking yang mengejutkan.

-- ANTI-PATTERN: range lock yang lebih luas dari yang dibutuhkan
-- Ini memasang gap lock pada semua nilai antara 100 dan 200
SELECT * FROM products WHERE price BETWEEN 100 AND 200 FOR UPDATE;

-- Jika ada transaksi lain yang mencoba INSERT produk dengan price=150,
-- ia akan diblokir meskipun produk itu belum ada.

-- BENAR: lock berdasarkan primary key jika memungkinkan
-- Lebih presisi, tidak ada gap lock
SELECT * FROM products WHERE id IN (42, 43, 44) FOR UPDATE;

-- Atau jika range lock memang diperlukan, pastikan kamu menyadari
-- bahwa INSERT di rentang itu akan diblokir selama transaction berlangsung.

Akses Tabel dan Baris dalam Urutan yang Konsisten #

Deadlock hampir selalu terjadi karena dua transaksi mengakses resource yang sama dalam urutan yang berbeda. Menetapkan dan mematuhi konvensi urutan akses adalah cara paling efektif untuk mencegah deadlock.

-- ANTI-PATTERN: urutan lock yang tidak konsisten di dua bagian kode

-- Di endpoint transfer:
START TRANSACTION;
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- lock wallet dulu
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- baru lock account
...
COMMIT;

-- Di endpoint pembayaran:
START TRANSACTION;
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- lock account dulu ← terbalik!
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- baru lock wallet
...
COMMIT;

-- Jika keduanya berjalan bersamaan:
-- Transfer memegang lock wallet, menunggu lock account
-- Pembayaran memegang lock account, menunggu lock wallet → DEADLOCK

-- BENAR: tetapkan konvensi urutan dan patuhi di seluruh codebase
-- Konvensi: SELALU lock wallets sebelum accounts, tidak pernah sebaliknya.

-- Di endpoint transfer:
START TRANSACTION;
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- wallet dulu
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- baru account
COMMIT;

-- Di endpoint pembayaran (urutan SAMA):
START TRANSACTION;
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- wallet dulu
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- baru account
COMMIT;
-- Tidak ada deadlock karena urutan selalu konsisten.

Gunakan Lock Timeout untuk Mencegah Menunggu Selamanya #

Secara default, transaksi yang menunggu lock akan menunggu sampai innodb_lock_wait_timeout tercapai (default 50 detik di MySQL). Untuk aplikasi web, menunggu 50 detik jelas tidak bisa diterima. Set timeout yang sesuai dengan SLA aplikasi.

-- Set timeout lock wait untuk session ini (dalam detik)
SET innodb_lock_wait_timeout = 5;

-- Sekarang jika transaksi menunggu lock lebih dari 5 detik,
-- ia akan mendapat error:
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- Di aplikasi, tangkap error ini dan berikan respons yang sesuai ke user
-- (misalnya: "Sistem sedang sibuk, silakan coba lagi dalam beberapa saat")

Monitor Locking Secara Aktif #

Lock yang tidak dimonitor adalah lock yang baru ketahuan saat sudah menjadi insiden. Ada beberapa query yang bisa dijalankan untuk memantau kondisi locking secara real-time.

-- Melihat semua transaksi yang sedang aktif dan lock yang mereka pegang
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_rows_locked,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- Transaksi dengan duration_seconds tinggi adalah kandidat long transaction
-- yang perlu diinvestigasi.

-- Melihat lock yang sedang menunggu (proses yang diblokir)
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_seconds
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

-- Query ini menampilkan: siapa yang diblokir, oleh siapa, dan sudah berapa lama.
-- Sangat berguna untuk investigasi lock contention real-time.

Kapan Locking Memang Dibutuhkan #

Tidak semua operasi perlu locking eksplisit. Berikut panduan sederhana untuk memutuskan:

GUNAKAN explicit locking (SELECT ... FOR UPDATE) jika:
  ✓ Kamu akan memodifikasi baris yang baru saja kamu baca
  ✓ Ada risiko race condition nyata (banyak request bisa mengakses baris yang sama)
  ✓ Konsistensi data sangat kritikal (finansial, stok, status processing)
  ✓ Atomic operation tidak cukup karena logika bisnisnya kompleks

JANGAN gunakan explicit locking jika:
  ✗ Kamu hanya membaca tanpa akan menulis
  ✗ Data yang dibaca bukan untuk keputusan kritikal
  ✗ Atomic update (SET balance = balance - X) sudah cukup menangani masalahnya
  ✗ Optimistic locking (version column) lebih sesuai karena conflict rate rendah
  ✗ Query tidak menggunakan index (locking akan melebar ke table level)

PERTIMBANGKAN optimistic locking jika:
  ✓ Conflict rate rendah — mayoritas operasi tidak bertabrakan
  ✓ Kamu ingin maksimal konkurensi tanpa memblokir proses lain
  ✓ Retry setelah conflict bisa dilakukan dengan aman

Anti-Pattern yang Harus Dihindari #

-- ✗ Anti-pattern 1: FOR UPDATE tanpa index → table lock terselubung
SELECT * FROM orders WHERE created_at > '2024-01-01' FOR UPDATE;
-- Jika created_at tidak ter-index, seluruh tabel di-scan dan dikunci

-- ✓ Solusi: pastikan ada index pada kolom di WHERE, atau gunakan primary key

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 2: LOCK TABLES di aplikasi produksi
LOCK TABLES wallets WRITE, orders WRITE;
-- Semua proses lain ke kedua tabel ini diblokir total

-- ✓ Solusi: gunakan row-level lock dengan SELECT ... FOR UPDATE di dalam transaction

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 3: transaction dibuka tapi tidak pernah di-commit atau di-rollback
-- (transaksi zombie karena exception tidak tertangani)
START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
-- [exception terjadi di sini, tapi tidak ada ROLLBACK]
-- Lock dipegang sampai connection timeout atau database restart

-- ✓ Solusi: selalu gunakan try-finally atau mekanisme serupa untuk memastikan
-- COMMIT atau ROLLBACK selalu dipanggil, bahkan jika ada exception

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 4: mengasumsikan SELECT tanpa lock aman untuk operasi kritikal
SELECT balance FROM wallets WHERE user_id = 1;
-- [cek: apakah saldo cukup?]
UPDATE wallets SET balance = balance - 10000 WHERE user_id = 1;
-- Race condition: saldo bisa berubah antara SELECT dan UPDATE

-- ✓ Solusi: gunakan atomic update atau SELECT FOR UPDATE dalam transaction
START TRANSACTION;
SELECT balance FROM wallets WHERE user_id = 1 FOR UPDATE;
-- [cek saldo]
UPDATE wallets SET balance = balance - 10000 WHERE user_id = 1;
COMMIT;

Checklist Review Locking #

DESAIN TRANSACTION:
  □ Setiap transaction sesingkat mungkin — hanya selebar operasi write yang perlu dilindungi
  □ Tidak ada HTTP call, file I/O, atau komputasi berat di dalam transaction
  □ Semua path eksekusi (termasuk exception) selalu menutup transaction (COMMIT/ROLLBACK)
  □ Lock timeout sudah dikonfigurasi sesuai SLA (bukan default 50 detik)

PENGGUNAAN LOCK:
  □ SELECT FOR UPDATE hanya digunakan jika data akan segera dimodifikasi
  □ Semua query yang menggunakan FOR UPDATE dipastikan menggunakan index
  □ EXPLAIN dijalankan pada query FOR UPDATE untuk memverifikasi tidak ada full table scan
  □ Range lock (BETWEEN, >, <) dengan FOR UPDATE disadari implikasinya terhadap gap lock
  □ LOCK TABLES tidak digunakan di kode aplikasi

DEADLOCK PREVENTION:
  □ Urutan akses tabel dan baris konsisten di seluruh codebase
  □ Konvensi urutan lock terdokumentasi dan diketahui seluruh tim
  □ Tidak ada transaction yang mengunci lebih banyak resource dari yang diperlukan

MONITORING:
  □ INNODB_TRX dipantau untuk mendeteksi long transaction
  □ INNODB_LOCK_WAITS dipantau untuk mendeteksi lock contention
  □ Deadlock log di-alert jika frekuensinya naik
  □ innodb_lock_wait_timeout dikonfigurasi dengan nilai yang wajar

ALTERNATIF LOCKING:
  □ Atomic operation (conditional UPDATE) dipertimbangkan sebelum menggunakan FOR UPDATE
  □ Optimistic locking (version column) dipertimbangkan untuk conflict rate rendah
  □ Keputusan "mengapa menggunakan pessimistic locking di sini" terdokumentasi

Ringkasan #

Locking adalah pedang bermata dua — digunakan tepat ia menjaga konsistensi data, digunakan sembarangan ia menjadi bottleneck yang membuat sistem terasa lambat tanpa sebab jelas.
Shared lock memungkinkan banyak reader bersamaan tapi memblokir writer. Exclusive lock memblokir semua transaksi lain — reader maupun writer.
Row-level lock hanya terjadi jika query menggunakan index — tanpa index, InnoDB melakukan full table scan dan lock melebar ke seluruh tabel tanpa peringatan.
Gap lock mengunci ruang kosong di antara nilai index — ia memblokir INSERT di rentang yang dikunci, termasuk untuk nilai yang belum ada. Ini sering menjadi sumber blocking yang mengejutkan.
Durasi transaction = durasi lock — semakin singkat transaction, semakin singkat lock dipegang, semakin kecil dampaknya terhadap konkurensi. Ini adalah aturan paling penting dalam locking.
Jangan taruh operasi lambat di dalam transaction — HTTP call, file I/O, atau komputasi berat di dalam transaction yang memegang lock bisa membuat proses lain menunggu detik hingga menit.
Deadlock dicegah dengan urutan akses yang konsisten — tetapkan konvensi urutan penguncian tabel dan baris, dan patuhi di seluruh codebase tanpa pengecualian.
Lock timeout harus dikonfigurasi eksplisit — default 50 detik terlalu lama untuk aplikasi web. Set ke nilai yang sesuai dengan SLA dan tangkap error timeout di aplikasi.
Monitoring adalah wajib — INNODB_TRX untuk mendeteksi long transaction, INNODB_LOCK_WAITS untuk mendeteksi contention, dan deadlock log untuk mendeteksi masalah urutan akses.
Locking bukan satu-satunya solusi — pertimbangkan atomic operation dan optimistic locking sebelum menggunakan pessimistic locking. Pilih yang paling ringan yang masih memenuhi kebutuhan konsistensi.

← Sebelumnya: Replication Berikutnya: Race Condition →