TCL - Transaction Control Language

Definição

Uma transação é uma unidade lógica de trabalho que consiste em uma ou mais operações SQL que devem ser executadas como um todo indivisível.

A transação garante que todas as operações sejam concluídas com sucesso ou, em caso de falha, todas sejam desfeitas (tudo ou nada).

Como funciona o COMMIT

O comando COMMIT torna permanentes todas as alterações feitas na transação atual. Após o COMMIT:

• As alterações são gravadas definitivamente no banco de dados
• Outros usuários podem ver as alterações
• Os locks (bloqueios) são liberados
• Uma nova transação começa automaticamente

-- Exemplo básico de COMMIT
UPDATE funcionarios
SET salario = salario * 1.10
WHERE departamento_id = 10;

-- Verificar quantas linhas foram afetadas
-- (5 linhas atualizadas)

COMMIT;  -- Confirma as alterações

-- Agora as alterações são permanentes e visíveis para todos

-- Exemplo de COMMIT implícito
UPDATE funcionarios SET salario = 5000 WHERE id = 10;  -- Transação iniciada

CREATE TABLE teste (id NUMBER);  -- DDL faz COMMIT automático!
-- A atualização acima foi confirmada automaticamente

ROLLBACK;  -- Não vai desfazer o UPDATE, pois já foi commitado!

Como funciona o ROLLBACK

O comando ROLLBACK desfaz todas as alterações feitas na transação atual desde o último COMMIT. Após o ROLLBACK:

• Todas as alterações são descartadas
• O banco retorna ao estado anterior ao início da transação
• Os locks são liberados
• Uma nova transação começa automaticamente

-- Exemplo básico de ROLLBACK
UPDATE funcionarios
SET salario = salario * 2
WHERE departamento_id = 10;

SELECT salario FROM funcionarios WHERE departamento_id = 10;
-- Salários dobrados (ainda não confirmado)

ROLLBACK;  -- Desfaz as alterações

SELECT salario FROM funcionarios WHERE departamento_id = 10;
-- Salários voltaram aos valores originais

DELETE FROM funcionarios WHERE departamento_id = 10;
-- Ops! Queria excluir apenas um funcionário!

ROLLBACK;  -- Desfaz a exclusão

INSERT INTO funcionarios VALUES (1, 'João', 'joao@email.com', SYSDATE, 5000, 99);
-- ORA-02291: parent key not found (departamento 99 não existe)

ROLLBACK;  -- Descarta a tentativa de inserção

O que é um SAVEPOINT?

Um SAVEPOINT é um marcador dentro de uma transação que permite fazer ROLLBACK parcial, sem desfazer toda a transação.

-- Criando savepoints
INSERT INTO departamentos VALUES (40, 'Marketing', 'Rio de Janeiro', 150000);
SAVEPOINT sp1;  -- Primeiro ponto de salvamento

UPDATE funcionarios SET salario = salario * 1.05 WHERE departamento_id = 10;
SAVEPOINT sp2;  -- Segundo ponto de salvamento

DELETE FROM funcionarios WHERE id_funcionario = 999;
SAVEPOINT sp3;  -- Terceiro ponto de salvamento

-- Desfazer apenas até sp2 (mantém INSERT e UPDATE, desfaz DELETE)
ROLLBACK TO SAVEPOINT sp2;

-- Ou desfazer até sp1 (mantém apenas INSERT)
ROLLBACK TO SAVEPOINT sp1;

-- Ou desfazer tudo
ROLLBACK;

-- Processo complexo com múltiplos passos
BEGIN
    -- Passo 1: Criar novos registros
    INSERT INTO departamentos_novo
    SELECT * FROM departamentos_antigo;
    SAVEPOINT passo1_completo;

    -- Passo 2: Atualizar referências
    UPDATE funcionarios
    SET departamento_id = departamento_id + 1000;
    SAVEPOINT passo2_completo;

    -- Passo 3: Validar dados
    UPDATE funcionarios
    SET status = 'MIGRADO';

    -- Se algo deu errado no passo 3, voltar para passo2
    -- ROLLBACK TO SAVEPOINT passo2_completo;

    -- Se tudo OK, confirmar tudo
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Desfaz tudo em caso de erro
        RAISE;
END;
/

Conceito de Isolamento

O isolamento garante que transações simultâneas não interfiram entre si. O Oracle usa locks (bloqueios) e controle de concorrência multiversão (MVCC).

-- T1: Iniciar transação
UPDATE funcionarios
SET salario = 6000
WHERE id = 10;

-- Aguardando...
-- (não fez COMMIT ainda)


-- T3: Confirmar
COMMIT;

-- T2: Tentar atualizar
-- mesma linha
UPDATE funcionarios
SET salario = 7000
WHERE id = 10;

-- ESPERA! Sessão 1 tem lock

-- T4: Após COMMIT da Sessão 1
-- Agora executa e pega lock

Exemplo 1: Transferência entre Contas

-- Transferir R$ 1000 da conta 100 para conta 200
SAVEPOINT inicio_transferencia;

-- Debitar conta origem
UPDATE contas
SET saldo = saldo - 1000
WHERE numero_conta = 100;

-- Verificar se ficou negativo
SELECT saldo INTO v_saldo
FROM contas
WHERE numero_conta = 100;

IF v_saldo < 0 THEN
    ROLLBACK TO SAVEPOINT inicio_transferencia;
    RAISE_APPLICATION_ERROR(-20001, 'Saldo insuficiente');
END IF;

-- Creditar conta destino
UPDATE contas
SET saldo = saldo + 1000
WHERE numero_conta = 200;

-- Registrar histórico
INSERT INTO historico_transferencias VALUES
    (seq_transf.NEXTVAL, 100, 200, 1000, SYSDATE);

-- Confirmar toda a transação
COMMIT;

Exemplo 2: Importação de Dados com Validação

-- Importar dados em lote com validação
DECLARE
    v_erros NUMBER := 0;
BEGIN
    SAVEPOINT inicio_importacao;

    -- Inserir dados temporários
    INSERT INTO funcionarios_temp
    SELECT * FROM arquivo_externo;

    -- Validar dados
    SELECT COUNT(*) INTO v_erros
    FROM funcionarios_temp
    WHERE email NOT LIKE '%@%'
       OR salario <= 0
       OR departamento_id NOT IN (SELECT id_departamento FROM departamentos);

    IF v_erros > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Encontrados ' || v_erros || ' registros inválidos');
        ROLLBACK TO SAVEPOINT inicio_importacao;
    ELSE
        -- Mover dados validados para tabela final
        INSERT INTO funcionarios
        SELECT * FROM funcionarios_temp;

        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Importação concluída com sucesso');
    END IF;
END;
/

-- Atualizar salários com registro de auditoria
BEGIN
    -- Registrar estado anterior
    INSERT INTO auditoria_salarios
    SELECT id_funcionario, salario, USER, SYSDATE, 'ANTES'
    FROM funcionarios
    WHERE departamento_id = 10;

    SAVEPOINT antes_atualizacao;

    -- Aplicar aumento
    UPDATE funcionarios
    SET salario = salario * 1.10
    WHERE departamento_id = 10;

    -- Registrar estado posterior
    INSERT INTO auditoria_salarios
    SELECT id_funcionario, salario, USER, SYSDATE, 'DEPOIS'
    FROM funcionarios
    WHERE departamento_id = 10;

    -- Verificar se ultrapassou orçamento
    SELECT SUM(salario) INTO v_total
    FROM funcionarios
    WHERE departamento_id = 10;

    IF v_total > v_orcamento_depto THEN
        ROLLBACK TO SAVEPOINT antes_atualizacao;
        RAISE_APPLICATION_ERROR(-20002, 'Orçamento ultrapassado');
    ELSE
        COMMIT;
    END IF;
END;
/