MR Ferry

Isolation adalah kemampuan database untuk menghandle transaksi yang dilakukan lebih dari satu sesi secara bersamaan. Isolation adalah bahasan paling kompleks dibanding keyword ACID lainnya. Pada beberapa bahasa pemrograman atau framework by default Isolationnya mengikuti default Isolation dari database, tapi kita juga bisa mengganti Isolationnya pada masing-masing transaksinya. Seperti pada Java kita bisa set Transaction Isolation level pada class Connection. Atau kalau menggunakan framework seperti Spring Data JPA, bisa dengan mengisi value isolation pada annotasi @Transactional.

Secara umum Isolation memiliki 4 level:

• Read Uncommitted;
• Read Committed;
• Repeatable Read;
• Serializable;

Masing-masing level tersebut, secara umum memiliki efek samping:

• Dirty Read;
• Non-repeatable Read;
• Phantom Read;
• Lost Updates;
• Transaction Blocking;

Contoh kasusnya kita akan menggunakan tabel berikut:

Table fruit

Untuk melihat Isolation Level yang sedang berjalan di PostgreSql bisa eksekusi query berikut:

SHOW TRANSACTION ISOLATION LEVEL;

Kalau MySql query-nya seperti ini:

SELECT @@TX_ISOLATION;

Untuk mengganti Isolation Level di PostgreSql pada suatu sesi, misalnya diganti ke Serializable, bisa eksekusi command berikut:

BEGIN;

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

COMMIT;

Untuk MySql kurang lebih seperti ini:

BEGIN;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

COMMIT;

Untuk database lain tinggal disesuaikan aja syntax-nya. Atau kalau pakai tools Database seperti Datagrip di IntelliJ, di bagian atas Query Console ada pilihan Transaction Isolation, tinggal ganti aja di sana. Untuk tools lain tinggal sesuaikan aja settingannya. Oh ya, sebelum mempraktekkannya pastikan auto-commit pada tools database dimatikan, karena by default setiap command bakal auto-commit seperti pada Datagrip, MySql Workbench, PgAdmin, dll. Atau mempraktekkannya lewat command line saja secara manual.

Read Uncommitted

Artinya saat sesi A berlangsung, secara bersamaan sesi B melakukan perubahan yang belum di-commit, dan sesi A akan membaca perubahan tersebut. Contohnya seperti berikut:

Sesi A

BEGIN;

SELECT * FROM fruit f WHERE id = 1;

Sesi A dimulai dengan melakukan selection terhadap buah dengan id = 1. Lalu akan muncul data Apel dengan stok 20 buah.

Sesi B

BEGIN;

UPDATE fruit f SET stock = stock - 1 WHERE id = 1;

SELECT * FROM fruit f WHERE id = 1;

Pada sesi B, secara bersamaan terjadi update stok terhadap buah dengan id = 1 sebanyak 1 buah tanpa melakukan commit. Hasilnya stok apel jadi 19.

Sesi A

UPDATE fruit f SET stock = stock - 1 WHERE id = 1;

SELECT * FROM fruit f WHERE id = 1;

COMMIT;

Misalkan sesi A melakukan update yang sama, maka perubahan pada sesi B yang belum di-commit akan terbaca. Hasilnya stok buah pada sesi A akan berkurang menjadi 18 buah.

Dirty Read Phenomena

Kondisi di atas disebut Dirty Read. Permasalahannya adalah misalkan sesi B melakukan rollback di saat perubahan pada sesi A sudah terlanjur di-commit. Hal tersebut akan membuat data berantakan karena membaca perubahan yang belum jelas.

Read Committed

Ini adalah kebalikan dari Read Uncommitted, karena hanya membaca perubahan dari sesi lain yang sudah di-commit. Lalu kita coba lagi skenario di atas.

Sesi A

BEGIN;

SELECT * FROM fruit f WHERE id = 1;

Sesi B

BEGIN;

UPDATE fruit f SET stock = stock - 1 WHERE id = 1;

SELECT * FROM fruit f WHERE id = 1;

Sesi A

UPDATE fruit f SET stock = stock - 1 WHERE id = 1;

Sekarang, sesi A akan dikunci perubahannya karena ada perubahan data yang sama pada sesi B yang belum di-commit. Kuncinya akan dilepas saat transaksi perubahan pada sesi B di-commit atau rollback. Jadi Read Committed aman dari Dirty Read Phenomena😎.

Non-repeatable Read Phenomena

Read committed juga memiliki efek samping, yaitu Non-repeatable Read. Misalnya kita ingin menampilkan jumlah stok beserta list buah di bawahnya. Itu artinya kita akan melakukan query sum dari stok tersebut, dan query untuk menampilkan list buah. Lalu secara bersamaan diantara query tersebut terjadi perubahan stok yang di-commit oleh sesi lain. Permasalahannya adalah hasil query pertama dan query kedua pada sesi yang sama jadi berbeda. Contohnya seperti berikut:

Sesi A

BEGIN;

SELECT sum(stock) FROM fruit f WHERE expiring_date >= '2023-12-01'; 

Pada sesi A, kita melakukan query jumlah stok buah yang expire setelah Desember 2023, hasilnya adalah 65, karena pada tabel di atas stok nangka adalah 15 dan stok mangga adalah 50.

Sesi B

BEGIN;

UPDATE fruit f SET stock = 60 WHERE name = 'mangga';

COMMIT;

Di saat bersamaan sebelum sesi A melakukan query kedua, terjadi update stok mangga menjadi 60 dan di-commit oleh sesi B.

Sesi A

SELECT * FROM fruit f WHERE expiring_date >= '2023-12-01';

Saat sesi A melakukan query kedua, maka akan didapatkan data stok nangka sebanyak 15 dan stok mangga sebanyak 60. Ini tentu nggak nyambung dengan query pertama yang menyatakan bahwa jumlah stok keduanya adalah 65. User jadi bingung, “di bagian jumlah stoknya 65, tapi kok di listnya kalau dihitung manual 75?”😕.

Repeatable Read

Ini maksudnya database akan membaca kembali data yang pernah dibaca sebelumnya pada sesi yang sama. Misalkan skenario pada Non-repeatable Read di atas kita ulangi menggunakan Repeatable Read:

Sesi A

BEGIN;

SELECT sum(stock) FROM fruit f WHERE expiring_date >= '2023-12-01'; 

Sesi B

BEGIN;

UPDATE fruit f SET stock = 60 WHERE name = 'mangga';

COMMIT;

Sesi A

SELECT * FROM fruit f WHERE expiring_date >= '2023-12-01';

Sekarang hasil query pertama dan query kedua pada sesi A sudah nyambung dan ga membingungkan user. Jadi Repeatable Read ini aman dari Non-repeatable Read Phenomena😎.

Phantom Read Phenomena

Repeatable Read juga memiliki Read Phenomena pada beberapa database, yaitu Phantom Read. Jika Non-repeatable Read terkait update data, maka Phantom Read terkait insertion atau deletion. Seperti kasus berikut:

Sesi A

BEGIN;

SELECT * FROM fruit f WHERE expiring_date BETWEEN '2023-03-01' AND '2023-03-31';

Pada sesi A akan dilakukan selection buah yang expire pada bulan maret. Berdasarkan tabel di atas, buah yang expire pada bulan maret hanya jeruk dengan stok sebanyak 5 buah.

Sesi B

BEGIN;

SELECT * FROM fruit f WHERE expiring_date BETWEEN '2023-03-01' AND '2023-03-31';

INSERT INTO fruit (name, stock, expiring_date)
VALUES ('jambu', 5, '2023-03-01');

COMMIT;

Pada sesi B, disaat bersamaan juga dilakukan selection buah yang expire pada bulan Maret, serta ada penambahan data baru yang di-commit, yaitu jambu dengan stok sebanyak 5 buah dan expire di bulan Maret.

Sesi A

INSERT INTO fruit (name, stock, expiring_date)
VALUES ('pepaya', 5, '2023-03-01');

SELECT sum(stock) FROM fruit f WHERE expiring_date BETWEEN '2023-03-01' AND '2023-03-31';

Lalu, pada sesi A, juga terjadi penambahan data baru, yaitu pepaya sebanyak 5 buah yang expire di bulan Maret. Lalu dihitung jumlah stok buah yang expire di bulan Maret setelah penambahan buah pepaya tadi. Ekspektasi sesi A harusnya 10 karena stok sebelumnya 5 jeruk, ditambah 5 pepaya. Tapi hasilnya malah 15, karena sesi A melakukan sinkronisasi dengan committed data dari transaksi lain saat melakukan insertion, sehingga transaksi sesi B ikut terbaca. Inilah yang disebut Phantom Read.

Yang perlu diperhatikan, behavior Repeatable Read pada beberapa database berbeda. Phantom Read seperti di atas adalah behavior yang bisa terjadi pada MySql. Sedangkan pada PostgreSql, hasilnya jadi 10 karena Repeatable Read pada PostgreSql ga akan melakukan sinkronisasi dengan committed data dari transaksi lain saat melakukan insertion.

Serializable

Ini adalah level tertinggi, dimana transaksi data yang sama hanya bisa dilakukan oleh satu sesi saja, sedangkan sesi lainnya hanya read-only dan transaksinya diblok. Sekarang skenario Phantom Read di atas kita ulangi menggunakan Serializable Isolation:

Sesi A

BEGIN;

SELECT * FROM fruit f WHERE expiring_date BETWEEN '2023-03-01' AND '2023-03-31';

Sesi B

BEGIN;

SELECT * FROM fruit f WHERE expiring_date BETWEEN '2023-03-01' AND '2023-03-31';

INSERT INTO fruit (name, stock, expiring_date)
VALUES ('jambu', 5, '2023-03-01');

COMMIT;

Bagi database yang memakai 2 Phase Commit seperti MySql, insertion pada sesi B akan dikunci hingga sesi A melakukan commit atau rollback meskipun sesi A hanya Read-only. Sedangkan bagi database yang memakai Snapshot seperti PostgreSql sesi B masih bisa melakukan insertion, tapi insertion pada sesi A yang akan diblok.

Sesi A

INSERT INTO fruit (name, stock, expiring_date)
VALUES ('pepaya', 5, '2023-03-01');

Sekarang transaksinya diblok. Untuk melanjutkannya sesinya harus di-abort dan diulangi dari awal. Sehingga tidak ada Phantom Read karena Serializable terbebas dari Phenomena apapun😎.

Transaction Blocking

Serializable juga ada efek sampingnya, yaitu performa database jadi lebih berat karena harus memastikan hanya ada satu transaksi yang dieksekusi pada suatu data dan memblok transaksi lain.

Verdict

Itulah masing-masing keempat Isolation beserta efek sampingnya. Read Uncommitted adalah Isolation paling jelek karena bisa membuat data berantakan. Pada beberapa database, Read Uncommitted sudah dihapus. Salah satu database yang masih memiliki Read Uncommitted Isolation adalah MySql. Read Committed adalah default Isolation pada sebagian besar database, seperti PostgreSql dan Oracle. Sedangkan pada MySql default Isolationnya adalah Repeatable Read. Repeatable Read cocok untuk kasus yang membutuhkan selection berkali-kali dalam satu sesi agar data yang ditampilkan nyambung. Tidak semua database mengalami Phantom Read Phenomena saat menggunakan Repeatable Read, contohnya PostgreSql. Serializable adalah Isolation paling aman dari berbagai phenomena, tapi tidak semua kasus cocok menerapkan Serializable karena secara performa lebih berat, semuanya tergantung kasus yang dihadapi. Selain Read Phenomena, ada juga efek samping lain yang juga dapat mengakibatkan anomaly data, yaitu Lost Updates yang akan dijelaskan pada tulisan selanjutnya.

Untuk melihat tulisan terkait ACID lainnya bisa dibaca pada link berikut:

• Atomicity
• Consistency
• Durability