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. 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 IF-THEN-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;