Belajar SQL PostgreSQL - Transaction Management, ACID & Concurrency Control

#Pendahuluan

Selamat datang di episode 11 series Belajar SQL PostgreSQL! Sejauh ini kita belajar mengolah data — tapi ada satu pertanyaan besar yang belum kita jawab: apa yang terjadi kalau dua user mengubah data yang sama secara bersamaan? Bayangkan dua orang membeli produk yang stoknya tinggal satu, dua transaksi menarik uang dari saldo yang sama, atau dua order memakai voucher yang sama. Tanpa perlindungan yang tepat, semua skenario ini bisa menghasilkan data yang salah dan hilangnya uang sungguhan.

Jawaban atas semua masalah ini adalah transaction — mekanisme yang menjamin sekelompok operasi database berjalan sebagai satu kesatuan yang tak terpisahkan. Dan di balik transaction, ada empat pilar yang disebut ACID. Memahami keduanya adalah batas antara aplikasi yang "kelihatan jalan" dengan aplikasi yang benar-benar aman.

Di episode ini, kita akan membahas prinsip ACID, perintah transaksi BEGIN, COMMIT, ROLLBACK, dan SAVEPOINT, fenomena concurrency dan isolation level di PostgreSQL, serta locking eksplisit dengan SELECT ... FOR UPDATE untuk mencegah race condition.

#Prinsip ACID pada Database

ACID adalah akronim empat properti yang menjamin keandalan transaksi database:

#Perintah Transaksi

#BEGIN, COMMIT, dan ROLLBACK

Ketiga perintah ini adalah siklus hidup transaksi:

• BEGIN: memulai transaksi.
• COMMIT: menyimpan semua perubahan secara permanen.
• ROLLBACK: membatalkan semua perubahan sejak BEGIN.

BEGIN;
 
INSERT INTO orders (customer_id, total)
VALUES ('1c2d3e4f-0000-0000-0000-000000000001', 150000);
 
UPDATE inventory
SET stock = stock - 1
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001';
 
COMMIT;

Jika salah satu statement di atas gagal — katakanlah stok tidak cukup dan CHECK (stock >= 0) menolak — kalian bisa memilih ROLLBACK untuk mengembalikan semuanya, termasuk insert order yang sudah jalan. Atomicity dijamin.

#SAVEPOINT: Titik Pemulihan Parsial

Kadang kita tidak ingin membatalkan seluruh transaksi — hanya sebagian. SAVEPOINT membuat titik pemulihan parsial:

BEGIN;
 
INSERT INTO orders (customer_id, total) VALUES (1, 50000);
 
SAVEPOINT order_created;
 
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (10, 20, 1);
 
ROLLBACK TO order_created;
UPDATE order_items SET quantity = 2 WHERE order_id = 10;
 
COMMIT;

Setelah ROLLBACK TO order_created, hanya perubahan sejak savepoint tersebut yang dibatalkan — insert order tetap dipertahankan. Ini berguna untuk alur bisnis yang bercabang dengan fallback.

#Transaction Isolation Levels & Concurrency Phenomena

Isolation membahas: seberapa "terisolasi" transaksi satu dari yang lain. Tanpa isolasi yang cukup, muncul fenomena concurrency yang mendistorsi hasil.

#Fenomena Concurrency

#Isolation Level di PostgreSQL

PostgreSQL punya tiga isolation level (PostgreSQL tidak pernah mengalami dirty read karena MVCC):

Read Committed adalah default PostgreSQL: setiap statement melihat snapshot terbaru yang sudah commit. Untuk transaksi yang panjang dan butuh pandangan konsisten, gunakan Repeatable Read. Serializable dipakai saat akurasi ketat lebih penting daripada throughput.

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

#Explicit Locking: SELECT ... FOR UPDATE

Isolation level melindungi dari fenomena baca, tapi tidak mencegah write-write race. Bayangkan dua transaksi sama-sama membaca stok 5, keduanya mengurangi 1, dan keduanya menulis 4 — padahal seharusnya jadi 3. Ini disebut race condition, dan inilah asal dari bencana double spending dan overbooking.

Solusinya adalah locking eksplisit: kunci baris agar transaksi lain menunggu.

#FOR UPDATE: Kunci untuk Mengubah Data

SELECT ... FOR UPDATE mengunci baris yang dibaca sampai transaksi selesai. Transaksi lain yang mencoba mengunci atau mengubah baris yang sama akan menunggu:

BEGIN;
 
SELECT stock
FROM inventory
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001'
FOR UPDATE;
 
UPDATE inventory
SET stock = stock - 1
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001';
 
COMMIT;

Dengan FOR UPDATE, transaksi kedua yang memproses order untuk produk yang sama akan menunggu sampai transaksi pertama commit. Race condition double spending berhasil dicegah.

#FOR SHARE: Kunci Berbagi untuk Membaca

SELECT ... FOR SHARE mengunci baris agar tidak bisa diubah oleh transaksi lain, tapi masih bisa dibaca (bahkan di-lock share oleh banyak transaksi). Cocok saat beberapa proses ingin memastikan data tidak berubah selama memproses:

BEGIN;
SELECT id, price FROM products WHERE id = 5 FOR SHARE;
COMMIT;

#Isolasi Sisi Pembaca: Row Lock vs Blokir Baca

Perlu ditegaskan: dengan MVCC, baris yang di-lock FOR UPDATE masih bisa dibaca oleh transaksi lain (mereka melihat versi snapshot lama). Yang diblokir hanya pembaruan — transaksi lain harus menunggu sebelum mengunci/mengubah baris tersebut. Ini kombinasi terbaik: konsistensi untuk penulis, concurrency untuk pembaca.

#Kesalahan Umum

#Penutup

Di episode 11 ini kita sudah memahami fondasi keandalan database: prinsip ACID (Atomicity, Consistency, Isolation, Durability), perintah transaksi BEGIN, COMMIT, ROLLBACK, dan SAVEPOINT, fenomena concurrency dan isolation level, serta locking eksplisit dengan SELECT ... FOR UPDATE dan FOR SHARE untuk mencegah race condition.

Inti yang harus dibawa pulang:

• ACID menjamin transaksi aman: atomic, konsisten, terisolasi, dan tahan lama.
• BEGIN → statement → COMMIT atau ROLLBACK adalah pola transaksi yang benar.
• SAVEPOINT memungkinkan rollback parsial tanpa membatalkan seluruh transaksi.
• Default PostgreSQL adalah Read Committed; Repeatable Read dan Serializable untuk kebutuhan ketat.
• SELECT ... FOR UPDATE adalah senjata mencegah double spending dan overbooking.

Di episode 12 selanjutnya, kita masuk ke dunia database objects tingkat lanjut: Views, Materialized Views & Generated Columns — mulai dari standard view untuk menyederhanakan akses data, materialized view untuk query analitik berkecepatan tinggi dengan REFRESH ... CONCURRENTLY, hingga generated columns yang menghitung nilai secara otomatis.