MySQL realizar transações ACID e integridade referencial

Qualquer aplicação que inclua uma base de dados deve obedecer às características ACID, nas aplicações e gestores profissionais de bases de dados o conceito ACID refere-se às características ou propriedades que garantem que as transações nas bases de dados sejam realizadas de forma segura e confiável. Especificamente, ACID significa atomicidade, consistência, isolamento e durabilidade.

Uma transação dentro de um banco de dados é, por exemplo, inserir um registro ou, se o vemos como um modelo de negócio, Adicionar novo cliente, Modificar produto. As transações sempre produzem uma alteração, insere, modifica, exclui, uma consulta não é uma transação, pois não produz alterações.
Especificando propriedades ACID

Atomicidade

É a propriedade que garante e verifica se a transação foi realizada ou não e esta propriedade indica que se uma operação não for realizada completa então ela é cancelada, por exemplo suponha que estamos inserindo um registro e o servidor trava, um registro é gravado no meio, então, devido à atomicidade, esse registro não será gravado.
Outro exemplo, se um pagamento online está sendo feito e o valor é deduzido de nossa conta, mas o pagamento falha ou o sistema trava, a transação é cancelada e o banco de dados não a registra.

Consistência

É a propriedade que garante que as transações que podem terminar sem problemas sejam executadas. Esse conceito tem a ver com a integridade do banco de dados. Evita que os dados sejam alterados e percam o significado ou fiquem sem qualquer referência, por exemplo, um cliente não pode ser eliminado enquanto tiver feito uma compra em algum momento. Se você deseja excluir o cliente, deve primeiro excluir todas as faturas e dados relacionados a esse cliente.

Isolamento

É a propriedade que garante que se duas ou mais transações ocorrerem ao mesmo tempo, elas serão executadas uma após a outra e se forem executadas em paralelo cada uma o fará independentemente da outra para evitar possíveis erros.

Durabilidade

É a propriedade que se responsabiliza por garantir que a transação foi realizada e as alterações efetuadas na transação são permanentes, mesmo em caso de qualquer problema como falta de energia elétrica ou falhas no sistema.
O MySQL suporta o formato InnoDB, que é uma forma de armazenamento de dados para MySQL, incluído como formato de tabela padrão em todas as distribuições do MySQL. Este formato suporta transações do tipo ACID e integridade referencial.
Vamos fazer alguns exemplos de implementação de ACID com Mysql, as consultas podem então ser implementadas em qualquer linguagem de programação. Nesta base de dados, cada usuário terá um nível de privilégios e ações que poderá executar no sistema da empresa. Vamos nos concentrar apenas nessa funcionalidade.
Começaremos criando um banco de dados CompanyDB do phpmyadmin, então vamos criar uma tabela de usuários e selecionar o mecanismo de armazenamento InnoDB.

 - Estrutura da tabela para a tabela `usuários` CREATE TABLE IF NOT EXISTS` users` (` userid` int (10) NOT NULL, `name` varchar (150) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Nós adicionamos alguns dados à tabela de usuários

 INSERT INTO `users` (` userid`, `name`) VALORES (1, 'Carlos Alberte'), (2, 'Pablo Callejos'), (3, 'Ana Bolena');

Neste caso, criamos a chave primária da tabela de usuários que será a id do usuário

 - Índices da tabela `users` ALTER TABLE` users` ADD PRIMARY KEY (` userid`);

Em seguida, vamos criar a tabela de níveis

 - Estrutura da tabela para a tabela `níveis` CREATE TABLE IF NOT EXISTS` levels` (` levelid` int (11) NOT NULL, `level` varchar (50) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Nós adicionamos alguns dados à tabela de níveis

 INSERT INTO `níveis` (` levelid`, `level`) VALORES (1, 'Básico'), (2, 'Intermediário'), (3, 'Avançado');

Em seguida, criamos a tabela de privilégios, onde iremos indicar o nível de permissões de cada usuário

 - Estrutura da tabela para `privilégios` CREATE TABLE IF NOT EXISTS` privilégios` (` idprivilege` int (11) NOT NULL, `idlevel` int (11) NOT NULL DEFAULT '0',` idusuario` int (11) NOT NULL ) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Nós adicionamos alguns dados à tabela de privilégios

 INSERT INTO `privilégios` (` privilegeid`, `levelid`,` userid`) VALORES (1, 1, 1), (2, 2, 3), (3, 1, 2);

Em seguida, adicionamos os relacionamentos do editor SQL, atribuo uma chave estrangeira que relaciona os privilégios e a tabela de usuários por meio de id de usuário e a chave estrangeira que relaciona privilégios com níveis por meio de id de nível.

 - Filtros para a tabela `privileges` ALTER TABLE` privileges` ADD CONSTRAINT` levellfk` FOREIGN KEY (`levelid`) REFERENCES` levels` (`levelid`), ADD CONSTRAINT` privilegesfk` FOREIGN KEY (`userid`) REFERÊNCIAS` usuários `(` userid`);

Em seguida, consultamos o banco de dados para ver se os dados estão corretos

 SELECT users.name, levels.level FROM usuários, níveis, privilégios WHERE privileges.idlevel = levels.levelid ANDusers.userid = privileges.userid

Vamos ver como funciona a seguir tentamos inserir os privilégios para um usuário e nível que não existem.
A instrução será a seguinte INSERT INTO privilégios VALUES (privilegeid, userid, levelid); portanto

 INSERIR EM VALORES de privilégios (6, 8,10);

Isso dá um erro, pois a chave estrangeira de ID do usuário na tabela de privilégios criaria uma inconsistência se adicionarmos um usuário que não existe na tabela de usuários, aqui evitamos o erro, com o formato MySam os dados teriam sido salvos sem problemas.
Em seguida, tentaremos excluir um usuário da tabela de usuários:

 DELETE FROM `users` ONDE userid = 3

Ao executar a instrução SQL nos dará um erro, pois o cliente não pode ser deletado porque possui dados em outras tabelas neste caso o cliente com id 3 está na tabela de privilégios, se quisermos deletá-lo devemos primeiro removê-lo de todas as mesas e depois da mesa do cliente.

Para deletar este tipo de registro com chaves estrangeiras, o chamado delete é usado em CACHOEIRA, em que todos os registros relacionados a uma consulta específica serão excluídos de todas as tabelas nas quais eles têm relacionamentos de chave estrangeira. Para realizar esta transação, usamos a função EM DELETE CASCADE.
A primeira coisa será dar permissão para deletar em cascata, lembre-se que ao iniciar por padrão o tipo de referência é RESTRINGIR o que indica que os dados naquele campo da tabela não podem ser atualizados ou deletados, isto é por razões de segurança, qualquer sentença que for executada não poderá realizar nenhuma transação, então mudamos as permissões de modificação e deleção.

 ALTER TABLE `priviles` ADD CONSTRAINT` privilegesfk` FOREIGN KEY (` userid`) REFERENCES` users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE;

Realizamos a consulta SQL novamente

 DELETE FROM `users` ONDE userid = 3

Em seguida, podemos realizar a consulta sql para verificar se ele não está mais em nenhuma tabela:

 SELECT users.name, levels.level FROM users, levels, privileges WHERE privileges.idlevel = levels.idlevel AND users.userid = privileges.userid

O usuário 3 foi removido da tabela de usuários e também da tabela de privilégios, uma vez que o ID do usuário tinha uma chave estrangeira lá.
Isso é o mesmo para atualizações ao modificar, ele pode ser colocado em cascata para manter a integridade referencial no Mysql e o relacionamento entre as tabelas.
Vamos ver o que acontece se adicionarmos dados incorretos na inserção da cadeia, por exemplo, adicionamos um usuário, mas quando adicionamos privilégio obtemos o id errado

 INSERT INTO users VALUES (5, 'Julia Montaña'); Privilégios INSERT INTO (`privilegeid`,` levelid`, `userid`) VALORES (6, 2, 6);

Nesse caso, o usuário será salvo, mas não seus privilégios porque o ID 6 não existe na tabela de usuários.Gostou e ajudou este tutorial?Você pode recompensar o autor pressionando este botão para dar a ele um ponto positivo