MySQL esegue transazioni ACID e integrità referenziale

Qualsiasi applicazione che includa un database deve rispettare le caratteristiche ACID, nelle applicazioni e nei gestori di database professionali il concetto ACID si riferisce alle caratteristiche o proprietà che garantiscono che le transazioni nei database siano eseguite in modo sicuro e affidabile. Nello specifico ACID sta per atomicità, consistenza, isolamento e durata.

Una transazione all'interno di un database è, ad esempio, l'inserimento di un record o, se lo vediamo come un modello di business, Aggiungi nuovo cliente, Modifica prodotto. Le transazioni producono sempre un cambiamento, inseriscono, modificano, eliminano, una query non è una transazione poiché non produce cambiamenti.
Specificare le proprietà dell'ACID

Atomicita

È la proprietà che garantisce e verifica se la transazione è stata eseguita o meno e questa proprietà indica che se un'operazione non viene eseguita completa allora viene annullata, ad esempio supponiamo che stiamo inserendo un record e il server va in crash, un record viene registrato nel mezzo, quindi a causa dell'atomicità questo record non verrà registrato.
Un altro esempio se viene effettuato un pagamento online e l'importo viene detratto dal nostro conto ma il pagamento fallisce o il sistema va in crash, allora la transazione viene annullata e il database non la registra.

Consistenza

È la proprietà che garantisce l'esecuzione di transazioni che possono concludersi senza problemi. Questo concetto ha a che fare con l'integrità del database. Impedisce che i dati vengano modificati e perdano significato o siano senza alcun riferimento, ad esempio, un cliente non può essere cancellato mentre ha effettuato un acquisto in qualche momento. Se desideri eliminare il cliente, devi prima eliminare tutte le fatture e i dati relativi a quel cliente.

Solitudine

È la proprietà che garantisce che se due o più operazioni si verificano contemporaneamente verranno eseguite una dopo l'altra e se vengono eseguite in parallelo ciascuna lo farà indipendentemente dall'altra per evitare possibili errori.

Durata

È la proprietà che è responsabile di garantire che una transazione sia stata effettuata e che le modifiche apportate dalla transazione siano permanenti, anche in caso di problemi come mancanza di energia elettrica o guasti del sistema.
MySQL supporta il formato InnoDB, che è una forma di archiviazione dati per MySQL, inclusa come formato di tabella standard in tutte le distribuzioni MySQL. Questo formato supporta transazioni di tipo ACID e integrità referenziale.
Faremo alcuni esempi di implementazione di ACID con Mysql, le query potrebbero quindi essere implementate in qualsiasi linguaggio di programmazione. In questo database, ogni utente avrà un livello di privilegi e azioni che può eseguire nel sistema dell'azienda. Ci concentreremo solo su quella funzionalità.
Inizieremo creando un database AziendaDB da phpmyadmin, quindi creeremo una tabella utenti e selezioneremo il motore di archiviazione InnoDB.

 - Struttura della tabella per la tabella `users` CREATE TABLE IF NOT EXISTS` users` (`userid` int (10) NOT NULL,` name` varchar (150) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Aggiungiamo alcuni dati alla tabella utenti

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

In questo caso creiamo la chiave primaria della tabella utenti che sarà userid

 - Indici della tabella `users` ALTER TABLE` users` ADD PRIMARY KEY (`userid`);

Successivamente creeremo la tabella dei livelli

 - Struttura della tabella per la tabella `levels` CREATE TABLE IF NOT EXISTS` level` (`levelid` int (11) NOT NULL,` level` varchar (50) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Aggiungiamo alcuni dati alla tabella dei livelli

 INSERT INTO `levels` (` levelid`, `level`) VALORI (1, 'Base'), (2, 'Intermedio'), (3, 'Avanzato');

Creiamo quindi la tabella dei privilegi, dove indicheremo il livello dei permessi di ciascun utente

 - Struttura della tabella per la tabella `privileges` CREATE TABLE IF NOT EXISTS` privilegi` (`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;

Aggiungiamo alcuni dati alla tabella dei privilegi

 INSERT INTO `privileges` (`privilegi`, `levelid`,` userid`) VALORI (1, 1, 1), (2, 2, 3), (3, 1, 2);

Quindi aggiungiamo le relazioni dall'editor SQL, assegno una chiave esterna che mette in relazione i privilegi e la tabella degli utenti tramite l'id utente e la chiave esterna che mette in relazione i privilegi con i livelli tramite l'id di livello.

 - Filtri per la tabella `privileges` ALTER TABLE` privilegi` ADD CONSTRAINT `levellfk` FOREIGN KEY (` levelid`) REFERENCES `levels` (` levelid`), ADD CONSTRAINT `privilegesfk` FOREIGN KEY (` userid`) REFERENCES ` utenti ` (`ID utente`);

Successivamente consultiamo il database per vedere se i dati sono corretti

 SELECT utenti.nome, livelli.livello FROM utenti, livelli, privilegi WHERE privilegi.idlevel = livelli.levelid ANDusers.userid = privilegi.userid

Vediamo come funziona successivamente proviamo ad inserire i privilegi ad un utente e livello che non esistono.
L'istruzione sarà la seguente INSERT INTO privilegi VALUES (privilegeid, userid, levelid); così

 INSERIRE NEI privilegi VALORI (6, 8,10);

Questo dà un errore poiché la chiave esterna di userid nella tabella dei privilegi creerebbe un'incoerenza se aggiungiamo un utente che non esiste nella tabella degli utenti, qui evitiamo l'errore, con il formato MySam i dati sarebbero stati salvati senza problemi.
Successivamente proveremo a eliminare un utente dalla tabella utenti:

 DELETE FROM `utenti` WHERE userid = 3

Quando si esegue l'istruzione SQL ci darà un errore, perché il client non può essere eliminato perché ha dati in altre tabelle in questo caso il client con id 3 è nella tabella dei privilegi, se vogliamo eliminarlo dobbiamo prima rimuoverlo da tutti i tavoli e dopo il tavolo del cliente.

Per cancellare questo tipo di record con chiavi esterne si usa quello chiamato delete in CASCATA, in cui tutti i record relativi a una determinata query verranno eliminati in tutte le tabelle in cui hanno relazioni di chiave esterna. Per effettuare questa transazione utilizziamo la funzione SU ELIMINA CASCATA.
La prima cosa sarà dare il permesso di cancellare in cascata, ricorda che all'avvio di default il tipo di riferimento è LIMITARE che indica che i dati in quel campo della tabella non possono essere aggiornati o cancellati, questo per motivi di sicurezza, qualsiasi frase eseguita non sarà in grado di eseguire alcuna transazione, quindi cambiamo i permessi di modifica e cancellazione.

 ALTER TABLE `privileges` ADD CONSTRAINT` privilegif.webpk` FOREIGN KEY (`userid`) REFERENCES` users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE;

Eseguiamo di nuovo la query SQL

 DELETE FROM `utenti` WHERE userid = 3

Quindi possiamo eseguire la query sql per verificare che non sia più in nessuna tabella:

 SELEZIONA utenti.nome, livelli.livello FROM utenti, livelli, privilegi WHERE privilegi.idlevel = livelli.idlevel AND users.userid = privilegi.userid

L'utente 3 è stato rimosso dalla tabella degli utenti e anche dalla tabella dei privilegi, poiché l'id utente aveva una chiave esterna.
Questo è lo stesso per gli aggiornamenti durante la modifica, può essere collegato a cascata per mantenere l'integrità referenziale in Mysql e la relazione tra le tabelle.
Vediamo cosa succede se aggiungiamo dati errati nell'inserimento della catena, ad esempio aggiungiamo un utente, ma quando aggiungiamo privilegi otteniamo l'id sbagliato

 INSERIRE NEI VALORI degli utenti (5, 'Julia Montaña'); INSERT INTO privilegi (`privilegeid`,` levelid`, `userid`) VALUES (6, 2, 6);

In questo caso, l'utente verrà salvato ma non i suoi privilegi perché l'ID 6 non esiste nella tabella utenti.Ti è piaciuto e hai aiutato questo Tutorial?Puoi premiare l'autore premendo questo pulsante per dargli un punto positivo