Transacao_MySQL.

Advanced Series – Stored
Procedures Parte 2
(Transações)
Neste artigo, daremos continuidade aos procedimentos
armazenados, ampliando os recursos que foram
abordados na Parte 1 desta série de artigos.
Neste artigo, daremos continuidade aos procedimentos armazenados, ampliando os
recursos que foram abordados na Parte 1 desta série de artigos. Vamos enriquecer os
conceitos a cada passo, passando pelo conceito de transação e os atributos em
conformidade com as propriedades ACID, como o MySQL garante essa conformidade,
o modo AUTOCOMMIT e exemplos que exibem a sintaxe utilizada para produzir os
recursos abordados. Serão apresentadas as principais estruturas para iniciar e finalizar
transações e como utilizarmos de SAVEPOINT.
Após este artigo, você será capaz de:
·
Definir o que é uma transação e quais as suas vantagens;
·
Definir o que é COMMIT e ROLLBACK e quais são as suas funções;
·
Definir o que é o modelo ACID;
·
Explicar como o MySQL dá suporte ao modelo ACID;
·
Definir e utilizar AUTOCOMMIT;
·
Iniciar e finalizar uma transação no MySQL;
·
Definir e utilizar SAVEPOINT;
Introdução – Transação
Sabemos que grande parte dos vários processos existentes no mundo real, como
transferências de valores em instituições bancárias, DOC’s, compras em comércio
eletrônico e outros serviços utilizam transações que são um atributo ou artifício comum
em bancos de dados comerciais. Muitos conceitos surgiram desde que Edgard Frank
Codd, ou simplesmente Ted, divulgou as Leis do Modelo Relacional e criou este
modelo. Tais leis eram para serem seguidas à risca para que um SGBD realmente
contemplasse tal modelo, mas, alguns destes relaxam nas implementações para garantir
mais performance. Alguns autores como C. J. Date que é a principal referência após a
morte de Codd, deram continuidade ao trabalho e defende o conceito de transação da
seguinte forma:
“Transação é uma unidade lógica de trabalho, envolvendo diversas operações de
bancos dados.”
(C. J. Date – Introdução a Sistemas de Bancos de Dados – página 63)
Há ainda quem diga que uma transação é “todo e qualquer comando que altera o estado
do banco de dados”, mas de acordo com o que defende Codd e C. J. Date, este conceito
não está completo e veremos mais à frente o por quê. Ainda, o usuário deve ser capaz de
informar ao sistema quando operações distintas fazem parte de uma transação.
Basicamente, esta começa quando uma operação BEGIN é executada, e termina quando
uma operação COMMIT ou ROLLBACK correspondente é executada. Desta forma,
podemos perceber que uma transação logo é formada caracteristicamente por estes
comandos.
Podemos consistir o conceito de transação com um exemplo clássico que é utilizado
quando precisamos iniciar este assunto em sala de aula, que é uma explicação análoga a
uma transferência bancária. Em meio a um processo de transferência de valor entre
contas, temos alguns comandos SQL que são executados todos de uma vez, formando a
tal unidade lógica, que por sua vez, envolve diversas operações de bancos de dados, tais
como INSERT e UPDATE. Transações também podem conter DELETE e SELECT,
mas no caso da transferência, utilizaremos somente o UPDATE.
Digamos que nossa agência tenha dois correntistas, A e B e num belo dia, A resolve
fazer uma transferência de R$ 50,00 para B. Esta transação será feita pela internet,
através de um sistema de internet banking, então, A acessará a sua conta e completará a
operação. Do lado servidor, o banco de dados recebe o valor e a conta de destino, tal
valor é subtraído da conta de origem e adicionado na conta de destino. Nesse momento
há uma conferência por parte do SGBD – “tudo correu bem” – então a transação é
finalizada com sucesso. Vamos esboçar o procedimento.
Figura 01. Transferência de valor entre contas.
Como o procedimento da Figura 01?
Na linha 1, trocamos o delimitador para utilizarmos o “;” em meio ao procedimento, na
linha 2, começamos a definição do procedimento com o comando CREATE
PROCEDURE, seguido pelo seu nome e os parâmetros, que são respectivamente a
origem do valor, o destino do valor e o valor monetário. Na linha 3, utilizamos o
comando BEGIN para delimitar os comandos de nosso procedimento. Nas linhas 4 e 5,
temos os comandos UPDATE que subtrai o valor monetário da conta de origem e o
soma a conta de destino.
COMMIT e ROLLBACK
Neste básico exemplo, não utilizamos COMMIT e ROLLBACK mas já dá para
entendermos o que é uma transação. Em um exemplo mais à frente, veremos que o
COMMIT é uma operação de confirmação de que correu tudo bem e que todos os
comandos que fazem parte da unidade lógica, ou do procediemtno armazenado, forma
executados com sucesso e o banco de dados encontra-se em um estado consistente. O
ROLLBACK retornará todos os comandos anteriores àquele onde houve um erro, ou
seja, tudo será desfeito se houve um problema com algum comando dentro de uma
transação.
Para melhor entendermos tal situação de ROLLBACK que é também chamada por Date
de “recuperação do estado consistente”, usemos novamente nosso exemplo e imagine
se caso o primeiro comando seja executado, mas na hora de creditar o valor na conta B
houvesse um erro, para onde iria o dinheiro? Perdeu? Como é? Isso não poderia
acontecer de maneira nenhuma. Nesse caso, o SGBD se encarregaria de fazer o
ROLLBACK dos comandos anteriores ao erro retornando o banco de dados para o
momento de antes, devolveria na verdade o valor para A, mantendo intacta sua quantia
de saldo imediatamente anterior ao início da transação. Sendo assim, vamos aos
conceitos:
COMMIT: indica o término de uma transação bem-sucedida. Ela informa ao
gerenciador de transações que uma unidade lógica de trabalho foi concluída com
sucesso, o banco de dados já está novamente em um estado consistente e todas as
atualizações feitas por essa unidade de trabalho já podem se tornar permanentes.
ROLLBACK: assinala o término de uma transação malsucedida. Ela informa ao
gerenciador de transações que algo saiu errado, que o banco de dados pode estar em um
estado inconsistente, e que todas as atualizações feitas pela unidade lógica de trabalho
até agora devem ser desfeitas.
As vantagens do uso de transações em um ambiente web por exemplo, são inúmeras. As
principais são:
Não carregar com processamento no lado cliente;
Ter uma estrutura modular;
Evitar perda de informações, pois um usuário pode simplesmente fechar o browser
enquanto e apagar todo o carrinho de compras;
Diminuir o tráfego na rede já que estaremos trafegando parâmetros e os entregando ao
banco de dados;
Ter controle sobre os erros que possam acontecer com as lógicas de definição de fluxo
de dados;
Controle de atualizações de registros através de bloqueios.
Propriedades ACID
Para que haja toda essa engenharia, devemos ainda conhecer as propriedades ACID, que
são atributos que toda transação precisa ter para que não existam problemas durante a
execução. Não aprofundaremos muito em tais propriedades pois esse tema daria um
novo artigo, mas, o ACID é uma sigla que significa justamente:
Atomicidade: toda transação deverá ser atômica, o verdadeiro “tudo ou nada”;
Consistência: as transações devem preservar a consistência do banco de dados, ou seja,
transforma um estado consistente do banco de dados em outro estado consistente, sem
necessariamente preservar o estado de consistência em todos os pontos intermediários.
Isolamento: as transações são isoladas umas das outras, de acordo com o nível de
isolamento definido no momento em que a transação se inicia, que no MySQL são:
REPEATABLE READ, READ COMMITED, READ UNCOMMITED e
SERIALIZABLE. (Figura 02.)
Figura 02. Nível de isolamento padrão definido no MySQL.
·
Durabilidade: uma vez comprometida uma transação, suas atualizações
sobrevivem no banco de dados mesmo que haja uma queda subseqüente do sistema.
Suporte do MySQL às Propriedades ACID
O MySQL dá 100% de suporte às propriedades ACID com a Engenharia de
Armazenamento INNODB da INNOBASE Oy, que é a Engenharia ou Engine mais
comumente utilizada para se trabalhar com um ambiente seguro, como possibilidade de
Auto Recovery que é a recuperação do banco de dados após uma falha e amparo com
vários arquivos de log. Para maiores informações sobre o INNODB, consulte o site da
INNOBASE Oy. O suporte para este atributo é provido pela MySQL AB.
AUTOCOMMIT
Normalmente, em SGBD’s como o SQL Server e o MySQL, gerenciadores com os
quais tenho mais experiência, temos transações implícitas que são estreladas pelos
comandos SELECT, INSERT, UPDATE e DELETE, ou seja, comandos DML e
transações explícitas que podem ser iniciadas por um BEGIN e finalizadas por um
END.
As transações implícitas são aquelas que têm um COMMIT interno e já tornam
quaisquer modificações nos dados permanentes no(s) arquivo(s) de dados em disco.
Transações explícitas são aquelas que têm um início e fim explicitado pelo
desenvolvedor. O MySQL utiliza o modo chamado AUTOCOMMIT para controlar a
efetividade da modificação de dados das transações no banco de dados, ou seja, se
AUTOCOMMIT = 1, as transações implícitas sempre terão um COMMIT interno,
enquanto que, se AUTOCOMMIT = 0, o SGBD esperará por um COMMIT explícito
para tornar permanente as últimas manipulações nos dados.
Vejamos como funciona o AUTOCOMMIT num comparativo. A Figura 03 tratará de
uma transação implícita com o modo AUTOCOMMIT = 1, em seguida faremos um
teste com o modo AUTOCOMMIT = 0, já na Figura 04.
Figura 03. Com AUTOCOMMIT = 1, instruções DML recebem um COMMIT interno
e se tornam permanentes em disco.
Figura 04. Com AUTOCOMMIT = 0, qualquer manipulação nos dados aguarda por
um COMMIT ou ROLLBACK.
Trabalhando com Transações no MySQL
Para trabalharmos com transações no MySQL, podemos iniciá-las explicitamente ou
dentro de um procedimento armazenado, o qual é o foco deste artigo. Como uma
transação é delimitada por um BEGIN [...] END, podemos utilizar os seguintes
comandos para iniciarmos uma transação:
BEGIN (o mais comum);
BEGIN WORK;
START TRANSACTION;
Pegando embalo novamente na transferência bancária, podemos melhorar o
procedimento anterior, adicionando mais semântica ao mesmo. Já sabemos como
implementar estruturas condicionais dentro dos procedimentos que é o famoso IFTHEN-ELSE e veremos no próximo exemplo como aninhar os blocos de início e fim da
transação. Basicamente, faremos a conferência dos parâmetros enviados para o
procedimento, após a conferência entramos na lógica que manipulará os valores na
transação de transferência que ao completar a execução dos comandos parte da unidade
lógica, envia um COMMIT para efetivar os dados da memória para o arquivo de dados
e caso os valores não forem informados, o processamento cairá no ELSE e a transação
será restaurada com o ROLLBACK. Na verdade, esse procedimento ainda não tem o
conceito totalmente coberto do que realmente é uma transação, mas, como um dia eu
escutei de um amigo, “vamos com calma, dividir e conquistar”. A Figura 05 traz a
nova versão do procedimento armazenado.
Figura 05. Utilizando condicional, COMMIT e ROLLBACK.
Ao executarmos o procedimento na própria linha de comando, passando os parâmetros
de forma equivocada, a mensagem de erro será disparada e o ROLLBACK acontecerá,
como mostra a Figura 06.
Figura 06. Informando parâmetros inconsistentes do ponto de vista da lógica do
procedimento com “user variables”.
Utilizamos na chamada do procedimento, um recurso que pode ser criado per
connection, ou seja, são variáveis do usuário, amarradas à conexão do usuário que
recebem um valor através da declaração SET. Depois de atribuídos os valores, estes são
passados como parâmetro para o procediemtno armazenado. Como usamos && que é
um sinônimo de AND na condição, @v_val é igual a 0 e não maior que 0, então o erro
foi retornado, caindo no ELSE e executando o ROLLBACK.
SAVEPOINT
A partir do MySQL 4.0.14 e 4.1.1. o INNODB suporta os comando SQL SAVEPOINT
e ROLLBACK TO SAVEPOINT. Esse recurso nos permites criar “pontos de
salvamento” em meio a uma transação, possibiltando que esta seja recuperada até
determinado ponto. Pode ser feito em transações implícitas e em meio a uma transação
explícita, dentro de procedimentos, independente do valor de AUTOCOMMIT.
Obrigatoriamente, um SAVEPOINT será vinculado a uma transação, caso um novo
ponto seja adicionado fora de um BEGIN [...] END, ao retornar para este com um
ROLLBACK TO SAVEPOINT name, um erro será enviado salientando a inexistência
do ponto de salvamento referenciado, como mostra a Figura 07.
Figura 07. Criando um ponto de salvamento, um SAVEPOINT fora de instruções
BEGIN [...] END.
A Figura 08 mostra a criação de um ponto de salvamento e ao retorno ao momento
anterior após algumas inserções de dados.
Conclusão
Concluímos então mais uma parte do nosso estudos sobre os procedimentos
armazenados ou stored routines no MySQL e agora, já temos ciência de como funciona
uma transação, com vários atributos para controle interno como os pontos de
salvamento, COMMIT e ROLLBACK, além de termos visto os conceitos de das
propriedades ACID.
No próximo artigo da série, trabalharemos com mais prática envolvendo as transações,
níveis de isolamento e tratamento de erros.
Referência Bibliográfica
Manual MySQL 5.0 - http://dev.mysql.com/doc/refman/5.0/en/;
·
Dubois, Paul/Stefan, Hinz/Pedersen, Carsten – MySQL 5.0 Certification Guide –
MySQL Press;
·
Date, C. J. – Introdução a Sistemas de Bancos de Dados – American Edition –
Editora Campus;
Leia mais em: http://www.devmedia.com.br/advanced-series-stored-procedures-parte-2transacoes/7445