MySQL – Le transazioni

Una transazione in un database è una sequenza di queries eseguite durante una singola sessione.
Ad esempio, si potrebbe inserire un record in una tabella, inserire un altro record in un’altra tabella, ed eseguire un aggiornamento su un record ancora diverso.
Senza usare le transazioni, ogni singola query ha effetto immediato e non può essere annullata.
Con le transazioni, è possibile impostare i punti di inizio e fine e quindi eseguire (commit) o annullare (rollback) tutte le queries in base alle esigenze, (per esempio, se una query non riesce, possimoa annullarle tutte).

Le interazioni commerciali comunemente richiedono l’utilizzo delle transazioni, anche qualcosa di base come il trasferimento di 100 euro da un conto ad un’altro è in realtà un processo con diverse fasi:

• Confermare che l’utente A abbia 100 € sul suo conto.
• Diminuire il conto 100 €.
• Aumentare la quantità di denaro sul conto dell’utente B di 100 €.
• Verificare che l’aumento ha funzionato.

Se uno dei passaggi fallisse, si vorrebbe annullarti tutti. Per esempio, se i soldi non potessero essere depositati sul conto dell’utente B, dovrebbero essere restituiti nel all’utente A, fino a quando l’intera transazione sia eseguita nel suo complesso.
Per eseguire le transazioni con MySQL, è necessario utilizzare il tipo di tabella InnoDB (o storage engine). Per iniziare una nuova transazione nel client MySQL, scrivere:

START TRANSACTION;

Una volta che la transazione è iniziata, è possibile eseguire le queries.
Quando terminato, possiamo inserire COMMIT per eseguire tutte le queries o ROLLBACK per annullarle tutte.
Dopo aver eseguito il COMMIT o il ROLLBAK delle queries, la transazione è considerata completata, e MySQL ritorna nella modalità autocommit.
Questo significa che ogni query ora sarà eseguita con effetto immediato. Per iniziare un’altra transazione, basta digitare nuovamente START TRANSACTION.
E’ importante sapere che alcuni tipi di queries non possono essere ripristinate.
In particolare quelle che creano, modificano, troncano (vuotano), o eliminano le tabelle o creare o cancellano un database non possono essere annullate.
Inoltre, utilizzando una di queste queries si avrà l’effetto di commettere e terminare la transazione corrente.
Infine, è necessario comprendere che le transazioni sono particolari per ogni connessione. Così un utente connesso tramite il client MySQL ha una transazione diversa da un altro utente del client MySQL, che sono entrambi diversi da uno script PHP attivo.

Esempio Pratico

Dopo la teoria, finalmente un po’ di pratica, colleghiamoci al nostro client MySQL e creiamo una nuova tabella (senza curarci troppo del processo di normalizzazione, dopotutto è un esempio quindi cerchiamo di rendere le cose semplici):

CREATE TABLE accounts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
fullName VARCHAR(40) NOT NULL,
saldo DECIMAL(10,2) NOT NULL DEFAULT 0.0,
PRIMARY KEY (id)
) ENGINE=InnoDB;

L’aspetto più importante della definizione della tabella è il suo motore: InnoDB.
Come detto in questo articolo per le installazioni Windows InnoDb è lo storage engine predefinito altrimenti è MyISAM.
Popoliamo la tabella:

INSERT INTO accounts (fullName, saldo)
VALUES ('Mario Rossi', 23450.65),
('Carlo Bianchi', 435010.43),
('Luca Verdi', 930.00);

La cosa importante da notare è che MySQL automaticamente esegue questa query, in quanto nessuna transazione è già iniziata.
Iniziamo ora una transazione e mostriamo il contenuto corrente della tabella:

START TRANSACTION;
SELECT * FROM accounts;

Sottraiamo 100 € dal conto di Carlo Bianchi (o qualsiasi altro utente):

UPDATE accounts
SET saldo = (saldo-100)
WHERE id=2;

Sebbene MySQL indicherà che una riga è stata colpita, l’effetto non è permanente fino a quando la transazione non è stata completata.
Aggiungiamo 100 € al conto di Mario Rossi.

UPDATE accounts
SET saldo = (saldo+100)
WHERE id=1;

Questo è l’opposto dello step precedente, in pratica abbiamo spostato 100 € da una persona ad un’altra.
Controlliamo il risultato:

SELECT * FROM accounts;

Come possiamo notare le modifiche sono state apportate correttamente.
Eseguiamo il roll back della transazione (e ricontrolliamo):

ROLLBACK;
SELECT * FROM accounts;

Il comando ROLLBACK ritorna il database nello stato in cui era prima di iniziare la transazione.
Il comando termina anche la transazione, tornando MySQL alla sua modalità autocommit.
Rieseguiamo i passaggi precedenti, ma questa volta confermiamo la transazione. Assicuriamoci prima di avviare di nuovo la transazione oppure le queries saranno eseguite immediatamente.
Eseguiamo il COMMIT della transazione e confermiamo i risultati:

COMMIT;
SELECT * FROM accounts;

Questa volta, l’intera transazione è permanente, nel senso che le modifiche sono ormai in atto. COMMIT conclude anche la transazione, tornando alla modalità autocommit di MySQL.

Una delle grandi caratteristiche delle transazioni è che offrono protezione da un evento casuale che si verifica, ad esempio un crash del server.
O una transazione viene eseguita nella sua interezza o tutte le modifiche vengono ignorate.

Tips

Per modificare la natura del MySQL autocommit:

SET AUTOCOMMIT=0;

In questo caso, non c’è bisogno di digitare START TRANSACTION e nessuna query sarà permanente fino a quando la transazione non sarà conclusa (o utilizziamo una query ALTER, CREATE, etc,).

Per creare dei savepoints nelle transazioni:

SAVEPOINT savepoint_name;

Poi potremmo eseguire il roll back a quel punto:

ROLLBACK TO SAVEPOINT savepoint_name;