Transazioni

Le transazioni assicurano che le query che modificano valori in relazione tra loro ma appartenenti a più di una tabella del data base siano eseguite come se si trattasse di un'unica query. Ciò che si desidera, in particolare, è che se una query termina in errore allora tutte le tabelle coinvolte nell'operazione vengano ripristinate allo stato antecedente l'inizio della transazione.

L'operazione di annullare la modifica alle tabelle è detta Roll-Back.

Si può richiamare il Roll-Back di una transazione quando si trovano errori oppure in seguito a un'istruzione.

Le transazioni, usate in tabelle InnoDB hanno le seguenti proprietà:

• Atomicità. Le transazioni sono operazioni indivisibili che riescono o falliscono nel loro insieme.
• Consistenza. Le tabelle del data base si trovano in uno stato consistente sia prima che dopo le transazioni.
• Isolamento. Le transazioni avvengono separatamente tra loro. InnoDB usa un bloccaggio a livello di record, in modo che i dati non siano modificati fino a quando l'operazione non è completa.
• Durabilità. Al termine completo della transazione i dati vengono memorizzati permanentemente nel data base.

I Savepoint nelle transazioni, sono posizioni a cui il Roll-Back si arresterà. I Savepoint sono collocati a discrezione del programmatore. Le operazioni della transazione che precedono i savepoint comporteranno un aggiornamento del database anche se la transazione fallisce.

InnoDB offre 4 livelli di isolamento:

1. Read Uncommitted: Una transazione può vedere modifiche alle righe fatte da un'altra transazione persino prima che sia avvenuto il commit.
2. Read Committed: Una transazione può vedere modifiche alle righe fatte da altre transazioni solo dopo che il commit di queste è avvenuto prima che la transazione iniziasse.
3. Repeatable Read: Se una transazione esegue un'istruzione di selezione due volte, viene restituito lo stesso risultato in entrambi i casi, anche se la tabella è stata modificata.
4. Questo livello di isolamento è simile a Repeatable Read ma isola le transazioni. Le righe che devono essere viste da una transazione non sono visibili da un'altra transazione fino a quando la transazione è completa.

L'isolamento di default di InnoDB ` il numero 3: Repeatable Read. Il livello di isolamento può essere impostato anche in fase di esecuzione con l'istruzione SET TRANSACTION.

Le transazioni sono iniziate con SET TRANSACTION e terminano con un'istruzione COMMIT o con un'istruzione ROLL-BACK.
COMMIT conferma l'esecuzione delle istruzioni SQL, l'istruzione ROLL-BACK le annulla.

Come esempio di transazioni creiamo un'applicazione di negozio online. Le tabelle coinvolte riguardano la vendita, il carrello e l'inventario e saranno modificate a seconda dell'esito del controllo della carta di credito del cliente.

Se l'acquisto è approvato la vendita viene confermata, l'ordine verrà preso in considerazione e l'articolo verrà sottratto dal carico di magazzino. Se la carta di credito non è riconosciuta la vendita viene annullata e alle tabelle si applicherà il Roll-Back per riportarle al loro stato precedente.

Creazione del database e apertura del database.

    mysql> create database carrello;
    Query OK, 1 row affected (0.05 sec)
    mysql> use carrello;
    Database changed

Creazione della tabella vendite, contenente i dati del cliente e dell'articolo acquistato.

    mysql> create table vendite (
       -> nome varchar(25),
       -> IDutente mediumint(9) not null,
       -> IDprod int (11),
       -> indirizzo varchar(250) not null,
       -> dataOrdine date not null,
       -> nomeProd varchar(50),
       -> prezzo decimal(6,2) not null
       -> );
    Query OK, 0 rows affected (0.23 sec)

Creazione della tabella magazzino contenente gli articoli disponibili

    mysql> create table magazzino (
        -> IDprod int(11) not null,
        -> qtaScorta int(11) not null,
        -> nomeProd varchar(50) not null
        -> );
    Query OK, 0 rows affected (0.08 sec)

Creazione della tabella spedizione

    mysql> create table spedizione (
        -> IDprod int(11) not null,
        -> nome varchar(25),
        -> indirizzo varchar(250),
        -> dataAcquisto date not null,
        -> dataSpedizione date not null
        -> );
Query OK, 0 rows affected (0.08 sec)

Inserire almeno un prodotto di magazzino nella tabella magazzino:

    mysql> insert into magazzino 
        -> (IDprod, qtaScorta, nomeProd)
        -> values ('1234', '250', 'detersivo');
Query OK, 1 row affected (0.08 sec)

Le tabelle Vendite e Spedizione sono vuote

Creare un utente e concedergli i permessi di accesso lal database:

    mysql> grant all on carrello.* to dondiego@localhost 
         -> identified by 'zorro';
    Query OK, 0 rows affected (0.25 sec)

Le transazioni non rispondono agli eventi, cioè il Roll Back o il Commit non può essere introdotto da linea di commando per completare la transazione.

Per usare efficacemente le transazioni, quindi, bisogna inserirle in un programma. Nell'esempio seguente la transazione o viene approvata o viene annullata a seconda del valore della variabile $accettata che indica se la carta di credito, o la forma di pagamento scelta, è riconosciuta.