Manipulação de Transações SQLite

Introdução

Neste laboratório, exploraremos o tratamento de transações SQLite, com foco na manutenção da integridade dos dados através do uso de transações. Aprenderemos como iniciar e confirmar (commit) transações, garantindo que múltiplas operações relacionadas sejam tratadas como uma única unidade atômica.

Este laboratório irá guiá-lo através do processo de iniciar uma transação usando BEGIN TRANSACTION, fazendo alterações no banco de dados e, em seguida, salvando permanentemente essas alterações usando a instrução COMMIT. Você também aprenderá como desfazer alterações usando ROLLBACK e usar SAVEPOINT para um controle mais granular. Esta experiência prática solidificará sua compreensão de como as transações garantem a atomicidade, consistência, isolamento e durabilidade (propriedades ACID) no SQLite.

Criar um Banco de Dados e Tabela

Neste primeiro passo, criaremos um banco de dados SQLite e uma tabela para armazenar dados do usuário. Isso fornecerá a base para explorar o tratamento de transações nos passos subsequentes.

Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.

Agora, vamos criar um banco de dados SQLite chamado mydatabase.db. Execute o seguinte comando para criar o arquivo do banco de dados e abrir a ferramenta de linha de comando SQLite:

sqlite3 mydatabase.db

Você verá um prompt indicando que agora está dentro do shell SQLite:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Em seguida, crie uma tabela chamada users para armazenar informações básicas do usuário. Esta tabela terá três colunas: id (um identificador único), name e balance. Digite o seguinte comando SQL no prompt sqlite> e pressione Enter:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

Este comando configura a tabela users onde:

• id é um inteiro que serve como chave primária para cada usuário.
• name é um campo de texto que representa o nome do usuário.
• balance é um número real que representa o saldo da conta do usuário.

Agora, insira alguns dados iniciais na tabela users:

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

Esses comandos adicionam dois usuários, Alice e Bob, com saldos iniciais de 100.0 e 200.0, respectivamente.

Para confirmar se os dados foram adicionados corretamente, execute este comando para visualizar todos os registros na tabela:

SELECT * FROM users;

Saída Esperada:

1|Alice|100.0
2|Bob|200.0

Esta saída mostra o id, name e balance para cada registro. O comando SELECT * recupera todas as colunas da tabela especificada.

Iniciar e Confirmar uma Transação

Neste passo, você aprenderá como iniciar e confirmar (commit) uma transação. As transações são essenciais para garantir a integridade dos dados ao realizar múltiplas operações relacionadas.

Para iniciar uma transação, use o comando BEGIN TRANSACTION:

BEGIN TRANSACTION;

Este comando informa ao SQLite para começar a rastrear as alterações, mas não para aplicá-las permanentemente ao banco de dados até que você confirme explicitamente a transação.

Agora, vamos transferir 50.0 de Alice para Bob:

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

Esses comandos atualizam os saldos de Alice e Bob dentro da transação. O saldo de Alice é diminuído em 50.0, e o saldo de Bob é aumentado em 50.0.

Para salvar as alterações, use o comando COMMIT:

COMMIT;

Este comando confirma a transação, tornando as alterações permanentes.

Verifique as alterações consultando a tabela novamente:

SELECT * FROM users;

Saída Esperada:

1|Alice|50.0
2|Bob|250.0

Esta saída confirma que a transação foi confirmada com sucesso, e os saldos de Alice e Bob foram atualizados de acordo.

Reverter uma Transação

Neste passo, você aprenderá como usar o comando ROLLBACK para desfazer as alterações feitas durante uma transação. Isso é essencial para lidar com erros e garantir a integridade dos dados.

Primeiro, inicie uma nova transação:

BEGIN TRANSACTION;

Agora, vamos tentar transferir 100.0 de Alice para Bob, mas desta vez, simularemos uma condição de erro:

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

Como Alice só tem 50.0, a primeira atualização resultará em um saldo negativo. Embora o SQLite não imponha restrições por padrão, simularemos um erro ocorrendo após esta operação.

Para desfazer as alterações, use o comando ROLLBACK:

ROLLBACK;

Este comando reverte a transação, desfazendo quaisquer alterações feitas desde a instrução BEGIN TRANSACTION.

Verifique se as alterações foram revertidas consultando a tabela users:

SELECT * FROM users;

Saída Esperada:

1|Alice|50.0
2|Bob|250.0

Esta saída confirma que a transação foi revertida com sucesso, e os saldos de Alice e Bob permanecem inalterados.

Implementar Savepoints

Neste passo, você aprenderá como usar savepoints dentro de transações. Savepoints permitem que você crie pontos intermediários dentro de uma transação para os quais você pode reverter (rollback), sem reverter a transação inteira.

Primeiro, inicie uma nova transação:

BEGIN TRANSACTION;

Crie um savepoint chamado savepoint1:

SAVEPOINT savepoint1;

Agora, vamos transferir 20.0 de Alice para Bob:

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

Crie outro savepoint chamado savepoint2:

SAVEPOINT savepoint2;

Vamos adicionar um novo usuário chamado 'Charlie' com um saldo inicial de 300.0:

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

Agora, digamos que decidimos que adicionar Charlie foi um erro. Podemos reverter para savepoint1, o que desfará a instrução INSERT e a transferência de 20.0 entre Alice e Bob:

ROLLBACK TO SAVEPOINT savepoint1;

Verifique as alterações após a reversão para savepoint1:

SELECT * FROM users;

Saída Esperada:

1|Alice|50.0
2|Bob|250.0

Você deve ver que Charlie não está na tabela, e os saldos de Alice e Bob estão de volta aos seus valores antes de savepoint1.

Agora, vamos transferir 10.0 de Alice para Bob:

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

Finalmente, confirme a transação:

COMMIT;

Adicionar uma Constraint para Prevenir Saldos Negativos

Neste passo, você adicionará uma restrição à tabela users para evitar saldos negativos. Isso ajudará a garantir a integridade dos dados, impedindo transações inválidas.

Como o SQLite não suporta adicionar restrições a tabelas existentes usando ALTER TABLE ADD CONSTRAINT, precisamos recriar a tabela com a restrição. Primeiro, inicie uma transação para garantir que todas as operações sejam atômicas:

BEGIN TRANSACTION;

Crie uma nova tabela com a mesma estrutura, mas incluindo uma restrição CHECK:

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL CHECK (balance >= 0)
);

Copie todos os dados da tabela antiga para a nova tabela:

INSERT INTO users_new SELECT * FROM users;

Exclua a tabela antiga:

DROP TABLE users;

Renomeie a nova tabela para o nome original:

ALTER TABLE users_new RENAME TO users;

Confirme a transação para tornar as alterações permanentes:

COMMIT;

Agora, a tabela users possui uma restrição CHECK que garante que a coluna balance sempre seja maior ou igual a 0.

Vamos tentar transferir 1000 de Alice para Bob, o que violará a restrição:

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';

Desta vez, a instrução UPDATE gerará um erro porque viola a restrição CHECK. Você verá uma mensagem de erro como: Erro: Restrição CHECK violada: balance >= 0.

Reverter a transação:

ROLLBACK;

Verifique se as alterações foram revertidas:

SELECT * FROM users;

Saída Esperada:

1|Alice|40.0
2|Bob|260.0

Isso confirma que o ROLLBACK reverteu com sucesso o banco de dados ao seu estado original antes da transação falha.

Resumo

Neste laboratório, você aprendeu os fundamentos do tratamento de transações em SQLite. Você cobriu como iniciar e confirmar transações, reverter alterações (rollback), implementar savepoints para um controle mais granular e adicionar restrições para garantir a integridade dos dados. Essas habilidades são cruciais para construir aplicações de banco de dados robustas e confiáveis.