Understanding Transactions and Locking

"In PostgreSQL, everything is a transaction"

Setiap perintah SQL yang kamu kirim ke PostgreSQL selalu dijalankan dalam sebuah transaksi.

  • Kalau kamu tidak eksplisit menulis BEGIN ... COMMIT, PostgreSQL akan otomatis membuat transaksi "sementara" di balik layar.

  • Jadi satu query tunggal seperti SELECT * FROM users; itu sudah dianggap sebuah transaksi oleh PostgreSQL.

Contoh kasus SELECT now(), now();

SELECT now(), now();

  • PostgreSQL membuka transaksi → mengeksekusi query → menutup transaksi.

  • Fungsi now() mengembalikan waktu transaksi saat dimulai.

  • Karena kedua now() dipanggil dalam transaksi yang sama, maka hasilnya identik (timestamp sama persis).

Outputnya:

now                         | now
----------------------------+----------------------------
2024-05-24 12:59:33.594603+02 | 2024-05-24 12:59:33.594603+02

Kedua kolom identik, karena waktu transaksi dibekukan saat query mulai.

Kalau query dieksekusi lagi

SELECT now();

  • PostgreSQL buat transaksi baru → maka nilai now() berbeda dari transaksi sebelumnya.

  • Karena transaksi baru dimulai pada waktu yang berbeda.

Perbedaan now() vs clock_timestamp()

  • now() → waktu transaksi, sama selama 1 transaksi berjalan.

  • clock_timestamp() → waktu sistem saat ini, selalu berubah sesuai jam nyata.

Contoh:

SELECT now(), clock_timestamp(), pg_sleep(2), now(), clock_timestamp();

Hasil:

now                  | clock_timestamp
---------------------+---------------------
2024-05-24 13:00:00  | 2024-05-24 13:00:00
2024-05-24 13:00:00  | 2024-05-24 13:00:02

  • now() tetap sama (karena masih dalam 1 transaksi).

  • clock_timestamp() berubah (karena baca langsung dari jam sistem).

"If more than one statement has to be a part of the same transaction, the BEGIN statement must be used"

Kalau kita ingin beberapa perintah SQL berjalan dalam satu transaksi yang sama, kita harus secara eksplisit membuka blok transaksi dengan BEGIN (atau START TRANSACTION).

  • Satu query tunggal = implicit transaction (otomatis dibuka & ditutup).

  • Banyak query yang harus konsisten = pakai BEGIN ... COMMIT supaya semuanya dianggap satu kesatuan atomik.

Contoh BEGIN ... COMMIT

BEGIN;            -- mulai transaksi
SELECT now();     -- eksekusi query pertama
SELECT now();     -- eksekusi query kedua
COMMIT;           -- akhiri transaksi

Hasil:

2024-05-24 13:00:39.864604+02
2024-05-24 13:00:39.864604+02

Kenapa sama? Karena kedua SELECT now() dieksekusi dalam satu transaksi → maka now() mengembalikan waktu transaksi (dibekukan sejak BEGIN).

Perintah COMMIT

  • COMMIT → menyelesaikan transaksi dengan sukses → semua perubahan jadi permanen dan terlihat oleh transaksi lain.

  • Variasi penulisan:

    • COMMIT

    • COMMIT WORK

    • COMMIT TRANSACTION → semuanya identik.

  • Alias: END → sama artinya dengan COMMIT.

Perintah ROLLBACK

  • ROLLBACK → membatalkan transaksi yang sedang berjalan.

  • Semua perubahan dalam transaksi tersebut dibatalkan, tidak akan terlihat oleh transaksi lain.

  • Alias: ABORT → sama dengan ROLLBACK.

Contoh:

BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
ROLLBACK;

saldo user tidak berubah karena transaksi dibatalkan.

Transaction chaining (COMMIT AND CHAIN)

  • Biasanya setelah COMMIT, transaksi berakhir → kalau mau mulai transaksi baru harus tulis BEGIN lagi.

  • Dengan COMMIT AND CHAIN, PostgreSQL otomatis membuka transaksi baru setelah COMMIT, dengan properti yang sama (misalnya mode read-only).

  • Kebalikannya: COMMIT AND NO CHAIN → transaksi berakhir normal, tidak lanjut otomatis.

Contoh penggunaan CHAIN

SHOW transaction_read_only;   -- default: Off

BEGIN TRANSACTION READ ONLY;  -- mulai transaksi read-only
SELECT 1;
COMMIT AND CHAIN;             -- commit + otomatis mulai transaksi baru dengan mode yang sama
SHOW transaction_read_only;   -- hasilnya masih On (read-only mode)
SELECT 1;
COMMIT AND NO CHAIN;          -- commit + akhiri transaksi (tidak lanjut)
SHOW transaction_read_only;   -- kembali Off (default)

Ringkasan perintah penting

Perintah
Fungsi

BEGIN / START TRANSACTION

Memulai blok transaksi

COMMIT / END

Menyelesaikan transaksi, simpan perubahan

ROLLBACK / ABORT

Membatalkan transaksi, buang perubahan

COMMIT AND CHAIN

Commit + otomatis buka transaksi baru dengan properti yang sama

COMMIT AND NO CHAIN

Commit + selesai (transaksi tidak lanjut)

  • Gunakan BEGIN ... COMMIT kalau butuh beberapa statement satu kesatuan atomik.

  • ROLLBACK dipakai kalau transaksi gagal/ingin dibatalkan.

  • CHAIN berguna untuk aplikasi high-latency → mengurangi overhead round trip karena tidak perlu BEGIN ulang.

"Handling errors inside a transaction"

  • Dalam PostgreSQL, transaksi hanya bisa di-commit kalau benar-benar bebas dari error.

  • Kalau ada satu error saja, seluruh transaksi dianggap rusak (aborted).

  • Setelah error, semua perintah berikutnya dalam transaksi itu akan diabaikan, sampai kita ROLLBACK.

Contoh transaksi gagal

BEGIN;
SELECT 1;      -- berhasil
SELECT 1 / 0;  -- ERROR: division by zero
SELECT 1;      -- ERROR: transaksi sudah aborted
COMMIT;        -- tidak commit, otomatis jadi ROLLBACK

Output:

ERROR: division by zero
ERROR: current transaction is aborted
ROLLBACK

Jadi begitu ada error (division by zero), transaksi masuk mode aborted → semua query berikutnya ditolak. COMMIT tetap bisa diketik, tapi PostgreSQL akan menggantinya dengan ROLLBACK.

Solusi: SAVEPOINT

  • SAVEPOINT adalah "checkpoint" di dalam transaksi.

  • Kalau ada error setelah SAVEPOINT, kita bisa balik lagi ke titik itu pakai ROLLBACK TO SAVEPOINT.

  • Dengan cara ini, kita tidak perlu membatalkan seluruh transaksi.

Contoh penggunaan SAVEPOINT

BEGIN;
SELECT 1;                -- berhasil
SAVEPOINT a;             -- bikin checkpoint bernama 'a'
SELECT 2 / 0;            -- ERROR: division by zero
ROLLBACK TO SAVEPOINT a; -- balik lagi ke checkpoint 'a'
SELECT 3;                -- berhasil
COMMIT;                  -- transaksi selesai sukses

Output:

1
ERROR: division by zero
ROLLBACK
3
COMMIT

Dengan ROLLBACK TO SAVEPOINT, error "dibuang", dan transaksi bisa dilanjutkan.

Jumlah SAVEPOINT

  • PostgreSQL mendukung savepoint dalam jumlah sangat banyak (bahkan ratusan ribu).

  • Jadi aman dipakai untuk aplikasi besar dengan transaksi kompleks.

Menghapus SAVEPOINT

Kalau savepoint sudah tidak dibutuhkan, bisa dihapus:

RELEASE SAVEPOINT a;

  • Setelah di-RELEASE, savepoint tersebut hilang dan tidak bisa dipakai lagi.

  • Semua savepoint otomatis hilang saat transaksi berakhir (COMMIT atau ROLLBACK).

Ringkasan perintah penting

Perintah
Fungsi

SAVEPOINT name

Buat checkpoint di dalam transaksi

ROLLBACK TO SAVEPOINT name

Kembali ke kondisi transaksi saat savepoint dibuat

RELEASE SAVEPOINT name

Hapus savepoint dari transaksi

(otomatis)

Semua savepoint hilang saat transaksi selesai

  • Error sekecil apapun membuat transaksi PostgreSQL rusak total.

  • Kalau tidak pakai savepoint → harus ROLLBACK seluruh transaksi.

  • Kalau pakai savepoint → kita bisa "lompat balik" ke titik aman, tanpa kehilangan semua progress.

"Transactional DDLs"

  • DDL (Data Definition Language) adalah perintah SQL yang mengubah struktur database, contoh:

    • CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, dll.

  • Di banyak database komersial (misalnya Oracle, MySQL versi lama), DDL langsung auto-commit → transaksi berjalan otomatis diakhiri.

  • PostgreSQL berbeda → hampir semua DDL bisa dijalankan dalam transaksi, sehingga bisa digabung dengan DML (insert/update/delete) dan tetap atomik.

Keuntungan: kalau ada error, bisa ROLLBACK dan database balik ke kondisi semula, tanpa "setengah jalan" perubahan.

Contoh DDL di dalam transaksi

BEGIN; 
CREATE TABLE t_test (id int);
ALTER TABLE t_test ALTER COLUMN id TYPE int8;
\d t_test;      -- cek struktur tabel
ROLLBACK;
\d t_test;      -- tabel hilang, tidak jadi dibuat

Output:

CREATE TABLE
ALTER TABLE
Column | Type
-------+-------
id     | bigint
ROLLBACK
Did not find any relation named "t_test".

Jadi walaupun sudah CREATE + ALTER, karena akhirnya ROLLBACK, tabel tidak pernah benar-benar ada.

DDL yang tidak bisa dipakai dalam transaksi

Beberapa pengecualian (karena sifatnya low-level & global):

  • DROP DATABASE

  • CREATE TABLESPACE

  • DROP TABLESPACE dll.

Selain itu, hampir semua DDL bisa transactional.

Kenapa ini penting?

  • Deploy software lebih aman → misalnya upgrade schema CMS (Content Management System).

    • Kalau upgrade dijalankan dalam satu transaksi:

      • Lama → tetap jalan.

      • Baru → bisa jalan.

      • Campuran setengah upgrade → tidak pernah terjadi (karena atomik).

  • Bisa gabungkan beberapa modul / migration script → jalankan dalam satu deployment transaction.

  • Kalau ada error di tengah, tinggal ROLLBACK → database tetap bersih, tidak dalam kondisi "setengah berubah".

Konsep
PostgreSQL
DB lain (umumnya)

DDL dalam transaksi

Bisa (kecuali beberapa pengecualian)

Tidak bisa, auto-commit

Kalau ada error

Bisa ROLLBACK, semua perubahan dibatalkan

Tidak bisa rollback, struktur sudah berubah

Keuntungan

Aman untuk deployment, atomic, konsisten

Risiko inkonsistensi saat upgrade

PostgreSQL memperlakukan hampir semua DDL seperti DML → bisa transactional. Hal ini bikin deployment, upgrade schema, dan perubahan besar jadi lebih aman, atomik, dan konsisten.

"Understanding basic locking"

  • PostgreSQL punya mekanisme locking supaya data tetap konsisten ketika ada banyak transaksi yang berjalan bersamaan.

  • Locking tidak selalu berarti "semua transaksi diblokir". PostgreSQL pakai konsep MVCC (Multi-Version Concurrency Control) → PostgreSQL tidak mengunci data yang sedang dibaca (read) oleh sebuah transaksi. Sebaliknya, saat sebuah transaksi melakukan read, PostgreSQL akan membuat "snapshot" atau versi data yang berlaku pada saat transaksi dimulai. Ketika sebuah transaksi melakukan write (misalnya, UPDATE atau DELETE), PostgreSQL tidak menimpa data yang lama. Ia menciptakan versi data yang baru. Versi data yang lama masih ada dan dapat dilihat oleh transaksi lain yang dimulai sebelum write tersebut selesai. Hal inilah yang memungkinkan transaksi read berjalan tanpa harus menunggu transaksi write selesai.

Contoh awal: membuat tabel & data

CREATE TABLE t_test (id int);
INSERT INTO t_test VALUES (0);

  • Tabel t_test berisi satu baris dengan id = 0.

  • PostgreSQL bisa melayani ribuan user membaca data tanpa saling menghalangi.

Kesimpulan awal:

  • Banyak pembaca (readers) bisa berjalan bersamaan tanpa blocking.

Kasus read vs write (MVCC)

Misalnya ada 2 transaksi:

Transaction 1
Transaction 2

BEGIN;

BEGIN;

UPDATE t_test SET id = id + 1 RETURNING *; → hasil = 1

SELECT * FROM t_test; → hasil = 0 (versi lama)

COMMIT;

COMMIT;

Kenapa hasilnya beda?

  • Transaction 1 update baris jadi 1, tapi belum commit.

  • Transaction 2 tetap melihat versi lama (0) karena MVCC → transaksi hanya bisa lihat data yang sudah committed saat dia mulai.

Catatan penting:

  • Write tidak akan terlihat oleh transaksi lain sampai di-commit.

  • Read tidak pernah terblokir oleh write.

Kasus write vs write (concurrent update)

Bagaimana kalau dua transaksi update baris yang sama?

Transaction 1
Transaction 2

BEGIN;

BEGIN;

UPDATE t_test SET id = id + 1 RETURNING *; → hasil = 2

UPDATE t_test SET id = id + 1 RETURNING *; → menunggu Transaction 1

COMMIT;

masih menunggu

selesai, baris = 2

reread baris, update jadi 3

COMMIT;

Jadi PostgreSQL mengantre update pada row yang sama → update dijalankan berurutan, bukan bersamaan. Hasil akhir konsisten: id = 3. Tidak ada update yang hilang (no lost update).

Efek pada pembaca (readers)

  • Sementara 2 transaksi tadi update baris, pembaca tetap bisa SELECT tanpa terblokir.

  • PostgreSQL hanya mengunci row yang sedang diupdate, bukan seluruh tabel.

  • Artinya: kalau tabel punya 1.000 baris, bisa ada 1.000 transaksi update berjalan paralel, masing-masing mengupdate baris berbeda.

Ringkasan aturan locking dasar

Situasi
Perilaku PostgreSQL

Banyak pembaca (read-only)

Tidak saling blokir

Baca vs Tulis

Reader tetap jalan, lihat versi lama (MVCC)

Tulis vs Tulis (row sama)

Transaksi kedua harus menunggu yang pertama selesai

Tulis vs Tulis (row beda)

Bisa jalan bersamaan (hanya lock row terkait)

Semua write selesai + commit

Perubahan baru terlihat oleh transaksi berikutnya

Inti konsepnya:

  • PostgreSQL menggunakan MVCC → baca tidak menghalangi tulis, tulis tidak menghalangi baca.

  • Row-level lock: hanya baris yang diupdate/diubah yang terkunci, bukan seluruh tabel.

  • Update aman → PostgreSQL memastikan update berurutan, sehingga tidak ada data yang hilang walaupun banyak user update bersamaan.

Dalam praktik nyata, salah satu kesalahan yang sangat sering terjadi adalah saat developer membuat penomoran manual menggunakan SELECT max(id) lalu menambahkan +1 untuk insert data baru.

Contoh:

Transaction 1

Transaction 2

BEGIN;

BEGIN;

SELECT max(id) FROM product; → hasil 17

SELECT max(id) FROM product; → hasil 17

Developer memutuskan untuk INSERT (18, ...)

Developer juga memutuskan untuk INSERT (18, ...)

COMMIT;

COMMIT;

Masalah:

  • Bisa terjadi duplicate key violation (dua row dengan ID sama).

  • Atau dua entry identik muncul.

  • Kedua hasil itu jelas tidak diinginkan.

Solusi Sederhana dengan Explicit Locking

PostgreSQL menyediakan LOCK untuk mencegah masalah concurrency seperti ini.

Syntax LOCK:

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ];

Ada 8 jenis lock mode dengan tingkat kekuatan berbeda:

  1. ACCESS SHARE

    • Lock teringan.

    • Diambil otomatis oleh SELECT.

    • Hanya konflik dengan ACCESS EXCLUSIVE.

    • Contoh: SELECT bisa tetap jalan kecuali ada DROP TABLE.

  2. ROW SHARE

    • Diambil oleh SELECT ... FOR SHARE.

    • Konflik dengan EXCLUSIVE dan ACCESS EXCLUSIVE.

  3. ROW EXCLUSIVE

    • Diambil oleh INSERT, UPDATE, DELETE.

    • Konflik dengan SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

  4. SHARE UPDATE EXCLUSIVE

    • Diambil oleh CREATE INDEX CONCURRENTLY, ANALYZE, ALTER TABLE, VACUUM.

    • Konflik dengan banyak lock lain, termasuk EXCLUSIVE dan ACCESS EXCLUSIVE.

  5. SHARE

    • Diambil saat membuat index biasa.

    • Konflik dengan lock tipe update/row exclusive/exclusive.

  6. SHARE ROW EXCLUSIVE

    • Diambil oleh CREATE TRIGGER, beberapa ALTER TABLE.

    • Konflik dengan semua lock kecuali ACCESS SHARE.

  7. EXCLUSIVE

    • Sangat membatasi.

    • Menghalangi read maupun write.

    • Jika diambil, transaksi lain tidak bisa baca/tulis.

  8. ACCESS EXCLUSIVE

    • Lock terberat.

    • Dipakai oleh DROP TABLE, TRUNCATE, ALTER TABLE.

    • Menghalangi semua operasi lain (read & write).

Contoh Solusi dengan Lock

Untuk menghindari masalah duplikasi ID, salah satu cara:

BEGIN;
LOCK TABLE product IN ACCESS EXCLUSIVE MODE;
INSERT INTO product 
SELECT max(id) + 1, 'nama_produk'
FROM product;
COMMIT;

Dengan ACCESS EXCLUSIVE MODE, hanya 1 transaksi yang bisa akses table product. Namun, ini sangat berat karena semua read & write lain akan tertahan.

Catatan Penting

  • Cara ini bisa bekerja tapi tidak recommended.

  • Karena selama transaksi berjalan, tidak ada user lain yang bisa baca/tulis ke tabel.

  • Jadi, gunakan ini hanya sebagai solusi darurat.

Best Practice

Daripada manual max(id)+1, gunakan:

  • Serial/Bigserial → otomatis sequence.

  • Identity column (GENERATED ALWAYS AS IDENTITY).

  • UUID jika tidak mau numeric sequence.

Dengan begitu, kita tidak perlu explicit locking berat, dan PostgreSQL sudah menjamin keunikan tanpa blocking seluruh table.

Jadi ringkasnya:

  • Kesalahan umum = pakai SELECT max(id)+1 untuk insert ID.

  • Bisa pecah karena concurrency → duplicate key.

  • Solusi darurat = pakai LOCK TABLE ... IN ACCESS EXCLUSIVE MODE.

  • Tapi lebih baik pakai sequence/identity supaya aman dan efisien.

Konteks Permasalahan

Ketika aplikasi atau query tiba-tiba lambat / hang, salah satu penyebab umum adalah adanya lock. Namun, mendeteksi lock tidak trivial. PostgreSQL menyediakan pg_stat_activity untuk memantau status session.

Contoh query:

SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = 'test';

Output contoh:

-[ RECORD 3 ]-------------------------------
pid              | 23068
wait_event_type  | Client
wait_event       | ClientRead
query            | lock table t_test in access exclusive mode ;

-[ RECORD 4 ]-------------------------------
pid              | 23071
wait_event_type  | Lock
wait_event       | relation
query            | SELECT count(*) FROM t_test;

Penjelasan:

  • PID 23068 → Session yang sedang menjalankan lock table.

  • PID 23071 → Session lain yang menunggu lock (wait_event = relation).

  • Dari sini kita bisa tahu siapa yang nge-hold lock dan siapa yang ke-block.

Masalah Awal

Kasus klasik: kita butuh membuat nomor unik (misalnya nomor invoice).

  • Cara naif → pakai table lock dengan ACCESS EXCLUSIVE.

  • Tapi cara ini berbahaya → semua transaksi lain terblokir hanya demi generate 1 nomor invoice.

Solusi Alternatif dengan Watermark

Alih-alih lock seluruh tabel, kita bisa gunakan row lock dengan tabel watermark.

Langkah-langkah:

-- Tabel invoice utama
CREATE TABLE t_invoice (id int PRIMARY KEY);

-- Tabel watermark (hanya 1 baris untuk counter)
CREATE TABLE t_watermark (id int);

-- Isi awal counter
INSERT INTO t_watermark VALUES (0);

-- Generate nomor invoice baru
WITH x AS (
  UPDATE t_watermark
  SET id = id + 1
  RETURNING *
)
INSERT INTO t_invoice
SELECT * FROM x RETURNING *;

Hasil:

id
----
1
(1 row)

Penjelasan:

  1. UPDATE t_watermark SET id = id + 1 RETURNING *

    • Mengunci satu baris watermark.

    • Counter bertambah +1.

    • Nilai baru dikembalikan.

  2. INSERT INTO t_invoice SELECT * FROM x

    • Masukkan nilai counter ke tabel invoice.

  3. PostgreSQL menjamin bahwa hanya satu transaksi yang bisa update row watermark pada waktu yang sama → unik terjamin.

Keuntungan Pendekatan Watermark

  • Hanya row lock, bukan table lock → jauh lebih ringan.

  • Tidak ada blocking terhadap query SELECT di tabel invoice.

  • Skala lebih baik karena hanya 1 baris yang dikunci, bukan seluruh tabel.

  • Cocok untuk use case: nomor invoice, nomor antrian, kode unik, dll.

Jadi ringkasnya:

  • Untuk cek lock → gunakan pg_stat_activity → bisa lihat query yang menahan & yang menunggu lock.

  • Untuk solusi penomoran unik → hindari table lock.

  • Gunakan teknik watermark table + row lock agar tetap konsisten tanpa mengorbankan scalability.

kita sudah bahas bahwa di PostgreSQL semua operasi adalah transaksi. Nah, transaksi itu tidak hanya soal BEGIN/COMMIT/ROLLBACK saja, tapi juga soal bagaimana satu transaksi melihat data yang sedang berubah oleh transaksi lain. Itulah yang disebut dengan transaction isolation level.

Kasus pada READ COMMITTED (default di PostgreSQL)

  • PostgreSQL secara default menggunakan READ COMMITTED.

  • Artinya: setiap statement SQL dalam sebuah transaksi akan mengambil snapshot baru dari database.

  • Jadi kalau transaksi 1 menjalankan query, dia melihat "keadaan saat query itu mulai dieksekusi", meskipun sebelumnya ada query lain di dalam transaksi yang sama.

  • Hasilnya bisa berubah-ubah kalau query dijalankan ulang, karena ada transaksi lain yang sudah commit di tengah-tengah.

Contoh tabel t_account punya total saldo = 300.

Transaction 1 (READ COMMITTED):

BEGIN;
SELECT sum(balance) FROM t_account; -- hasil 300
-- (sementara transaksi 2 jalan)
SELECT sum(balance) FROM t_account; -- hasil 400
COMMIT;

Transaction 2:

BEGIN;
INSERT INTO t_account (balance) VALUES (100);
COMMIT;

Transaction 1 pertama kali lihat 300, tapi query kedua kali dia dapat 400, karena snapshot-nya diambil ulang.

Kasus pada REPEATABLE READ

  • Kalau kita set isolasi ke REPEATABLE READ, snapshot dibekukan di awal transaksi, bukan per query.

  • Jadi selama transaksi berjalan, semua query akan melihat data yang sama meskipun ada transaksi lain yang sudah commit.

Transaction 1 (REPEATABLE READ):

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM t_account; -- hasil 300
-- (sementara transaksi 2 jalan)
SELECT sum(balance) FROM t_account; -- tetap 300
COMMIT;

Transaction 2:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO t_account (balance) VALUES (100);
COMMIT;

Transaction 1 tetap melihat 300 sampai akhir, karena snapshot "dibekukan". Baru transaksi berikutnya (atau transaksi lain yang mulai setelah commit transaksi 2) akan melihat 400.

Kapan pakai READ COMMITTED vs REPEATABLE READ?

  • READ COMMITTED → cocok untuk aplikasi OLTP (online transaksi harian).

    • Karena user biasanya butuh lihat data terbaru secepat mungkin.

    • Resiko inkonsistensi kecil, dan performanya oke.

  • REPEATABLE READ → cocok untuk laporan, analisis, atau batch processing.

    • Karena data harus konsisten dari awal sampai akhir.

    • Misalnya, bikin laporan keuangan bulanan: halaman pertama dan halaman terakhir harus punya dasar data yang sama.

Pentingnya

  • Isolation level ini sering diabaikan developer → akhirnya muncul anomali transaksi seperti:

    • Non-repeatable read (hasil berubah saat query diulang dalam 1 transaksi).

    • Phantom read (baris baru muncul di query berikutnya).

  • Kadang bug ini baru ketahuan bertahun-tahun setelah aplikasi dipakai, karena kasus tertentu jarang muncul tapi bisa bikin error fatal.

Oke, mari saya teruskan dan jabarkan biar makin jelas ya. Kita sudah sampai pada Serializable Snapshot Isolation (SSI) di PostgreSQL.

Serializable Snapshot Isolation (SSI)

PostgreSQL mendukung 3 level isolasi saja:

  1. READ COMMITTED (default)

  2. REPEATABLE READ

  3. SERIALIZABLE

READ UNCOMMITTED tidak didukung, kalau dicoba akan otomatis diubah jadi READ COMMITTED.

Konsep dasar SERIALIZABLE

  • Tujuannya: kalau sebuah transaksi benar-benar aman saat dijalankan sendiri (single user), maka dia juga aman saat dijalankan paralel dengan transaksi lain.

  • PostgreSQL akan menjaga ilusi seolah-olah semua transaksi dijalankan satu per satu (serial execution), meskipun di realita berjalan paralel.

  • Ini artinya tidak ada anomali concurrency seperti:

    • Dirty read

    • Non-repeatable read

    • Phantom read

    • Write skew

Tapi ada konsekuensi:

  1. Performance penalty → PostgreSQL perlu lebih banyak tracking dependency antar transaksi.

  2. Kemungkinan transaksi gagal → PostgreSQL bisa menolak commit dengan error seperti:

    ERROR: could not serialize access due to concurrent update

    Sehingga aplikasi harus siap retry.

Contoh Deadlock (klasik)

Misalkan tabel:

CREATE TABLE t_deadlock (id int);
INSERT INTO t_deadlock VALUES (1), (2);

Transaction 1:

BEGIN;
UPDATE t_deadlock SET id = id * 10 WHERE id = 1;
UPDATE t_deadlock SET id = id * 10 WHERE id = 2;

Transaction 2:

BEGIN;
UPDATE t_deadlock SET id = id * 10 WHERE id = 2;
UPDATE t_deadlock SET id = id * 10 WHERE id = 1;

Terjadi deadlock karena T1 menunggu T2 dan T2 menunggu T1. PostgreSQL akan mendeteksi ini, lalu setelah deadlock_timeout (default 1 detik) akan memaksa salah satu transaksi rollback:

ERROR: deadlock detected
DETAIL: Process 91521 waits for ShareLock...

Contoh Konflik SERIALIZABLE

Kasus lain, misalkan:

Transaction 1:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t_deadlock; -- lihat id=1,2
DELETE FROM t_deadlock;   -- mencoba delete

Transaction 2:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DELETE FROM t_deadlock;   -- menghapus baris lebih dulu
COMMIT;

Transaction 1 akan gagal saat delete, karena barisnya sudah "mati" akibat transaksi lain. PostgreSQL akan memaksa rollback:

ERROR: could not serialize access due to concurrent update

Kesimpulan

  • READ COMMITTED → default, tiap statement pakai snapshot baru. Cocok OLTP harian.

  • REPEATABLE READ → snapshot dibekukan per transaksi, cocok untuk laporan / analisis konsisten.

  • SERIALIZABLE (SSI) → menjaga illusion eksekusi serial, paling aman, tapi:

    • bisa ada error "could not serialize access" → aplikasi harus retry.

    • lebih berat dari sisi performa.

  • PostgreSQL tidak mendukung READ UNCOMMITTED.

Jadi, kalau aplikasinya butuh konsistensi absolut (misalnya sistem keuangan bank), SERIALIZABLE bisa dipakai. Tapi harus diiringi dengan logic retry di level aplikasi.

Utilizing Advisory Locks

Konsep

PostgreSQL sudah punya sistem locking yang canggih dan efisien (row-level, table-level, MVCC, dsb). Namun, ada kebutuhan khusus: bagaimana kalau kita ingin sinkronisasi antar aplikasi (bukan sekadar antar query database)?

Dari sinilah lahir Advisory Locks.

  • Advisory lock tidak mengunci row/tabel, melainkan angka (integer atau bigint).

  • Jadi ini lebih ke mekanisme sinkronisasi aplikasi ketimbang pengendalian data.

  • Lock ini tidak otomatis dilepas saat COMMIT → developer wajib melakukan unlock secara eksplisit.

Contoh Skenario

Misalkan dua session mencoba mengunci angka 15:

Transaction 1:

BEGIN;
SELECT pg_advisory_lock(15);
-- berhasil dapat lock

Transaction 2:

BEGIN;
SELECT pg_advisory_lock(15);
-- akan menunggu sampai T1 melepas lock

Transaction 1 lanjut:

COMMIT;
SELECT pg_advisory_unlock(15);

Transaction 2 baru bisa lanjut setelah pg_advisory_unlock(15) dijalankan. Perhatikan: COMMIT saja tidak otomatis melepas advisory lock.

Fungsi Penting Advisory Locks

  • pg_advisory_lock(key) → ambil lock (akan menunggu kalau sudah dipakai orang lain).

  • pg_advisory_try_lock(key) → coba ambil lock, langsung gagal kalau sedang dipakai orang lain.

  • pg_advisory_unlock(key) → lepaskan lock spesifik.

  • pg_advisory_unlock_all() → lepaskan semua lock yang dimiliki session.

Contoh Kasus Real

Misalnya aplikasi butuh generate nomor invoice unik (mirip watermark table tadi), tapi ingin lebih fleksibel:

-- Session 1
BEGIN;
SELECT pg_advisory_lock(1001); -- lock "nomor invoice generator"
-- jalankan logika buat generate invoice
INSERT INTO invoice VALUES (...);
SELECT pg_advisory_unlock(1001);
COMMIT;

Session lain yang mencoba pg_advisory_lock(1001) harus menunggu sampai lock dilepas. Dengan cara ini, kita bisa memastikan tidak ada race condition saat bikin nomor invoice, tanpa mengunci tabel invoice.

Catatan Penting

  • Advisory locks cocok untuk kontrol concurrency antar aplikasi (misalnya job scheduler, queue worker, generator ID unik).

  • Karena tidak otomatis dilepas saat COMMIT, kita harus hati-hati agar tidak lupa unlock.

  • Jika aplikasi crash, PostgreSQL akan melepaskan lock saat koneksi database ditutup.

Jadi singkatnya:

  • Row/Table locks → untuk konsistensi data.

  • Advisory locks → untuk sinkronisasi aplikasi di atas database.

Berikut adalah penjelasan lengkap dan to-the-point dalam bahasa Indonesia mengenai "Optimizing Storage and Managing Cleanup" berdasarkan teks yang diberikan, mencakup semua aspek tanpa ada bagian yang terlewat, sesuai permintaan Anda:

Optimasi Penyimpanan dan Pengelolaan Pembersihan di PostgreSQL

Transaksi adalah bagian integral dari sistem PostgreSQL, namun memiliki konsekuensi tertentu. Salah satu konsekuensi adalah data yang dilihat oleh pengguna konkuren bisa berbeda, tergantung pada status transaksi. Selain itu, operasi seperti DELETE dan UPDATE tidak langsung menimpa data karena adanya kemungkinan ROLLBACK. Hal ini menyebabkan kebutuhan untuk pembersihan data (cleanup) dilakukan secara asinkronus. Berikut adalah penjelasan mendalam tentang cara PostgreSQL mengelola penyimpanan dan pembersihan data.

Pengenalan VACUUM

Transaksi di PostgreSQL menghasilkan "dead rows" (baris mati) yang tidak lagi relevan tetapi tetap ada di penyimpanan sampai dibersihkan. Operasi seperti DELETE atau UPDATE menciptakan baris mati karena data lama harus dipertahankan sampai transaksi selesai, untuk mendukung ROLLBACK atau visibilitas data bagi transaksi lain. Untuk menangani baris mati ini, PostgreSQL menyediakan perintah VACUUM.

Sintaks VACUUM (berdasarkan dokumentasi PostgreSQL):

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

Opsi yang tersedia:

  • FULL [boolean]: Menulis ulang seluruh tabel, mengunci tabel (locking), dan mengembalikan ruang ke sistem file.

  • FREEZE [boolean]: Membekukan tuple untuk mencegah wraparound ID transaksi.

  • VERBOSE [boolean]: Menampilkan laporan detail proses VACUUM.

  • ANALYZE [boolean]: Memperbarui statistik optimizer untuk perencanaan kueri.

  • DISABLE_PAGE_SKIPPING [boolean]: Memaksa VACUUM memeriksa semua halaman, bahkan yang sudah terlihat oleh semua transaksi.

  • SKIP_LOCKED [boolean]: Melewatkan tabel yang tidak bisa langsung dikunci untuk menghindari konflik.

  • INDEX_CLEANUP {AUTO | ON | OFF}: Mengontrol pembersihan indeks.

  • PROCESS_MAIN [boolean]: Memproses tabel utama.

  • PROCESS_TOAST [boolean]: Memproses tabel TOAST (untuk data besar seperti teks panjang).

  • TRUNCATE [boolean]: Mengontrol apakah ruang kosong di akhir tabel dikembalikan ke sistem file.

  • PARALLEL integer: Menentukan jumlah CPU core untuk VACUUM paralel.

  • SKIP_DATABASE_STATS [boolean]: Melewatkan pembaruan statistik database.

  • ONLY_DATABASE_STATS [boolean]: Hanya memperbarui statistik database.

  • BUFFER_USAGE_LIMIT size: Membatasi penggunaan buffer selama VACUUM.

Fungsi VACUUM:

  • Mengunjungi halaman yang berpotensi memiliki baris mati.

  • Menandai ruang kosong di Free Space Map (FSM) untuk digunakan kembali.

  • Biasanya tidak mengecilkan ukuran tabel, kecuali jika hanya ada baris mati di akhir tabel.

Catatan Penting:

  • VACUUM tidak mengembalikan ruang ke sistem file dalam kebanyakan kasus, tetapi memungkinkan ruang dalam tabel digunakan kembali.

  • Penyusutan tabel hanya terjadi jika baris mati berada di akhir tabel, yang jarang terjadi.

Konfigurasi VACUUM dan Autovacuum

Dulu, VACUUM harus dijalankan secara manual, tetapi sekarang PostgreSQL memiliki autovacuum, sebuah proses latar belakang yang otomatis menangani pembersihan. Autovacuum berjalan setiap menit (dikonfigurasi melalui autovacuum_naptime = 1 di postgresql.conf) dan memeriksa apakah ada pekerjaan pembersihan yang perlu dilakukan. Jika ada, autovacuum dapat memulai hingga tiga proses pekerja (autovacuum_max_workers).

Kapan autovacuum berjalan? Autovacuum dipicu berdasarkan pengaturan berikut di postgresql.conf:

  • autovacuum_vacuum_threshold = 50: Minimal 50 baris mati diperlukan untuk memicu VACUUM.

  • autovacuum_analyze_threshold = 50: Minimal 50 baris untuk memperbarui statistik optimizer.

  • autovacuum_vacuum_scale_factor = 0.2: VACUUM berjalan jika 20% data tabel berubah.

  • autovacuum_analyze_scale_factor = 0.1: Statistik diperbarui jika 10% data berubah.

  • autovacuum_vacuum_insert_threshold = 1000: Mulai PostgreSQL 13, autovacuum juga dipicu oleh INSERT (jika lebih dari 1000 baris ditambahkan) untuk menangani beban kerja hanya INSERT.

Catatan:

  • Autovacuum tidak langsung membuat proses pekerja; ia meminta proses utama untuk melakukannya demi menghindari zombie processes dan meningkatkan keandalan.

  • Autovacuum biasanya juga memperbarui statistik optimizer selama proses VACUUM untuk menghemat waktu.

Masalah Wraparound Transaksi

PostgreSQL menggunakan Transaction ID (XID) untuk mengelola konkurensi. Setiap transaksi diberi ID unik, tetapi ID ini terbatas (bukan tak terbatas). Ketika ID transaksi mendekati batas maksimum, PostgreSQL harus mencegah transaction ID wraparound agar sistem tetap berfungsi dengan benar.

Bagaimana cara kerja wraparound?

  • PostgreSQL menggunakan watermark untuk melacak urutan transaksi.

  • VACUUM menyesuaikan watermark ini dengan "membekukan" tuple (mengatur relfrozenxid di pg_class) sehingga transaksi lama tidak lagi relevan.

  • Pengaturan penting di postgresql.conf:

    • autovacuum_freeze_max_age = 200000000: Batas usia transaksi sebelum VACUUM dipaksa untuk mencegah wraparound.

    • autovacuum_multixact_freeze_max_age = 400000000: Batas usia multixact ID sebelum VACUUM dipaksa.

Mengapa ini penting?

  • Jika watermark tidak disesuaikan, PostgreSQL bisa kehabisan ID transaksi, menyebabkan database berhenti berfungsi.

  • VACUUM memastikan ada cukup ID transaksi untuk masa depan.

  • Commit Log (clog), yang menyimpan status transaksi (running, aborted, committed), juga dibersihkan oleh VACUUM untuk mencegah penumpukan.

Catatan:

  • Transaksi yang hanya membaca tidak meningkatkan XID (menggunakan virtual transaction ID), sehingga menghemat ID transaksi.

  • Membekukan tuple (freezing) adalah proses penting untuk performa, yang akan dibahas lebih lanjut pada bab tentang optimasi kueri.

VACUUM FULL

Selain VACUUM biasa, ada VACUUM FULL, yang menulis ulang seluruh tabel dan mengembalikan ruang ke sistem file. Namun, ada kelemahan besar:

  • VACUUM FULL mengunci tabel, mencegah operasi tulis selama proses berlangsung.

  • Pada tabel besar, ini bisa menyebabkan downtime yang signifikan, sehingga pengguna mungkin mengira database mati.

  • Alternatif: Gunakan ekstensi seperti pg_squeeze (https://www.cybertec-postgresql.com/en/introducing-pg_squeeze-a-postgresql-extension-to-auto-rebuild-bloated-tables/), yang menulis ulang tabel tanpa mengunci operasi tulis.

Rekomendasi:

  • Hindari VACUUM FULL kecuali benar-benar diperlukan, terutama pada tabel besar.

  • Gunakan VACUUM biasa atau alat seperti pg_squeeze untuk performa lebih baik.

Melihat VACUUM Bekerja

Untuk memahami cara kerja VACUUM, berikut adalah contoh praktis:

Membuat dan mengisi tabel:

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);

  • Tabel t_test berisi 100.000 baris.

  • autovacuum_enabled = off memastikan tidak ada pembersihan otomatis, sehingga kita bisa mengontrol proses VACUUM.

Memeriksa ukuran tabel:

SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 3544 kB

  • pg_relation_size mengembalikan ukuran tabel dalam byte.

  • pg_size_pretty mengonversi ke format yang mudah dibaca (kB, MB, dll.).

Melakukan UPDATE:

UPDATE t_test SET id = id + 1;

  • Operasi UPDATE menyalin semua baris karena data lama harus dipertahankan untuk konkurensi dan kemungkinan ROLLBACK.

  • Ukuran tabel bertambah:

SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 7080 kB

Menjalankan VACUUM:

VACUUM t_test;

  • Ukuran tabel tetap sama (7080 kB) karena VACUUM tidak mengembalikan ruang ke sistem file, tetapi menandai ruang kosong di FSM untuk digunakan kembali.

  • Update berikutnya tidak akan menambah ukuran tabel karena ruang kosong digunakan kembali:

UPDATE t_test SET id = id + 1;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 7080 kB

  • Namun, update ketiga akan meningkatkan ukuran tabel karena ruang kosong sudah habis:

UPDATE t_test SET id = id + 1;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 10 MB

Menghapus data:

DELETE FROM t_test WHERE id > 99000 OR id < 1000;
-- Menghapus 1999 baris
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 3504 kB

  • Ukuran tabel menyusut karena baris mati berada di akhir tabel, memungkinkan VACUUM mengembalikan ruang ke sistem file. Ini adalah kasus langka.

Melihat posisi fisik data:

SELECT ctid, * FROM t_test ORDER BY ctid DESC;

  • ctid menunjukkan posisi fisik baris di disk.

  • Data di akhir tabel mungkin teracak karena blok terakhir tidak penuh saat tabel pertama kali diisi, dan UPDATE mengisi blok tersebut.

Pelajaran Penting:

  • Memahami penyimpanan adalah kunci untuk performa dan administrasi database.

  • VACUUM biasanya tidak mengecilkan tabel, tetapi memungkinkan penggunaan kembali ruang kosong.

Fitur Lain VACUUM

VACUUM telah ditingkatkan dengan berbagai fitur untuk mendukung konkurensi dan performa:

  • DISABLE_PAGE_SKIPPING: Memaksa VACUUM memeriksa semua halaman, bahkan yang sudah terlihat oleh semua transaksi. Berguna untuk pembersihan menyeluruh.

  • SKIP_LOCKED: Melewatkan tabel yang tidak bisa langsung dikunci, meningkatkan konkurensi pada sistem dengan banyak transaksi.

  • INDEX_CLEANUP: Mengontrol pembersihan indeks. Menonaktifkan pembersihan indeks bisa mempercepat VACUUM dalam situasi kritis (misalnya, untuk mencegah wraparound), tetapi jarang digunakan.

  • PROCESS_TOAST: Memungkinkan melewatkan pembersihan tabel TOAST (untuk data besar). Jarang diperlukan.

  • TRUNCATE: Mengontrol apakah ruang di akhir tabel dikembalikan ke sistem file (default: true).

  • PARALLEL: Memungkinkan VACUUM menggunakan beberapa CPU core untuk tabel besar, meningkatkan kecepatan.

Ringkasan

Optimasi penyimpanan dan pengelolaan pembersihan di PostgreSQL bergantung pada pemahaman transaksi, konkurensi, dan VACUUM. Berikut poin-poin utama:

  • Transaksi menciptakan baris mati yang perlu dibersihkan secara asinkronus oleh VACUUM.

  • Autovacuum otomatis menangani pembersihan dan pembaruan statistik, dengan konfigurasi seperti autovacuum_vacuum_scale_factor dan autovacuum_freeze_max_age.

  • VACUUM tidak mengembalikan ruang ke sistem file kecuali baris mati berada di akhir tabel.

  • Wraparound transaksi dicegah dengan membekukan tuple dan menyesuaikan watermark.

  • VACUUM FULL harus dihindari pada tabel besar karena mengunci tabel; gunakan alternatif seperti pg_squeeze.

  • Fitur seperti SKIP_LOCKED, PARALLEL, dan INDEX_CLEANUP meningkatkan fleksibilitas dan performa VACUUM.

Apa Itu Wraparound di PostgreSQL?

Wraparound (atau transaction ID wraparound) adalah masalah potensial di sistem database PostgreSQL yang terjadi ketika nomor identitas transaksi (Transaction ID atau XID) mencapai batas maksimum dan mulai "berputar" kembali dari awal. Ini seperti odometer mobil yang reset ke nol setelah mencapai 999.999 km—bukan berarti mobil mundur, tapi urutan waktu bisa menjadi ambigu. Wraparound bisa menyebabkan kegagalan serius pada database jika tidak ditangani dengan benar. Berikut penjelasan lengkap dan to-the-point:

1. Konteks Dasar: Bagaimana Transaction ID Bekerja di PostgreSQL

  • PostgreSQL menggunakan Transaction ID (XID) sebagai nomor unik untuk setiap transaksi yang memodifikasi data (seperti INSERT, UPDATE, DELETE). XID ini digunakan untuk mengelola konkurensi (multi-user access) dan visibilitas data.

    • Contoh: Jika transaksi Anda punya XID 4711 dan transaksi lain punya XID 4712, transaksi 4711 tidak bisa melihat data dari 4712 karena 4712 masih berjalan.

    • Jika XID Anda 4711 dan transaksi lain adalah 3900 (lebih lama), Anda bisa melihat data tersebut.

  • XID terbatas: PostgreSQL menggunakan 32-bit integer, sehingga maksimal sekitar 4 miliar XID (tepatnya 2^32 - 1). Ini cukup besar, tapi bisa habis dalam beban kerja tinggi (misalnya, ribuan transaksi per detik).

  • Tidak semua transaksi membakar XID: Transaksi baca-only (SELECT) hanya punya virtual XID, jadi XID hanya bertambah untuk transaksi yang menulis data.

2. Bagaimana Wraparound Terjadi?

  • XID diberikan secara berurutan dan monotonik (selalu naik), tapi ketika mencapai batas maksimum (sekitar 4 miliar), sistem mulai "wrap around" dan kembali ke 1.

  • Masalahnya: PostgreSQL membedakan urutan transaksi berdasarkan perbandingan XID (mana yang lebih baru). Setelah wraparound, XID baru (misalnya 5) bisa terlihat lebih tua daripada XID lama (misalnya 3.999.999.999).

    • Contoh sederhana:

      • Transaksi A: XID = 800.000.000 (lama, sudah committed).

      • Transaksi B: XID = 4.000.000.000 (baru, masih berjalan).

      • Setelah wraparound: Transaksi C: XID = 5 (seharusnya baru, tapi terlihat lebih tua dari A).

    • Tanpa mekanisme pelindung, PostgreSQL bisa salah mengasumsikan urutan waktu, menyebabkan data tidak konsisten atau transaksi gagal.

  • PostgreSQL mencegah kebingungan ini dengan watermark (penanda batas aman) di katalog sistem seperti pg_class:

    • relfrozenxid: Watermark untuk XID biasa.

    • relminmxid: Watermark untuk multixact ID (ID untuk operasi multi-transaksi, seperti locking bersama).

3. Dampak Wraparound Jika Tidak Ditangani

  • Pencegahan otomatis: PostgreSQL akan mengunci database (menolak transaksi baru) jika watermark mendekati batas wraparound, untuk menghindari kegagalan total.

  • Downtime: Database bisa "beku" selama berjam-jam atau hari, tergantung ukuran data. Pada tabel besar, ini berarti aplikasi tidak bisa menulis data.

  • Risiko lain:

    • Commit Log (CLOG) membengkak karena menyimpan status setiap XID (running, committed, aborted).

    • Statistik optimizer tidak akurat, memengaruhi performa kueri.

  • Dalam kasus ekstrem, database bisa crash atau memerlukan restorasi dari backup.

4. Cara Mencegah Wraparound: Peran VACUUM dan Autovacuum

  • VACUUM adalah "pembersih" utama yang menyesuaikan watermark dengan membekukan tuple (freezing):

    • VACUUM memindai baris mati (dead rows) dan mengubah tuple lama menjadi "beku" (tidak lagi bergantung pada XID lama).

    • Ini memindahkan watermark maju, memastikan XID baru tetap "lebih tua" dari yang lama secara logis.

  • Autovacuum (proses otomatis) menjalankan VACUUM secara berkala untuk mencegah wraparound:

    • Pengaturan kunci di postgresql.conf:

      • autovacuum_freeze_max_age = 200000000 (default): Jika usia XID tabel melebihi ini, VACUUM dipaksa untuk membekukan tuple dan mencegah wraparound.

      • autovacuum_multixact_freeze_max_age = 400000000 (default): Sama untuk multixact ID.

    • Autovacuum memantau usia XID setiap menit dan memulai pekerja jika diperlukan.

  • Tips pencegahan:

    • Pantau dengan query: SELECT datname, age(datfrozenxid) FROM pg_database; (usia XID database dalam transaksi).

    • Jika mendekati batas (misalnya > 1 miliar), tingkatkan frekuensi autovacuum atau jalankan VACUUM manual.

    • Hindari menonaktifkan autovacuum kecuali untuk tabel sementara (seperti staging di data warehouse).

    • Pada versi PostgreSQL terbaru (seperti 17), fitur seperti autovacuum_vacuum_insert_threshold membantu menangani beban INSERT-only.

5. Contoh Praktis

Bayangkan tabel dengan 1 miliar transaksi:

  • XID awal: 1 hingga 1.000.000.000.

  • Setelah VACUUM rutin, watermark naik ke 900.000.000 → XID lama diabaikan.

  • Tanpa VACUUM: Saat XID mencapai 4 miliar, wraparound terjadi → database lock untuk VACUUM paksa (bisa memakan waktu lama pada tabel besar).

Ringkasan

Wraparound adalah risiko kegagalan database PostgreSQL akibat keterbatasan Transaction ID yang "berputar" setelah batas maksimum. Ini ditangani secara proaktif oleh VACUUM dan autovacuum, yang membekukan tuple lama dan menyesuaikan watermark untuk menjaga urutan waktu transaksi tetap aman. Pemantauan rutin dan konfigurasi yang tepat (seperti autovacuum_freeze_max_age) mencegah downtime. Ini adalah konsep kunci untuk stabilitas jangka panjang, terutama pada database produksi dengan beban tinggi.

Read Phenomena

Dirty Read

  • Definisi: Ketika sebuah transaksi membaca data yang belum di-commit oleh transaksi lain.

  • Kapan terjadi: Pada isolation level READ UNCOMMITTED.

  • Contoh:

    • T1 update saldo user A dari 100 → 50 (belum commit).

    • T2 membaca saldo A dan melihat 50.

    • T1 kemudian rollback, saldo A kembali 100.

    • T2 sudah membaca data "kotor" (dirty).

Non-Repeatable Read

  • Definisi: Ketika sebuah transaksi membaca baris yang sama lebih dari sekali, tapi hasilnya berbeda karena ada transaksi lain yang mengubah atau menghapus baris itu lalu commit.

  • Kapan terjadi: Pada isolation level READ COMMITTED.

  • Contoh:

    • T1 baca saldo user A, hasil = 100.

    • T2 update saldo A jadi 200 lalu commit.

    • T1 baca lagi saldo A, hasil = 200 (berbeda dari sebelumnya).

    • Data yang dibaca tidak konsisten antar read dalam transaksi T1.

Phantom Read

  • Definisi: Ketika sebuah transaksi melakukan query dengan kondisi (WHERE clause) yang sama lebih dari sekali, tetapi hasil jumlah baris yang dikembalikan berbeda karena transaksi lain menambahkan atau menghapus baris baru yang memenuhi kondisi.

  • Kapan terjadi: Pada isolation level REPEATABLE READ.

  • Contoh:

    • T1: SELECT * FROM orders WHERE amount > 100 → dapat 5 baris.

    • T2: Insert order baru dengan amount = 150, lalu commit.

    • T1 jalankan query yang sama lagi → dapat 6 baris.

    • Muncul phantom row (baris hantu) yang sebelumnya tidak ada.

Lost Update

  • Definisi: Ketika dua transaksi meng-update data yang sama, dan salah satu update tertindih (hilang) karena transaksi lain overwrite hasilnya.

  • Kapan terjadi: Biasanya di isolation level rendah, tanpa locking eksplisit.

  • Contoh:

    • T1 baca saldo A = 100, lalu kurangi jadi 90.

    • T2 juga baca saldo A = 100, lalu kurangi jadi 80.

    • Keduanya commit, saldo A akhirnya 80 (update T1 hilang).

Ringkasan Isolation Level dan Anomalinya

Isolation Level
Dirty Read
Non-Repeatable Read
Phantom Read

Read Uncommitted

✅ Bisa

✅ Bisa

✅ Bisa

Read Committed

❌ Tidak

✅ Bisa

✅ Bisa

Repeatable Read

❌ Tidak

❌ Tidak

✅ Bisa

Serializable

❌ Tidak

❌ Tidak

❌ Tidak

Jadi intinya:

  • Dirty Read → baca data yang belum commit.

  • Non-Repeatable Read → baca baris yang sama, hasil berubah.

  • Phantom Read → baca query dengan kondisi sama, jumlah baris berubah.

  • Lost Update → hasil update hilang karena tabrakan.

1. "Row-level locks are acquired when rows are being updated (or deleted or marked for update)."

Artinya:

  • Begitu ada operasi UPDATE atau DELETE, PostgreSQL otomatis memasang row-level exclusive lock pada baris yang ditarget.

  • Bahkan pada kasus SELECT ... FOR UPDATE, row juga dianggap “marked for update” sehingga PostgreSQL pasang lock juga.

Contoh:

-- Session 1
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- row id=1 terkunci

👉 Baris id=1 sekarang di-lock, tidak bisa diubah transaksi lain sampai COMMIT/ROLLBACK.

2. "Row-level locks don't affect data querying."

Maksudnya:

  • Lock ini hanya menghalangi transaksi lain yang ingin mengubah row (UPDATE, DELETE, SELECT ... FOR UPDATE/SHARE).

  • SELECT biasa (read-only) tidak terganggu, karena PostgreSQL menggunakan MVCC (Multi-Version Concurrency Control) → pembaca bisa tetap melihat versi row sesuai snapshot transaksinya.

Contoh:

-- Session 1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- row id=1 terkunci

-- Session 2
SELECT * FROM users WHERE id = 1;
-- tetap bisa baca (tidak blocking), hanya membaca snapshot

3. "They block writers to the same row only."

  • Row lock hanya berlaku per baris, bukan seluruh tabel.

  • Jadi jika satu row terkunci, transaksi lain masih bisa menulis row lain.

Contoh:

-- Session 1
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- lock hanya di row id=1

-- Session 2
UPDATE users SET balance = balance - 50 WHERE id = 2;
-- bisa jalan langsung, karena row berbeda

👉 Berbeda dengan table lock, di mana semua row di tabel ikut terdampak.

4. "PostgreSQL doesn't remember any information about modified rows in memory and so has no limit to the number of rows locked at one time."

Maksudnya:

  • PostgreSQL tidak punya in-memory lock table dengan ukuran terbatas (seperti database lama yang harus nyimpen daftar row lock di RAM).

  • Lock row di PostgreSQL di-embed langsung di row tuple (di heap page).

  • Dengan cara ini, PostgreSQL secara teori bisa mengunci jutaan row dalam satu transaksi tanpa ada limit “jumlah lock” di memori.

  • Tentu saja tetap ada overhead (WAL, MVCC cleanup, disk I/O), tapi tidak ada batas keras pada jumlah row lock.

Contoh:

-- Session 1
BEGIN;
UPDATE users SET balance = balance - 10;
-- misalnya ada 1 juta row, semuanya di-lock
-- PostgreSQL sanggup, karena lock tersimpan di row level (tidak ada cap di memory)

Jadi singkatnya:

  1. Row lock otomatis saat UPDATE/DELETE/SELECT ... FOR UPDATE.

  2. Tidak ganggu SELECT biasa, hanya memblokir transaksi yang mau menulis ke row sama.

  3. Scoped ke row, bukan ke tabel.

  4. Tidak ada batasan jumlah row lock, karena lock dicatat di row, bukan di memory global.

PreviousPostgres Roles dan PrivilegesNextIndexing

Last updated