Suporte a transações (transaction-safe):

Uma transação compreende um ou mais comandos para a manipulação de dados que devem ser executados como se fossem apenas um. O exemplo mais simples é o caso de um sistema para bancos.

Quando um correntista faz uma transferência entre duas contas, o sistema deve subtrair o valor da transferência do saldo da conta de origem e adicionar este valor à conta destino. Ao iniciar a transação, devemos informar ao banco de dados que ele deve criar a transação:

BEGIN;

E, em seguida, enviar os comandos SQL:

UPDATE contas SET saldo = saldo - 100 WHERE conta = "origem";

UPDATE contas SET saldo = saldo + 100 WHERE conta = "destino";

Ao enviarmos todos os comandos necessários (pode haver outros, como criar os lançamentos em outros arquivos e assim por diante) devemos informar ao BD que a transação acabou e que ele pode gravar os dados:

COMMIT;

Caso a conta destino não possa receber o valor informado por estar fechada, podemos cancelar todos os comandos enviados desde o BEGIN. Mas para isso, o comando COMMIT não pode ter sido enviado:

ROLLBACK;

E pronto, tudo volta a ser como antes. Embora apenas dois comandos SQL sejam executados pelo BD em menos de um segundo, este tempo "ínfimo" pode ser o necessário para tudo dar errado em caso de falta de luz ou falha de hardware.

Recuperação de falhas (crash recovery):

Seguindo o mesmo exemplo acima, no caso de uma falha no hardware ou qualquer outro inconveniente, um COMMIT mal sucedido será revertido ao estado anterior pelo engine MyISAM, que utiliza logs para gravar as últimas transações e recuperar as tabelas em caso de erro. Com isso, diminui-se a possibilidade de "sumir o dinheiro" no caso da aplicação citada acima. Para recuperar uma falha, basta apenas reiniciar o MySQL e o engine MyISAM se encarregará do resto.

Bloqueio de registros (row-level locking):

Como exemplo, dois operadores irão atualizar o valor de uma conta corrente: o cliente 1 irá adicionar R$ 100,00 e o cliente 2 irá subtrair R$ 50,00. Só que as duas operações ocorrem simultaneamente.

Sem o bloqueio, os clientes 1 e 2 lêem o saldo de uma conta como R$800,00, o primeiro adiciona R$ 100,00 e grava no BD o saldo de R$ 900,00. O cliente 2 subtrai R$ 50,00 e grava R$ 750,00. Nesta situação, os R$ 100,00 depositados sumiram!

Ao se utilizar o bloqueio, aquele cliente que leu o registro primeiro pode bloquear a tabela até gravar as alterações, e o segundo cliente fica esperando liberar o bloqueio.

As tabelas INNODB têm suporte a bloqueio por tabela, ou seja, mesmo que outro cliente deseje alterar o valor de outro registro, deve aguardar que outros clientes gravem os dados primeiro e liberem a tabela. Já no caso das tabelas MyISAM o bloqueio pode ser feito por registro, deixando a aplicação muito mais rápida. E para melhorar a situação, o engine MyISAM ocupa pouca memória por registro bloqueado.

Para bloquear um ou mais registros para leitura, basta adicionar ao final da query os comandos: LOCK IN SHARE MODE (Ex.: SELECT * FROM tabela WHERE codigo=1 LOCK IN SHARE MODE). Este modo de bloqueio serve quando se deseja bloquear um registropai para adicionar um registro-filho, e ter certeza que o registro-pai existirá quando for feita a inserção do registro-filho.

Ao fazer alteração no registro, utilize no final da query o comando FOR UPDATE (Ex.: SELECT * FROM tabela WHERE codigo=1 FOR UPDATE). Todos os registros selecionados serão bloqueados. O bloqueio termina quando for enviado o comando COMMIT.

Integridade referencial (FOREIGN KEY constraints):

Em uma aplicação que utilize o MySQL, deve-se certificar que ao tentar apagar um registro-pai, os filhos também devem ser apagados ou a exclusão deve ser cancelada. Com a utilização de foreign keys deixamos esta tarefa para o MySQL.

Na versão atual do MySQL (3.23.49), o suporte é para apenas a restrição de índices. Mas a partir da versão 3.23.50, o suporte se estende para excluir os registros-filho ou deixar o valor deles como nulo (NULL).

Os índices na tabela-filho não são criados automaticamente, e devem ser criados anteriormente.

Para criar as tabelas siga o exemplo abaixo:

CREATE TABLE tabela_pai ( codigo INT NOT NULL, PRIMARY KEY (codigo)) TYPE=INNODB;

CREATE TABLE tabela_filho( codigo INT, codigo_pai INT,INDEX tabpai_ind (codigo_pai), FOREIGN KEY (codigo_pai) REFERENCES tabela_pai(codigo) ON DELETE CASCADE ) TYPE=INNODB;

A criação do índice segue a seguinte sintaxe:

FOREIGN KEY (coluna_da_tabela_filha) REFERENCES nome_da_tabela_pai (campo_na_tabela_pai).

Onde campo_na_tabela_pai deve ser um índice exclusivo e coluna_da_tabela_filha deve ter um índice apenas para um campo, assim como codigo na tabela_pai e codigo_pais na tabela_filho devem ser do mesmo tipo e, dependendo do tipo, do mesmo tamanho.

Deve-se atentar para um problema na versão atual do MySQL. Quando existem tabelas com relacionamentos, não se deve usar o comando ALTER TABLE nas mesmas, ou os relacionamentos serão perdidos. Para se alterar uma tabela com relacionamento ou até mesmo criar mais relacionamentos é necessário excluir a tabela e recriá-la com as alterações necessárias. A partir da versão 3.23.50 será possível utilizar o comando ALTER TABLE nessas tabelas.