Tingkat isolasi SERIALIZABLE

Transaksi dan Penanganan Kesalahan di SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Tingkat isolasi paling ketat

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Perbandingan tingkat isolasi

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

Mengunci record dengan SERIALIZABLE

Kueri dengan klausa WHERE berbasis rentang indeks -> Mengunci hanya record tersebut
Kueri tidak berbasis rentang indeks -> Mengunci seluruh tabel

SERIALIZABLE - kueri berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Record terkunci

SERIALIZABLE - kueri berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Record terkunci

Transaksi 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Harus menunggu!

SERIALIZABLE - kueri berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Transaksi 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Harus menunggu!

SERIALIZABLE - kueri berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

COMMIT TRAN

Transaksi 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Akhirnya dieksekusi!

SERIALIZABLE - kueri berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Transaksi 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (200, 'Phantom', 'Ph', '[email protected]', 555666222);

Langsung disisipkan!

SERIALIZABLE - kueri tidak berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

Mengunci seluruh tabel

Transaksi 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Harus menunggu!

SERIALIZABLE - kueri tidak berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

Transaksi 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Harus menunggu!

SERIALIZABLE - kueri tidak berbasis rentang indeks

Transaksi 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

COMMIT TRAN

Transaksi 2

...

INSERT INTO customers 
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Akhirnya dieksekusi!

SERIALIZABLE - ringkasan

Kelebihan:

Konsistensi data baik: Mencegah dirty, non-repeatable, dan phantom read

Kekurangan:

Anda bisa terblokir oleh transaksi SERIALIZABLE

Kapan digunakan?:

Saat konsistensi data wajib

Ayo berlatih!

Transaksi dan Penanganan Kesalahan di SQL Server