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;
• Read Skew;
• Write Skew;
• 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 @@SESSION.TX_ISOLATION; # mysql 5
SELECT @@SESSION.transaction_isolation; # mysql 8

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

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Untuk MySql kurang lebih seperti ini:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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 nonaktif.

Read Uncommitted artinya adalah 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.

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 adalah kebalikan dari Read Uncommitted, yaitu 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😎.

Read committed juga memiliki efek samping, yaitu Non-Repeatable Read atau disebut juga Fuzzy 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 60 + 15 = 75?”😕.

Selain Non-Repeatable Read, Read Committed juga memiliki efek samping lainnya, yaitu Read Skew. Misalnya sesi A melakukan selection pada suatu tabel yang memiliki relasi dengan dengan tabel lain. Lalu di sesi B terjadi perubahan pada tabel relasi. Sehingga ketika sesi A melakukan query pada tabel relasi akan membaca perubahan yang dilakukan sesi B. Misalnya contoh tabelnya seperti ini:

Table fruit

Table category

Buah apel pada tabel fruit memiliki relasi dengan tabel category pada kolom category_id dengan id = 1.

Sesi A

BEGIN;

SELECT name, category_id FROM fruit f where id = 1;

Pada sesi A kita melakukan query ke tabel fruit dengan id = 1 dan menampilkan data buah apel dengan category_id = 1.

Sesi B

BEGIN;

UPDATE category c SET name = 'Monokotil' WHERE id = 1;
UPDATE fruit f SET name = 'salak' WHERE id = 1;

COMMIT;

Di saat bersamaan Sesi B melakukan update category dengan mengubah nama category pada id = 1 menjadi 'Monokotil' dan tabel fruit dengan id = 1 menjadi 'salak' lalu di-commit.

Sesi A

SELECT * FROM category c where id = 1;

Sesi A melakukan query pada tabel category dengan id sesuai category_id dari hasil query sebelumnya pada tabel fruit.

Di sini akan terjadi Read Skew karena category name dan fruit name telah diganti oleh Sesi B. Sedangkan user pada Sesi A mendapati hasil query tabel fruit dengan nama 'apel', tapi saat query ke tabel category berdasarkan category_id dari hasil query sebelumnya, malah mendapati category dengan nama 'Monokotil'. Tentu itu akan membingungkan user pada Sesi A karena apel bukan monokotil. Dia tidak menyadari ada perubahan di antara dua query yang dilakukan.

Repeatable Read maksudnya adalah database akan membaca kembali data yang pernah dibaca sebelumnya beserta relasinya pada sesi yang sama sehingga Non-Repeatable Read dan Read Skew tidak akan terjadi di sini. 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. Repeatable Read ini aman dari Non-Repeatable Read Phenomena😎.

Kita reproduce juga Read Skew di atas menggunakan Repeatable Read.

Sesi A

BEGIN;

SELECT name, category_id FROM fruit f where id = 1;

Sesi B

BEGIN;

UPDATE category c SET name = 'Monokotil' WHERE id = 1;
UPDATE fruit f SET name = 'salak' WHERE id = 1;

COMMIT;

Sesi A

SELECT * FROM category c where id = 1;

Repeatable Read ini juga aman dari Read Skew😎.

Repeatable Read juga memiliki Read Phenomena pada beberapa database, yaitu Phantom Read. Phantom Read adalah kondisi ketika terdapat data "hantu" yang baru masuk atau menghilang setelah query pertama👻. 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. Postgresql menggunakan Snapshot Isolation yang lebih canggih pada Repeatable Read sehingga ga akan ada sinkronisasi hingga melakukan commit.

Serializable adalah level tertinggi, dimana transaksi dalam satu kelompok eksekusi data 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 menerapkan 2 Phase Locking Serializable 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 menerapkan Snapshot Serializable Isolation (SSI) 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😎.

Serializable juga ada efek sampingnya, yaitu performa database jadi lebih berat karena harus memastikan hanya ada satu transaksi yang dieksekusi pada sekelompok data dan memblok transaksi lain jika konflik. Kalau transaksinya ada 10 yang konflik, berarti hanya 1 yang diproses sedangkan 9 sisanya harus ngantri giliran satu-persatu. Selain itu masing-masing database memiliki implementasi berbeda, pastikan sebelum menggunakannya untuk memahami behavior Serializable pada database yang digunakan. Ini hanyalah jalan terakhir untuk kasus yang sangat spesifik. Hindari transaksi yang panjang karena sangat berpotensi terjadi deadlock atau timeout, jadi pastikan alur transaksinya diatur dengan efisien. Bagi database yang menerapkan 2 phase locking, melakukan select menggunakan where clause Primary Key atau Unique Key dengan result data kosong dapat nge-blok insertion pada range index suatu tabel sehingga transaksi lainnya jadi ga bisa insert ke tabel itu meski datanya ga berhubungan. Jadi pertimbangkan juga penggunaan select sebelum insert yang berpotensi lock yang tidak diinginkan.

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 karena menerapkan Snapshot Isolation. 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, yaitu Update Anomaly seperti Write Skew & Lost Updates. Secara umum, Repeatable Read biasanya digunakan ketika transaksi yang dilakukan dalam satu sesi hanya read-only. Sedangkan kalau dalam sesi tersebut ada perubahan data, biasanya yang digunakan adalah Read Committed dengan Version Locking. Tapi semuanya kembali lagi tergantung kebutuhan bisnis. Untuk melihat tulisan terkait ACID lainnya bisa dibaca pada tulisan sebelumnya.