Le transazioni in Mysql e MariaDB. Cosa sono e perchè assicurano l'integrità dei dati in una sequenza di queries.
Le TRANSACTION in MySQL, o MariaDB, sono fondamentali per creare una piattaforma web robusta e sicura, perchè consentono, in presenza di una sequenza di query (insert, update, delete) tra loro collegate ed errori in una query, di annullare tutte le query precedenti, ripristinano quindi tutti i dati allo stato iniziale ed evitando così il disallineamento dei dati.
Comincio col dirti che per usare le transazioni le tabelle devono usare come Storage Engine "InnoDB" (e non ad esempio "MyIsam").
Ipotizziamo di utilizzare un database chiamato "test", ed al suo interno creiamo una tabella, molto semplificata, chiamata "msisdn" contente dei numeri di cellulare, con i seguenti due campi
- `msisdn_id`: è un id univoco auto incrementale assegnato ai records
- `msisdn_num`: qui salvo il numero di cellulare
- `msisdn_lastabb` : qui salvo la data dell'ultimo abbonamento (ad esempio ad un servizio telefonico) di questo numero
CREATE TABLE `msisdn` (
`msisdn_id` int(10) UNSIGNED NOT NULL,
`msisdn_num` bigint(15) UNSIGNED DEFAULT NULL,
`msisdn_lastabb` datetime DEFAULT NULL COMMENT 'data ultimo abbonamento'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
ALTER TABLE `msisdn`
ADD PRIMARY KEY (`msisdn_id`),
ADD KEY `msisdn_num` (`msisdn_num`);
ALTER TABLE `msisdn`
MODIFY `msisdn_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Inseriamo subito un record, cioè un numero di cellulare, che al momento non ha sottoscrizioni quindi il campo relativo alla data di ultima attivazione è vuoto.
INSERT INTO `msisdn` (msisdn_num) VALUES (347123456789);Adesso creiamo una seconda tabella chiamata "msisdn_abb" dove salviamo gli abbonamenti di questo numero, un record per abbonamento. L'utente potrebbe quindi avere più abbonamenti attivati e disattivati, quindi più record.
Si tratta quindi di una tabella collegata alla precedente.
Questa tabella contiene 4 campi
- `msisdn_abb_id` : è un id univoco auto incrementale assegnato ai records
- `msisdn_abb_msisdn` : qui salvo il numero di cellulare
- `msisdn_abb_start` : qui salvo la data di inizio dell'abbonamento
- `msisdn_abb_stop` : qui salvo la data di fine abbonamento (quando l'abbonamento verrà disattivato)
CREATE TABLE `msisdn_abb` (
`msisdn_abb_id` int(10) UNSIGNED NOT NULL,
`msisdn_abb_msisdn` int(10) UNSIGNED DEFAULT NULL,
`msisdn_abb_start` datetime DEFAULT NULL COMMENT 'data inizio abbonamento',
`msisdn_abb_stop` datetime DEFAULT NULL COMMENT 'data fine abbonamento'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
ALTER TABLE `msisdn_abb`
ADD PRIMARY KEY (`msisdn_abb_id`),
ADD KEY `msisdn_abb_msisdn` (`msisdn_abb_idmsisdn`),
ADD KEY `msisdn_abb_idope` (`msisdn_abb_idope`);
ALTER TABLE `msisdn_abb`
MODIFY `msisdn_abb_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Bene, adesso che abbiamo le nostre tabelle, ipotizziamo che il numero, inserito prima, si abboni: dovrò effettuare due query
- la prima sarà aggiornare (UPDATE) il record della tabella "msisdn" aggiornando il campo "msisnd_lastabb" con la data di inizio abbonamento
- la seconda sarà creare (INSERT) un nuovo record nella tabella "msisdn_abb" dove creiamo il nuovo abbonamento
Procediamo con la prima query
UPDATE `msisdn` SET msisdn_lastabb=NOW() WHERE msisdn_num='347123456789' LIMIT 1;Procediamo con la seconda query
INSERT INTO `msisdn_abb` (msisdn_abb_msisdn,msisdn_abb_start) VALUES ('347123456789',NOW());Se le due query fossero corrette, come lo sono in questo esempio, tutto ok, nessun problema.
Ma supponiamo che la seconda query vada in errore... provochiamolo volutamente indicando un campo con un nome non corretto
INSERT INTO `msisdn_abb` (msisdn_abb_msisdn,msisdn_abb_startX) VALUES ('347123456789',NOW());Questa query andrebbe in errore, e ci ritroveremmo con il record non inserito, ma con la data di ultimo abbonamento aggiornata nella prima tabella!
Avremo quindi un disallineamento dei dati.
Pensiamo questo esempio più in grande, con varie tabelle coinvolte, e molti records da aggiornare, ed un problema su una query.
Le TRANSACTIONS servono proprio in queste situazioni.
Vediamole a livello teorico, e poi mettiamole in pratica con un esempio in PHP.
La sequenza di query deve cominciare con
START TRANSACTION;La transazione è iniziata, ed è possibile indicare la sequenza di queries, che resteranno in stato "pending" fino a quando non eseguo il COMMIT
COMMIT;Fino ad allora, è come se le query non fossero mai state eseguite... o quasi (vedremo a fine articolo che non è proprio sempre così)
Se, durante la sequenza di queries, volessimo annullare le query precedenti, dobbiamo effettuare un ROLLBACK
ROLLBACK;E’ importante sapere che alcuni tipi di queries non possono essere annullate.
In particolare quelle per creare, modificare, svuotare (truncate) o eliminare le tabelle, o che creano o cancellano un database.
Vediamo come utilizzare le TRANSACTIONS In un esempio pratico in PHP.
Le transazioni in Mysql e PHP
Per prima cosa creiamo in file php chiamato "db.php" la connesione al nostro database. Per la connessione utilizziamo l'estensione mysqli con approccio ad oggetti.
<?php
$db ="test";
$db_host = "localhost";
$db_user = "root";
$db_password = "miapassword";
$db_connection = new mysqli($db_host, $db_user, $db_password, $db);
if ($db_connection->connect_error) {
die("Si è verificato il seguente problema tecnico: " . $db_connection->connect_error);
}
?>Creiamo poi un file "index.php", che include il file precedente, ed esegue le query viste prima (quella con l'errore forzato)
<?php
include "db.php";
if (!$db_connection->query("UPDATE `msisdn` SET msisdn_lastabb=NOW() WHERE msisdn_num='347123456789' LIMIT 1")) {
printf("Error message: %s\n", $mysqli->error);
exit;
}
if (!$db_connection->query("INSERT INTO `msisdn_abb` (msisdn_abb_msisdn,msisdn_abb_startX) VALUES ('347123456789',NOW())") {
printf("Error message: %s\n", $mysqli->error);
exit;
}
$db_connection->close();
?>Questo script eseguirebbe la prima query, mentre la seconda andrebbe in errore, con l'avviso "Error message" seguito dalla descrizione dell'errore.
In nostro obiettivo è, in presenza di errori nelle query, effettuare un ROLLBACK su tutte le query effettuate: nel nostro caso significa riportare il campo "msisdn_lastabb" al valore precedente l'UPDATE. Vediamo cose procedere.
La sequenza delle query deve iniziare con START TRANSACTION (utilizzando mysqli va indicato begin_transaction()). Seguono le nostre query
Al termine di tutte le query dobbiamo effettuare il COMMIT, cioè diciamo a MySQL di procedere con l'esecuzione di tutte le query della lista. Senza COMMIT le query non verrano esegute.
Modifichiamo quindi il nostro script così, inserendo per adesso entrambe le query senza errori:
<?php
include "db.php";
/* Inzia la transazione */
$db_connection->begin_transaction();
if (!$db_connection->query("UPDATE `msisdn` SET msisdn_lastabb=NOW() WHERE msisdn_num='347123456789' LIMIT 1")) {
printf("Error message: %s\n", $mysqli->error);
exit;
}
if (!$db_connection->query("INSERT INTO `msisdn_abb` (msisdn_abb_msisdn,msisdn_abb_start) VALUES ('347123456789',NOW())") {
printf("Error message: %s\n", $mysqli->error);
exit;
}
/* Eseguo il Commit */
$db_connection->commit();
/* Chiudo la connessione al database */
$db_connection->close();
?>Prova lo script. Vedrai che le due query verranno eseguite correttamente.
Adesso, rimuovi il commit cioè togli questa parte di codice e riprova lo script
$db_connection->commit();Vedrai che le query non verranno eseguite.
Quindi ricapitolando: iniziamo la sequenza di query con START TRANSACTION, e le terminiamo con COMMIT.
Torniamo al nostro problema originario: se la seconda query fallisse come faccio ad annullare la prima query?
Reinseritamo la seconda query, volutamente errata, nello script, e, poco prima dell'avviso "Error message" facciamo un bel ROLLBACK!
<?php
include "db.php";
/* Inzia la transazione */
$db_connection->begin_transaction();
if (!$db_connection->query("UPDATE `msisdn` SET msisdn_lastabb=NOW() WHERE msisdn_num='347123456789' LIMIT 1")) {
$db_connection->rollback();
printf("Error message: %s\n", $mysqli->error);
exit;
}
if (!$db_connection->query("INSERT INTO `msisdn_abb` (msisdn_abb_msisdn,msisdn_abb_startX) VALUES ('347123456789',NOW())") {
$db_connection->rollback();
printf("Error message: %s\n", $mysqli->error);
exit;
}
/* Eseguo il Commit */
$db_connection->commit();
/* Chiudo la connessione al database */
$db_connection->close();
?>Con l'istruzione ROLLBACK andiamo ad annullare tutte le query indicate tra la START TRANSACTION ed il ROLLBACK: nel nostro esempio significa che la prima query verrà annullata.
Abbiamo visto il caso di un UPDATE seguito da un INSERT.
Adesso vediamo un caso con due INSERT... te lo propongo per farti vedere una cosa interessante.
La tabella "msisdn" ha già un record (il numero caricato il precedenza), con id incrementale 1 (abbiamo un solo record al momento). Per cui al prossimo INSERT in quella tabella mi aspetterei che il nuovo record abbia l'id con valore 2. Giusto? Allora prova questo script dove inseriamo un nuovo numero e una nuova sottoscrizione, quindi avremo due INSERT, ed il rollback sulla seconda query perchè inseriamo il solito errore forzato.
<?php
include "db.php";
/* Inzia la transazione */
$db_connection->begin_transaction();
if (!$db_connection->query("INSERT INTO `msisdn` (msisdn_num) VALUES (347987654321)")) {
$db_connection->rollback();
printf("Error message: %s\n", $mysqli->error);
exit;
}
if (!$db_connection->query("INSERT INTO `msisdn_abb` (msisdn_abb_msisdn,msisdn_abb_startX) VALUES ('347987654321',NOW())") {
$db_connection->rollback();
printf("Error message: %s\n", $mysqli->error);
exit;
}
/* Eseguo il Commit */
$db_connection->commit();
/* Chiudo la connessione al database */
$db_connection->close();
?>Il ROLLBACK sulla seconda query annullerà la prima INSERT nella tabella "msisdn". E questo era già chiaro. Quindi ti aspetteresti che nella prossima INSERT nella tabella "msisdn" venisse assegnato all'ID incrementale il valore 2. Ma non è così! Verrà assegnato il valore 3. Infatti, anche se la query era stata annullata, oramai l'ID era già stato assegnato. Questa è l'unica "pecca" del meccanismo delle transazioni.
Provate da soli un caso con un DELETE e relativo ROLLBACK: l'istruzione DELETE verrà annullata ed il record "ripristinato" (in realtà non è mai stato cancellato perchè la cancellazione avverrebbe, come hai imparato, al COMMIT).
Per concludere ripassiamo quanto abbiamo appreso oggi.
In presenza di una sequenza di query (INSERT, UPDATE, DELETE) relative a record tra loro correlati, se una di queste query fallisse, verrebbe meno l'integrità dei dati lasciando alcuni records non aggiornati. Le transazioni ci consentono di effettuare un rollback ed annullare le query della nostra sequenza.
