Una delle classiche problematiche che un DBMS deve gestire è l'accesso
simultaneo ai dati da parte di diversi utenti, sia in lettura che in
aggiornamento.
Una situazione tipica di questo genere è il caso in cui, ad esempio, due
utenti leggono lo stesso dato con l'intenzione di aggiornarlo:
evidentemente uno dei due lo farà per primo, e a quel punto il secondo
utente, quando tenterà a sua volta un aggiornamento, troverà una
situazione variata rispetto al momento in cui aveva letto i dati, col
rischio di creare situazioni incongruenti.
Un'altra classica situazione che pone dei problemi è quella in cui
un'applicazione deve effettuare più aggiornamenti logicamente collegati
fra loro, tanto da richiedere che tutti gli aggiornamenti siano annullati
qualora uno solo di essi dovesse fallire.
Le soluzioni per questi problemi sono, nella forma più semplice, i lock
sulle tabelle, e in quella più avanzata le transazioni. Queste ultime sono disponibili su tabelle InnoDB - lo Storage Engine di default a partire dalla versione 5.5 del DBMS - oltre che su NDB, un meccanismo dedicato ai Cluster che vedremo in seguito. Al contrario, non possono essere utilizzate su tabelle MyISAM.
Cominciamo con l'analisi dei lock, che possiamo
considerare dei vincoli di "uso esclusivo" che un utente può ottenere su
determinate tabelle per il tempo necessario a svolgere le operazioni che
gli sono necessarie. Con i lock si possono simulare (parzialmente)
transazioni, o in alcuni casi semplicemente velocizzare le operazioni di
scrittura, qualora vi siano grosse moli di dati da inserire. L'uso dei
lock è consigliato solo con le tabelle di tipo MyISAM, che non supportano
le transazioni.
Un lock può essere richiesto in lettura o in scrittura:
nel primo caso l'utente ha la garanzia che nessuno farà aggiornamenti
sulla tabella bloccata fino a quando non sarà rilasciato il lock, ma agli
altri utenti viene comunque lasciata la possibilità di leggere sulla
stessa tabella. In questo caso però nemmeno l'utente che ha ottenuto il
lock può fare aggiornamenti.
Il lock in scrittura invece impedisce agli altri utenti qualsiasi tipo di
accesso alla tabella, e consente all'utente che l'ha ottenuto operazioni
di lettura e scrittura.
Vediamo la sintassi delle operazioni di lock, ricordando che esse
richiedono il privilegio LOCK TABLES nonché quello di SELECT sulle
tabelle interessate:
LOCK TABLES
tabella [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tabella [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
Innanzitutto notiamo che è possibile effettuare il lock su più tabelle
con un'unica istruzione LOCK TABLES; in realtà, più che di una
possibilità si tratta di un obbligo.
Infatti ogni istruzione LOCK TABLES causa il rilascio dei lock ottenuti
in precedenza: di conseguenza, se avete bisogno di ottenere lock su più
tabelle, siete obbligati a farlo con un'unica istruzione.
Ad ogni tabella di cui chiediamo il lock è possibile associare un alias,
esattamente come nelle query: anche in questo caso siamo vincolati ad
usare questo sistema qualora le query che ci accingiamo ad effettuare
utilizzino gli alias. In pratica, dopo avere ottenuto un lock, le nostre
query possono utilizzare solo le tabelle su cui abbiamo
i lock: non è possibile quindi, in presenza di lock attivi, accedere ad
altre tabelle; inoltre, a queste tabelle dovremo accedere utilizzando
gli stessi alias definiti in fase di lock. Qualora una
tabella sia presente più volte in una query, avremo evidentemente bisogno
di più di un alias: di conseguenza dovremo ottenere un lock per
ogni alias, sebbene la tabella sia la stessa.
La clausola LOCAL associata ad un READ lock consente ad altri utenti di
effettuare inserimenti che non vadano in conflitto con le nostre letture.
La clausola LOW_PRIORITY associata ad un WRITE lock fa sì che la
richiesta dia la precedenza alle richieste di lock in lettura
(normalmente invece un lock in scrittura ha priorità più alta).
I lock ottenuti vengono rilasciati con l'istruzione:
UNLOCK TABLES
In realtà abbiamo già visto che anche una nuova richiesta di lock causa
il rilascio dei precedenti; inoltre i lock vengono rilasciati
automaticamente alla chiusura della connessione, qualora non sia stato
fatto esplicitamente.
Le transazioni
Passiamo ora alle transazioni, con particolare riferimento alle tabelle
InnoDB.
L'uso delle transazioni permette di "consolidare" le modifiche alla base
dati solo in un momento ben preciso: dal momento in cui avviamo una
transazione, gli aggiornamenti rimangono sospesi (e invisibili ad altri
utenti) fino a quando non li confermiamo (commit); in
alternativa alla conferma è possibile annullarli
(rollback).
Innanzitutto va segnalato che MySQL gira di default in autocommit
mode: questo significa che tutti gli aggiornamenti vengono
automaticamente consolidati nel momento in cui sono eseguiti. Se siamo in
autocommit, per iniziare una transazione dobbiamo usare l'istruzione
START TRANSACTION; da questo punto in poi tutti gli
aggiornamenti rimarranno sospesi. La transazione può essere chiusa con
l'istruzione COMMIT, che consolida le modifiche, oppure
con ROLLBACK, che annulla tutti gli aggiornamenti
effettuati nel corso della transazione. Possiamo utilizzare anche
COMMIT AND CHAIN o ROLLBACK AND CHAIN,
che provocano l'immediata apertura di una nuova transazione, oppure
COMMIT RELEASE o ROLLBACK RELEASE, che
oltre a chiudere la transazione chiudono anche la connessione al server.
Con l'istruzione SET AUTOCOMMIT=0 possiamo disattivare
l'autocommit: in questo caso non è più necessario avviare le transazioni
con START TRANSACTION, e tutti gli aggiornamenti rimarranno sospesi fino
all'uso di COMMIT o ROLLBACK.
All'interno di una transazione è anche possibile stabilire dei
savepoint, cioè degli stati intermedi ai quali possiamo
ritornare con una ROLLBACK, invece di annullare interamente la
transazione.
Vediamo un esempio:
START TRANSACTION
...istruzioni di aggiornamento (1)...
SAVEPOINT sp1;
...istruzioni di aggiornamento (2)...
ROLLBACK TO SAVEPOINT sp1;
...istruzioni di aggiornamento (3)...
COMMIT
In questo caso, dopo avere avviato la transazione abbiamo eseguito un
primo blocco di aggiornamenti, seguito dalla creazione del savepoint col
nome 'sp1'; in seguito abbiamo eseguito un secondo blocco di
aggiornamenti; l'istruzione ROLLBACK TO SAVEPOINT sp1 fa sì che
"ritorniamo" alla situazione esistente quando abbiamo creato il
savepoint: in pratica solo il secondo blocco di aggiornamenti viene
annullato, e la transazione rimane aperta; una semplice ROLLBACK invece
avrebbe annullato tutto e chiuso la transazione.
La COMMIT effettuata dopo il terzo blocco fa sì che vengano consolidati
gli aggiornamenti effettuati nel primo e nel terzo blocco.
È bene ricordare che un utilizzo corretto delle transazioni è possibile
solo utilizzando lo stesso tipo di tabelle all'interno di ogni
transazione. È altamente
sconsigliato ovviamente l'utilizzo di tabelle MyISAM nelle transazioni,
in quanto su di esse non è possibile effettuare il ROLLBACK e gli
aggiornamenti relativi sono immediatamente effettivi: in caso di ROLLBACK
quindi si genererebbero proprio quelle inconsistenze che l'uso delle
transazioni mira ad evitare.
Ricordiamo anche che alcuni tipi di operazioni non sono annullabili: in
generale tutte quelle che creano, eliminano, o alterano la struttura di
database e tabelle. È bene quindi evitare di includere in una transazione
tali operazioni, che fra l'altro, nella maggior parte dei casi, causano
una implicita COMMIT.
In alcuni casi è utile utilizzare due clausole particolari quando si
effettua una SELECT:
SELECT .......... FOR UPDATE;
SELECT .......... LOCK IN SHARE MODE;
La clausola FOR UPDATE stabilisce un lock su tutte le righe lette che
impedirà ad altri utenti di leggere le stesse righe fino al termine della
nostra transazione; evidentemente si utilizza quando leggiamo un dato con
l'intenzione di aggiornarlo. La clausola LOCK IN SHARE MODE invece
stabilisce un lock che impedisce solo gli aggiornamenti, garantendoci che
il contenuto della riga rimarrà invariato per la durata della nostra
transazione.
Isolation level
Un aspetto importante relativamente alle transazioni è il livello di
isolamento al quale vengono effettuate. I livelli possibili sono quattro,
e li elenchiamo in ordine crescente:
-
READ UNCOMMITTED: a questo livello sono visibili gli
aggiornamenti effettuati da altri utenti anche se non
consolidati: è un comportamento non propriamente
transazionale, che può dare ovviamente seri problemi di consistenza dei
dati; va utilizzato solo quando non abbiamo preoccupazioni di questo
tipo e abbiamo bisogno di velocizzare le letture -
READ COMMITTED: a questo livello gli aggiornamenti
diventano visibili solo dopo il consolidamento -
REPETEABLE READ: in questo caso perchè un
aggiornamento diventi visibile deve essere non solo consolidato, ma
anche la transazione che legge deve essere terminata; in pratica, la
stessa lettura ripetuta all'interno di una transazione darà sempre lo
stesso risultato; è la modalità di default -
SERIALIZABLE: come nel caso precedente, ma in più, la
semplice lettura di un dato provoca il blocco degli aggiornamenti fino
al termine della transazione; in sostanza è come se ogni SELECT venisse
effettuata con la clausola LOCK IN SHARE MODE
Il livello di isolamento utilizzato può essere determinato dall'opzione
di avvio del server --transaction-isolation (vedere lez. 4, facendo
attenzione alla diversa sintassi delle opzioni); per sapere qual è il
livello in uso possiamo usare l'istruzione SELECT
@@tx_isolation; inoltre possiamo modificarlo con la seguente
istruzione:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
Se omettiamo le clausole GLOBAL e SESSION la modifica è valida solo per
la transazione successiva; con SESSION impostiamo il valore per l'intera
connessione, mentre con GLOBAL modifichiamo il valore per il server: tale
valore verrà quindi adottato su tutte le connessioni aperte
successivamente (non su quelle già aperte); in
quest'ultimo caso è necessario il privilegio SUPER.
Per concludere, abbiamo già detto che l'uso di LOCK TABLES è consigliato
solo su storage engine non transazionali. Ricordiamo anche che se lo
usiamo su storage engine transazionali un'istruzione LOCK TABLES causerà
una implicita COMMIT di una eventuale transazione aperta. All'opposto,
avviare una transazione provoca l'implicita esecuzione di una UNLOCK
TABLES.