ISOLATION LEVEL di MYSQL

Dalam menyambut pembuatan program program MLM yang bernama **** di kantor saya yang bakal menjadi tandingan situs aplikasi binary nya DBS ,
saya sempat berdiksusi ama rekan kerja dan juga Master MySQL kita bung rizky prihanto, yang kebetulan hari ini lagi jadi pembicara bagi komunitas mysql. ada beberapa hal menarik

Dulu bung rizky pernah bilang kepada daku mengenai mekanisme transaksi di mysql untuk progam **** yang kita sedang develop ini. Kalau tidak salah, dulu kami sempat di bahas mengenai mekanisme locking table, di mana default dari INNODB adalah kalo tabel itu di select maka akan secara otomatis tabel tersebut locking.

Coba baca manual mysql dikit dikit, ternyata bener ketika di select dulu, maka tabel itu akan ke lock tapi ... bagi temen temen yang belum tau,
patut di ketahui bahwa mekanisme itu terjadi dalam ruang lingkup :

1. statement transaction
2. tabel yang ke lock itu tergantung pada tipe isolation levelnya, pada kasus isolation level repeatble read, read commited dan unread commited, operasi select tidak akan melock table, tapi operasi update otomatis akan melocking tabel , nah pada isolation level serializable, operasi select akan melakukan locking table.
   

oke muncul kata kata aneh ya.

1. START TRANSACTION
2. COMMIT
3. SAVEPOINT
4. ROLLBACK TO SAVEPOINT
5. ROLLBACK
6. INSERT DELAYED
7. SNAPSHOT
8. SERIALIZABLE
9. TRANSACTION ISOLATION LEVEL

nah email ini ... akan membahas beberapa di antaranya yaitu START TRANSACTION, COMMIT, ROLLBACK dan TRANSACTION ISOLATION LEVEL.

TRANSACTION ISOLATION LEVEL

seperti yang dulu pernah di bahas sama master DBMS kita di stt telkom bapak dhinta darmontoro, dan bagi kelas malam yang sekelas sama aku, pasti gak asing sama istilahh ini, karena dulu ada kerja lab nya ... ya salah satunya mendemo kan mekanisme isolation level, tapi pada waktu itu menggunakan ORACLE, bukan MYSQL. Bagaimana dengan MYSQL, gak jauh berbeda ternyata

ISOLATION LEVEL TERBAGI MENJADI

1. READ COMMITED,
2. UNREAD COMMITED,
3. REPEATABLE READ
4. SERIALIZABLE

Nah kita coba satu satu aja ya ...

kita mulai dari default nya INNODB dulu

REPEATABLE READ

kita liat dulu dengan menuliskan sintaks berikut ini

SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

ya sudah REPEATABLE READ

aku coba buka 2 console di kubuntu ku agar bisa menjalankan 2 transaksi yang berbeda.

REPEATABLE READ KASUS 1

Transaksi 1 :

trx1 mysql> begin;
Query OK, 0 rows affected (0.00 sec)

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

trx1 mysql> update mysaldo set saldo = saldo + 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 200 |
+-------+
1 row in set (0.00 sec)

Transaksi 2 :

trx2 mysql> begin;
Query OK, 0 rows affected (0.00 sec)

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

kita kembali dulu ke Transaksi 1

kemudian kita lakukan commit di Transaksi 1

trx1 mysql> commit;
Query OK, 0 rows affected (0.00 sec)

lalu kita kembali ke Transaksi 2 dan lakukan select lagi

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

masih 100, karena apa karena di transaksi 2 kita belum melakukan commit
nah inilah yang di sebut REPATABLE READ
coba lakukan commit ...

trx2 mysql> commit;
Query OK, 0 rows affected (0.00 sec)

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 200 |
+-------+
1 row in set (0.00 sec)

REPEATABLE READ KASUS 2

Transaksi 1 :

trx1 mysql> begin;
Query OK, 0 rows affected (0.00 sec)


trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

trx1 mysql> update mysaldo set saldo = saldo - 50;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 50 |
+-------+
1 row in set (0.00 sec)

NAH SKARANG KITA LIHAT TRANSAKSI 2 YA ...

trx2 mysql> begin;
Query OK, 0 rows affected (0.00 sec )

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

trx2 mysql> update mysaldo set saldo = saldo + 200;

DIA AKAN MENUNGGU TRANSAKSI 1 MELAKUKAN COMMIT DULU, JADI KONDISINYA TIDAK LANGSUNG MUNCUL PESAN
mysql> Query OK, 1 row affected (44.27 sec)

NAH SETELAH aku masuk lagi ke Transaksi 1 dan melakukan

trx1 mysql> commit;
Query OK, 0 rows affected (0.01 sec)

MAKA di TRANSAKSI 2
baru muncul

trx2 Query OK, 1 row affected (44.27 sec)
Rows matched: 1 Changed: 1 Warnings: 0

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 250 |
+-------+
1 row in set (0.00 sec)

JADI TERNYATA REPEATABLE READ , data yang di baca selalu konsisten , tidak mungkin menghasilkan result/nilai yang berbeda, inti dari repeatble read ini adalah result select selalu konsisten, bukan begitu ? seperti yang terlihat di atas, coba kita bandingkan dengan metode isolation level yang lainnya:

READ UNCOMMITED

kita ubah dulu isolation level dari mysql menjadi read uncommited

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| READ-UNCOMMITTED | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

yoii sudah sudah ...

READ UNCOMMITED KASUS 1

Transaksi 1 :

trx1 mysql> begin;
Query OK, 0 rows affected (0.00 sec)

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

trx1 mysql> update mysaldo set saldo = saldo + 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 200 |
+-------+
1 row in set (0.00 sec)

berhenti sampai sini, transaksi 1 tidak kita commit dulu ... langsung ke transaksi 2

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 200 |
+-------+
1 row in set (0.00 sec)

sudah bisa di baca di trx2 walaupun belum di commit lho di trx1

kalo aku rollback trx1 , di trx2 result dari select di mysaldo kembali menjadi 100

beda khan dengan isolation level repeateble read di atas, oke lanjut ke kasus 2

READ UNCOMMITED KASUS 2

oke dari read uncommited kasus 1, pikiranku yang agak menggelitik adalah ... misalnya transaksi ku yang pertama menambah jadi 100, jadi saldo ku sekarang adalah 200.
dan aku sebagai user sudah bisa langsung lihat ... bahwa duitku sekarang adalah 200, hasil dari penambahan di transaksi 1, walaupun transaksi 1 belum di commit.

Nah aku tergoda membelanjakan duitku itu 200 untuk beli pulsa misalnya. Ketika aku mau beli pulsa seharaga 200, tiba tiba trx1 yang penambahan 100 itu rollback bukannya commit ..... lalu gimana dong ????

kita coba aja ya ....

Transaksi 1 :

trx1 mysql> begin;
Query OK, 0 rows affected (0.00 sec)

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

trx1 mysql> update mysaldo set saldo = saldo + 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

stop gak di commit dulu pindah ke transaksi 2

Transaksi 2 :

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 200 |
+-------+
1 row in set (0.00 sec)

mysql> update mysaldo set saldo = saldo - 200;

UPDATE INI TERNYATA TIDAK BISA ... DIA STATUS NYA WAITING TRANSAKSI 1 COMMIT DULU ATAU ROLLBACK,
KALO TRANSAKSI 1 COMMIT artinya PROSES BENER GAK ADA YANG SALAH, SALDO KU SEKARANG JADI 0 ...
TAPI KALO TERNYATA TRANSAKSI 1 ROLLBACK , apakah yang akan terjadi ???

di transaksi 1 kita melakukan rollback :

trx1 mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

INILAH YANG TERJADI DI TRANSAKSI 2 :

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| -100 |
+-------+
1 row in set (0.00 sec)

Hasilnya -100
untuk itulah isolation level read uncommited ini sering di kenal dengan sebutan DIRTY READ


READ COMMITED

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+------------------+
| READ-COMMITTED | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)


READ COMMITED KASUS 1

Transaksi 1

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

trx1 mysql> update mysaldo set saldo = saldo + 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

STOP JANGAN DI COMMIT DULU .. lanjutkan ke transaksi 2 dulu

Transaksi 2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

Kita lihat pada kasus 1 ini tidak ada beda dengan isolation level repeatable read ... hasil transaksi 1 ketika belum di commit tidak bisa di baca di transaksi 2
berbeda dengan read uncomited yang sudah langsung bisa di baca di transaksi 2

lanjut ...

pada transakasi 1 kita melakukan commit

trx1 mysql> commit;
Query OK, 0 rows affected (0.02 sec)

Kita lihat apa yang terjadi di transaksi 2

mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 200 |
+-------+
1 row in set (0.00 sec)

Kita lihat di sini ... level isolation level ini berbeda dengan repeatable read, jika repeatable read kasus seperti ini, di transaksi 2 belum akan kebaca menjadi bernilai 200. tapi masih 100 karena transkasi 2 nya harus di commit dulu juga baru bisa kebaca 200

untuk kasus kedua dari model dari read commited ini saya rasa udah terbayang. Yang jelas jika saya di transaksi 1 dapat tambahan 100 dan belum commit
maka saya tidak akan bisa membaca saldo saya 200, dan ketika transaksi pertama sudah commit baru terbaca 200, artinya ketika di transaksi kedua 200 ini mau saya habis belanjakan sudah tidak di jumpai masalah pada isolation level read uncommited

lanjut pada isolation level terakhir ....


SERIALIZABLE

set isolation level jadi serializeable ...

nah serializeable ini satu langkah lebih dari pada isolation level repeatable read. karena dengan serializable ini query select sudah di anggap transaksi dan akan melakukan locking tabel. jadi jika pada transaksi 1 kita hanya melakukan select, kemudian di interrupt transaksi kedua dengan melakukan update.
maka update di transkasi kedua ini tidak akan jalan sebelum transkasi 1 commit dulu. Di mana hal ini masih memungkinkan jida di lakukan dengan isolation level repeatable read

Transaksi 1

trx1 mysql> begin;
Query OK, 0 rows affected (0.00 sec)

trx1 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

STOP di interrupt transaksi kedua

Transaksi 2

trx2 mysql> begin;

Query OK, 0 rows affected (0.00 sec)

trx2 mysql> select * from mysaldo;
+-------+
| saldo |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

mysql> update mysaldo set saldo = saldo + 100;

Nah update ini tidak bisa , karena tabel mysaldo masih ke lock gara gara operasi select pada transaksi 1. Kita lihat dengan demikian upaya modifikasi tabel pada transaksi 2 tidak akan bisa menunggu transaksi 1 commit dulu.


aku pribadi masih tetep megang repeatble read, serializeable lebih bagus, tapi lebih banyak menggunakan resource dan punya issue performance ...

tapi seperti yang ada dalam forum mysql , kalo urusan nya sudah data penting, WHO CARES ABOUT PERFORMANCE ????

tanggapan, pertanyaan ... share, dan koreksi atas tulisan ini ....

monggo kita bahas ....