SNAPSHOT

Transaksi dan Penanganan Kesalahan di SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Setiap perubahan disimpan di database tempDB
Hanya melihat perubahan yang committed sebelum transaksi SNAPSHOT dimulai serta perubahan sendiri
Tidak melihat perubahan transaksi lain setelah transaksi SNAPSHOT dimulai
Baca tidak memblokir tulis dan tulis tidak memblokir baca
Dapat terjadi konflik update

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - perbandingan level isolasi

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	ya	ya	ya
READ COMMITTED	tidak	ya	ya
REPEATABLE READ	tidak	tidak	ya
SERIALIZABLE	tidak	tidak	tidak
SNAPSHOT	tidak	tidak	tidak

SNAPSHOT - contoh

Transaksi1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaksi2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Tidak terblokir!

SNAPSHOT - contoh

Transaksi1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaksi2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Tidak terblokir!

SNAPSHOT - ringkasan

Kelebihan:

Konsistensi data baik: mencegah dirty, non-repeatable, dan phantom reads tanpa blocking

Kekurangan:

tempDB bertambah

Kapan digunakan?:

Saat konsistensi wajib dan tidak ingin ada blocking

READ COMMITTED SNAPSHOT

Mengubah perilaku READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

Default OFF
Untuk mengaktifkan ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Jika ON, setiap pernyataan READ COMMITTED hanya melihat perubahan yang sudah committed sebelum pernyataan dimulai
Tidak ada konflik update

READ COMMITTED SNAPSHOT - contoh

Transaksi1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaksi2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT - contoh

Transaksi1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaksi2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

WITH (NOLOCK)

Untuk membaca data yang belum committed
READ UNCOMMITTED berlaku untuk seluruh koneksi / WITH (NOLOCK) berlaku untuk tabel tertentu
Gunakan pada level isolasi apa pun saat hanya ingin membaca data belum committed dari tabel tertentu

WITH (NOLOCK) - contoh

Saldo awal akun 5 = $35.000

Transaksi1

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

Transaksi2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

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

Ayo berlatih!

Transaksi dan Penanganan Kesalahan di SQL Server