SNAPSHOT ": "SNAPSHOT

Transações e tratamento de erros no SQL Server

Miriam Antona

Software Engineer

SNAPSHOT ": "SNAPSHOT

Toda modificação é armazenada no banco tempDB
Você só vê mudanças confirmadas antes do início da transação SNAPSHOT e as suas próprias mudanças
Não vê mudanças de outras transações após o início da transação SNAPSHOT
Leitura não bloqueia escrita e escrita não bloqueia leitura
Pode haver conflitos de atualização

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - isolation level comparison ": "SNAPSHOT - comparação de níveis de isolamento

	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
SNAPSHOT	no	no	no

SNAPSHOT - example ": "SNAPSHOT - exemplo

Transaction1

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       |

Transaction2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Não fica bloqueada!

SNAPSHOT - example ": "SNAPSHOT - exemplo

Transaction1

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       |

Transaction2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Não fica bloqueada!

SNAPSHOT - summary ": "SNAPSHOT - resumo

Prós:

Boa consistência: evita dirty, non-repeatable e phantom reads sem bloqueio

Contras:

Crescimento do tempDB

Quando usar?:

Quando precisa de consistência e não quer bloqueios

READ COMMITTED SNAPSHOT ": "READ COMMITTED SNAPSHOT

Altera o comportamento de READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

OFF por padrão
Para usar ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Com ON, toda instrução READ COMMITTED só vê mudanças confirmadas antes do início dessa instrução
Não há conflitos de atualização

READ COMMITTED SNAPSHOT - example ": "READ COMMITTED SNAPSHOT - exemplo

Transaction1

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

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT - example ": "READ COMMITTED SNAPSHOT - exemplo

Transaction1

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

COMMIT TRAN

Transaction2

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) ": "WITH (NOLOCK)

Usado para ler dados não confirmados
READ UNCOMMITTED se aplica a toda a conexão / WITH (NOLOCK) a uma tabela específica
Use em qualquer nível de isolamento quando quiser ler dados não confirmados de tabelas específicas

WITH (NOLOCK) - example ": "WITH (NOLOCK) - exemplo

Saldo original da conta 5 = $35.000

Transaction1

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

Transaction2

...

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

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

Let's practice! ": "Vamos praticar!

Transações e tratamento de erros no SQL Server