Corso MariaDB / Iniziare le transazioni con InnoDB, COMMIT e ROLLBACK

Una transazione riunisce modifiche di dati collegate in un'unica unità da confermare. Con InnoDB, iniziare con START TRANSACTION e terminare con COMMIT se il risultato è corretto oppure ROLLBACK se va annullato; tutte le istruzioni devono usare la stessa connessione e rimanere entro la transazione non ancora confermata.

Definire le modifiche che devono riuscire insieme

L'esercizio modifica due saldi per osservare conferma e annullamento senza lasciare aggiornata soltanto una parte. È una prova di transazioni del database con dati fittizi, non un sistema di pagamento completo. Un sistema reale richiede anche registrazioni, idempotenza, autorizzazioni e audit. La guida ai controlli di UPDATE e DELETE mostra come annullare modifiche a righe selezionate.

Controllo Scopo Limite da ricordare
START TRANSACTION Iniziare l'unità nella connessione corrente Non riavviare una transazione sopra una già attiva
COMMIT Confermare le modifiche Un ROLLBACK successivo non annulla quel commit
ROLLBACK Annullare il lavoro non confermato Non comprende tutte le connessioni né tutte le istruzioni DDL
autocommit Regolare la conferma automatica fuori dalle transazioni esplicite Il solo valore non identifica una transazione attiva

InnoDB è il motore di archiviazione che fornisce il comportamento transazionale delle tabelle dell'esempio. La documentazione delle transazioni MariaDB spiega START TRANSACTION, COMMIT, ROLLBACK e i confini di commit implicito.

Preparare una tabella InnoDB separata per i saldi

Collegarsi come course_user a shopcourse e controllare server e database con SELECT VERSION(), DATABASE();. La guida al client e ai file SQL spiega come eseguire SQL nel client MariaDB. Verificare con SHOW TABLES che accounts_35_lab non esista; se esiste, esaminarne definizione e contenuto prima di decidere se è eliminabile.

Eseguire la preparazione completa seguente prima della prova transazionale. Usare dati che non servono ad applicazioni e non ripetere la creazione della tabella dentro la transazione da osservare.

USE shopcourse;
CREATE TABLE accounts_35_lab (
 account_id INT PRIMARY KEY,balance DECIMAL(12,2) NOT NULL CHECK(balance>=0)
) ENGINE=InnoDB;
INSERT INTO accounts_35_lab VALUES(1,10000),(2,5000);
SELECT @@autocommit,@@in_transaction;
SHOW CREATE TABLE accounts_35_lab;

Verificare che la definizione usi InnoDB e che il saldo iniziale sia 10000.00 per l'ID 1 e 5000.00 per l'ID 2. Eseguire tutta la sequenza su una connessione. Uno strumento che cambia connessione o conferma automaticamente ogni istruzione modifica il significato della procedura.

In una connessione ordinaria senza transazione attiva si possono osservare autocommit=1 e in_transaction=0. Dopo START TRANSACTION, autocommit può rimanere 1 mentre in_transaction diventa 1. Non dedurre quindi l'assenza di una transazione dal solo autocommit. Concludere il lavoro precedente prima di iniziare la prova.

Controllare entrambe le modifiche prima di ROLLBACK

START TRANSACTION;
SELECT account_id,balance FROM accounts_35_lab
WHERE account_id IN (1,2) ORDER BY account_id FOR UPDATE;
UPDATE accounts_35_lab SET balance=balance-3000
WHERE account_id=1 AND balance>=3000;
SELECT ROW_COUNT() AS debit_rows;

Proseguire soltanto se debit_rows vale 1. Se vale zero o si verifica un errore, eseguire ROLLBACK; su questa connessione e non eseguire l'UPDATE di accredito seguente. FOR UPDATE blocca le righe lette durante la transazione; occorre comunque controllare quante righe sono state modificate.

UPDATE accounts_35_lab SET balance=balance+3000 WHERE account_id=2;
SELECT ROW_COUNT() AS credit_rows;

Anche credit_rows deve valere 1. Altrimenti eseguire ROLLBACK; e fermarsi. Una riga destinataria mancante non deve lasciare un addebito senza accredito. Leggere i saldi con il blocco seguente soltanto dopo il successo di entrambi i controlli.

SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

Partendo dai dati iniziali invariati, debit_rows e credit_rows devono essere entrambi 1 e i saldi devono diventare 7000.00 e 8000.00. Se una fase modifica un numero inatteso di righe, annullare la transazione e non continuare le modifiche. Questo SQL è un esercizio sorvegliato: un programma deve implementare esplicitamente i rami di errore e i controlli sulle righe interessate.

ROLLBACK;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

I saldi tornano a 10000.00 e 5000.00. Distinguere le modifiche non confermate visibili alla propria connessione da quelle già confermate. Se i saldi non tornano, verificare identità della connessione, eventuali COMMIT automatici e istruzioni che possono avere concluso la transazione nel mezzo.

Ripetere i controlli e confermare con COMMIT

Ripetere nell'ordine i blocchi di addebito, accredito e lettura dei saldi, controllando ogni volta debit_rows e credit_rows. Scegliere questa volta il COMMIT seguente al posto del blocco ROLLBACK soltanto quando conteggi e saldi sono corretti. Il percorso di errore non deve proseguire fino a COMMIT.

COMMIT;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

I saldi confermati sono 7000.00 e 8000.00. Un normale ROLLBACK successivo non ripristina i valori iniziali. Per ripetere l'esercizio, preparare nuovamente i dati in modo intenzionale dopo aver verificato che gli oggetti siano eliminabili. Applicare una variazione opposta a dati reali non equivale automaticamente a ripristinare tutti gli effetti della transazione.

Considerare errori, blocchi e commit impliciti

L'errore di una singola istruzione non annulla sempre l'intera transazione. Per esempio, una violazione CHECK può annullare l'istruzione fallita lasciando ancora nella transazione le modifiche precedenti. Gestire esplicitamente il tipo di errore e il comportamento del driver, impedendo che il ramo di errore raggiunga COMMIT.

Mantenere i blocchi per il minor tempo possibile. Nella prova manuale concludere subito il confronto e il ROLLBACK; nel programma evitare attese di input dell'utente mentre si possiedono blocchi. Avviare START TRANSACTION quando una transazione è già attiva può confermare il lavoro precedente: il comando di avvio non sostituisce l'annullamento.

Le istruzioni di definizione dei dati, o DDL, come CREATE, ALTER, DROP e TRUNCATE presentano commit impliciti o confini particolari. I motori non transazionali non offrono lo stesso comportamento di annullamento di InnoDB. Anche una chiamata a un'API esterna o il trasferimento di un file non vengono annullati da un semplice ROLLBACK del database.

Concludere il lavoro aperto prima di azzerare l'esercizio

Se l'esecuzione si interrompe a metà, impartire ROLLBACK nella connessione interessata e controllare @@in_transaction. Accertarsi che la tabella non contenga dati da conservare prima di eliminarla per una nuova prova. Mantenere la pulizia della struttura separata dall'esperimento di annullamento dei dati.

ROLLBACK;
DROP TABLE shopcourse.accounts_35_lab;