Nível de isolamento SERIALIZABLE

Transações e tratamento de erros no SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Nível de isolamento mais restritivo

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Comparação de níveis de isolamento

	Leituras sujas	Leituras não repetíveis	Leituras fantasmas
READ UNCOMMITTED	sim	sim	sim
READ COMMITTED	não	sim	sim
REPEATABLE READ	não	não	sim
SERIALIZABLE	não	não	não

Bloqueando registros com SERIALIZABLE

Consulta com cláusula WHERE baseada em faixa de índice -> Bloqueia só esses registros
Consulta não baseada em faixa de índice -> Bloqueia a tabela inteira

SERIALIZABLE - consulta baseada em faixa de índice

Transação 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 |

Registro bloqueado

SERIALIZABLE - consulta baseada em faixa de índice

Transação 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 |

Registro bloqueado

Transação 2

...

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

Tem que esperar!

SERIALIZABLE - consulta baseada em faixa de índice

Transação 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 |

Transação 2

...

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

Tem que esperar!

SERIALIZABLE - consulta baseado em faixa de índice

Transação 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

Transação 2

...

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

Enfim executado!

SERIALIZABLE - consulta baseada em faixa de índice

Transação 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 |

Transação 2

...

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

Inserido na hora!

SERIALIZABLE - consulta não baseada em faixa de índice

Transação 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 |

Bloqueia a tabela inteira

Transação 2

...

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

Tem que esperar!

SERIALIZABLE - consulta não baseada em faixa de índice

Transação 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 |

Transação 2

...

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

Tem que esperar!

SERIALIZABLE - consulta não baseada em faixa de índice

Transação 1

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

    SELECT * FROM customers;

COMMIT TRAN

Transação 2

...

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

Enfim executado!

SERIALIZABLE - resumo

Prós:

Boa consistência dos dados: evita leituras sujas, não repetíveis e fantasmas

Contras:

Você pode ficar bloqueado por uma transação SERIALIZABLE

Quando usar?:

Quando a consistência dos dados é essencial

Vamos praticar!

Transações e tratamento de erros no SQL Server