READ COMMITTED & REPEATABLE READ

Transaksi dan Penanganan Kesalahan di SQL Server

Miriam Antona

Software Engineer

READ COMMITTED

Level isolasi default
Tidak dapat membaca data yang diubah oleh transaksi lain yang belum commit atau rollback

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

READ COMMITTED - perbandingan level isolasi

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes

READ COMMITTED - mencegah dirty read

Saldo awal akun 5 = $35.000

Transaksi1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Transaksi2

...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

Harus menunggu!

READ COMMITTED - mencegah dirty read

Saldo awal akun 5 = $35.000

Transaksi1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

COMMIT TRAN;

Transaksi2

...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

READ COMMITTED - memilih tanpa menunggu

Transaksi1

BEGIN TRAN
  SELECT current_balance
  FROM accounts WHERE account_id = 5;

Transaksi2

...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED - ringkasan

Kelebihan:

Mencegah dirty read

Kekurangan:

Mengizinkan non-repeatable read dan phantom read
Dapat diblokir oleh transaksi lain

Kapan digunakan?:

Ingin memastikan hanya membaca data yang sudah commit; non-repeatable dan phantom read tetap mungkin

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Tidak bisa membaca data yang belum di-commit dari transaksi lain
Jika data sudah dibaca, transaksi lain tidak bisa mengubah data itu sampai transaksi REPEATABLE READ selesai

REPEATABLE READ - perbandingan level isolasi

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes

REPEATABLE READ - mencegah non-repeatable read

Transaksi1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT current_balance FROM accounts
    WHERE account_id = 5;

| current_balance |
|-----------------|
| 35000,00        |

Transaksi2

...

UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;

Harus menunggu!

REPEATABLE READ - mencegah non-repeatable read

Transaksi1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT current_balance FROM accounts
    WHERE account_id = 5;

    SELECT current_balance FROM accounts
    WHERE account_id = 5;

| current_balance |
|-----------------|
| 35000,00        |

COMMIT TRAN

Transaksi2

...

UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;

Harus menunggu!

REPEATABLE READ - mencegah non-repeatable read

Transaksi1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT current_balance FROM accounts
    WHERE account_id = 5;

    SELECT current_balance FROM accounts
    WHERE account_id = 5;

COMMIT TRAN

Transaksi2

...

UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;

(1 rows affected)

REPEATABLE READ - ringkasan

Kelebihan:

Mencegah transaksi lain mengubah data yang Anda baca; mencegah non-repeatable read
Mencegah dirty read

Kekurangan:

Mengizinkan phantom read
Anda bisa diblokir oleh transaksi REPEATABLE READ.

Kapan digunakan?:

Hanya ingin membaca data yang sudah commit dan tidak ingin transaksi lain mengubah data yang Anda baca. Phantom read tidak masalah

Ayo berlatih!

Transaksi dan Penanganan Kesalahan di SQL Server