Gestire le transazioni in MySQL

Nella precedente lezione abbiamo visto come gestire operazioni concorrenti e simultanee attraverso la definizione di vincoli (lock) e come è possibile impostarne uno a livello di tabella mediante il comando LOCK TABLES. In questa lezione vedremo, invece, come far fronte al medesimo problema sfruttando la potenza delle transazioni all’interno del nostro DBMS MySQL. Si vuole sottolinare che quanto esposto nella presente lezione farà particolare riferimento alle tabelle InnoDB, essendo vivamente sconsigliato utilizzare le transazioni con le tabelle MyISAM.

Cosa sono le transazioni?

Le transazioni (o transaction) sono uno strumento grazie al quale è possibile far fronte alla problematica della concorrenza di operazioni all’interno di un database.

Volendo semplificare possiamo dire che le transazioni sono delle macro-operazioni che racchiudono, al loro interno, una serie di sub-operazioni le quali possono raggiungere il successo o il fallimento solo restando unite. Se una sola delle operazioni previste all’interno della transaction fallisce, allora fallisce l’intera transazione generando un rollback che ripristina la situazione al momento immediatamente antecedente all’inizio delle operazioni.

Questa caratteristica è detta atomicità ed è uno dei quattro elementi tipici delle transazioni:

Atomicità: le transazioni sono operazioni indivisibili che riescono o falliscono globalmente, nel loro insieme. La transaction può avere solo due risultati finali:
- Aborted: le operazioni sono fallite e, pertanto, è come se non fossero mai state eseguite non comportando alcun cambiamento nella base dati;
- Committed: le operazioni hanno avuto successo e producono i loro effetti sulla base dati.
Consistenza: le tabelle del database si trovano in uno stato consistente sia prima che dopo la transazione, la quale non non produce nessun effetto che vada a violare i vincoli di integrità.
Isolamento: le diverse transazioni avvengono separatamente tra loro. InnoDB, infatti, adopera un vincolo a livello di singola riga, in modo che i dati non siano modificati fino a quando l’operazione non è completa.
Durabilità: al termine della transazione (quando tutte le operazioni in essa contenute hanno raggiunto il successo) i dati vengono memorizzati in modo permanente nella base dati. Questo perché una volta decretato il successo viene fatto un side-effect fisico sulla base di dati, la quale aggiorna i valori perdendo quelli precedenti.

Rollback e Commit

Nel momento in cui avviamo una transazione in MySQL, le modifiche effettuate dalle singole operazioni in essa contenute restano "sospese" ed "invisibili" agli utilizzatori del database, fino a quando non vengono confermate mediante un commit. Viceversa, tali mutamenti possono essere annullati del tutto mediante un rollback il quale, come già detto, ripristina la situazione immediatamente antecedente all’inizio della transaction.

E’ importante precisare che MySQL gira, di default, in modalità autocommit: questo significa che i cambiamenti vengono applicati immediatamente all’atto dell’esecuzione del comando senza che sia necessario avanzare un’esplicita richiesta di commit.

Per "bloccare" questo modus operandi di MySQL sarà, pertanto, necessario avviare esplicitamente una transazione con START TRANSACTION: a questo punto tutte gli effetti delle operazioni di modifica sui dati resteranno sospesi in attesa di COMMIT o ROLLBACK i quali, entrambi, chiudono la transazione.

START TRANSACTION;
...
...
...
COMMIT;

In alternativa all’uso di START TRANSACTION è possibile disattivare l’autocommit in questo modo:

SET autocommit = 0;

In questo caso tutte le operazioni resteranno sospese in attesa di un esplicito comando di COMMIT o ROLLBACK.

I savepoint

Come dice il nome stesso, i savepoint sono dei "punti di salvataggio" (definiti arbitrariamente dal programmatore), cioè una sorta di stati intermedi ai quali è possibile ritornare selettivamente mediante un ROLLBACK: in questo caso, infatti, il comando di rollback non viene utilizzato per annullare tutte le modifiche ma per tornare ad un punto ben preciso del processo, salvaguardando le modifiche precedenti a quest’ultimo ed annullando solo quelle ad esso successive. Le operazioni della transazione che precedono i savepoint, pertanto, comporteranno un aggiornamento del database anche se la transazione fallisce.

Vediamo di seguito come creare dei savepoint e come effettuare rollback selettivi:

START TRANSACTION;

...primo blocco di istruzioni...

SAVEPOINT p1;

...secondo blocco di istruzioni...

ROLLBACK TO SAVEPOINT p1;

...terzo blocco di istruzioni...

COMMIT;

Nel nostro esempio viene creato un savepoint dopo un primo blocco di istruzioni, il secondo blocco, invece, è seguito da un rollback che lo annulla riportando la situazione al savepoint "p1". Infine abbiamo un terzo blocco di istruzioni seguito da un COMMIT. In pratica le modifiche apportate dal primo e terzo blocco saranno effettive, mentre quelle del secondo blocco no.

N.B.: come detto all’inizio di questa lezione, è vivamente sconsigliato utilizzare le transaction con tabelle di tipo MyISAM essendo queste ultime prive del supporto ai rollback operando sempre i autocommit!

Impostare lock a livello di riga

Come detto, InnoDB consente – all’interno di una transazione – di impostare dei lock a livello di riga (e non solo, come nel caso di MyISAM, a livello di tabella). Per farlo, all’inizio della transaction, si deve acquisire il lock mediante una SELECT sui record interessati seguita dalle seguenti clausole:

SELECT … FOR UPDATE – Questo comando imposta un lock su tutti i record selezionati segnalando al DBMS l’evidente intenzione di procedere ad un loro aggiornamento; gli altri utenti non potranno leggere le righe coinvolte.
SELECT … LOCK IN SHARE MODE – In questo caso viene impostato un lock sulle righe coinvolte nella selezione che consente anche ad altri utenti di leggerne il contenuto ma gli impedisce di modificarlo rino a quando non si verificherà il commit, garantendo all’utente che beneficia del lock che i valori non verranno alterati nel corso della transazione.

Vediamo un paio di esempi:

SELECT * FROM utenti WHERE id = 123 LOCK IN SHARE MODE;

In questo caso anche altre transazioni potranno accedere in lettura alla riga identificata dall’id 123, ma nessuna di queste potrà modificarne i valori, almeno sino a quando la transazione che detiene il lock non sia terminata.

SELECT * FROM utenti WHERE id = 123 FOR UPDATE;

In questo caso, invece, le altretransazioni non potranno nemmeno leggere i dati del record con id 123 in quanto "saranno presto aggiornati": sino a quando la transazione che detiene il lock non termina (con un commit o un rollback) i dati di quel record non saranno accessibili nemmeno in sola lettura.

Livello di isolamento

Un aspetto importante delle transaction è il cosidetto isolation level (o livello di isolamento) al quale esse vengono effettuate. Esistono quattro diversi livelli possibili:

READ UNCOMMITTED – Una transazione può vedere modifiche alle righe fatte da un’altra transazione anche se non è ancora intervenuto il commit. Attenzione: questo è un comportamento non propriamente transazionale e va utilizzato con cautela valutandone gli eventuali rischi.
READ COMMITTED – Una transazione può vedere modifiche alle righe fatte da altre transazioni solo se il commit di queste è avvenuto prima che la transazione iniziasse.
REPEATABLE READ – Se una transazione esegue un’istruzione di selezione più volte, viene restituito sempre lo stesso risultato, anche se nel mentre la tabella è stata modificata da un altra transazione. Questo è il livello di default.
SERIALIZABLE – Questo livello di isolamento è simile al precedente ma isola le transazioni. I record che vengono visti da una transazione non sono modificabili da un’altra transazione fino a quando la prima transazione non è completa. In pratica viene simulato il comportamento già visto con SELECT … LOCK IN SHARE MODE.

Per conoscere il livello di isolamento attualemnte in uso possiamo eseguire questo comando:

SELECT @@tx_isolation;

Per modificare il livello di isolamento possiamo agire a livello di configurazione del nostro DBMS MySQL, in alternativa possiamo eseguire tramite la console il comando SET TRANSACTION ISOLATION LEVEL seguito dal nome del livello da impostare:

SET TRANSACTION ISOLATION LEVEL nuovo_livello

Questo comando imposta il livello di isolamento solo per la transazione successiva, tuttavia:

se aggiungiamo SESSION dopo l’istruzione SET il livello di isolamento resterà settato sino al termine della connessione corrente;
se aggiungiamo GLOBAL dopo l’istruzione SET il livello di isolamento riguarderà il server e verrà applicato a tutte le connessioni successive.