> For the complete documentation index, see [llms.txt](https://lab-sammi.gitbook.io/me/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lab-sammi.gitbook.io/me/understanding-transactions-and-locking.md).
# 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();`**
```sql
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**
```sql
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:
```sql
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`**
```sql
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:
```sql
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`**
```sql
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**
```sql
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**
```sql
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:
```sql
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**
```sql
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**
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
-- 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):**
```sql
BEGIN;
SELECT sum(balance) FROM t_account; -- hasil 300
-- (sementara transaksi 2 jalan)
SELECT sum(balance) FROM t_account; -- hasil 400
COMMIT;
```
**Transaction 2:**
```sql
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):**
```sql
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:**
```sql
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:
```sql
CREATE TABLE t_deadlock (id int);
INSERT INTO t_deadlock VALUES (1), (2);
```
**Transaction 1:**
```sql
BEGIN;
UPDATE t_deadlock SET id = id * 10 WHERE id = 1;
UPDATE t_deadlock SET id = id * 10 WHERE id = 2;
```
**Transaction 2:**
```sql
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:**
```sql
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t_deadlock; -- lihat id=1,2
DELETE FROM t_deadlock; -- mencoba delete
```
**Transaction 2:**
```sql
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:**
```sql
BEGIN;
SELECT pg_advisory_lock(15);
-- berhasil dapat lock
```
**Transaction 2:**
```sql
BEGIN;
SELECT pg_advisory_lock(15);
-- akan menunggu sampai T1 melepas lock
```
**Transaction 1 lanjut:**
```sql
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:
```sql
-- 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):
```sql
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** (), 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**:
```sql
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**:
```sql
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**:
```sql
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:
```sql
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 7080 kB
```
**Menjalankan VACUUM**:
```sql
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:
```sql
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:
```sql
UPDATE t_test SET id = id + 1;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Hasil: 10 MB
```
**Menghapus data**:
```sql
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**:
```sql
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:
```sql
-- 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:
```sql
-- 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:
```sql
-- 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:
```sql
-- 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.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://lab-sammi.gitbook.io/me/understanding-transactions-and-locking.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.