Módulo 17 — Transações & Níveis de Isolamento

1) ACID & Log de Transações

Transações garantem Atomicidade, Consistência, Isolamento e Durabilidade. O log de transações registra as mudanças para garantir rollback/recovery.

• Atomicidade: tudo ou nada.
• Consistência: regras de integridade preservadas.
• Isolamento: visões coerentes entre sessões concorrentes.
• Durabilidade: confirmado = persistido no log.

2) Transações: Básico

-- Padrão explícito
BEGIN TRAN;
  -- DMLs aqui
  INSERT INTO dbo.Pedidos(...) VALUES (...);
  UPDATE dbo.Estoque SET Qtd = Qtd - 1 WHERE Id = @IdProduto;
COMMIT;

-- Abortando alterações
BEGIN TRAN;
  DELETE FROM dbo.Itens WHERE PedidoId = @Pid;
ROLLBACK;

-- SAVEPOINT (parcial)
BEGIN TRAN;
  -- etapa 1
  SAVE TRAN Etapa1;
  -- etapa 2
  IF (@ErroEtapa2 = 1)
     ROLLBACK TRAN Etapa1;  -- desfaz só até o savepoint
COMMIT;

3) TRY/CATCH, XACT_STATE & XACT_ABORT

BEGIN TRY
  BEGIN TRAN;
    -- Lógica
    INSERT INTO dbo.Pagamentos(...) VALUES (...);
    UPDATE dbo.Pedidos SET Status = 'Pago' WHERE Id = @PedidoId;
  COMMIT;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0 ROLLBACK; -- -1 (irrecuperável) ou 1 (ativo)
  DECLARE @msg nvarchar(4000) = ERROR_MESSAGE();
  THROW 51000, @msg, 1;  -- Propaga com severidade definida
END CATCH;

-- XACT_ABORT: aborta automaticamente a transação em erros de tempo de execução
SET XACT_ABORT ON;
BEGIN TRAN;
  -- sua lógica
COMMIT;

4) Bloqueios & Escalonamento

O SQL Server usa bloqueios shared (S), exclusive (X), update (U) e intent (IS/IX/SIX) em grãos variados (linha, página, tabela). Há escalonamento para reduzir overhead.

5) Níveis de Isolamento

-- Ajustando por sessão
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
-- Voltar ao padrão
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

6) Row Versioning (RCSI & Snapshot)

Habilita leituras otimistas via versões de linha armazenadas no tempdb.

-- No banco de dados (execute no contexto "master" ou conectado ao servidor)
ALTER DATABASE MeuDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MeuDB SET ALLOW_SNAPSHOT_ISOLATION ON;

• RCSI: transparente para sessões em READ COMMITTED; reduz bloqueio de leitura.
• Snapshot: por sessão (SET TRANSACTION ISOLATION LEVEL SNAPSHOT).
• Custo: aumenta escrita/IO no tempdb; monitore uso de versão.

7) Deadlocks: causas & tratamento

Deadlock é um ciclo de dependência entre transações. O SQL Server escolhe uma vítima e aborta uma delas (1205).

• Ordene acessos a recursos sempre no mesmo sequenciamento.
• Mantenha transações curtas e objetivas.
• Use UPDLOCK ao ler para atualizar, reduzindo conversões S→X.
• Capture eventos (Extended Events) para analisar o grafo.

-- Política de prioridade (opcional)
SET DEADLOCK_PRIORITY LOW;   -- ou NORMAL/HIGH/Número (-10..10)

-- Padrão de retry simples
DECLARE @tentativas int = 3, @ok bit = 0;
WHILE @tentativas > 0 AND @ok = 0
BEGIN
  BEGIN TRY
    BEGIN TRAN;
      -- sua lógica aqui
    COMMIT;
    SET @ok = 1;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    IF ERROR_NUMBER() = 1205
    BEGIN
      SET @tentativas -= 1;
      WAITFOR DELAY '00:00:01'; -- backoff
    END
    ELSE
      THROW;
  END CATCH
END

8) Hints & armadilhas

• NOLOCK = READ UNCOMMITTED: pode retornar linhas não confirmadas, duplicadas ou perdidas. Use com extrema cautela.
• UPDLOCK: lê com intenção de atualizar, evitando conversão S→X.
• HOLDLOCK (= SERIALIZABLE): mantém bloqueios até o fim da transação.
• READPAST: ignora linhas bloqueadas (cuidado com “pular” registros).
• ROWLOCK/PAGLOCK/TABLOCK: força granularidade; evite sem necessidade.

9) Laboratório Guiado

9.1) Preparação

IF OBJECT_ID('dbo.Contas','U') IS NOT NULL DROP TABLE dbo.Contas;
GO
CREATE TABLE dbo.Contas(
  Id int IDENTITY PRIMARY KEY,
  Cliente nvarchar(100) NOT NULL,
  Saldo   decimal(12,2) NOT NULL
);
INSERT INTO dbo.Contas (Cliente, Saldo) VALUES
(N'Ana', 1000.00), (N'Bruno', 800.00);
GO

9.2) Transferência com garantia de atomicidade

DECLARE @De int = 1, @Para int = 2, @Valor decimal(12,2) = 200.00;

BEGIN TRY
  SET XACT_ABORT ON;
  BEGIN TRAN;
    UPDATE dbo.Contas SET Saldo = Saldo - @Valor WHERE Id = @De;
    UPDATE dbo.Contas SET Saldo = Saldo + @Valor WHERE Id = @Para;
  COMMIT;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0 ROLLBACK;
  THROW;
END CATCH;

9.3) Testando isolamento

-- Sessão A
BEGIN TRAN;
  UPDATE dbo.Contas SET Saldo = Saldo + 10 WHERE Id = 1;
-- (não commitar ainda)

-- Sessão B (em READ COMMITTED padrão): ficará bloqueada ao tentar ler a mesma linha
SELECT * FROM dbo.Contas WITH (READCOMMITTEDLOCK) WHERE Id = 1;

-- Sessão B (com RCSI habilitado no banco): leitura não bloqueia, retorna versão estável

9.4) Snapshot por sessão

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
  SELECT * FROM dbo.Contas WHERE Id = 1; -- lê versão consistente da abertura
  WAITFOR DELAY '00:00:05';
  SELECT * FROM dbo.Contas WHERE Id = 1; -- mesma versão (snapshot)
COMMIT;

9.5) Simulando deadlock (didático)

-- Sessão A
BEGIN TRAN;
  UPDATE dbo.Contas SET Saldo = Saldo + 1 WHERE Id = 1;
  WAITFOR DELAY '00:00:03';
  UPDATE dbo.Contas SET Saldo = Saldo + 1 WHERE Id = 2;
-- (aguarda)

-- Sessão B (executar quase ao mesmo tempo)
BEGIN TRAN;
  UPDATE dbo.Contas SET Saldo = Saldo + 1 WHERE Id = 2;
  WAITFOR DELAY '00:00:03';
  UPDATE dbo.Contas SET Saldo = Saldo + 1 WHERE Id = 1;
-- Uma das sessões receberá erro 1205

10) Exercícios

1. Padrão TRY/CATCH: Implemente um bloco transacional com XACT_STATE e THROW para um cenário de faturamento.
2. RCSI: Liste os comandos para habilitar READ COMMITTED SNAPSHOT no seu banco e explique um impacto positivo e um negativo.
3. Deadlock: Crie um script que pode causar deadlock e resolva com ordenação de acesso + UPDLOCK.
4. Isolamento: Mostre um exemplo que produz non-repeatable read em READ COMMITTED e como evitar.
5. Hints: Demonstre READPAST e explique quando (não) usar.

11) Gabarito (sugestões)

-- 1) TRY/CATCH + XACT_STATE
BEGIN TRY
  BEGIN TRAN;
    -- ... lógica ...
  COMMIT;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0 ROLLBACK;
  THROW;
END CATCH;

-- 2) Habilitar RCSI + Snapshot
ALTER DATABASE MeuDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MeuDB SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 3) Deadlock (solução com ordenação + UPDLOCK)
BEGIN TRAN;
  -- Acesse sempre na ordem Id ascendente
  UPDATE dbo.Contas WITH (UPDLOCK) SET Saldo = Saldo + 1 WHERE Id = 1;
  UPDATE dbo.Contas WITH (UPDLOCK) SET Saldo = Saldo + 1 WHERE Id = 2;
COMMIT;

-- 4) Non-repeatable em RC e evitando com RR
-- Sessão A (RC): lê 100; outra sessão atualiza para 120; reler e obtém 120 (non-repeatable)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
  SELECT Saldo FROM dbo.Contas WHERE Id = 1;
  WAITFOR DELAY '00:00:05';
  SELECT Saldo FROM dbo.Contas WHERE Id = 1;
COMMIT;
-- Evitar:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
  SELECT Saldo FROM dbo.Contas WHERE Id = 1; -- mantém S-lock até o COMMIT
  WAITFOR DELAY '00:00:05';
  SELECT Saldo FROM dbo.Contas WHERE Id = 1;
COMMIT;

-- 5) READPAST
-- Ignora linhas bloqueadas (pode “pular” registros); útil para filas com múltiplos consumidores
SELECT TOP (1) *
FROM dbo.Fila WITH (READPAST, ROWLOCK, UPDLOCK)
ORDER BY Id;

12) Erros Comuns & Checklist

13) Próximos Passos

• Instrumentar Extended Events para blocos e deadlocks.
• Criar diretrizes de isolamentos por tipo de workload.
• Preparar o Módulo 18: Índices & Otimização de Consultas (Parte I).