Tingkat isolasi transaksi

Transaksi dan Penanganan Kesalahan di SQL Server

Miriam Antona

Software Engineer

Apa itu konkruensi?

Konkruensi: dua atau lebih transaksi yang membaca/mengubah data bersama pada saat yang sama.

Mengisolasi transaksi kita dari transaksi lain

Tingkat isolasi transaksi

READ COMMITTED (default)
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT

SET TRANSACTION ISOLATION LEVEL 
    {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT}

Mengetahui tingkat isolasi saat ini

SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'UNSPECIFIED' 
    WHEN 1 THEN 'READ UNCOMMITTED' 
    WHEN 2 THEN 'READ COMMITTED' 
    WHEN 3 THEN 'REPEATABLE READ ' 
    WHEN 4 THEN 'SERIALIZABLE' 
    WHEN 5 THEN 'SNAPSHOT' 
END AS transaction_isolation_level 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

| transaction_isolation_level |
|-----------------------------|
| READ COMMITTED              |

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Tingkat isolasi paling longgar
Membaca baris yang diubah oleh transaksi lain yang belum di-commit atau di-rollback

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Tingkat isolasi paling longgar
Membaca baris yang diubah transaksi lain tanpa di-commit/rollback.

	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	ya	ya	ya

Dirty read

Saldo awal akun 5 = $35.000

Transaksi1

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

ROLLBACK TRAN;

Transaksi2

...

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

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

Non-repeatable read

Transaksi1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
    SELECT * FROM accounts WHERE account_id = 5;

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

Transaksi2

...

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

Non-repeatable read

Transaksi1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
    SELECT * FROM accounts WHERE account_id = 5;

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

    SELECT * FROM accounts WHERE account_id = 5;

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

Transaksi2

...

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

Phantom read

Transaksi1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts
    WHERE current_balance BETWEEN 45000 AND 50000

| account_number       | ... | current_balance |
|----------------------|-----|-----------------|
| 55555555552020202020 | ... | 50000,00        |

Transaksi2

...

BEGIN TRAN
INSERT INTO accounts
    VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN

Phantom read

Transaksi1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts 
    WHERE current_balance BETWEEN 45000 AND 50000

| account_number       | ... | current_balance |
|----------------------|-----|-----------------|
| 55555555552020202020 | ... | 50000,00        |

SELECT * FROM accounts 
    WHERE current_balance BETWEEN 45000 AND 50000

| account_number       |...| current_balance |
|----------------------|---|-----------------|
| 55555555553939393939 |...| 45000,00        | Phantom!
| 55555555552020202020 |...| 50000,00        |

Transaksi2

...

BEGIN TRAN
INSERT INTO accounts
    VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN

READ UNCOMMITTED - ringkasan

Kelebihan:

Lebih cepat, tidak memblokir transaksi lain.

Kekurangan:

Memungkinkan dirty read, non-repeatable read, dan phantom read.

Kapan digunakan?:

Tidak ingin diblokir transaksi lain dan tidak keberatan fenomena konkruensi.
Secara eksplisit ingin melihat data yang belum di-commit.

Ayo berlatih!

Transaksi dan Penanganan Kesalahan di SQL Server