Transaksi di MySQL (InnoDB).

Transaksi adalah sekumpulan perintah SQL yang diperlakukan sebagai satu unit kerja: semuanya berhasil, atau tidak ada yang berlaku sama sekali. Contoh klasiknya transfer saldo: mengurangi saldo A dan menambah saldo B harus terjadi bersamaan. Kalau server mati di tengah jalan, uang tidak boleh hilang.

Catatan ini untuk MySQL 8 dengan engine InnoDB. MyISAM tidak mendukung transaksi.

1. ACID

2. Perintah Dasar

START TRANSACTION;   -- atau BEGIN;

UPDATE accounts SET balance = balance - 100000 WHERE id = 1;
UPDATE accounts SET balance = balance + 100000 WHERE id = 2;

COMMIT;              -- simpan permanen
-- ROLLBACK;         -- atau batalkan semuanya

Savepoint untuk membatalkan sebagian:

START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 50000);
SAVEPOINT sebelum_item;
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 99, 1);
ROLLBACK TO SAVEPOINT sebelum_item;  -- hanya item yang batal
COMMIT;

Autocommit

Secara default autocommit = 1: setiap statement di luar START TRANSACTION langsung di-commit. Jika kamu mematikannya (SET autocommit = 0), transaksi baru terbuka otomatis dan tetap terbuka sampai kamu COMMIT, dan ini sumber lock yang tertahan lama.

Implicit commit

Statement DDL seperti CREATE TABLE, ALTER TABLE, DROP, TRUNCATE memicu commit otomatis atas transaksi yang sedang berjalan. Jangan campur DDL dan DML dalam satu transaksi dengan harapan bisa di-rollback.

Contoh di aplikasi (PHP PDO)

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $pdo->beginTransaction();
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100000, 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100000, 2]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Di Laravel cukup DB::transaction(function () { ... }, 3);. Argumen kedua adalah jumlah percobaan ulang jika terjadi deadlock.

3. Anomali Konkurensi

Saat dua transaksi berjalan bersamaan, beberapa masalah bisa muncul:

4. Isolation Level

Catatan khusus InnoDB pada REPEATABLE READ:

• Consistent read (SELECT biasa) membaca snapshot yang dibuat saat pembacaan pertama dalam transaksi (MVCC). Jadi SELECT biasa tidak melihat baris baru dari transaksi lain, sehingga phantom tidak terlihat.
• Locking read (FOR UPDATE/FOR SHARE), UPDATE, dan DELETE membaca data terbaru dan memasang next-key lock untuk mencegah insert ke rentang yang sedang dibaca.

Mengecek dan mengubah isolation level:

SELECT @@transaction_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- hanya untuk transaksi berikutnya
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ COMMITTED sering dipakai di sistem dengan tulis tinggi karena memasang lebih sedikit gap lock, sehingga deadlock lebih jarang. Konsekuensinya, aplikasi harus siap menghadapi non-repeatable read.

5. Locking di InnoDB

InnoDB memakai row-level lock, bukan mengunci seluruh tabel.

Penting: Lock dipasang pada index, bukan pada baris mentah. Jika WHERE di UPDATE tidak memakai index, InnoDB harus memindai (dan mengunci) jauh lebih banyak baris, bahkan bisa semua baris tabel. Index yang tepat juga berarti lock yang lebih sempit.

6. SELECT … FOR UPDATE: Mencegah Lost Update

Kasus: stok produk tinggal 1, dua pembeli checkout bersamaan.

Salah (baca lalu tulis tanpa lock):

SELECT stock FROM products WHERE id = 7;         -- dua transaksi sama-sama dapat 1
UPDATE products SET stock = 0 WHERE id = 7;      -- keduanya "berhasil" membeli

Benar (kunci baris saat membaca):

START TRANSACTION;
SELECT stock FROM products WHERE id = 7 FOR UPDATE;  -- transaksi kedua menunggu di sini
-- aplikasi cek: stock > 0 ?
UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;

Alternatif tanpa lock eksplisit, dengan update atomik bersyarat:

UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock > 0;
-- affected rows = 0 → stok habis

Varian locking read di MySQL 8:

Contoh worker antrean yang aman dijalankan paralel:

START TRANSACTION;
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'running' WHERE id = ?;
COMMIT;

7. Deadlock

Deadlock terjadi saat dua transaksi saling menunggu lock yang dipegang lawannya:

T1: UPDATE accounts ... WHERE id = 1;   -- lock baris 1
T2: UPDATE accounts ... WHERE id = 2;   -- lock baris 2
T1: UPDATE accounts ... WHERE id = 2;   -- menunggu T2
T2: UPDATE accounts ... WHERE id = 1;   -- menunggu T1 → deadlock

InnoDB mendeteksinya otomatis dan me-rollback salah satu transaksi (yang lebih “kecil”) dengan error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Melihat deadlock terakhir:

SHOW ENGINE INNODB STATUS\G
-- cari bagian "LATEST DETECTED DEADLOCK"

Mencatat semua deadlock ke error log:

SET GLOBAL innodb_print_all_deadlocks = ON;

Mengurangi deadlock

1. Akses baris dalam urutan yang konsisten (misalnya selalu urut id naik). Pada contoh transfer, kunci akun dengan id lebih kecil dulu.
2. Transaksi sependek mungkin. Jangan memanggil API eksternal atau menunggu input user di dalam transaksi.
3. Index yang tepat agar lock yang dipasang sedikit.
4. Pertimbangkan READ COMMITTED untuk mengurangi gap lock.
5. Siapkan retry di aplikasi. Deadlock bukan bug fatal, melainkan kondisi normal di sistem konkuren; ulangi transaksi dari awal.

Lock wait timeout vs deadlock

Poin kedua sering menjebak: setelah error 1205, transaksi masih terbuka dengan perubahan sebelumnya. Aplikasi harus melakukan ROLLBACK eksplisit (atau set innodb_rollback_on_timeout=ON di konfigurasi server).

Mencari transaksi yang menahan lock:

SELECT * FROM sys.innodb_lock_waits\G
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

8. Kesalahan Umum

Referensi

• MySQL 8.0 — START TRANSACTION, COMMIT, and ROLLBACK
• MySQL 8.0 — Transaction Isolation Levels
• MySQL 8.0 — InnoDB Locking
• MySQL 8.0 — Locking Reads
• MySQL 8.0 — Deadlocks in InnoDB