SQL Server: O que são Transactions e como usar

Bancos de dados SQL em geral, não somente o o SQL Server, são transacionais, isto é, eles permitem você executar uma sequência de operações como um bloco indivisível de forma a garantir a integridade dos dados em um ambiente com acesso concorrente.

Em outras palavras uma Transação, ou Transactions, é o processo todo de consulta ou manipulação do banco de dados. É uma forma de estabelecer que algo deva ser feito atomicamente, ou seja, ou faz tudo ou faz nada, não pode fazer pela metade. É tudo feito em uma viagem da aplicação para o banco de dados. Em condições normais enquanto a transação não termina outras transações não podem ver o que esta está fazendo.

Esses 3 comandos SQL são para controlar isso.

O BEGIN TRANSACTION indica onde ela deve começar, então os comando SQL a seguir estarão dentro desta transação.

O COMMIT TRANSACTION indica o fim normal da transação, o que tiver de comando depois já não fará parte desta transação. Neste momento tudo o que foi manipulado passa fazer parte do banco de dados normalmente e operações diversas passam enxergar o que foi feito.

O ROLLBACK TRANSACTION também fecha o bloco da transação e é a indicação que a transação deve ser terminada, mas tudo que tentou ser feito deve ser descartado porque alguma coisa errada aconteceu e ela não pode terminar normalmente. Nada realizado dentro dela será perdurado no banco de dados.

Ao contrário do que muita gente acredita rollback no contexto de banco de dados não significa reverter e sim voltar ao estado original. Um processo de reversão seria de complicadíssimo à impossível. Um processo de descarte é simples e pode ser atômico.

A maioria dos comandos SQL são transacionais implicitamente, ou seja, ele por si só já é uma transação. Você só precisa usar esses comandos citados quando precisa usar múltiplos comandos e todos esses devam rodar atomicamente. Ou seja, eles funcionam como as chaves de um código, eles criam um bloco. Na verdade está mais para o using do C# já que há uma consequência garantida no final da execução.

CREATE TABLE ValueTable (id int);  
BEGIN TRANSACTION;              -- aqui começa a transação
       INSERT INTO ValueTable VALUES(1);  
       INSERT INTO ValueTable VALUES(2);  
COMMIT;                         -- aqui termina e "grava" tudo

Enquanto não dá o COMMIT essas inserções não constam de fato no banco de dados. Um ROLLBACK descartaria tudo feito antes.

A documentação tem mais informações e o assunto é interessante, pena não ter mais perguntas sobre isto.

Para melhor compreensão é preciso entender o que é o ACID.

O que é o ACID no SQL?

A sigla ACID significa:

1. AtomicidadeÉ algo indivisível. Ou tudo o que está em uma transação deve ser realizado com sucesso, ou nada deve ser realizado. Pelo menos nada deva ser considerado como realizado. Sem a atomicidade fica difícil se não impossível manter as outras características, por isso a transação é importante.
2. ConsistênciaO banco de dados deve ter uma transação terminada em estado consistente, ou seja, deve respeitar todas as regras impostas no banco de dados para todos os envolvidos na transação.
3. IsolamentoUma transação não pode interferir em outra enquanto está em atividade. Só após sua conclusão é que o seu resultado ficará disponível para outras transações.
4. DurabilidadeAo final da transação o resultado deve permanecer no banco de dado, aconteça o que acontecer.

Sem essas características o banco de dados não é confiável. Uma operação pode terminar pela metade, ou pode estar em um estado que causará problemas, ou manipulará dados que ainda não se sabe se serão úteis ou finais ou ainda pode perder o que foi feito.

Alguns bancos de dados se dizem ACID quando na verdade são apenas quase ACID 🙂 Eles são ACID, pero no mucho, tem alguns casos que eles não garantem isso, ou seja eles dizem que estão mais ou menos grávidos 🙂

O conceito é implementado para evitar condições de corrida e problemas semelhantes. No link tem um exemplo de conta bancária que é o clássico problema.

1. Se você precisa atualizar o saldo de uma conta precisa garantir que tudo o que é feito para mudar o saldo deve ser realizado até o fim, não pode mudar em uma tabela e não mudar em outra, não pode debitar em uma conta e deixar de debitar em outra (atomicidade).
2. No final da transação não pode deixar o saldo com um valor que foi determinado que não seja permitido, por exemplo: saldo normal mais limite ser negativo, ou o saldo não ter uma operação vinculada a ele (consistência).
3. Não pode deixar outras transações verem o crédito até que se conclua, já que a transação pode não dar certo ou pode ser que durante ela também tenha outros créditos ou débitos que afetarão o resultado final (isolamento).
4. Não pode deixar de manter o novo valor do saldo e tudo o que foi realizado para se chegar nele (durabilidade).

Normalmente isto é obtido por um sistema de journaling, write ahead log ou alguma forma semelhante. É importante que as operações da transação sejam feitas fora do acesso normal do banco de dados e só entre no seu final. As técnicas mais utilizadas são two phase locking, multi version concurrency control e snapshot isolation.

Artigo da Wikipedia.

Bancos de dados Relacionais e NoSQL

Os bancos de dados chamados de NoSQL não costumam ser ACID (alguns até são, alguns são parcialmente, ou seja, não são). Os que são não oferecem todas aquelas vantagens que dizem que o NoSQL tem, não existe milagre. Ou eles não são confiáveis. Claro que problemas que o desempenho geral e a distribuição são mais importantes que a confiabilidade da informação então ele é útil. Bancos de dados ACID podem ter melhor desempenho em certos cenários.

Se não souber o que está fazendo o NoSQL pode ser trágico e muito mais lento. NoSQL opta pelo BASE que podemos chamar de oposto do ACID, ainda que não seja bem assim.

Veja sobre o CAP theorem. Ele mostra que não existe milagre. Não acredite em falsos profetas que prometem que vão resolver todos os problemas e quebrar o CAP theorem.

Voltando a falar sobre Transações (Transactions) em SQL

-- código #1
declare @contaDe char(6), @contaPara char(6), @Valor money;

set @contaDe= '029820';
set @contaPara= '407302';
set @Valor= 1200.00;

BEGIN TRANSACTION;

UPDATE CONTA_CORRENTE
  set saldoConta= saldoConta - @Valor
  where numConta = @contaDe;

UPDATE CONTA_CORRENTE
  set saldoConta= saldoConta + @Valor
  where numConta = @contaPara;

COMMIT;
go

Python 3.11 vem com grandes melhorias de desempenho