Níveis de isolamento de transações

Transações e tratamento de erros no SQL Server

Miriam Antona

Software Engineer

O que é concorrência?

Concorrência: duas ou mais transações leem/alteram dados compartilhados ao mesmo tempo.

Isolar nossa transação das demais

Níveis de isolamento de transações

READ COMMITTED (padrão)
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT

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

Como saber o nível atual

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

Nível de isolamento menos restritivo
Lê linhas modificadas por outra transação que ainda não foi confirmada nem desfeita

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Nível de isolamento menos restritivo
Lê linhas modificadas por outras transações sem terem sido confirmadas/desfeitas.

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

Leituras sujas (dirty reads)

Saldo original da conta 5 = US$ 35.000

Transação1

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

ROLLBACK TRAN;

Transação2

...

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

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

Leituras não repetíveis

Transação1

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

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

Transação2

...

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

Leituras não repetíveis

Transação1

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        |

Transação2

...

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

Leituras fantasmas

Transação1

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        |

Transação2

...

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

Leituras fantasmas

Transação1

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        | Fantasma!
| 55555555552020202020 |...| 50000,00        |

Transação2

...

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

READ UNCOMMITTED - resumo

Prós:

Pode ser mais rápido, não bloqueia outras transações.

Contras:

Permite dirty reads, leituras não repetíveis e leituras fantasmas.

Quando usar?:

Você não quer ser bloqueado por outras transações e aceita fenômenos de concorrência.
Você quer ver dados não confirmados.

Vamos praticar!

Transações e tratamento de erros no SQL Server