SQL – Para que serve uma MySQL Transaction?

SQL – Para que serve uma MySQL Transaction?

27 de dezembro de 2017 3 Por Ramos de Souza Janones
Powered by Rock Convert

Bancos de dados SQL em geral, não somente o MySQL, 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.

O Problema

No exemplo citado na pergunta, imagine que as queries #1, #2 e #3 são operações que afetam a base de dados e não usamos uma transação para controlá-las. Vamos usar como exemplo um e-commerce:

  1. Atualiza dados de entrega do cliente
  2. Insere um novo registro da compra efetuada, verificando se possui estoque
  3. Debita o estoque dos produtos

Agora imagine dois clientes tentando finalizar suas compras neste e-commerce fictício. O servidor recebe duas requisições quase simultaneamente e começa a processar os pedidos na sequência apresentada acima. Os dois pedidos estão sendo processados simultaneamente em threads diferentes.

Imagine ainda que tanto o cliente A quanto o cliente B selecionaram um produto que tem apenas uma unidade em estoque. Podemos acabar com a seguinte linha de execução:

  1. Thread A atualiza dados do cliente A (passo #1), verifica o estoque insere o registro da compra (passo #2)
  2. A thread A é bloqueada e B passa a ser executada
  3. Thread B atualiza dados do cliente B (passo #1), verifica o estoque insere o registro da compra (passo #2)
  4. Thread B atualiza o estoque, que agora fica zerado
  5. A thread B é bloqueada e A passa a ser executada
  6. Thread A atualiza o estoque, que agora fica negativo!

Note que apesar do código verificar o estoque a ordem de execução faz com que a verificação não seja garantida no passo seguinte.

A Solução

Bancos de dados transacionais usam o conceito ACID:

  • Atomicidade: uma transação é uma sequência de operações indivisível, ou é executado como um todo, ou tudo é desfeito.
  • Consistência: ao final da transação, o estado dos dados deve ser consistente.
  • Isolamento: embora alguns sistemas permitam quebrar o isolamento, em geral, uma transação em andamento não pode ser acessada por outras transações de modo a evitar leitura de um estado inconsistente, uma “sujeira”.
  • Durabilidade: em caso de sucesso (commit) a persistência dos dados deve ser garantida

Para garantir esses conceitos, em geral, os bancos de dados usam bloqueios quando ocorrem acessos simultâneos à mesma estrutura de dados. Ou seja, se alguém já está mexendo nos dados, os demais tem que esperar ele acabar e aguardar sua vez na fila.

Leia também:  

Na prática

Ao usar bancos de dados transacionais, nós podemos usufruir deste controle de gerenciamento por parte dos SGBDRs (Sistemas Gerenciadores de Bancos de Dados Relacionais).

Incluindo o conceito de transação ACID no exemplo anterior, vamos ver como fica a execução:

  1. Thread A inicia uma transação, atualiza dados do cliente A (passo #1), verifica o estoque insere o registro da compra (passo #2)
  2. A thread A é bloqueada e B passa a ser executada
  3. Thread B inicia uma transação, mas ao tentar atualizar os dados do cliente B ela é bloqueada porque a transação de A ainda não acabou
  4. Thread A atualiza o estoque, que agora fica zerado, e faz commit na transação.
  5. A thread B é desbloqueada e passa a ser executada
  6. A thread B atualiza dados do cliente B (passo #1), verifica o estoque e retorna um erro pois não encontra o produto disponível
  7. Thread B executa um rollback para desfazer as alterações que já havia efetuado

O resultado final é como se somente a thread A tivesse executado e B nunca existisse.

Nem tudo é um mar de rosas

Existem alguns problemas inerentes às transações ACID, sendo o desempenho o maior deles.

Embora seja importante garantir a integridade dos dados, para muitos sistemas onde a disponibilidade é o fator mais crítico, um modelo que bloqueia acessos simultâneos torna-se inviável.

Este é um dos principais fatores para o surgimento e a adoção de diversos sistemas de bancos de dados não transacionais e NoSQL.

Powered by Rock Convert
Como vender Software - Seja desktop, web ou MobilePowered by Rock Convert

O importante é entender que o uso de transações tem um custo e em algumas ocasiões este pode ser alto demais. Uma das representações mais comuns do trade-off de persistência de dados é a seguinte (retirada deste artigo):

Trade-off entre propriedades de bancos de dados

O gráfico demonstra que consistência, disponibilidade e particionamento (escalar o banco de dados em diversos nós) são recursos que afetam uns aos outros. Você simplesmente não pode ter o melhor dos três, segundo o teorema de CAP.

Bancos de dados relacionais geralmente sacrificam o particionamento em prol da consistência e da disponibilidade, enquanto alguns sistemas NoSQL sacrificam a consistência dos dados.

O que são as transações Begin, Commit e Rollback?

Na verdade transação é 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. Entenda o que é o ACID.

LEIA TAMBÉM:  SQL - Índices desnecessários no banco são um problema?

Esses 3 comandos SQL são para controlar isso.

BEGIN TRANSACTION indica onde ela deve começar, então os comando SQL a seguir estarão dentro desta transaçã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.

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 comando 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.

14

Transações garantem atomicidade a um conjunto de operações, ou seja, ou todas elas terão sucesso, ou todas elas falharão. Um exemplo clássico é o de uma transferência bancária, onde duas alterações são necessárias nos seus registros: 1) reduzir o saldo da conta A em X; 2) aumentar o saldo da conta B em X. Se a primeira operação for executada, e logo em seguida o sistema apresentar uma falha, o seu banco de dados ficará inconsistente: há X a menos na conta A, mas a conta B continua com o mesmo saldo. Inverter as operações não ajuda muito, pois pode ocorrer o caso inverso.

LEIA TAMBÉM:  SQL e Regex - Entenda quando e como usar em instruções SQL

No seu exemplo, o SGBD garante que – no seu armazenamento persistente – ou todas as query 1query 2 e query 3 completarão com sucesso (commit), ou o banco permanecerá no mesmo estado que se encontrava antes da query 1 começar (rollback). Note que um rollback explícito não é necessário para que essa garantia seja cumprida – se o sistema falhar antes do commit, quando o mesmo for reiniciado o SGBD se encarregará de desfazer as ações parciais. Do mesmo modo, se o sistema falhar logo após o commit – antes que a persistência do restante dos dados esteja assegurada – o SGBD após a reinicialização se encarregará de completar as ações parciais, tal como registradas no seu diário (journal).

Outro benefício das transações, como apontado por @hernandes, é promover o isolamento do acesso por processos concorrentes. Se um processo iniciou uma sequência de queries, e outro processo quer acessar o banco, não seria correto que ele o acessasse no estado [provisório] no qual o primeiro processo o deixou. Não só o banco estaria [potencialmente] inconsistente, mas não há garantia alguma que aquelas alterações serão efetuadas de fato (pois o primeiro processo pode dar rollback). Ainda, alterações pelo processo 2 poderiam afetar as operações do processo 1.

Existem vários níveis de isolamento, que dependem do SGBD e de outros fatores:

  • read uncommited – uma transação pode ler as escritas de outra antes do commit;
  • read commited – uma transação só pode ler dados que já passaram pelo commit. Entretanto, duas ou mais leituras sucessivas [iguais] podem retornar dados diferentes (se outras transações acontecerem durante o curso da primeira);
  • repeatable read – além da característica anterior, garante-se que toda linha lida uma vez durante o curso da transação permaneça igual durante leituras subsequentes. Ainda assim, é possível que uma mesma query retorne um número diferente de linhas e/ou linhas diferentes (se a transação concorrente acrescentou uma linha nova, por exemplo);
    • Nota: esse é o nível padrão do InnoDB no MySQL.
  • serializable – o fenômeno acima não ocorre, toda leitura igual terá um resultado igual (alternativamente: o sistema não permitirá um commit se houver colisão entre duas transações concorrentes).

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

LEIA TAMBÉM:  Principais diferenças entre MongoDB e MySQL ou outro RDBMS?

SQL – Banco de dados

Artigos e dicas sobre banco de dados que você vai gostar:

MongoDB

– Principais diferenças entre MongoDB e MySQL ou outro RDBMS

– Southbank Software apresenta dbKoda: uma ferramenta de desenvolvimento Open Source para MongoDB

MySQL e Instruções SQL em geral

– SQL e Regex – Entenda quando e como usar em instruções SQL

– MySQL – Manter o formato Date e DateTime em formato dd/mm/Year e dd/mm/Year:HH:mm:ss

– SQL – O que são VIEWS SQL, vantagens e desvantagens

– Instrução Insert MySQL e JSON com todos os bancos brasileiros

– PHP e MySQL – Entendendo as codificações iso-8859-1 e UTF-8 e outras

– Como redefinir a senha de root do MySQL ou do MariaDB

– Para que serve uma MySQL Transaction?

– Banco de Dados – Índices desnecessários no banco são um problema?

– Entenda a diferença entre LIKE, IN e BETWEEN no MySQL

– LinkedIn desenvolve a ferramenta MySQL Query Analyzer

– Como otimizar consultas MySQL

– Quando é interessante desnormalizar o banco de dados?

– Entenda Views em SQL, vantagens e desvantagens

– O que é normalização de banco de dados

– Por que não devemos usar funções do tipo mysql_*

SQL – Entenda as diferenças entre aspas simples e duplas

– SQL – Not IN ou Not EXISTS quando usar?

– SQL – Entendendo JOINS

– PHP Do Zero Ao Profissional – Análise do Curso

 

Powered by Rock Convert
Siga os bons!
Últimos posts por Ramos de Souza Janones (exibir todos)

Sumário
Para que serve uma MySQL Transaction?
Nome do artigo
Para que serve uma MySQL Transaction?
Descrição
Bancos de dados SQL em geral, não somente o MySQL, 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.
Autor
Nome
Ramos da Informática
Logo